MCS – Clients, Suppliers, And Employees Management System

· Entity Relationship Diagram (ERD): –

Relational Schema: –

Employee

Employee_ID (PK)

Name

Address

Phone

Start_Date

Hourly_Rate

Contact_Detail

Contact_No (PK)

Contact_Type

Contact

Address

Zip_Code (PK)

Address_Type

Address

Business_Account

Account_No (PK)

Comman_Name

Tax

ABN_No

Status

BA_Contact

Account_No (PK,FK)

Contact_No (PK,FK)

BA_Address

Account_No(PK,FK)

Address_No (PK,FK)

Contact_People

Contact_People_ID (PK)

Account_No (FK)

Name

Address

Phone

Contact_People_Contact

Contact_People_ID (PK,FK)

Conatct_No (PK,FK)

Contact_People_Address

Contact_People_ID (PK,FK)

Address_No (PK,FK)

Supplier

Supplier_ID (PK)

Name

Address

Phone

Supplier_Account

Account_No(PK,FK)

Supplier_ID (FK)

Part

Part_ID (PK)

Category

Manufacture

Description

Status

Supply

Part_Number (PK)

Supplier_ID (FK)

Part_ID (FK)

Sell_Price

Sell_Tax

Date

Status

Client

Client_ID (PK)

Name

NameOfInvoice

Commenec_Date

Invoice

Invoice_No(PK)

Client_ID (FK)

Date

Due_Date

Total_Amount

Tax

Status

Paid_Date

Jobs

Job_Number (PK)

Client_ID (FK)

Employee_ID (FK)

Invoice_No (FK)

Open_Date

Work

Job_Status

Job_Invoice_Fee

Job_Invoive_Fee_No (PK)

Invoice_No(FK)

Job_Number (FK)

Job_Fee

Tax

Invoice_PaymentDate

Sale

Sale_No (PK)

Transaction_ID (FK)

Client_Job_No(FK)

Sell_Price

Tax

Freight_Cost

Client_Account

Account_No(PK,FK)

Client_No(FK)

Supplementary Design requirements: –

