10.2. Normalisation to third normal form (3NF)
A subsection of Information Technology, 9626, through 10. Database and file concepts
Listing 8 of 8 questions
Below is an extract from a database of students who have chosen to study Maths at AS Level. The database is in unnormalised (0NF) form. Student number Name Second subject Third subject Eashan Wadhwa Chemistry Physics Karan Bansal Physics Chemistry Manan Malik Chemistry Biology Neel Bhatia Biology Chemistry Ridhima Shekhar History Biology Rohin Pannu Geography History Saransh Anand History Geography Shraddha Aggarwal Biology Chemistry Rekha Pooja Physics Chemistry Sundar Rakshana Physics Chemistry Describe the characteristics of the data above which make it unnormalised and explain how it can be transformed into first normal form (1NF).
9626_m17_qp_12
THEORY
2017
Paper 1, Variant 2
View
A company provides cars for several leasing organisations in districts of London, in the UK, such as Enfield or Lewisham. Agents buy cars from a number of showrooms. The company buys a car from an agent before then providing it for lease. The holding company needs to keep a record of: • each car • which agent bought each car • which showroom it came from. An example of a database for this company in unnormalised (0NF) form is shown below. Car ID Make Model Agent ID Showroom Agent Name Agent Email LO01 Lopa Wolf Jones, Enfield John Hart jhart@welovecars.com FO01 Frod Squire Morgan, Lewisham Carl Mason cmason@amazingcars.com YA01 Yayota Badger 003 Jones, Greenwich Joe Smith jsmith@mew.com CN01 CNX Rabbit Lewis, Ealing John Hart jhart@welovecars.com WX01 WX Gull Jones, Brent John Hart jhart@welovecars.com YA02 Yayota Loaf Evans, Lambeth Carl Mason cmason@amazingcars.com FO02 Frod Peer Evans, Lambeth Joe Smith jsmith@mew.com LO02 Lopa Fox Jones, Enfield John Hart jhart@welovecars.com YA03 Yayota Plum Morgan, Lewisham John Hart jhart@welovecars.com CN02 CNX Chariot 003 Lewis, Ealing Joe Smith jsmith@mew.com Convert this database to third normalised form by completing the field names for each table provided. You are not required to enter any data into the tables. Cars Field1 Field2 Field3 Field4 Field5 Agents Field1 Field2 Field3 Field4 Showrooms Field1 Field2 Field3 Complete the relationship diagram below by completing the tables and showing the types of relationships between them. In the top cell write the name of the table and write the field names underneath. Indicate the primary key for each table and any foreign keys .
9626_w18_qp_12
THEORY
2018
Paper 1, Variant 2
View
Questions Discovered
8