- Paper Database System Design 1 (35.47 KiB) Viewed 62 times
Paper: Database System Design
-
answerhappygod
- Site Admin
- Posts: 899604
- Joined: Mon Aug 02, 2021 8:13 am
Paper: Database System Design
Paper: Database System Design
Question 3 Normalisation [20 marks] Table 1 shows data about properties rented to dients in Auckland. It also shows details of the owners who own the property. clientNo propertyNo Name pAddress rents tart rentFinish rent ownerNo Name CR76 PG4 John Key 350 C040 Tina Murphy PG16 CR76 450 C093 Tony Brunt PG4 CR56 6 Lawrence St Auckland 5 Novar DI, Auckland 6 Lawrence St Auckland 2 Manor Rd, Auckland 5 Novar Dr, Auckland 1-Jul-13 31-Aug-14 1-Sep-14 1-Sep-15 1-Sep-12 10-Jun-13 10-Oct- 13 1-Dec-14 1-Nov- 15 10-Aug-16 350 John Key Alice Stewart Alice Stewart Alice Stewart Tina Murphy C040 C093 PG36 CR56 375 PG16 C093 Tony Brunt Tony Brunt CR56 450 Table 1: A sample report - "List of properties by clients and owners" a. In what level of normalisation is the above relation? Justify your answer. b. Deri ve a set of relations in third normal form (3NF), using the data shown in Table 1. Clearly show each step of normalisation - UNF, INF, 2NF, and 3NF. Remember to show all primary keys in each relation with a solid underline and foreign keys with a dashed underline. c. Draw an ER diagram from the derived 3NF relations and include all attributes, primary keys, foreign keys, cardinality and participation.
Question 3 Normalisation [20 marks] Table 1 shows data about properties rented to dients in Auckland. It also shows details of the owners who own the property. clientNo propertyNo Name pAddress rents tart rentFinish rent ownerNo Name CR76 PG4 John Key 350 C040 Tina Murphy PG16 CR76 450 C093 Tony Brunt PG4 CR56 6 Lawrence St Auckland 5 Novar DI, Auckland 6 Lawrence St Auckland 2 Manor Rd, Auckland 5 Novar Dr, Auckland 1-Jul-13 31-Aug-14 1-Sep-14 1-Sep-15 1-Sep-12 10-Jun-13 10-Oct- 13 1-Dec-14 1-Nov- 15 10-Aug-16 350 John Key Alice Stewart Alice Stewart Alice Stewart Tina Murphy C040 C093 PG36 CR56 375 PG16 C093 Tony Brunt Tony Brunt CR56 450 Table 1: A sample report - "List of properties by clients and owners" a. In what level of normalisation is the above relation? Justify your answer. b. Deri ve a set of relations in third normal form (3NF), using the data shown in Table 1. Clearly show each step of normalisation - UNF, INF, 2NF, and 3NF. Remember to show all primary keys in each relation with a solid underline and foreign keys with a dashed underline. c. Draw an ER diagram from the derived 3NF relations and include all attributes, primary keys, foreign keys, cardinality and participation.
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!