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 |
|
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 |
- |
- |
|
|
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 |
- |
- |
|
|
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 |
- |
- |
|
|
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.
To export a reference to this article please select a referencing stye below:
My Assignment Help. (2021). MCS - Clients, Suppliers, And Employees Management System (Essay).. Retrieved from https://myassignmenthelp.com/free-samples/ict211-database-design/common-attributes.html.
"MCS - Clients, Suppliers, And Employees Management System (Essay).." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/ict211-database-design/common-attributes.html.
My Assignment Help (2021) MCS - Clients, Suppliers, And Employees Management System (Essay). [Online]. Available from: https://myassignmenthelp.com/free-samples/ict211-database-design/common-attributes.html
[Accessed 14 November 2024].
My Assignment Help. 'MCS - Clients, Suppliers, And Employees Management System (Essay).' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/ict211-database-design/common-attributes.html> accessed 14 November 2024.
My Assignment Help. MCS - Clients, Suppliers, And Employees Management System (Essay). [Internet]. My Assignment Help. 2021 [cited 14 November 2024]. Available from: https://myassignmenthelp.com/free-samples/ict211-database-design/common-attributes.html.