- 1 (36.63 KiB) Viewed 62 times
**
-
answerhappygod
- Site Admin
- Posts: 899604
- Joined: Mon Aug 02, 2021 8:13 am
**
**
Table 1 shows data about properties rented to clients in Auckland. It also shows details of the owners who own the property. clientNo property No cName PAddress rentStart rentFinish rent ownerNo oName CR76 John Key 1-Jul-13 31-Aug-14 350 C040 PG4 PG16 Tina Murphy Tony Brunt CR76 1-Sep-14 1-Sep-15 450 C093 PG4 CR56 6 Lawrence St. Auckland 5 Novar Dr, Auckland 6 Lawrence St. Auckland 2 Manor Rd. Auckland 5 Novar Dr, Auckland 1-Sep-12 10-Jun-13 350 C040 Tina Murphy John Key Alice Stewart Alice Stewart Alice Stewart PG36 C093 CR56 10-Oct-13 1-Dec-14 375 PG16 C093 Tony Brunt Tony Brunt CR56 1-Nov-15 10-Aug-16 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. (3 marks) b. Derive a set of relations in third normal form (3NF), using the data shown in Table 1. Clearly show each step of normalisation - UNF, 1NF, 2NF, and 3NF. Remember to show all primary keys in each relation with a solid underline and foreign keys with a dashed underline. (12 marks) c. Draw an ER diagram from the derived 3NF relations and include all attributes, primary keys, foreign keys, cardinality and participation. (5 marks)
Table 1 shows data about properties rented to clients in Auckland. It also shows details of the owners who own the property. clientNo property No cName PAddress rentStart rentFinish rent ownerNo oName CR76 John Key 1-Jul-13 31-Aug-14 350 C040 PG4 PG16 Tina Murphy Tony Brunt CR76 1-Sep-14 1-Sep-15 450 C093 PG4 CR56 6 Lawrence St. Auckland 5 Novar Dr, Auckland 6 Lawrence St. Auckland 2 Manor Rd. Auckland 5 Novar Dr, Auckland 1-Sep-12 10-Jun-13 350 C040 Tina Murphy John Key Alice Stewart Alice Stewart Alice Stewart PG36 C093 CR56 10-Oct-13 1-Dec-14 375 PG16 C093 Tony Brunt Tony Brunt CR56 1-Nov-15 10-Aug-16 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. (3 marks) b. Derive a set of relations in third normal form (3NF), using the data shown in Table 1. Clearly show each step of normalisation - UNF, 1NF, 2NF, and 3NF. Remember to show all primary keys in each relation with a solid underline and foreign keys with a dashed underline. (12 marks) c. Draw an ER diagram from the derived 3NF relations and include all attributes, primary keys, foreign keys, cardinality and participation. (5 marks)
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!