COMP1711-8711 Data Modelling Assignment
- Subject Code :
COMP1711-8711
- Country :
Australia
General Specification
Derive relations from the supplied Entity-Relationship Diagram given below into a logical model representation in terms of the relational data model. Identify a primary key for each relation and subsequently all foreign keys.
You need to use the database description language (DBDL) as described in Chapter 17 of the textbook, for example
.png)
You also need to justify the reasoning behind the derivation (e.g. why does it contain a foreign key, can the foreign key be NULL, why has a particular ON UPDATE been chosen, etc.), for example
|
Client is a strong entity. The composite attribute name has only its constituent simple attributes fName and lName listed. prefType has been merged into the Client entity from the Preference entity from a 1:1 States relationship with mandatory participation on both sides. staffNo has been posted into the Client relation from a 1:* Registers relationship where Client was the child and Staff was the parent. staffNo is NOT NULL as Client has mandatory participation in the Registers relationship. UPDATE CASCASE was chosen to update staffNo whenever the attribute changes in the Staff table. DELETE NO ACTION has been selected to stop Staff being deleted without first updating Client.
|
b) For simplicity lets assume that we have the following entities in our conceptual model( only for attempting this part) : Vehicle and Booking
We now want to validate our conceptual model through normalisation. List the functional dependencies and translate the conceptual model to 3NF. Show each step, using guidance from chapter 14 of the book and the lecture.
Vehicle: Convert this table to 3NF
|
depotID |
address |
phone |
regNum |
typeID |
fleetNum |
colour |
make |
model |
doors |
body |
trim |
|
20 |
IST Flinders 5000 |
1400111111 1300111111 |
ABC001 |
AQ51 |
100 |
Blue |
Audi |
Q5 |
4 |
SUV |
Standard |
|
30 |
Tonsley Flinders 5500 |
555111444 555111444 3100200505 |
DEF003 |
AQ51 |
100 |
Red |
Audi |
Q3 |
4 |
Small SUV |
Sports |
|
20 |
IST Flinders 5000 |
1400111111 1300111111 |
XYZ200 |
MCX2 |
200 |
Gold |
Mazda |
CX30 |
4 |
SUV |
Luxury |
|
30 |
Tonsley Flinders 5500 |
555111444 555111444 3100200505 |
WXY350 |
HAC3 |
300 |
Silver |
Honda |
Accord |
4 |
Sedan |
Standard |
Booking: Fill some dummy data (about 4 rows) and convert this table to the 3NF
|
BookingID |
HiredDate |
InsuranceID |
InsuranceCost |
PolicyType |
startDate |
hireDays |
ClientID |
ClientPostcode |
(c) Translate your answer to (a) into SQL (in a file .sql) and build the database using capabilities of SQLite. Include the relevant DROP statements and specify ALL primary and foreign keys. If you want to demonstrate ISO SQL that is not available in SQLite than include as a comment, but ensure that you have correct, runnable SQLite database as well. You also need to populate your database (INSERT INTO …) with some sample data and perform some simple queries to ensure it works correctly.
