Databases

1 Explain what is meant by a foreign key. 2

One mark for each of the following, to a maximum of 2.

• An attribute in one table that is the primary key of another table
• It creates the link between the two tables

Model answer. A foreign key is an attribute in one table which holds values drawn from the primary key of another table. It is the mechanism that creates a relationship between the two, allowing each record in the first table to be associated with exactly one record in the second, and it is what makes joins possible when querying data spread across several tables.

2 Explain why a database should be normalised to third normal form. 5

One mark for each of the following, to a maximum of 5.

• Normalisation removes redundant duplicated data
• Update anomalies are avoided, since a fact is changed in only one place
• Insertion anomalies are avoided, so records can be added independently
• Deletion anomalies are avoided, so removing a record does not destroy unrelated data
• Less storage is used and data consistency is improved

Model answer. Normalising to third normal form ensures that every non key attribute depends on the key, the whole key and nothing but the key, which means each individual fact is stored in exactly one place. The most important consequence is the elimination of anomalies. Without normalisation, an update anomaly occurs when a fact stored in several rows is changed in one of them and not the others, leaving the database internally inconsistent with no indication of which copy is right. An insertion anomaly occurs when a new record cannot be added because unrelated information is missing, for example being unable to record a new course until a student has enrolled on it. A deletion anomaly occurs when removing a record destroys information that was only stored there, such as losing all record of a department because its last employee left. Normalisation also reduces the total storage required, since data is not duplicated across many rows, and it improves overall data integrity because there is only ever one authoritative copy of each fact to keep correct.

3 Write an SQL query that returns the name and course title of every student enrolled on a course, given tables Students(ID, Name), Courses(ID, Title) and Enrolments(StudentID, CourseID). 4

One mark for each of the following, to a maximum of 4.

• Selects the required columns from the correct tables
• Joins Students to Enrolments on the student ID
• Joins Enrolments to Courses on the course ID
• Uses correct SQL syntax throughout

Model answer. SELECT Students.Name, Courses.Title FROM Students INNER JOIN Enrolments ON Students.ID = Enrolments.StudentID INNER JOIN Courses ON Enrolments.CourseID = Courses.ID; The Enrolments table is the linking table resolving the many to many relationship between students and courses, so two joins are needed. The first connects each student to their enrolment records by matching the student ID, and the second connects each enrolment to the course it refers to by matching the course ID. Because the columns Name and Title come from different tables, they are qualified with their table names to avoid ambiguity.

4 Explain what is meant by the ACID properties of a transaction. 6

One mark for each of the following, to a maximum of 6.

• Atomicity means all operations in the transaction succeed or none do
• Gives an example such as a bank transfer not leaving money missing
• Consistency means the database moves from one valid state to another
• Isolation means concurrent transactions do not interfere with one another
• Durability means committed changes survive a system failure
• Together they guarantee the database remains reliable despite failures and concurrency

Model answer. Atomicity means that a transaction is indivisible: either every operation within it completes or none of them does. The standard example is transferring money between accounts, where debiting one account and crediting the other must happen together, since a failure between the two would make money simply disappear. Consistency means that a transaction takes the database from one valid state to another, with every rule, constraint and relationship still satisfied when it completes, so a transaction that would violate a foreign key constraint is rejected rather than applied. Isolation means that transactions running at the same time do not interfere with each other, and the result is as though they had run one after another in some order, which prevents one transaction reading data another has partially modified. Durability means that once a transaction has been committed, its changes are permanent and will survive a crash or power failure, which is achieved by writing to non volatile storage and maintaining a transaction log that can be replayed after a restart. Together these four properties are what allow a database to be trusted with financial and other critical data despite hardware failures and thousands of simultaneous users.

5 A table stores StudentID, StudentName, CourseID, CourseTitle and TutorName. Explain why this table is not in third normal form and describe how it should be restructured. 6

One mark for each of the following, to a maximum of 6.

• A student can take several courses, so there is a many to many relationship
• CourseTitle depends on CourseID rather than on the whole key
• This is a partial dependency violating second normal form
• TutorName depends on CourseID, which is a transitive dependency violating third normal form
• Create a Students table and a Courses table
• Create a linking table holding StudentID and CourseID as foreign keys

Model answer. The table is not in third normal form for several reasons. Because a student can take more than one course and a course has many students, the relationship is many to many, so identifying a row requires a composite key of StudentID and CourseID. Once that is recognised, StudentName depends only on StudentID and CourseTitle depends only on CourseID, so both are partially dependent on part of the composite key rather than on the whole of it, which breaks second normal form and causes the student's name and the course title to be repeated in every row. TutorName is worse still: it depends on CourseID, which is not itself the whole key, so it is a transitive dependency and breaks third normal form. The consequences are redundancy and anomalies, since changing a course title requires updating many rows and deleting the last enrolment on a course loses all record of that course and its tutor. The correct structure is three tables. A Students table holds StudentID as its primary key with StudentName. A Courses table holds CourseID as its primary key with CourseTitle and TutorName, or with a TutorID foreign key if tutors need their own table. An Enrolments linking table holds StudentID and CourseID as foreign keys, together forming its composite primary key, which resolves the many to many relationship into two one to many relationships and stores each fact exactly once.