COIT20247 Database Design and Development (T2 2017)
Assessment item 1— Entity Relationship Diagram (ERD)
Due date: Friday of Week 5 (11:45pm August 11, 2017) ASSESSMENT
Weighting: 30%
Length: No fixed length 1
Objectives
This assessment item is to develop a conceptual data model based on the following case study. It relates to the unit learning outcome numbers 1 and 3 in the unit profile.
Assessment task
In this assignment, you are going to perform the entity-relationship data modelling based on the following application scenario.
The application scenario: Australasia Auto Services
Australasia Auto Services (AAS) is a prestigious dealer of cars based in Rockhampton, Queensland. Their main business is to provide professional maintenance services for various cars and also sells a wide range of new vehicles. With the boom of its business in recent years, AAS needs a relational database system to store information related to its business. In this assignment, your task is to design an entity relationship (E-R) data model for a database to be used by AAS in order to manage various information related to their business activities efficiently. You should read the case study carefully, making a note of all the entities you think are involved, and of their attributes. Consider the relationships between the entities as you do this.
Although AAS hires many staff, each employee can be categorised one of three types: mechanic, salesperson, and administration staff. The duty of mechanic is to perform car maintenance service, while the salespersons are responsible for sales and the administration staff deal with a wide range of administrative jobs or miscellaneous tasks. Each employee at AAS has a unique employee number, and AAS maintains normal contact information about each employee. In addition, the technical trade level and expertise of mechanics, the number of year in sales experience for a salesperson, and the qualification of admin staff are to be recorded in the system.
After a phone booking, a client will bring a car in for service on a specified date and the admin staff will record the car details that include the registration number, manufacturer, make, model, produced year and colour. The admin staff will also record a description of service requirement. Then the system will assign a unique identifier number for this service job. It is necessary to point out that the client’s car could be a car purchased from AAS, or it may be bought from somewhere else. For the former case, some relevant details such as the salesperson name, price, and warranty due date are required to keep in track.
Usually each service job could be carried out by one or many mechanics. The data on service details such as which mechanics perform the service job, labour cost, part cost, date, start time and finish time must be recorded in the system. When a car is being repaired, it often involves replacing certain parts due to various damages. For this reason, the mechanic may check its records in the database on a list of part types. Each type has a unique part identifier, a description, manufacturer or brand information, a price per unit and the quantity available in the stock. If parts are required to replace during service, the mechanic records the quantity of each type used into the database.
From time to time, the clients may feel unsatisfactory on a service job done and therefore they may complain about service quality, expensive cost and other possible problems. If so, the lodged complaint should be recorded and processed appropriately by an admin staff, with a response addressed the outcome of investigation. Finally, AAS also wishes to maintain contact details for each client who requests service, including their residential address, contact phone, email and whether they are a senior citizen, who may receive some discount in a service.
You are required to develop an ER model to represent the information requirements for the application scenario (AAS) as described on last page.
Your ER model must:
• show all necessary entities, attributes and relationships
• show unique identifiers
• show multi-valued attributes (also called repeating attributes), if any
• show participation and cardinality
• show associative entities, if appropriate
• use the notation described in the set text
• use consistent and appropriate naming for entities and attributes throughout
Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ER model accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show these in your ER model accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order.
To get yourself started, ask yourself, 'If I were running this business, what things I would need to keep a list of?' Write those things down. For each thing, what information would you need to record about it?
Entity and attribute definitions
You are required to write brief definitions for the entities and attributes in your model. For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute. You should refer to the sections Naming and Defining Entity Types and Naming and Defining Attributes in the textbook. For example:
STUDENT: A Student is a person who has enrolled in one or more programs at the university. This includes Start Uni Now high school students but does not include prospective students.
Student No: Uniquely identifies each student.
Student Name: The full name of the student.
Date of Birth: The student’s date of birth.
…
PROGRAM: A Program is a program of study available at the university. Program code: Uniquely identifies each program Program name: The full name of the program.
…
What you should submit
You should submit a Microsoft Word document containing:
• A copy of your entity-relationship model(s).
• A list of business rules that are apparent from the case study.
• A list of any assumptions that you made (include any clarifications from your tutor) with justifications.
• Your entity and attribute definitions.
• A set of 3NF relations that are from the mapping of ER model.
(This item is optional. See a note below.)
Your document should contain appropriate identifying information (your student number, name, course number, etc.) and should be appropriately formatted. For example, your document should contain a very brief introduction stating the purpose of the document, as well as headings for each section.
Note: Due to some campus having weekend lectures and tutorials and the topic of 3NF in Week 5, the assessment on the transforming the ER data model to 3NF relations is optional in Assignment 1. You can include it in your word document if you can complete but no marks will be allocated to it. In Assignment 2, you must include the mapping of relations into 3 NF.
Important points
• You must work individually on this assignment.
• The model solution to this assignment will be released approximately TWO (2) weeks after the due date.
• Assignments that are submitted after the release of the model solution will receive zero.
• You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIT20247 course materials' if that was all you used.
• Extension requests must be submitted online (for AIC students, contact your local campus administration staff) before the assignment due date. A penalty of 5% of the total mark per calendar day will apply for late submissions without an extension, or zero if submitted after the release of the model solution.
• Before submitting your assignment, review the marking criteria as shown on the marking guide. Ensure that all the criteria are addressed in your submission.
• After submitting your assignment, please verify that your submission was successful, i.e.
download your submitted files to ensure they are correct and uncorrupted.
Assessment criteria
Item Maximum Marks
1 Entities have been identified, named and drawn correctly including super type and sub type entities; associative entities; weak entities (if any). 5
2 Provided appropriate attributes for all the entities. 4
3 Provided identifiers for all the entities 2
4 Drawn correct relationships between entities and named them appropriately. 5
5 Used proper symbols and shown min and max cardinalities for the relationships. Appropriate symbols have been used for super type and sub type relationships (if any) 5
6 Provided appropriate justification for assumptions and business rules. 5
7 Definitions of entities and relationships 3
8 Coverage-
Whether the modelling has covered the application scenario 1
Penalty due to plagiarism/copying
Late Penalty: Less 5% of available marks per calendar day.
Total 30