Task

Part A - Normalisation

1 Using the table and data given below, normalise the table to 3NF.

2 Use correct dependency diagram(s) to show each normalisation level.

3 Create an ERD to show the resultant tables at the 3NF level with all the attributes, Primary/Foreign keys etc.

Book_ID Book_Title Bk_ Author Date_Published Publisher Publisher_Location Borrower_Num D_Borrowed D_Returned

101 Management of Information Systems Laudon 2010 Pearson NSW AR258714 25/4/2014 4/5/2014

102 Intro to Information Systems Rainer 2012 Wiley QLD HC258321 10/2/2014 16/2/2014

103 Database systems Coronel 2011 Cengage VIC AR258714 25/4/2014 6/5/2014

104 Management Cole 2013 Pearson NSW KX25894 15/3/2014 23/3/2014

102 Intro to Information Systems Rainer 2012 Wiley QLD WC258568 20/2/2014 27/2/2014

103 Database systems Coronel 2011 Cengage VIC PS258963 25/5/2014 2/6/2014

102 Intro to Information Systems Rainer 2012 Wiley QLD BW258541 18/4/2014 26/5/2014

Part B - Modify existing ERD and create additional entities as required

Use the solution guide from Assessment Item 2A to complete this part.

At a later interview with the manager and the traffic coordinator of Drive-n-Drop, you find out that the company also has a number of trainers that are delegated various training assignments for both newly recruited and existing drivers. When drivers call in sick and cannot come to work, the Drive-n-Drop traffic coordinator needs to find a replacement driver; in this case any of the trainers who don't have a current training assignment may be chosen as relief drivers. Details about relief drivers and which shift they are covering need to be recorded.

Additionally, you are informed that drivers report if there is damage on the bus, prior to leaving the depot. The description of the damage is then recorded in a damage log along with the date, the time, and the number of the driver that reported the damage. If the damage is serious, the traffic coordinator assigns a replacement bus to the driver to complete his/her shift.

Drive-n-Drop has their own mechanics' workshop and they conduct regular services on all of their buses every 3 months. All service details are recorded to ensure that the buses are in good working order and comply with safety regulations.

Part B - Q1:

List the business rules and constraints that apply to the business case as outlined above

Part B - Q2:

Modify your ERD according to the new information you have received, as the above business activities (rules).

Ensure that you use the ERD solution from previous assessment 2A as a guide (this will be uploaded on Interact in week 7), then add any necessary entities, relationships, primary/foreign keys, etc.

Part C

Create the RDM with appropriate attributes, based on the newly added entities created by you. (Do not include the entities given to you from Assessment 2A), following the format given below:

Rationale

This assessment item is designed to test your understanding of more advanced database design, your ability to use an iterative approach to modelling, and to examine data problems due to redundancy.

In Part A, you are required to apply the normalisation process to existing data in order to minimise redundancies and data anomalies. Parts B & C are a continuation of Assessment Item 2A, using the same business case of Drive-n-Drop.