25-Comp-B3 Data Bases and File Systems · December 2013
Nivaar worked solution (AI-drafted; not reviewed by a licensed engineer)
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.
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.
