Relational databases

(a) A database is an organised, persistent store of data, structured so that it can be searched, sorted and updated efficiently (1) (b) A table is a structure holding data about one type of thing, such as students or products (1) (c) A record is all the data about one item — one row of a table (1) (d) A field is one item of data within a record — one column of a table (1) (e) A data type specifies what kind of data a field can hold, such as integer, real, Boolean, character, string or date/time (1)

(5)

(a) 3 records — one per row of data; the header row is not a record (1) (b) 5 fields — BookID, Title, Author, YearPublished, OnLoan (1) (c) BookID (1). It uniquely identifies each record, whereas a title or author could be repeated — a library may hold two books with the same title, or many books by the same author (1) (d) YearPublished — integer (a whole number, and years are compared and subtracted). OnLoan — Boolean (only two possible values, True or False) (1)

(5)

(a) The primary key of Member is MemberID, and the primary key of Loan is LoanID (2 — 1 each). Each uniquely identifies a record in its own table.

(b) MemberID in the Loan table is a foreign key (1), because it refers to the primary key MemberID of the Member table (1). (BookID is also a foreign key, referring to the primary key of a Book table.)

(c) A foreign key records which member a loan belongs to, and one member can have many loans — here M12 appears twice, for loans L900 and L901 (1). Foreign key values therefore need not be unique, unlike primary key values, which must always be unique because each identifies exactly one record (1).

(6)

(a) Data redundancy is the same data being stored more than once unnecessarily (1). Here, "Ms Rahman" and "12" are each stored three times — once for every student she tutors — and the repetition grows with every student added (1).

(b) Data inconsistency is copies of the same data disagreeing with each other, so it is impossible to tell which is correct (1). If Ms Rahman moves to Room 9 and only two of the three rows are updated, the table then states that her room is both 9 and 12, and nothing indicates which is right (1).

(c) Create a Tutor table holding each tutor's name and room once, with TutorID as its primary key, and store TutorID as a foreign key in the Student table instead of repeating the tutor's details (1).

Redundancy is eliminated, because the room is stored once however many students share the tutor — and inconsistency then becomes impossible, because with only one copy there is nothing for it to disagree with. A room change is a single update in a single place (1).

(6)

(a) MembershipNumber — string (1). Although it looks like a number, it begins with leading zeros, which an integer type would discard, turning 004512 into 4512. No arithmetic is ever performed on a membership number, so a numeric type gains nothing (1).

(b) MonthlyFee — real (1). The value has a fractional part and is used in calculations such as annual totals, so it must be a numeric type that stores decimals.

(c) HasPaid — Boolean (1). There are only two possible values, True or False, so a Boolean is exact and uses the least storage — whereas a string could hold inconsistent values such as "Yes", "Y" and "yes" (1).

(d) DateJoined — date/time (1). This allows dates to be compared and sorted correctly and the difference between dates to be calculated. Stored as a string, dates would sort character by character, so "12/03/2011" would come before "01/04/2010" (1).

(6 max)

Owner table — fields: OwnerID (primary key), OwnerName, Address, Phone (1)

Pet table — fields: PetID (primary key), PetName, Species, DateOfBirth, OwnerID (foreign key → Owner.OwnerID) (1)

Appointment table — fields: AppointmentID (primary key), AppointmentDate, AppointmentTime, Reason, PetID (foreign key → Pet.PetID) (1)

What this solves. Previously the owner's name, address and phone number were repeated on every appointment record, which is data redundancy — the same data stored more than once unnecessarily, wasting storage and growing with every visit (1).

In the new design each owner's details are stored once, in the Owner table, and the appointment reaches them by following PetID → Pet → OwnerID → Owner (1).

Because there is only one copy, a change of address is a single update in a single place, so data inconsistency becomes impossible — there is no second copy that could disagree. The design also allows an owner or a pet to be recorded before any appointment exists (1).

(6 max)

There is something in the owner's view. A single table is quicker to set up, and every value can be read without following a link to another table — which for a very small, unchanging list would be adequate (1).

But the design creates serious problems.

• Redundancy in customer data. J Wells's name and address are already stored twice, and would be stored again on every future order — the same data stored more than once unnecessarily (1)
• Redundancy in product data. The kettle's name and price appear twice, and would repeat for every sale of that product (1)
• Inconsistency follows directly. If the kettle's price rises to £24.99 and only some rows are updated, the table would show two different prices for the same product with no way of telling which is correct. The same applies if J Wells moves house (1)
• Nothing can exist independently. A new customer cannot be recorded before their first order, and a product in stock but never sold has nowhere to be stored at all (1)
• Deleting data destroys more than intended. Deleting order O503 would remove the only stored copy of R Patel's address (1)

Recommendation: three tables.

• Customer — CustomerID (PK), Name, Address
• Product — ProductID (PK), ProductName, Price
• Order — OrderID (PK), OrderDate, CustomerID (FK), ProductID (FK) (1)

Each customer and each product is then stored exactly once, so a price change or a change of address is one update, inconsistency becomes impossible, and customers and products can exist independently of any order.

On the simplicity objection: joining tables is extra work, but it is written once in a query, whereas the problems above recur on every single update — so the relational design is simpler in practice even though it looks more complicated on paper (1).

(8)

The requirements. A primary key must uniquely identify each record, must never change (because other tables refer to it), and must never be empty (1).

FullName — unsuitable. Names are not unique: in a hospital of thousands, two patients called the same thing is a certainty, and the records could then not be told apart (1). Names also change — on marriage or by deed poll — and changing a primary key would break every other record that refers to it (1).

DateOfBirth — clearly unsuitable. It fails uniqueness very badly, since hundreds of patients share any given birth date (1).

EmailAddress — closer, but still unsuitable. It is usually unique, but it changes when a patient switches provider, it may be empty because not every patient has one, and families sometimes share a single address — so uniqueness cannot be guaranteed either (1).

PatientNumber — the correct choice. Because it is generated by the system, it can be guaranteed unique, is never reused, is never empty, and there is no reason it would ever need to change, since it carries no real-world meaning that could become out of date (1).

Why stability matters most here. Other tables — appointments, prescriptions, test results — will hold PatientNumber as a foreign key. If the key could change, every one of those references would have to be found and updated together, and any that were missed would point to a patient that no longer exists (1).

Conclusion: use PatientNumber. The real-world fields remain useful for searching — staff will look patients up by name and date of birth — but searching does not require a field to be the primary key (1).

(8)

Data redundancy. Redundancy is the same data stored more than once unnecessarily (1). In a relational database each fact is stored once, in the table where it belongs, and other tables refer to it with a foreign key rather than repeating it — so a tutor's room or a product's price exists in exactly one place (1).

Data inconsistency. Inconsistency is copies of the same data disagreeing with each other, leaving no way to tell which is correct (1). Removing redundancy removes inconsistency as a possibility, not just as a risk: with only one copy of each fact, there is nothing for it to disagree with (1).

Data integrity. Because a foreign key must match an existing primary key value, the database can reject a reference to something that does not exist — an order cannot be recorded against a customer who is not in the Customer table (1).

Updating. A change is one update in one place rather than a search for every copy. Updating a single row cannot be partially completed, whereas updating forty rows can be (1).

Storage. Repeated text is replaced by a short key: storing "T07" in a thousand student records uses far less space than storing a tutor's name and room a thousand times (1).

Further benefits. The design is easier to extend — adding a field to one table does not disturb the others — and different access rights can be applied to different tables, so staff data can be restricted separately from student data (1).

(8)