AS & A Level Computer Science (9618)
Databases Banker
9 questions · about 6% of the marks we indexed · examined in 9 of 9 sessions.
Practice questions
Original questions written by HomiLabs in the style of the exam, with answers and mark schemes. They are not
Cambridge past paper questions and have not been reviewed by Cambridge. Try each one before opening the answer.
Q1SQL queries (DML) · Core · Paper 1 style · 7 marksA clinic in Faisalabad stores patient data in the table PATIENT. Some of the data is shown.PATIENTPatientID | FirstName | LastName | City | BloodGroup | AgeP101 | Ayesha | Khan | Lahore | O+ | 34P102 | Bilal | Ahmed | Faisalabad | A+ | 51P103 | Sana | Raza | Lahore | B+ | 27P104 | Hamza | Qureshi | Faisalabad | O+ | 45P105 | Mehwish | Iqbal | Multan | O+ | 62P106 | Usman | Tariq | Lahore | O+ | 19P107 | Zara | Malik | Faisalabad | AB- | 38
- (a) Write an SQL query to return the first name and last name of every patient with blood group O+ who is older than 30. List the patients with the oldest first. [4]
- (b) Write down the output of the query from part (a) when it is run on the data shown. [2]
- (c) State the output of this query:SELECT COUNT(PatientID) FROM PATIENT WHERE City = 'Lahore'; [1]
Show answer
- (a) SELECT FirstName, LastNameFROM PATIENTWHERE BloodGroup = 'O+' AND Age > 30ORDER BY Age DESC;
1 mark each: SELECT FirstName, LastName; FROM PATIENT; WHERE BloodGroup = 'O+' AND Age > 30 (both conditions); ORDER BY Age DESC.
- (b) Mehwish Iqbal; Hamza Qureshi; Ayesha Khan (in this order).
1 mark for the correct three patients only, 1 mark for the correct order.
- (c) 3
B1: 3 (P101, P103, P106).
A school library in Nairobi uses the table:BOOK(BookID, Title, Author, YearPublished, OnLoan)BookID is a string such as 'B0123'. YearPublished is an integer. OnLoan is a Boolean.
- (a) A new book has arrived: BookID B0457, title 'Ocean Engineers', author 'L. Marsh', 2023, available. Write an SQL statement to add this book to the table. [3]
- (b) Book B0219 has just been borrowed. Write an SQL statement to record this. [2]
- (c) All books published before 1990 are being given to another library. Write an SQL statement to remove them from the table. [2]
Show answer
- (a) INSERT INTO BOOK (BookID, Title, Author, YearPublished, OnLoan)VALUES ('B0457', 'Ocean Engineers', 'L. Marsh', 2023, FALSE);
1 mark INSERT INTO BOOK (with or without field list); 1 mark VALUES with all five values in the correct order; 1 mark strings in quotes and Boolean/number not in quotes.
- (b) UPDATE BOOKSET OnLoan = TRUEWHERE BookID = 'B0219';
1 mark UPDATE BOOK SET OnLoan = TRUE; 1 mark WHERE BookID = 'B0219'.
- (c) DELETE FROM BOOKWHERE YearPublished < 1990;
1 mark DELETE FROM BOOK; 1 mark WHERE YearPublished < 1990.
A school in Quetta has these two tables:STUDENT(StudentID, StudentName, ClassID)CLASS(ClassID, TeacherName, Room)
- (a) Write an SQL query to output the name of each student together with the room of that student's class, for all students in classes taught by 'Mr Siddiqui'. [4]
- (b) Explain why the attribute ClassID is needed in both tables. [2]
Show answer
- (a) SELECT STUDENT.StudentName, CLASS.RoomFROM STUDENTINNER JOIN CLASS ON STUDENT.ClassID = CLASS.ClassIDWHERE CLASS.TeacherName = 'Mr Siddiqui';
1 mark SELECT StudentName, Room; 1 mark FROM STUDENT INNER JOIN CLASS; 1 mark ON STUDENT.ClassID = CLASS.ClassID; 1 mark WHERE TeacherName = 'Mr Siddiqui'. Accept FROM STUDENT, CLASS WHERE STUDENT.ClassID = CLASS.ClassID AND …
- (b) ClassID is the primary key of CLASS and a foreign key in STUDENT; it links each student to one class so the tables can be joined.
1 mark: primary key in CLASS / foreign key in STUDENT; 1 mark: creates the relationship/link so data from both tables can be combined (joined) without storing teacher and room for every student.
A bakery in Karachi records sales in the table SALE.SALESaleID | Item | QuantityS01 | Naan | 40S02 | Rusk | 15S03 | Naan | 25S04 | Cake | 6S05 | Rusk | 20S06 | Naan | 35S07 | Cake | 4
- (a) Write the output of this query.SELECT Item, SUM(Quantity)FROM SALEGROUP BY ItemORDER BY Item; [3]
- (b) Write an SQL query to output the number of sales (rows) recorded for the item 'Naan'. [2]
- (c) Write an SQL query to output the average quantity per sale of 'Rusk'. [2]
Show answer
- (a) Cake 10Naan 100Rusk 35
1 mark for each correct row (Cake 10, Naan 100, Rusk 35) in alphabetical order; deduct 1 if order wrong.
- (b) SELECT COUNT(SaleID)FROM SALEWHERE Item = 'Naan';
1 mark SELECT COUNT(…) FROM SALE; 1 mark WHERE Item = 'Naan'. (Output would be 3.)
- (c) SELECT AVG(Quantity)FROM SALEWHERE Item = 'Rusk';
1 mark SELECT AVG(Quantity) FROM SALE; 1 mark WHERE Item = 'Rusk'. (Output would be 17.5.)
A weather service stores monthly rainfall readings for one month each year.READINGReadingID | Station | YearTaken | RainfallMMM1 | Lahore | 2023 | 18.4M2 | Murree | 2023 | 41.2M3 | Lahore | 2024 | 22.6M4 | Karachi | 2024 | 5.8M5 | Murree | 2024 | 36.0M6 | Karachi | 2023 | 7.4
- (a) Write an SQL query to output the station and the rainfall for every reading taken in 2024 with more than 20 mm of rain. [3]
- (b) The query SELECT AVG(RainfallMM) FROM READING WHERE YearTaken = 2024; is run. Calculate the value output, correct to 2 decimal places. [2]
- (c) Write an SQL query to output each station once, with the total rainfall recorded at that station. [3]
Show answer
- (a) SELECT Station, RainfallMMFROM READINGWHERE YearTaken = 2024 AND RainfallMM > 20;
1 mark SELECT Station, RainfallMM; 1 mark FROM READING; 1 mark WHERE YearTaken = 2024 AND RainfallMM > 20.
- (b) 21.47
M1: (22.6 + 5.8 + 36.0) ÷ 3; A1: 21.47 (64.4 ÷ 3 = 21.466…).
- (c) SELECT Station, SUM(RainfallMM)FROM READINGGROUP BY Station;
1 mark SELECT Station, SUM(RainfallMM); 1 mark FROM READING; 1 mark GROUP BY Station.
A sports club in Singapore uses three tables:MEMBER(MemberID, MemberName, JoinDate)COURT(CourtID, Surface, Indoor)BOOKING(BookingID, MemberID, CourtID, BookingDate, StartTime)Indoor is a Boolean field.
- (a) Write an SQL query to output the name of each member and the number of bookings that member has made, for bookings of courts that are indoor. Output the members with the most bookings first. [6]
- (b) Explain why GROUP BY is needed in your query. [2]
Show answer
- (a) SELECT MEMBER.MemberName, COUNT(BOOKING.BookingID)FROM MEMBERINNER JOIN BOOKING ON MEMBER.MemberID = BOOKING.MemberIDINNER JOIN COURT ON BOOKING.CourtID = COURT.CourtIDWHERE COURT.Indoor = TRUEGROUP BY MEMBER.MemberNameORDER BY COUNT(BOOKING.BookingID) DESC;
1 mark each: SELECT MemberName, COUNT(BookingID); join MEMBER to BOOKING on MemberID; join BOOKING to COURT on CourtID; WHERE Indoor = TRUE; GROUP BY MemberName (or MemberID); ORDER BY COUNT(…) DESC. Accept joins written as conditions in WHERE.
- (b) Without GROUP BY, COUNT would give one total for all rows; GROUP BY makes a separate count for each member.
1 mark: aggregate function COUNT combines rows; 1 mark: GROUP BY makes one result row (count) per member instead of a single overall count.
An airline at Islamabad airport wants a list of flight numbers, destinations and departure times for flights to Dubai that still have seats, earliest first. A trainee wrote:SELECT FlightNo Destination, DepartTimeFROM FLIGHTORDER BY DepartTime ASCENDINGWHERE Destination = Dubai AND Seats > 0;The table is FLIGHT(FlightNo, Destination, DepartTime, Seats).
- (a) The script contains four errors. Identify three of the errors and write the fully corrected script. [6]
Show answer
- (a) Any three of the four errors: (1) SELECT list items must be separated by commas (FlightNo, Destination); (2) the condition is written 'Destination = Dubai' without quotes – a string must be in quotes 'Dubai'; (3) ORDER BY is written before WHERE – WHERE must come before ORDER BY. (4) ASCENDING is not valid – use ASC or omit it.Corrected:SELECT FlightNo, Destination, DepartTimeFROM FLIGHTWHERE Destination = 'Dubai' AND Seats > 0ORDER BY DepartTime;
1 mark for each error identified (max 3): comma missing between FlightNo and Destination; 'Dubai' not in quotes; clause order (WHERE must come before ORDER BY); 'ASCENDING' should be ASC or omitted. 3 marks for the corrected script: 1 mark SELECT list with commas; 1 mark WHERE Destination = 'Dubai' AND Seats > 0 placed before ORDER BY; 1 mark ORDER BY DepartTime (or DepartTime ASC).
A software company stores staff data.STAFFStaffID | Name | Department | Salary | OfficeE1 | Rahim | Sales | 52000 | DhakaE2 | Leila | IT | 61000 | KarachiE3 | Tom | IT | 48000 | DhakaE4 | Nadia | Sales | 45000 | KarachiE5 | Imran | HR | 39000 | KarachiE6 | Mei | IT | 70000 | Dhaka
- (a) State the output of this query.SELECT Name FROM STAFFWHERE Department = 'IT' OR Salary > 50000ORDER BY Salary DESC; [2]
- (b) State the output of this query.SELECT Name FROM STAFFWHERE Department = 'IT' AND Office = 'Dhaka' OR Salary < 40000ORDER BY Name; [2]
- (c) Write an SQL statement to give every member of the Sales department a pay rise of 2000. [3]
Show answer
- (a) Mei, Leila, Rahim, Tom
1 mark correct four names only (Rahim is included because Salary > 50000; Tom because Department = 'IT'); 1 mark correct order by salary descending.
- (b) Imran, Mei, Tom
AND is applied before OR. 1 mark correct three names (Tom and Mei: IT in Dhaka; Imran: salary < 40000); 1 mark alphabetical order.
- (c) UPDATE STAFFSET Salary = Salary + 2000WHERE Department = 'Sales';
1 mark UPDATE STAFF; 1 mark SET Salary = Salary + 2000; 1 mark WHERE Department = 'Sales'.
A secondary school in Hyderabad stores its data in separate files.
- (a) The school's fee office and exam office each keep their own file containing students' names and addresses. Describe two problems this file-based approach can cause. [4]
- (b) State how a relational database would reduce one of these problems. [1]
Show answer
- (a) Data redundancy: the same student data is stored twice, wasting storage. Data inconsistency: if a family moves and only one office updates the address, the two files disagree.
Max 2 problems, 2 marks each (identify + describe in context): data redundancy – same data stored in several files; data inconsistency – copies updated in one file but not another; data dependence – programs tied to file structure so changing a file means rewriting programs; difficult to share / search across files.
- (b) Student details are stored once in one table and linked to other tables using keys, so there is only one copy to update.
B1: data stored once / in one table, linked by primary–foreign keys.
A hospital in Peshawar uses a relational DBMS.
- (a) State what is meant by a data dictionary. [1]
- (b) Give three items that would be stored in the data dictionary for this database. [3]
- (c) Explain what is meant by the logical schema of a database. [2]
Show answer
- (a) A file/table, managed by the DBMS, that stores metadata – data about the data in the database.
B1: stores metadata / data about the data (descriptions of the database structure).
- (b) Any three of: table names; field (attribute) names; data types of fields; field lengths; primary keys; foreign keys / relationships; validation rules; indexes; access rights of users.
1 mark each for any three valid items.
- (c) A model of the whole database showing its tables, attributes, keys and relationships, independent of how the data is physically stored.
1 mark: overall view/model of the structure (tables, fields, relationships); 1 mark: independent of the physical storage / DBMS software.
A private medical centre in Islamabad stores patient records, appointments and diagnoses in a database.
- (a) Describe three ways the DBMS can be used to keep the patient data secure. [6]
- (b) Explain why receptionists should be given different access rights from doctors. [2]
Show answer
- (a) Access rights: each user group (e.g. receptionists) is given permission to read/edit only some tables, so receptionists cannot see diagnoses. Usernames and passwords: only authorised staff can log in to the database. Backups: regular copies of the database are made (e.g. nightly) so data can be restored after loss.
2 marks per method (identify + describe in context), max 3 methods: access rights/user privileges; usernames and passwords; encryption of stored data; regular backups; audit trail/logging of access; views limiting what a user sees.
- (b) Receptionists only need contact and appointment details; restricting them protects confidential medical data and reduces the chance of accidental or malicious changes.
1 mark: they only need some of the data (appointments/contact details); 1 mark: medical records are confidential / prevents unauthorised viewing or changing.
A developer is building a stock database for an electronics shop in Dubai.
- (a) Describe the purpose of the developer interface of a DBMS. [2]
- (b) Describe the purpose of the query processor of a DBMS. [2]
- (c) Give one reason why the developer would use SQL directly instead of the developer interface. [1]
Show answer
- (a) It lets a developer create and manipulate the database structure and objects (tables, queries, forms, reports) without writing all the code, e.g. using design views and drag-and-drop tools.
1 mark: allows developers to create/design tables, queries, forms, reports; 1 mark: by a visual interface / without writing low-level code / with wizards.
- (b) It takes a query (e.g. in SQL), checks and optimises it and then executes it on the database, returning the results.
1 mark: receives/interprets queries (SQL); 1 mark: optimises and executes them, searching the data and returning results.
- (c) Any one: SQL can be embedded in a program/website; complex queries are easier to write precisely; SQL scripts can be saved and re-run; works on any relational DBMS.
B1 any valid reason.
A car-rental firm in Lahore has the tables CUSTOMER(CustomerID, Name, Phone) and RENTAL(RentalID, CustomerID, CarReg, StartDate).
- (a) Explain what is meant by referential integrity. [2]
- (b) The DBMS enforces referential integrity. Describe two actions that would be refused. [4]
Show answer
- (a) Every foreign key value must match an existing primary key value in the linked table (or be null), so no record refers to a record that does not exist.
1 mark: foreign key value must exist as a primary key in the related table; 1 mark: prevents records referring to non-existent records / orphan records.
- (b) Adding a RENTAL record with a CustomerID that is not in CUSTOMER would be refused. Deleting a CUSTOMER record while RENTAL records still contain that CustomerID would be refused.
2 marks each: inserting a child (RENTAL) record whose foreign key does not exist in the parent table; deleting/changing a parent (CUSTOMER) primary key that is still used by child records.
A regional cricket league in Sialkot keeps player details, team lists, match scores and player medical notes in several spreadsheets, one per team. Many players appear in more than one spreadsheet. Results are also shown on a public website.
- (a) Explain how using a relational database managed by a DBMS will improve data integrity, data privacy and data independence for the league, compared with the spreadsheets. Refer to the scenario. [6]
Show answer
- (a) Integrity: each player is stored once in a PLAYER table and linked by PlayerID, so there are no conflicting copies; validation rules and referential integrity stop scores being recorded for non-existent players. Privacy: access rights mean coaches can view only their own team's medical data, and the public website account can only read results. Independence: the data dictionary/schema separates the data from the programs, so a new field (e.g. strike rate) can be added without rewriting the scoring and website programs.
Up to 2 marks for each of the three areas (1 for the DBMS feature, 1 for linking it to the cricket scenario): integrity – single copy, validation, referential integrity; privacy – access rights, passwords, views, encryption; independence – structure held separately in schema/data dictionary so applications unaffected by changes.
A vehicle-licensing office stores data about vehicles and their owners in two tables, VEHICLE and OWNER(OwnerID, Name, Address).
- (a) Define each of these terms: entity, tuple, attribute. [3]
- (b) Identify the primary key of the table VEHICLE(RegNo, Make, Model, Colour, OwnerID) and justify your choice. [2]
- (c) Identify the foreign key in VEHICLE and state the table it links to. [2]
Show answer
- (a) Entity: a thing about which data is stored (e.g. a vehicle). Tuple: one row/record in a table. Attribute: one field/column in a table holding one item of data.
1 mark each.
- (b) RegNo – every vehicle has a unique registration number.
1 mark RegNo; 1 mark it is unique for each vehicle/record.
- (c) OwnerID; it links to the OWNER table (where OwnerID is the primary key).
1 mark OwnerID; 1 mark links to OWNER / primary key in owner table.
A bank in Kenya uses three tables:BRANCH(BranchCode, Town, ManagerName)ACCOUNT(AccountNo, HolderName, BranchCode, Balance)TRANSACTION(TransID, AccountNo, TransDate, Amount)
- (a) Identify the primary key of each table. [3]
- (b) Identify the foreign keys, and for each one name the table where it is the primary key. [2]
Show answer
- (a) BRANCH: BranchCode; ACCOUNT: AccountNo; TRANSACTION: TransID.
1 mark each.
- (b) ACCOUNT.BranchCode → BRANCH; TRANSACTION.AccountNo → ACCOUNT.
1 mark each.
A university in Lahore stores staff data in the table STAFF(StaffID, CNIC, Email, FirstName, LastName, Department). Each member of staff has a unique StaffID, a unique CNIC number and a unique email address.
- (a) Explain what is meant by a candidate key. Use this table to illustrate your answer. [2]
- (b) StaffID has been chosen as the primary key. State the term for a candidate key that is not chosen as primary key, and give one example from the table. [2]
- (c) Explain why the database designer might create a secondary key (index) on LastName. [2]
Show answer
- (a) An attribute (or smallest set of attributes) that could uniquely identify every record and so could be chosen as the primary key; here StaffID, CNIC and Email are all candidate keys.
1 mark: attribute(s) that could be a primary key / uniquely identify each tuple; 1 mark: example StaffID or CNIC (or Email).
- (b) Alternate key; CNIC (or Email).
1 mark alternate key; 1 mark CNIC/Email.
- (c) Staff are often searched by last name; an index on LastName lets the DBMS find matching records quickly without scanning the whole table.
1 mark: LastName is frequently used to search/sort; 1 mark: index makes searching faster. Note LastName is not unique, so it cannot be a primary key.
An exam centre stores:STUDENT(StudentID, Name, DateOfBirth)EXAM(ExamID, Subject, ExamDate)RESULT(StudentID, ExamID, Mark)Each student takes each exam at most once.
- (a) Explain why ExamID alone is not a suitable primary key for RESULT. [2]
- (b) Identify a composite primary key for RESULT and explain why it is suitable. [2]
- (c) State the relationship between STUDENT and RESULT, and between EXAM and RESULT. [2]
Show answer
- (a) The same exam is taken by many students, so ExamID repeats in many rows and cannot uniquely identify a record.
1 mark ExamID values repeat; 1 mark so not unique for each row.
- (b) (StudentID, ExamID): each student takes each exam once, so the pair is unique.
1 mark StudentID + ExamID; 1 mark the combination is unique.
- (c) STUDENT to RESULT is one-to-many; EXAM to RESULT is one-to-many.
1 mark each.
An online shop in Doha has:PRODUCT(ProductID, Description, Price) containing products K10, K11 and K12ORDERS(OrderID, CustomerID, OrderDate) containing orders O77 and O78ORDERLINE(OrderID, ProductID, Quantity), which is empty before the statements below are run in order.
- (a) Referential integrity is enforced. For each SQL statement, state whether it is accepted or refused and give a reason.(i) INSERT INTO ORDERLINE VALUES ('O77', 'K12', 2);(ii) INSERT INTO ORDERLINE VALUES ('O78', 'K30', 1);(iii) DELETE FROM PRODUCT WHERE ProductID = 'K12'; [3]
- (b) Suggest one change to the data that would allow statement (iii) to be accepted. [1]
Show answer
- (a) (i) Accepted – K12 exists in PRODUCT (and O77 exists in ORDERS). (ii) Refused – there is no product K30 in PRODUCT. (iii) Refused – order line O77/K12 still refers to product K12.
1 mark each with reason.
- (b) Delete (or change) all ORDERLINE records that refer to product K12 first.
B1.
A gym membership table is shown.MEMBERMemberNo | FirstName | LastName | Email | PhoneA01 | Hira | Farooq | hira.f@mail.pk | 0300-111A02 | Ali | Shah | ali.s@mail.pk | 0300-222A03 | Hira | Shah | hshah@mail.pk | 0300-333A04 | Kamran | Farooq | kf@mail.pk | 0300-222
- (a) From the data shown, identify which single attributes could be candidate keys. Give a reason for each attribute you reject. [4]
- (b) Explain why a database designer cannot decide that an attribute is a candidate key just by looking at the current data. [2]
Show answer
- (a) Candidate keys: MemberNo and Email (all values are unique and every member has their own). Rejected: FirstName – 'Hira' appears twice; LastName – 'Farooq' and 'Shah' repeat; Phone – '0300-222' is shared by two members (a family phone).
1 mark MemberNo and Email identified; 1 mark FirstName rejected (repeat); 1 mark LastName rejected (repeat); 1 mark Phone rejected (two members share a number).
- (b) The current data may happen to be unique, but future records could repeat the value; the designer must know the rule in the real world (e.g. emails must be unique) that guarantees uniqueness.
1 mark: current values unique by chance / more data will be added; 1 mark: must be guaranteed unique by the business rule / real-world meaning.
A hospital in Karachi is designing a database.
- (a) For each pair of entities, state the type of relationship (one-to-one, one-to-many or many-to-many).(i) DEPARTMENT and DOCTOR: each doctor works in one department; a department has many doctors.(ii) DOCTOR and PATIENT: a doctor treats many patients; a patient can be treated by many doctors.(iii) WARD and BED: a bed belongs to one ward; a ward has many beds. [3]
- (b) Describe how the one-to-many relationship between WARD and BED is shown in an E-R diagram. [2]
Show answer
- (a) (i) one-to-many (DEPARTMENT to DOCTOR). (ii) many-to-many. (iii) one-to-many (WARD to BED).
1 mark each.
- (b) A line joins the WARD and BED boxes; the end at BED has a crow's foot (many) and the end at WARD a single line (one).
1 mark line connecting the two entity boxes; 1 mark crow's foot at the BED (many) end only.
An online learning platform used across South Asia has STUDENT(StudentID, Name, Country) and COURSE(CourseCode, Title, Hours). A student enrols on many courses and a course has many students.
- (a) Explain why the many-to-many relationship between STUDENT and COURSE cannot be implemented directly in a relational database. [2]
- (b) Describe how the design should be changed. Give the attributes and primary key of any new table and describe the new relationships. [4]
Show answer
- (a) A field can only hold one value, so a student record cannot hold a list of courses (or a course a list of students) without repeating groups / redundancy.
1 mark: attributes are atomic – cannot store several foreign keys in one field; 1 mark: would cause repeating groups/redundant rows.
- (b) Add a link entity ENROLMENT(StudentID, CourseCode, StartDate) with composite primary key (StudentID, CourseCode), each part being a foreign key. STUDENT to ENROLMENT is one-to-many; COURSE to ENROLMENT is one-to-many.
1 mark new link table; 1 mark contains StudentID and CourseCode as foreign keys; 1 mark composite primary key (StudentID, CourseCode); 1 mark two one-to-many relationships replace the many-to-many.
A ride-hailing company in Islamabad: each driver registers exactly one car and each car belongs to exactly one driver. A driver makes many rides. Each ride has one passenger, and a passenger can make many rides.
- (a) Describe the relationships that should appear in the E-R diagram for DRIVER, CAR, RIDE and PASSENGER, stating the degree of each. [4]
- (b) Identify the foreign keys that the RIDE table must contain. [2]
Show answer
- (a) DRIVER–CAR: one-to-one (each driver registers one car, each car has one driver). DRIVER–RIDE: one-to-many. PASSENGER–RIDE: one-to-many. CAR–RIDE: one-to-many (optional; implied through driver).
1 mark each for DRIVER–CAR 1:1, DRIVER–RIDE 1:M, PASSENGER–RIDE 1:M; 1 mark for showing RIDE as the 'many' side holding the foreign keys DriverID and PassengerID.
- (b) DriverID and PassengerID.
1 mark each.
A wildlife park near Lahore wants a database. Each animal has an ID, a name and a date of birth, belongs to one species and lives in one enclosure. Each species has a common name and a conservation status. Each enclosure has an ID, a zone and an area in m². Each keeper has an ID, a name and a phone number. A keeper looks after many animals and each animal is looked after by several keepers.
- (a) Design a set of tables in third normal form for this scenario. Give each table in the form TABLE(Attribute1, Attribute2, …), show the primary key by writing it in capitals, and describe every relationship in the E-R diagram in words. [8]
Show answer
- (a) SPECIES(SPECIESID, CommonName, ConservationStatus)ENCLOSURE(ENCLOSUREID, Zone, AreaM2)ANIMAL(ANIMALID, Name, DateOfBirth, SpeciesID, EnclosureID)KEEPER(KEEPERID, KeeperName, Phone)CARES(KEEPERID, ANIMALID) – link tableRelationships: SPECIES 1–M ANIMAL; ENCLOSURE 1–M ANIMAL; KEEPER 1–M CARES; ANIMAL 1–M CARES (resolving KEEPER M–M ANIMAL).
1 mark each to max 8: SPECIES table with key; ENCLOSURE table with key; ANIMAL table with key; SpeciesID and EnclosureID as foreign keys in ANIMAL; KEEPER table with key; link table for keepers and animals; composite key of link table; correct description of all relationships (1:M each, M:M resolved). Accept CommonName as the primary key of SPECIES (with CommonName as the foreign key in ANIMAL) if justified as unique.
- (a) State the condition for a table to be in first normal form (1NF). [1]
- (b) State the extra condition for a table in 1NF to be in second normal form (2NF). [2]
- (c) State the extra condition for a table in 2NF to be in third normal form (3NF). [2]
Show answer
- (a) It has no repeating groups of attributes (each field holds a single atomic value).
B1.
- (b) It has no partial dependencies: every non-key attribute depends on the whole of the primary key, not on just part of a composite key.
1 mark depends on the whole key; 1 mark no partial key dependency (relevant to composite keys).
- (c) It has no non-key (transitive) dependencies: no non-key attribute depends on another non-key attribute.
1 mark no non-key dependencies; 1 mark every non-key attribute depends only on the key.
A community centre in Abu Dhabi stores members and the courses they attend:MEMBERCOURSES(MemberID, MemberName, Phone, Courses)Example row: M17, Faisal Haider, 050-1234567, 'C3 Arabic; C5 First Aid; C9 Chess'Each course has a code and a title.
- (a) Explain why the table is not in 1NF. [1]
- (b) Show the data in 1NF and 2NF. Write each table in the form TABLE(Attribute1, …), with the primary key in capitals. [4]
Show answer
- (a) It has a repeating group – the Courses attribute holds several course codes in one field.
B1.
- (b) 1NF: ENROL(MEMBERID, COURSECODE, MemberName, Phone, CourseTitle)2NF: MEMBER(MEMBERID, MemberName, Phone); COURSE(COURSECODE, CourseTitle); MEMBERCOURSE(MEMBERID, COURSECODE)
1 mark 1NF table with composite key MemberID + CourseCode; 1 mark MEMBER table; 1 mark COURSE table; 1 mark link table with composite key.
A spare-parts dealer in Gujranwala stores invoices as:SALES(InvoiceNo, InvoiceDate, CustomerID, CustomerName, CustomerCity, PartNo, PartName, UnitPrice, Quantity)One invoice can contain many parts; the quantity is for that part on that invoice.
- (a) The table is in 1NF with primary key (InvoiceNo, PartNo). Identify the partial dependencies and the transitive (non-key) dependency. [3]
- (b) Normalise the data to 3NF. Write each table in the form TABLE(Attribute1, …), with the primary key in capitals, and identify the foreign keys. [5]
Show answer
- (a) Partial: InvoiceDate, CustomerID, CustomerName and CustomerCity depend on InvoiceNo only; PartName and UnitPrice depend on PartNo only. Transitive: CustomerName and CustomerCity depend on CustomerID (a non-key attribute).
1 mark invoice attributes depend on InvoiceNo only; 1 mark part attributes depend on PartNo only; 1 mark CustomerName/CustomerCity depend on CustomerID.
- (b) INVOICE(INVOICENO, InvoiceDate, CustomerID) – CustomerID foreign keyCUSTOMER(CUSTOMERID, CustomerName, CustomerCity)PART(PARTNO, PartName, UnitPrice)INVOICELINE(INVOICENO, PARTNO, Quantity) – both parts are foreign keys
1 mark each table with correct key (4); 1 mark foreign keys correctly identified. Quantity must stay with the composite key.
A tourism board is building a database of heritage sites such as Lahore Fort and Mohenjo-daro.
- (a) Write the SQL to create the database HERITAGE. [1]
- (b) Write the SQL to create the table SITE with these fields: SiteID (exactly 5 characters, primary key), SiteName (up to 40 characters), City (up to 25 characters), YearBuilt (whole number), EntryFee (a decimal number), UNESCO (true/false). [5]
Show answer
- (a) CREATE DATABASE HERITAGE;
B1.
- (b) CREATE TABLE SITE( SiteID CHARACTER(5), SiteName VARCHAR(40), City VARCHAR(25), YearBuilt INTEGER, EntryFee REAL, UNESCO BOOLEAN, PRIMARY KEY (SiteID));
1 mark CREATE TABLE SITE( … ); 1 mark SiteID CHARACTER(5); 1 mark VARCHAR for SiteName and City with lengths; 1 mark INTEGER, REAL and BOOLEAN types correct; 1 mark PRIMARY KEY (SiteID).
A railway booking system has the tables TRAIN(TrainNo, TrainName, Coaches) and JOURNEY(JourneyID, TrainNo, TravelDate, DepartureTime).
- (a) Write the SQL to add a new field Platform, a whole number, to the table TRAIN. [2]
- (b) The table JOURNEY was created without keys. Write SQL to make JourneyID its primary key and TrainNo a foreign key that refers to TRAIN. [4]
- (c) State one data type suitable for DepartureTime and one suitable for TravelDate. [2]
Show answer
- (a) ALTER TABLE TRAIN ADD Platform INTEGER;
1 mark ALTER TABLE TRAIN; 1 mark ADD Platform INTEGER.
- (b) ALTER TABLE JOURNEY ADD PRIMARY KEY (JourneyID);ALTER TABLE JOURNEY ADD FOREIGN KEY (TrainNo) REFERENCES TRAIN(TrainNo);
1 mark ALTER TABLE JOURNEY ADD PRIMARY KEY; 1 mark (JourneyID); 1 mark ADD FOREIGN KEY (TrainNo); 1 mark REFERENCES TRAIN(TrainNo).
- (c) DepartureTime: TIME; TravelDate: DATE.
1 mark each.
A city air-quality project in Lahore records PM2.5 readings. Each sensor has a 4-character ID, a location (up to 30 characters) and the date it was installed. Each reading records the sensor ID, the date, the time, the PM2.5 value (decimal) and whether an alert was sent. A sensor takes at most one reading at any date and time.
- (a) Write the SQL DDL to create both tables, including all primary and foreign keys and suitable data types. [6]
- (b) Write an SQL query to output the location and PM2.5 value of every reading on 14/11/2026 where an alert was sent, highest PM2.5 first. [4]
Show answer
- (a) CREATE TABLE SENSOR( SensorID CHARACTER(4), Location VARCHAR(30), Installed DATE, PRIMARY KEY (SensorID));CREATE TABLE AIRREADING( SensorID CHARACTER(4), ReadDate DATE, ReadTime TIME, PM25 REAL, AlertSent BOOLEAN, PRIMARY KEY (SensorID, ReadDate, ReadTime), FOREIGN KEY (SensorID) REFERENCES SENSOR(SensorID));
1 mark SENSOR table structure; 1 mark SENSOR primary key; 1 mark AIRREADING fields with DATE/TIME/REAL/BOOLEAN; 1 mark composite primary key (SensorID, ReadDate, ReadTime); 1 mark FOREIGN KEY (SensorID); 1 mark REFERENCES SENSOR(SensorID). SENSOR must be created first.
- (b) SELECT SENSOR.Location, AIRREADING.PM25FROM SENSORINNER JOIN AIRREADING ON SENSOR.SensorID = AIRREADING.SensorIDWHERE AIRREADING.ReadDate = '14/11/2026' AND AIRREADING.AlertSent = TRUEORDER BY AIRREADING.PM25 DESC;
1 mark SELECT Location, PM25; 1 mark join on SensorID; 1 mark both WHERE conditions; 1 mark ORDER BY PM25 DESC. Accept any sensible date format, e.g. #14/11/2026#.
