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 Du
o 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
usiness 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
ealised 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
ealise 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 cu
ent 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 cu
ent 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 cu
ently 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, prefe
ed
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
equired 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
eports 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