Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

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.

Cite This Work

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.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
close