The files are attached below
ICT211 Database Design Task 2 ATMC Semester 1, 2018 ICT211 Database Design Task 2 Page 2 of 7 Assessment and Submission Details Marks: 30 % of the Total Assessment for the Course Due Date: Midnight Friday, Week 12 Assignments are to be submitted by SafeAssign. DO NOT SUBMIT THE ASSIGNMENT TO THE COURSE CO-ORDINATOR OR TUTOR. Submit your assignment to the link under Assessment -> Task 2 on Blackboard. The submission link will be open a week before the due date. Please follow the submission instructions provided. The assignment will be marked out of a total of 100 marks and forms 30% of the total assessment for the course. ALL assignments will be checked for plagiarism and/or collusion between individuals. Refer to your Course Outline or the Course Web Site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines. Note: Each student MUST be able to produce a copy of their assignment and this copy MUST be produced within 24 hours of it being requested by the Course Co-ordinator. Failure to produce the second copy of the assignment when requested may result in loss of marks or a fail grade for the assignment. Requests for an extension to an assignment extension MUST be made prior to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances. ICT211 Database Design Task 2 Page 3 of 7 Case Study: My Computer Specialist Pty Ltd – CRM Database Background My Computer Specialist Pty Ltd (MCS) is a computer and information system repair and support company based in the town of Dubbo in western NSW. Having been in business for nearly 5 years, MCS mainly provide onsite service to local small businesses with their IT needs but they also cater for personal computer repairs. Zeplin, the owner of MCS, initially used spreadsheets to keep track of the jobs, invoices and financial details. Because his business is not overly complex, and Zeplin has some good programming skills, his accountant encouraged Zeplin to create a simple database to keep track of his clients, job details, suppliers, parts and invoicing. Although programming comes easy to Zeplin, he soon realised that creating a consistent and reliable database was not so easy. Zeplin has called on you as a database design consultant to help design his database. Zeplin has discussed with you how his business works and what he has in mind. After some consultation, you both realise that MCS needs a multiuser Customer Relationship Management (CRM) database. However, as an experienced database designer, you know that you will not be able to deliver all the database functionality Zeplin is hoping for in the first project. You explain to Zeplin the need to define a core database system which will provide a foundation for added functionality in the future. User Requirements The following is what you and Zeplin agree would be a good core system for this first database design project: 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’. He does not want to include employees at this stage so ‘employee business accounts’ are excluded from the project scope. 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 or supplier and generate a unique ‘account number’ when a new client or supplier is entered. 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. 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. ICT211 Database Design Task 2 Page 5 of 7 Assignment Requirements and Deliverables Part A – 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.) Assumptions Part B – Submitted as a single plain text file with name
_crm.sql, 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 enter data which 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 statement that will produce the following data for a sample invoice (you will need to have the data in the database for this query): o Client name and account number, o Invoice number and the total amount and tax for the invoice, o 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). ICT211 Database Design Task 2 Page 6 of 7 Submission The completed assignment is to be submitted by SafeAssign on or before the due date. The