Topic 3.2: Database design and normalisation Questions

Identify the most appropriate data type for a student's exam mark out of 100, and justify your choice.

Choose the most appropriate data type for a postcode.

Choose the correct way to implement a many-to-many relationship.

A clinic's data is held in one un-normalized view: APPOINTMENTS (ClientID, ClientName, ClientDOB, StaffID, StaffName, VisitedOn, Service). A client may visit many times and a staff member sees many clients. Construct the database in third normal form for the three entities in this view.

A table holds StudentID, StudentName, CourseID, CourseName and TutorName, where TutorName depends on CourseID. Construct a set of tables in third normal form.

A table holds OrderID, ProductID, ProductName and Quantity, with OrderID and ProductID as a composite key. Construct the tables required for second normal form.

Suggest one situation in which a designer might choose not to normalise fully.

Choose the step that removes transitive dependencies.

Choose what a null value in a field means.

An online music shop stores orders like this: | OrderID | Customer | Albums | |---|---|---| | 501 | Lee | Blue; Halo | | 502 | Ana | Halo | Construct the table(s) in 1NF for this data.

A sports club stores members like this: | MemberID | Name | Sports | |---|---|---| | 7 | Ravi | tennis, squash | | 8 | Mia | rowing | Construct the table(s) in 1NF for this data.

A college lists course readings like this: | CourseID | Title | Books | |---|---|---| | C1 | Physics | B12, B40 | | C2 | History | B07 | Construct the table(s) in 1NF for this data.

A taxi firm keeps one table: | TripID | DriverID | DriverName | DriverPhone | Fare | |---|---|---|---|---| | T1 | D3 | Sam | 0711 | 12.50 | | T2 | D3 | Sam | 0711 | 8.00 | | T3 | D5 | Ola | 0799 | 15.00 | Construct two tables to remove the data redundancy.

A library keeps one table: | LoanID | MemberID | MemberName | BookID | DueDate | |---|---|---|---|---| | L1 | M9 | Kai | B2 | 03/05 | | L2 | M9 | Kai | B7 | 05/05 | Construct two tables to remove the data redundancy.

A vet keeps one table: | VisitID | PetID | PetName | OwnerPhone | Treatment | |---|---|---|---|---| | V1 | P4 | Rex | 0700 | jab | | V2 | P4 | Rex | 0700 | check | Construct two tables to remove the data redundancy.

A single table holds OrderID, OrderDate, CustomerID, CustomerName, CustomerCity, ProductID, ProductName, UnitPrice and Quantity. Construct a fully normalised set of tables.

State the requirements of first normal form.

In a clinic's ERD, the APPOINTMENT entity holds AppointmentID, PatientID, PatientName, DoctorID and DoctorRoom. Outline what could be done to bring this design into 3NF.

An ERD for an online shop has one ORDER entity holding OrderID, CustomerID, CustomerEmail, ProductCode and ProductPrice. Outline what could be done to bring this design into 3NF.

A school library's ERD has a LOAN entity with LoanID, BookISBN, BookTitle, StudentID and TutorGroup. Outline what could be done to bring this design into 3NF.