MCS - Clients, Suppliers, And Employees Management System (Essay).

Overview of MCS

Zeplin believes that, at its core, MCS primarily deals with Clients, Suppliers and Employees, all of whom may have an ‘account’ with his business that he calls a ‘Business Account’. The information that all business accounts share in common is account name, contact people and their contact details, address details, Tax and ABN numbers, and account status. The software will also need to know whether the business account is a client, supplier or employee and generate a unique ‘account number’ when a new client, supplier or employee is entered. Employees also have a start date, and hourly rate.

Zeplin has explained that each business account may have multiple contact people each of whom may have multiple contact details (i.e. there may be a mobile phone, land line, email address and other possible details such as web site, etc.). Contact people may also have multiple addresses (e.g. a physical address as well as a postal address). The business account itself may also have multiple contact details as well as multiple addresses. Suppliers: Information that is particular to ‘Supplier’ type accounts includes MCS account number with the supplier. Supplier accounts are uniquely identified by the ‘account number’. Suppliers may supply MCS with either ‘Services’ or ‘Parts’ or both. At this stage we are only concerned with ‘Parts’. ‘Services’ are outside this current project scope.

However, our design will need to be able to encompass ‘Services’ in future projects. To do this you have decided to include an attribute in your ‘Parts’ table for part type which can be either a ‘Part’ or ICT211 Database Design Task 2 Page 4 of 7 ‘Service’. ‘Parts’ will be uniquely identified by Part ID. The part information needed includes Part category, Manufacturer, Part description and whether it is a current part. Different suppliers may supply the same part. For example, the same memory module may be supplied through a number of different resellers, each with their own part number, sell price and sell tax. MCS also need to record the date the sell price was updated and whether the supplier currently supplies that part.

This part / supplier composite table is a weak entity that will be identified by both the Supplier ID and the Part ID. When parts are purchased from suppliers the details must be recorded. A unique Part Transaction ID is generated, and the Part ID and Supplier ID recorded. Other information about the transaction that is required includes the part Serial Number, Supplier Invoice number and date, Part status (whether it is in stock, sold or written off), purchase price and tax, Purchase freight cost. When the part is sold MCS requires Sell price and tax, freight cost and the Client’s Job number (this will attach the part to a client job and then be included on the client invoice.

Business Account Management

Clients: Information that is particular to Client type accounts includes Hourly charge rate, preferred Name on Invoice, ‘Invoice Attention to’ name, and Client commence date. Client accounts are uniquely identified by the ‘account number’. When a client initially contacts MCS the above business account is set up with all the client details. A job is opened for that client and a unique Job Number is generated. Job information stored is a possible Client reference number, Job opened date, Work required, Job Status, Employee name who entered the job. There is also a field for Invoice number which will populated when the job is included in an invoice.

When an invoice number is inserted the job status is automatically updated to ‘Job Invoiced’. Each job may have many Job details as a particular job may require a number of visits or separate actions. For example, a server install may include a process where the first visit is the initial physical install by one technician, a subsequent visit to get the server on the network by another technician, and possibly many subsequent visits to set the client’s computers to access the new server all under the one chargeable job. Job details information required includes a unique job detail ID, start date and time, finish date and time, total time, chargeable time (actual total time may not be total chargeable time), details of work done, and employee name responsible. Each Job may also have many parts used on that job.

The Job number is included as part of the Part Transaction record which records the sale price, serial numbers, etc as per the description above. A client may have many jobs, and jobs may have many job details (or none) and include many parts (or none). Job records containing parts and / or job details must be invoiced to the client. Each invoice may invoice one or more jobs and is uniquely identified by a generated invoice number. Records required for the invoice includes the client’s id, invoice date, invoice due date, total invoice amount and tax, an invoice Status, and an invoice paid date. The invoice will include a list of jobs to be invoiced and the individual job fee and tax.

Other than the invoice payment date, a system for invoice payments are not included within the scope of this project. and Deliverables Submitted as a MS Word Document:

Entity Relationship Diagram in Crows Foot Notation

Relational Schema – including Primary and Foreign Keys ? Supplementary Design requirements – for example but not limited to: o information on length of identifiers, postcodes, names, o data attribute information (compulsory, variable length / type, etc.)

Submitted as a single plain text file with name _crm_sql.txt, containing all your SQL implementation:

CREATE TABLE statements including all integrity constraints,

CREATE TRIGGER statements: o Automatically update the Job table - Job Status to ‘Job Invoiced’ when an invoice number is entered into that job,

INSERT INTO statements for populating the database: o Some sample data has been provided from an existing MCS database which you are required to enter (Note: not all the sample data attributes are applicable, you choose which data you will include).

The two worksheets of data are related by a common key, o The data provided is incomplete, you are expected to generate and data enerwhich will enable you to test the database and produce the sample reports required, o Data may need to be inserted in a particular order to comply with integrity constraints,

SELECT statements that will produce the following data: o A single query that generates a sample invoice (you will need to have the data in the database for this query) including:

Client name and account number,

Invoice number and the total amount and tax for the invoice,

At least three Jobs to invoice including the job number work required and that job amount and tax (the total of the jobs should equal the total invoice amount).

o A single query that generates a inventory report that shows what parts have been used on jobs in the last month.

o A single query that generates a financial report that shows the out-goings (parts used on jobs and total time x employee hourly rate), and incoming (total invoices). 

· Entity Relationship Diagram (ERD): -

Relational Schema: -

Employee

Employee_ID (PK)

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.