NivaarExam PrepOfficial exam papers ↗

25-Comp-B3 Data Bases and File Systems · December 2014

Question 4 of 8: ER diagram — Toronto County Airport

Nivaar worked solution (AI-drafted; not reviewed by a licensed engineer)

Notes on this paper

98-Comp-B3, Data Bases & File Systems — National Exams, December 2014. 3 hours, closed book (calculators permitted). Candidates were instructed to answer five questions: one of Questions 1/2, one of Questions 3/4, and three of Questions 5–8 — only those five are marked. All 8 questions are answered below for completeness (this is a study resource covering the full syllabus).

Check: the source page header prints “98-Comp-B3/ December 2014” while the title block prints “National Exams May 2014” — a date inconsistency on the printed cover page. This is treated as the December 2014 exam period, matching every subsequent page header. Also, the page-1 marking scheme lists Question 8 as having two part-(c) entries (“(c) 4 marks; (c) 6 marks”); read as a mislabelled (c)/(d), matching the body text's actual four sub-parts (a)(b)(c)(d).

Reference texts: Silberschatz, Korth & Sudarshan, Database System Concepts (7th ed.) — RAID, indexing, ER modelling, normal forms, transactions and serializability; Ramakrishnan & Gehrke, Database Management Systems (3rd ed.) — B+-tree indexing, SQL, relational algebra, and concurrency control.

Question 4: ER diagram — Toronto County Airport (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Approach. The shared attributes (SIN, union membership) plus the "technicians ARE employees, traffic controllers ARE employees, but each has its own distinct extra data" phrasing is a textbook ISA (generalization/specialization) hierarchy; the 3-way testing event is naturally a ternary relationship carrying its own descriptive attributes.

EmployeeSINUnionMembershipNoTechnicianNameAddressPhoneSalaryTrafficControllerLastExamDateModelModelNumberCapacityWeightAirplaneRegistrationNoTestFAATestNoNameMaxScoreISAExpertOnOfModelPerformsTestMN1NMNN
Fig. Q4 — Toronto County Airport conceptual schema. Triangle = ISA (generalization); thick edge = total participation.

Design decisions. Employee is a superclass entity (SIN as key, UnionMembershipNo), specialized via ISA into Technician (adds Name, Address, Phone, Salary — the attributes the question attaches specifically to technicians) and TrafficController (adds LastExamDate). Model is its own entity (ModelNumber key, Capacity, Weight) rather than Airplane attributes, since capacity/weight belong to the model, not to each individual airframe; OfModel connects Airplane (N, total participation — every airplane has exactly one model) to Model (1). ExpertOn is the stated many-to-many, overlapping-allowed relationship between Technician and Model. The FAA testing event genuinely involves three participants at once (which technician tested which airplane with which test) plus its own attributes (Date, HoursSpent, Score) that belong to the combination, not to any one participant — this is modelled as the ternary relationship PerformsTest connecting Technician, Airplane and Test.

Constraints the ER diagram cannot capture. The ISA hierarchy's disjointness and completeness (can one employee be both a Technician and a TrafficController? must every Employee be one or the other?) are not stated in the problem and, even where they are known, basic Chen ER notation can only annotate a generalization as disjoint/overlapping and total/partial — it has no way to structurally enforce those constraints the way, say, a foreign-key NOT NULL enforces total participation in the relational model. Likewise, a general rule such as "a technician can only be assigned to test an airplane whose model they are ExpertOn" would require comparing attribute values across two different relationships (ExpertOn and PerformsTest), which basic ER cannot express.