Section 19 Revision Questions

A relational database contains the following relations:

Product(ProductID, ProductName, Description, Price)
OrderDetails(OrderID, CustomerFirstName, CustomerSurname, CustomerAddress)
OrderLine(OrderID, ProductID, Quantity)

Why is this database not in 3NF? What issues could this cause? How could the database be normalised? The customer fields in the OrderDetails table are not dependent solely on the primary key of the table, OrderID This could lead to data redundancy (where the customer details would have to be stored multiple times unnecessarily) There might also be data inconsistency, where the details of a customer have not been updated in some places, and so there is confusion over which version is correct To fix this, a new table should be created, e.g.


                            Customer(CustomerID, FirstName, Surname, Address)

, and the OrderDetails table should be updated to only include the OrderID and CustomerID fields.

A relational database contains a table with the following description:

Product(ProductID, ProductName, Description, Price)

The Price field stores an whole number of pennies to avoid floating-point rounding errors.

Use SQL to create the Product table. CREATE TABLE Product ( ProductID INTEGER PRIMARY KEY,

  ProductName TEXT,

Description TEXT,

  Price INTEGER 
     )

A cinema uses a relational database containing four relations:

Film(FilmID, Title, Duration)
Screening(ScreeningID, FilmID, ScreeningDate, StartTime, ScreenNumber)
Customer(CustomerID, FirstName, LastName, EmailAddress)
Booking(ScreeningID, CustomerID, SeatNumber, IsPremiumSeat)

Assume all screenings start on time, and therefore end at StartTime + Duration.

Write a SQL query to find all premium seat customers who were watching a film at "12:34" on "2021-05-28". The query should only return the customers' full names and the title of the film they were watching. The results should be displayed in alphabetical order of last name.


                        SELECT FirstName, LastName, Title


                        FROM Customer


                        INNER JOIN Booking
                        ON Customer.CustomerID =
                        Booking.CustomerID


                        INNER JOIN Screening
                        ON Booking.ScreeningID =
                        Screening.ScreeningID


                        INNER JOIN Film
                        ON Screening.FilmID =
                        Film.FilmID


                        WHERE IsPremiumSeat = TRUE


                        AND ScreeningDate =
                        "2021-05-28"


                        AND StartTime <=
                        "12:34"


                        AND StartTime + Duration >=
                        "12:34"


                        ORDER BY LastName ASC

Alternative answer, not using JOINs:


                        SELECT FirstName, LastName, Title


                        FROM Customer,
                        Booking, Screening,
                        Film


                        WHERE Customer.CustomerID =
                        Booking.CustomerID


                        AND Booking.ScreeningID =
                        Screening.ScreeningID


                        AND Screening.FilmID =
                        Film.FilmID


                        AND IsPremiumSeat = TRUE


                        AND ScreeningDate =
                        "2021-05-28"


                        AND StartTime <=
                        "12:34"


                        AND StartTime + Duration >=
                        "12:34"


                        ORDER BY LastName ASC

Explain what is meant by a normalised database. All non-key attributes are dependent on the primary key of the table There are no partial dependencies / All non-key attributes depend on the whole key There are no transitive dependencies / All non-key attributes depend on nothing but the key All data is atomic / There are no repeating groups

Why is database normalisation important? To eliminate/avoid data inconsistency To eliminate/reduce data redundancy To improve the performance of the database

State the conditions under which concurrent access to a database could cause a problem, and explain how this could be dealt with using timestamp ordering. It can cause a problem when two users try to update the same record at the same time The database assigns read and write timestamps to each record, which store the last successful read and write operations on the record respectively When a read transaction happens on a record, the timestamp of the transaction must be greater than the write timestamp of the record When a write transaction happens on a record, the timestamp of the transaction must be greater than both the read and write timestamps of the record

Score: 0/0

Question 1 of ∞