IBDP Computer Science A3.2 Database design HL Paper 1 - New Syllabus

(a)

(i)
For the correct answer:

• A derived field is calculated using data that already exists within the database.
• Therefore, the derived value does not need to be entered separately and does not need to be stored, reducing unnecessary data storage.
• The value can be calculated when required, so it remains current when the underlying data changes.

Explanation: A derived field avoids storing information that can be calculated from existing data. This can reduce redundancy and ensure that the displayed value is up to date.

(ii)
For the correct answer:

• Select the patient’s DateOfBirth from the database.
• Subtract the year of birth from the current year to calculate the patient’s age.

Structured English:

Select DateOfBirth from the PATIENT table
Age = current year - year(DateOfBirth)

Example SQL:

SELECT DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS Age
FROM PATIENT;

Equivalent SQL or Structured English is acceptable.

Explanation: The patient’s DateOfBirth is an existing stored value. The current date is used together with the DateOfBirth to derive the patient’s current age.

(b)
For the correct answer:

• 1NF focuses on eliminating repeating groups, ensuring atomic values, and maintaining entity integrity.
• 2NF requires the database to already be in 1NF and additionally requires all non-key attributes to be fully functionally dependent on the whole primary key.
• Therefore, 2NF removes partial dependencies.
• 1NF applies to any table, whereas 2NF is particularly relevant to tables with composite keys.

Explanation: The main difference is that 1NF ensures that each field contains an atomic value and that repeating groups are removed. 2NF goes further by removing partial dependencies, where a non-key attribute depends on only part of a composite key.

(c)
For the correct answer, the data should be separated into appropriate relations so that the database is in 3NF.

Example 1:

PATIENT (PatientID, FirstName, Surname, PreferredName, DateOfBirth)

DEPARTMENT (DepartmentID, DepartmentName)

DOCTOR (DoctorID, FirstName, Surname, DepartmentID*)

APPOINTMENT (PatientID*, DoctorID*, Date, Time)

Here, PatientID is the primary key of PATIENT, DepartmentID is the primary key of DEPARTMENT, and DoctorID is the primary key of DOCTOR. The asterisk indicates a foreign key.

Alternative valid structure:

PATIENT (PatientID, FirstName, Surname, PreferredName, DateOfBirth)

DEPARTMENT (DepartmentID, DepartmentName)

DOCTOR (DoctorID, FirstName, Surname)

APPOINTMENT (PatientID*, DoctorID*, Date, Time, DepartmentID*)

Alternative primary key:

APPOINTMENT (AppointmentID, PatientID*, DoctorID*, Date, Time)

A single DateTime field may also be used instead of separate Date and Time fields.

Explanation: The patient details are stored once in the PATIENT table. Department information is stored separately, and doctor information is stored in the DOCTOR table. The APPOINTMENT table records which patient has an appointment with which doctor and when. Foreign keys maintain the relationships between the tables while reducing unnecessary duplication.

A LOCATION table may also be used instead of DEPARTMENT:

LOCATION (LocationID, LocationName)

with LocationID used as the corresponding foreign key.