MCS – Clients, Suppliers, And Employees Management System

· Entity Relationship Diagram (ERD): –

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Relational Schema: –

Employee

Employee_ID (PK)

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Name

Address

Phone

Email

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

Email

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

Email

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.