Part A - Entity-Relationship Diagram (ERD)
Draw an Entity-Relationship Diagram (ERD) for the data storage requirements of a business described in the case study below. Notes:
- It is recommended that you follow the modelling standard used in the text book and lecture slides.
- Use only attributes that uniquely identify instances of an entity that are mentioned in the case study. Additional attributes to uniquely identify entity instances should not be introduced.
- Some information in the case study is ambiguous, vague, and/or missing. This is intentional to encourage students to think, explore, and search for answers. There are two options:
1. Seek clarification about the case study by posting a message on the course forums for this assignment (after searching to make sure the same topic / question hasn't already been discussed). Any clarification received should be clearly documented in your assignment submission.
2. Make reasonable assumptions about missing or ambiguous information in the case study.
Marks may be lost for unreasonable assumptions. The course coordinator will determine if an assumption is reasonable.
The Case Study
ABZ Construction Company has branches (sites) all over Australia and owns several office buildings in all major cities. It wishes to install solar panels on their building roofs and manage these efficiently, as it can provide cost savings. As a result, they hired a consultant to design a database that will store all of the required data. The consultant has been informed that they wish to record details relating to sites, buildings in each site, installers, quotes provided by the installers for specific solar panels and the electricity generated through the solar panels on each building roof.
The data storage requirements are described below.
Each site is identified with a unique site Id, and has a street address, suburb, state, postcode, main contact phone number and a contact person for that site. As each site may have more than one building it is necessary to record Building number, roof availability, average shading factor (indicates the average hours of sunlight per day with a factor between 0.0 and 1.0) and the GPS coordinates (longitude and latitude). For simplicity reasons, calculation for shading factor is not provided here.
Each solar panel has a make, model, size (both width and length in mm), an output capacity (in watts), a recommended retail price (RRP) in Australian dollars, Efficiency percentage (between 0.0 and 100.0) and country of manufacture. Each combination of make and model for a solar panel must be unique. Each installer has a company name, contact person name, phone, fax, mobile number and email address. The date the installer became a solar certified installer and the installer's unique Australian Business Number (ABN) also need to be recorded.
When requested installers provide quotes for installing solar panels on these buildings. Each quote includes Quote reference number, issued date and cost breakdown (solar panel unit cost, number of solar panels, wiring costs, metering costs, labour costs and other costs).
Each building uses electricity (measure in KWh), and this usage is recorded at the end of every billing period (usually the 1 have been installed, a building will generate electricity (measured in KWh) and this will offset the building's usage at the end of each billing period. If the building is producing more energy than it uses, the excess energy will be sold to the grid at a price still to be determined. ABZ want to keep track of the energy produced and the energy used, and the net income or cost, for each building at the end of each billing period.
An installer may quote on the work at one or more locations, or they may decide not to quote at all, for example, the project may be too large for them or they may have too much work already, and so on. If an installer does quote on the work, then they will provide a quote with a reference number that is unique for that installer, and each quote will be specifically for a building on a particular site.
Payments, quote / product evaluation and servicing are outside the scope for this case study.
Day of every month, but this could vary). After the solar panels
Part B - Table design
"ABC Alloys" assigns mentors to their employees as part of their training program and records training cost. It has recorded data relating to employees, Department, Mentors (FYI- Each Department has a Mentor) assigned and the training cost in one table Employee_training.
Below is a small sample of some of this data:
Employee_Training
|
EmployeeID
|
EmpName
|
ContactNo
|
Position
|
Department
|
Mentor
|
Training Cost
|
|
E123
|
Gary Howard
|
9864
|
Senior Manager
|
HR
|
George Wellington
|
$ 5,000.00
|
|
E124
|
Barry Abott
|
9784
|
Assistant Manager
|
Accounting
|
Ross Trotter
|
$ 5,000.00
|
|
E125
|
Julie Leonard
|
2548
|
Manager
|
Marketing
|
Don Anderson
|
$ 5,000.00
|
|
E126
|
Mathew Foster
|
6547
|
Supervisor
|
Production
|
Barry Simpson
|
$ 2,500.00
|
|
E128
|
Richie
|
8795
|
Supervisor
|
HR
|
George Wellington
|
$ 2,500.00
|
|
E127
|
Richard Drew
|
5896
|
Administrator
|
Finance
|
Nick Lee
|
$ 3,000.00
|
Unfortunately, some anomalies arose while using this design.
Questions
Support your answers to questions below with any assumptions that help to clarify your understanding of data in the Employee_Training table.
1. Identify one anomaly that might arise from using the above table.
2. Identify all functional dependencies in the above mentioned table.
3. Explain why this table does not satisfy BCNF.
4. Convert this table into a set of BCNF tables that can hold the sample data shown in the above table.
Note: Document the tables in your design using the notation below.
Customer(CID, Name, Street, Suburb, State, PostCode, DiscountPercent, Email) SalesOrder(SOID, CID, Number, FullPrice, Discount, FinalPrice, TotalPaid, Status)
foreign key (CID) references Customer(CID)
Part C - Data Model Transformation
Implement the ER model you developed in Part A as a relational database design.
1. Transform your ER model into a relational database design.
Note: Document the tables in your design using the notation shown in the example below.
Customer(CID, Name, Street, Suburb, State, PostCode, DiscountPercent, Email)
SalesOrder(SOID, CID, Number, FullPrice, Discount, FinalPrice, TotalPaid, Status)
foreign key (CID) references Customer(CID)
2. Implement sufficient tables to support the requirements below. You do not need to implement (create) the entire database. You just need to implement enough of the database to demonstrate (and document) the following:
A. use of data types: provide examples of four different data types used in your design
B. primary key constraints: provide two examples
C. foreign key constraints: provide two examples
D. a unique constraint: provide one example
E. a cascading delete: provide one example
F. column constraints (a constraint that affects only one column): provide two examples
G. a table constraint (a constraint that affects more than one column): provide one example
H. default values: provide two examples
Note: You need to include brief documentation (in a text file or word document or in your script file) that documents and highlights where the above items are demonstrated in your database. e.g.
1. A Microsoft Word document containing:
- your Entity Relationship Diagram (ERD) for Part A
- your reasonable assumptions for Part A (if you have any)
- documentation for points of clarification for Part A (if you have any)
- your answers to questions in Part B
- your relational database design and implementation for Part C
- documentation describing the required features of your database implementation for
Part C.
2. Microsoft Access file containing:
- The tables and features you have implemented to support the requirements for Part C.