The ER Design
This ER design gives one possible data model for the OzCars application introduced in the first stage of this assignment. The design presented here is based on the discussions during Weeks 2 and 3 lectures.
To make the presentation clearer, the design is broken into a number of sections.
Other notational conventions in the ER diagrams:
• primary key attributes for entities are underlined
• total participation in a relationship is indicated by a thick line
• an arrow indicates that at most 1 entity is involved in the relationship
Data Types
To make things easier, I have defined some useful data types using the create domain statement. Some of the create domain statements use standard SQL patterns for specifying constraints, while others use PostgreSQL-specific regular expressions for this purpose. The domain definitions are given at the top of the template file.
Employee
The following diagram shows the entities, attributes and relationships that provide the information about the employee entity for OzCars.
Details:
• we use a system-generated, numeric EID as a primary key, since Salesman and Mechanic will be extensively referenced in the database.
• the database should store every employee’s name (consists of first name and last name), TFN and salary.
• both the salary and commission rate are integers, and must be larger than 0. Also refer to the assignment spec for further constraints on the commission rate (i.e., each salesman will have a negotiated commission rate ranging from 5% to max 20%).
• we assume that both first name and last name are not longer than 50 characters.
• TFN has exactly 9 digits.
• the mechanic’s license is alphanumeric, with exactly 8 characters.
• all attributes in the above ER diagram cannot have a null value.
Client and Car
The following diagram shows entities, attributes and relationships for clients and cars.
Details (Car):
• We assume that VIN is exactly 17-character long and does not include the letters I, O, or Q (to avoid confusion with numerals 1 and 0)
• Year is between 1970-2099 inclusive.
• Model and Manufacturer are of maximum 40 characters each.
• Car license is of maximum 6 alphanumeric characters.
• Except for the list of options, all other information as specified by their corresponding attributes are compulsory.
• Use the defined domain OptionType for a list of possible options.
Details (Client):
• we assume that name is no longer than 100 characters.
• we use a system-generated, numeric CID as a primary key.
• address is maximum 200 characters.
• Phone number has exactly 10 digits (mobile or landline with area code).
• if a client does not have an ABN (i.e., it is not a company), all information is compulsory except the email address. The database should check if the input email is of a proper email format (using the defined EmailType domain).
• ABN has exactly 11 digits.
• If an ABN is provided (i.e., it is a company), an optional Web address of the company is stored. A URL should start with http://…
Buy, Sell and Repair
The following diagram shows entities, attributes and relationships for the rest of the design for OzCars.
Details:
• Any monetary amounts should be defined using the type numeric with 2 decimal digits and they should all be positive numbers. All monetary amounts will not exceed 6 integral digits.
• Description has a maximum of 250 characters.
• RepairJob Number is a number between 1 and 999 inclusive.
• Phone number has exactly 10 digits (mobile or landline with area code).
• As specified in the requirements, the clients involved in a car transaction are the owners of the car.
What To Do Now
Make sure you read the above description thoroughly, and review the notes and exercises on ER-to-Relational mapping. Get a copy of the a1.sql template and see what is provided there. If any aspect of this design requires further clarification, ask a question under topic Assignment 1 (Stage 2) on the course website Forums.
Reminder: before you submit, ensure that your schema will load without error if used as follows on grieg:
% dropdb a1
% createdb a1
% psql a1 -f a1.sql
… will produce notices, but should have no errors …
% psql a1
… can start using the complete database …
Penalty: If we have to fix errors in your schema before it will load, you will incur 3 (out of a total of 10) marks “penalty”.