This content can't be printed from the browser. Use Export PDF with Pro for an offline copy.
Question 1
Fernway Watch coordinates volunteers who inspect walking routes. It records each volunteer's VolunteerID and name, and each route's RouteID and title; route identifiers are digit-only codes that may begin with zero.
A volunteer may inspect several routes, and a route may be inspected by several volunteers. Only one inspection is recorded for a particular volunteer–route pair. The inspection date and minutes spent are stored for that pair.
Outline why a character or text type is appropriate for RouteID.
[2]
State two ways in which keys help maintain the integrity of Fernway Watch's records.
[2]
VolunteerID is stored as an integer in the volunteer records. Explain why its corresponding field in inspection records should use a compatible integer type.
[3]
Construct Fernway Watch's relational table definitions from these requirements. Include the recorded attributes, mark all primary keys, and identify the referenced fields for foreign keys.
[5]
Question 2
Copper Finch Theatre is designing a database for productions, halls and scheduled performances. A production has ProductionID and Title; a hall has HallID, HallName and Capacity; a performance has PerformanceID, StartDateTime, TicketPrice and Cancelled.
A production can be registered before any performances are scheduled. Every performance presents exactly one production and takes place in exactly one hall. Each production may have many performances, and each hall may accommodate many performances over time, including none.
Outline what a conceptual schema would represent for Copper Finch Theatre.
[2]
Identify two suitable non-text data types for the theatre's attributes. For each type, name an attribute it could represent.
[2]
Explain why theatre staff should check relationship modality in the proposed ERD before the tables are implemented.
[3]
Construct Copper Finch Theatre's ERD as a drawing. Place the supplied attributes in their entities, distinguish primary keys, and show the minimum and maximum participation for both relationships. If you use min–max labels beside entity ends, use the usual convention that a label indicates how many instances of that entity can be associated with one instance at the opposite end. Thus, show 1..1 at the PRODUCTION and HALL ends, and 0..many at the PERFORMANCE end of each relationship.
[5]
Question 3
Brindle Equipment Cooperative lends equipment to its members from several depots. It is designing a relational database to manage its staff, depots and equipment loans.
Each depot has exactly one staff member as its manager. A staff member may manage at most one depot, and some staff members do not manage a depot. Depots and staff members are uniquely identified by DepotID and StaffID respectively.
Construct an ERD fragment for this relationship, showing the primary keys, cardinality and modality.
[3]
The equipment-loan section of the database has these requirements:
- Each member has a unique MemberID, a name and an email address.
- Each equipment item has a unique ItemID and a description.
- Each loan has a unique LoanID, a start date and a due date.
- Every loan belongs to exactly one member and concerns exactly one equipment item.
- A member may have no loans or many loans.
- An equipment item may have no loans or many loans over time.
Construct a complete ERD for this section of the database. Include the listed attributes, identify primary keys, and show the cardinality and modality of each relationship.
[5]
Describe how the conceptual, logical and physical schemas would each represent the cooperative's database design.
[3]
Staff repeatedly request a report showing every loan together with the member's current name and the equipment description. These names and descriptions rarely change. The cooperative proposes storing copies of them in a denormalized reporting table.
Outline how this proposal could improve report performance and one associated risk to data consistency.
[3]
Question 4
Bracken Air monitors air quality using sensors installed at monitoring stations. Its current SENSOR_REGISTER stores station and region details alongside each sensor, as illustrated below.
Current denormalized SENSOR_REGISTER records
| SensorID | SensorModel | StationID | StationName | RegionID | RegionName |
|---|---|---|---|---|---|
| S001 | AQ-100 | ST01 | Bracken Hill | R01 | North |
| S002 | AQ-200 | ST01 | Bracken Hill | R01 | North |
| S003 | AQ-100 | ST02 | Valley Gate | R01 | North |
| S004 | AQ-300 | ST02 | Valley Gate | R01 | North |
| S005 | AQ-200 | ST03 | Harbor Point | R02 | Coastal |
| S006 | AQ-100 | ST03 | Harbor Point | R02 | Coastal |
SensorID uniquely identifies a sensor and determines its model and StationID. Each station belongs to exactly one region; StationID determines StationName and RegionID. RegionID determines RegionName. Stations may contain several sensors, and regions may contain several stations. All fields contain single values.
public dashboard repeatedly retrieves sensor models together with station and region names. Outline one potential benefit of retaining a denormalized structure for this dashboard.
[2]
List two features that would be specified in the logical schema of this database.
[2]
Explain how a transitive dependency involving RegionName occurs in SENSOR_REGISTER.
[3]
Construct a replacement design for SENSOR_REGISTER that satisfies 3NF. Present each resulting relation with its attributes, label its primary key, and specify every foreign-key reference.
[5]
Build a Practice Exam — Computer Science HL
Test your knowledge with a custom paper containing questions from selected topics.
The Ultimate All-in-One
IB Platform
All content on this website has been developed independently from and is not endorsed by the International Baccalaureate Organization. International Baccalaureate and IB are registered trademarks owned by the International Baccalaureate Organization.