Entity Name	Attribute	Data type	Size	Not null/Null	Keys	Relationship Table
Employee	Employee_ID	int	–	Not null	PK	–
Name	varchar	50	Not null	–	–
Address	varchar	150	Not null	–	–
Phone	varchar	12	Not null	–	–
Email	varchar	50	Not null	–	–
Strat_Date	date	–	Not null	–	–
Hourly_Rate	decimal	(10,2)	Not null	–	–
Contact_Detail	Contact_No	int	–	Not null	PK	–
Contact_Type	varchar	50	Not null	–	–
Contact	varchar	12	Not null	–	–
Address	Address_No	int	–	Not null	PK	–
Address_Type	Varchar	50	Not null	–	–
Addess	Varchar	200	Not null	–	–
Business_Account	Account_No	int	–	Not null	PK	–
Comman_Name	varchar	100	Not null	–	–
Tax	Decimal	(10,2)	Not null	–	–
ABN_No	int	–	Not null	–	–
Status	varchar	50	Not null	–	–
BA_Contact	Account_No	int	–	Not null	PK,FK	Business_Account
Contact_No	int	–	Not null	PK,FK	Contact_Detail
BA_Address	Account_No	int	–	Not null	PK,FK	Business_Account
Address_No	int	–	Not null	PK,FK	Address
Contact_People	Contact_People_ID	int	–	Not null	PK	–
Account_No	int	–	Not null	FK	Business_Account
Name	Varchar	50	Not null	–	–
Address	varchar	200	Not null	–	–
Phone	Varchar	12	Not null	–	–
Email	Varchar	50	Not null	–	–
Contact_People_Contact	Contact_People_ID	int	–	Not null	PK,FK	Contact_People
Contact_No	int	–	Not null	PK,FK	Contact_Detail
Contact_People_Address	Contact_People_ID	int	–	Not null	PK,FK	Contact_People
Address_No	Int	–	Not null	PK,FK	Address
Supplier	Supplier_ID	int	–	Not null	PK	–
Name	Varchar	50	Not null	–	–
Address	Varchar	200	Not null	–	–
Phone	Varchar	12	Not null	–	–
Email	Varchar	50	Not null	–	–
Supplier_Account	Account_No	int	–	Not null	PK,FK	Business_Account
Supplier_ID	int	–	Not null	PK,FK	Supplier
Part	Part_ID	int	–	Not null	PK	–
Category	varchar	50	Not null	–	–
Manufacture	Varchar	100	Not null	–	–
Description	Long	–	Not null	–	–
Status	varchar	20	Not null	–	–
Supply	Part_Number	Int	–	Not null	PK	–
Supplier_ID	int	–	Not null	FK	Supplier
Part_ID	int	–	Not null	FK	Part
Sell_Price	Decimal	(10,2)	Not null	–	–
Sell_Tax	Decimal	(10,2)	Not null	–	–
Date	date	–	Not null	–	–
Status	varchar	30	Not null	–	–
Client	Client_ID	int	–	Not null	PK	–
Name	Varchar	50	Not null	–	–
NameOfInvoice	varchar	100	Not null	–	–
Commence_Date	Date	–	Not null	–	–
Invoice	Invoice_No	int	–	Not null	PK	–
Client_ID	int	–	Not null	FK	Client
Date	date	–	Not null	–	–
Due_Date	Date	–	Not null	–	–
Total_Amount	Decimal	(10,2)	Null	–	–
Tax	Decimal	(10,2)	Null	–	–
Status	varchar	50	Not null	–	–
Paid_Date	date	–	Not null	–	–
Jobs	Job_ID	int	–	Not null	PK	–
Client_ID	int	–	Not null	FK	Client
Employee_ID	int	–	Not null	FK	Employee
Open_Date	date	–	Not null	–	–
Work	Varchar	100	Not null	–	–
Job_Status	varchar	50	Null	–	–
Invoice_No	int	–	Null	FK	Invoice
Job_Detail	Job_Detail_No	int	–	Not null	PK	–
Job_Number	Int	–	Not null	FK	Jobs
Start_DateTime	DateTime	–	Not null	–	–
Finish_DateTime	DateTime	–	Not null	–	–
Total_Time	Decimal	(10,2)	Not null	–	–
Chargeable_Time	Decimal	(10,2)	Not null	–	–
WorkDone_Detail	Varchar	200	Not null	–	–
Employee_ID	int	–	Not null	FK	Employee
Job_Invoice_Fee	Job_Invoice_Fee_No	int	–	Not null	PK	–
Invoice_No	int	–	Not null	FK	Invoice
Job_Number	Int	–	Not null	FK	Jobs
Job_Fee	Decimal	(10,2)	Not null	–	–
Tax	Decimal	(10,2)	Not null	–	–
Invoice_PaymentDate	Date	–	Not null	–	–
Transaction	Transaction_ID	int	–	Not null	PK	–
Part_ID	int	–	Not null	FK	Part
Supplier_ID	Int	–	Not null	FK	Supplier
Supplier_InvoiceNo	int	–	Not null	FK	Invoice
Date	Date	–	Not null	–	–
Part_Status	Varchar	50	Not null	–	–
Price	Decimal	(10,2)	Not null	–	–
Tax	decimal	(10,2)	Not null	–	–
Freight_Cost	Decimal	(10,2)	Not null	–	–
Job_Client_ID	int	–	Not null	FK	Jobs
Sale	Sale_No	Int	–	Not null	PK	–
Transaction_ID	int	–	Not null	FK	transaction
Sell_Price	decimal	(10,2)	Not null	–	–
Tax	Decimal	(10,2)	Not null	–	–
Freight_Cost	Decimal	(10,2)	Not null	–	–
Client_Job_No	int	–	Not null	–	Jobs
Client_Account	Account_No	int	–	Not null	PK,FK	Business_Account
Client_ID	int	–	Not null	PK,FK	Client

I used client given data. All other data in the database are dummy data.
There some tables are mention like employee, client, and supplier but not mention the attributes in these tables. So I insert common attributes in these tables. For example: – name, address, phone, email.
There mention that in supplier; client has account with account number unique key. So I create suppler_Account and Client_Account table here account_No is primary key field.
All other information mention given case study.

· References

DuBois, P. (2013) MySQL. Upper Saddle River, NJ: Addison-Wesley.

DuBois, P. and Go?mez Pastor, J. (2005) MySQL. Madrid: Anaya Multimedia.

PATHAK, N. (2011) DATABASE MANAGEMENT SYSTEM. [S.l.]: HIMALAYA PUBLISHING HOUSE.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ MCS – Clients, Suppliers, And Employees Management System ”

Get high-quality paper

NEW! AI matching with writer