2022¶

Question 1 — SecondChance Dog Shelter [25 marks]¶

(i) Entity–Relational Model [8 marks]¶

Entities and Attributes¶

Additional Notes on Attributes¶

• Dog.breedName(s) — The requirement says "the name(s) of the breed(s), where identifiable", indicating a dog may have multiple breeds or none (stray dogs are often of unknown lineage). This is a multivalued attribute, modelled as a separate relationship/table Dog_Breeds(dogMicrochipID, breedName).
• Owner.ownerID — Not mentioned in the requirements as a stored field; we introduce a surrogate owner identifier since no natural unique key is specified (names and addresses are not guaranteed unique).
• Treatment.description — Examples include "grooming", "vaccination for parvovirus", etc. This is a free-text description.

Relationships¶

Explanation of Adopt relationship: The requirement states "an owner may adopt more than one dog, but a dog can only be adopted by a single owner at a time." This is a 1:N relationship from Owner to Dog. The adoptionDate is a relationship attribute (it records when the adoption occurred). "If an owner returns a dog after adoption, the adoption record is deleted" — this means the relationship instance is removed rather than updated, and the dog reverts to being unadopted (null owner).

Assumptions Made¶

1. microchipID is a natural key — It is either pre-existing or newly assigned, and is unique per dog.
2. A dog can have zero breeds — "where identifiable" implies some dogs have no identifiable breed.
3. A treatment involves at least one staff member — reasonable for a veterinary context.
4. Staff role is a simple string — e.g., "vet", "veterinary nurse", "assistant", "administrator". No separate Role entity needed as no operations on roles are described.
5. An owner record persists even after a dog is returned — the adoption record is deleted, but the owner entity remains (they may adopt again).
6. treatmentID is a surrogate identifier — no natural key is specified for treatments.

ER Diagram (Textual Representation)¶

┌──────────────────┐         ┌──────────────────┐
│      Dog         │         │    Treatment     │
├──────────────────┤ 1     N ├──────────────────┤
│ *microchipID     │───Undergoes──▶│ *treatmentID   │
│   description    │           │   description    │
└────────┬─────────┘           │   startDate      │
         │                     │   endDate        │
         │ Multivalued         └──────────────────┘
         │ breedName              │
    ┌────┴────┐                   │ 1
    │         │                   │ StaffedBy (M:N)
    ▼         │              ┌────┴────┐
┌────────┐   │              │         │
│breedName│   │              │  Staff  │
└────────┘   │              ├─────────┤
             │              │ *staffID │
             │              │   role   │
             │              │   name   │
             │              └─────────┘

┌──────────────────┐         ┌──────────────────┐
│     Owner        │  1    N │      Dog         │
├──────────────────┤──Adopts──▶│  (continued)     │
│ *ownerID         │           │ *microchipID     │
│   name           │           │   description    │
│   address        │           └──────────────────┘
│   phone          │
│   adoptionDate   │── relationship attribute
└──────────────────┘

(ii) Relational Model & Normalisation [6 marks]¶

Relational Schema¶

Dog(microchipID PK, description)
Dog_Breed(microchipID FK, breedName, PK: (microchipID, breedName), FK: microchipID REFERENCES Dog)

Treatment(treatmentID PK, microchipID FK NOT NULL REFERENCES Dog(microchipID),
          description, startDate, endDate)

Staff(staffID PK, role, name)

TreatmentStaff(treatmentID FK, staffID FK,
               PK: (treatmentID, staffID),
               FK: treatmentID REFERENCES Treatment,
               FK: staffID REFERENCES Staff)

Owner(ownerID PK, name, address, phone)

Adoption(ownerID FK, microchipID FK, adoptionDate,
         PK: (ownerID, microchipID),
         FK: ownerID REFERENCES Owner,
         FK: microchipID REFERENCES Dog)

Explanation of mapping choices:

Functional Dependencies and BCNF Verification¶

Dog(microchipID, description)

BCNF check: For every non-trivial FD X → Y, X must be a superkey. microchipID is the PK, so it is a superkey. BCNF satisfied.

Dog_Breed(microchipID, breedName)

BCNF check: The only candidate key is (microchipID, breedName). There are no non-trivial FDs where the determinant is not a superkey. BCNF satisfied.

Treatment(treatmentID, microchipID, description, startDate, endDate)

BCNF check: treatmentID is the PK and the sole determinant of all other attributes. BCNF satisfied.

Staff(staffID, role, name)

BCNF check: staffID is the PK. BCNF satisfied.

TreatmentStaff(treatmentID, staffID)

No non-trivial FDs exist. BCNF satisfied.

Owner(ownerID, name, address, phone)

BCNF check: ownerID is the PK. BCNF satisfied.

Adoption(ownerID, microchipID, adoptionDate)

BCNF check: The candidate key is (ownerID, microchipID), which determines adoptionDate. No other non-trivial FDs exist. BCNF satisfied.

All relations are in BCNF.

(iii) SQL Queries [11 marks]¶

(a) [2 marks] Retrieve all dogs who have had the treatment "Vaccinated for Parvovirus"¶

SELECT DISTINCT d.microchipID, d.description
FROM   Dog d
       JOIN Treatment t ON d.microchipID = t.microchipID
WHERE  t.description = 'Vaccinated for Parvovirus';

Explanation: We join Dog with Treatment on the foreign key relationship. DISTINCT is used to ensure that if a dog has received this treatment multiple times, it appears only once in the result. Both microchipID and description are returned to give a meaningful list of dogs.

(b) [3 marks] Count the number of veterinarians involved in all treatments for the dog with microchip number '00454'¶

SELECT COUNT(DISTINCT ts.staffID) AS num_veterinarians
FROM   Treatment t
       JOIN TreatmentStaff ts ON t.treatmentID = ts.treatmentID
       JOIN Staff s ON ts.staffID = s.staffID
WHERE  t.microchipID = '00454'
  AND  s.role = 'vet';

Explanation: We join Treatment with TreatmentStaff and Staff to find all staff involved in treatments for the specified dog. We filter by s.role = 'vet' to count only veterinarians (not veterinary nurses, assistants, or administrators). COUNT(DISTINCT staffID) ensures that if the same vet was involved in multiple treatments for this dog, they are counted only once.

© [3 marks] SQL to update the database when the dog with microchip number '00454' is returned to the shelter¶

-- Delete the adoption record for this dog
DELETE FROM Adoption
WHERE  microchipID = '00454';

Explanation: The requirement states "If an owner returns a dog after adoption, the adoption record is deleted." This means we remove the row from the Adoption table referencing this dog's microchipID. The dog itself is not deleted from the Dog table — it remains in the shelter, just no longer has an active adoption record. No cascade is needed on the Adoption table since deleting the adoption row does not affect any other table.

Alternative (if adoptionDate is stored directly on the Dog table instead of in Adoption):

UPDATE Dog
SET    adoptionDate = NULL
WHERE  microchipID = '00454';

However, the relational model above uses a separate Adoption table, so the DELETE approach is the correct one.

(d) [3 marks] Add information about whether an owner already owns any dogs¶

Part 1 — Augment the database to store this new information:

Add a column currentlyOwnsDogs (BOOLEAN) to the Owner table:

ALTER TABLE Owner
ADD    currentlyOwnsDogs BOOLEAN DEFAULT FALSE;

Part 2 — Deal with previously entered owner information:

Existing owners have no recorded value for this new column. We need to set it based on whether they have an active adoption record in the Adoption table:

UPDATE Owner
SET    currentlyOwnsDogs = TRUE
WHERE  ownerID IN (
    SELECT ownerID
    FROM   Adoption
);

UPDATE Owner
SET    currentlyOwnsDogs = FALSE
WHERE  currentlyOwnsDogs IS NULL;

Explanation:

• The first UPDATE sets currentlyOwnsDogs = TRUE for all owners who currently have an active adoption record (i.e., they own at least one dog right now).
• The second UPDATE sets currentlyOwnsDogs = FALSE for any remaining owners who still have NULL (i.e., owners with no active adoption record). This handles the case where some owners in the database have previously adopted dogs (their adoption records were deleted when dogs were returned) but currently own none.

Alternative approach — instead of a stored boolean, one could compute this on demand with a query:

SELECT o.ownerID, o.name,
       EXISTS (SELECT 1 FROM Adoption a WHERE a.ownerID = o.ownerID) AS currentlyOwnsDogs
FROM   Owner o;

However, the question asks to "store this new information," which implies adding a column to persist the value.

Question 2 — Employee Remuneration Database [25 marks]¶

(i) View-Based Access Control [6 marks]¶

Description:

Views provide a mechanism for restricting user access to specific subsets of data without granting direct table-level permissions. By creating a view that exposes only the columns Mary is authorised to see (id, firstName, surname), and then granting Mary access to that view (rather than to the underlying employee table), we ensure she cannot query the dateOfBirth column.

This is a standard DBMS security pattern: create a restricted view, then grant access to the view only.

SQL:

-- Step 1: Create a view that excludes dateOfBirth
CREATE VIEW employee_public AS
SELECT   id, firstName, surname
FROM     employee;

-- Step 2: Grant Mary access only to the view
GRANT SELECT ON employee_public TO Mary;

Explanation:

• The view employee_public contains only the three authorised columns. Any query against this view by Mary will return only those columns.
• Mary is granted SELECT on the view, not on the employee table. If Mary had SELECT on the employee table directly, she could query all columns including dateOfBirth.
• This approach leverages the DBMS's privilege model: privileges are tracked per object (table/view), so Mary's access is scoped to employee_public only.

Additional considerations (for completeness):

• If Mary needs to update data through the view, GRANT UPDATE ON employee_public TO Mary could be added, subject to updatable view rules (the view must be simple — a single table, no aggregates, no DISTINCT).
• The underlying employee table should have no direct grants to Mary.

(ii) Salary Constraint [7 marks]¶

Scenario: Michelle wants a salary of no less than €20,000 for everyone who does not receive a bonus and is not eligible for outside earnings.

Approach: CHECK constraint

The most appropriate mechanism is a CHECK constraint on the remuneration table, because:

• It is a domain-level integrity constraint (it governs the validity of data within a table).
• It must be enforced automatically on every INSERT and UPDATE.
• It is declarative and portable across DBMS implementations.

SQL:

ALTER TABLE remuneration
ADD CONSTRAINT min_salary_no_bonus
CHECK (
    NOT (bonus IS NULL AND bonusEligible IS NULL
         AND salary < 20000)
);

Explanation of the logic:

The condition "salary of no less than €20,000 to everyone who does not receive a bonus and is not eligible for outside earnings" translates to:

IF (bonus IS NULL AND bonusEligible IS NULL) THEN salary >= 20000

In SQL CHECK, the constraint must evaluate to TRUE for every valid row. We express the implication as:

NOT (bonus IS NULL AND bonusEligible IS NULL AND salary < 20000)

This is logically equivalent to:

(bonus IS NOT NULL OR bonusEligible IS NOT NULL OR salary >= 20000)

Meaning: a row is valid if any of these is true — the employee has a bonus, or is eligible for outside earnings, or has a salary of at least €20,000.

Alternative: CHECK with CASE semantics (more readable):

ALTER TABLE remuneration
ADD CONSTRAINT min_salary_no_bonus
CHECK (
    (bonus IS NULL AND bonusEligible IS NULL AND salary >= 20000)
    OR
    (bonus IS NOT NULL)
    OR
    (bonusEligible IS NOT NULL)
);

Why not a trigger? A trigger could enforce this, but a CHECK constraint is preferred because:

• It is evaluated by the DBMS engine natively (more efficient).
• It is part of the schema definition (visible in metadata).
• It cannot be bypassed by application logic errors.
• Triggers are only needed when the constraint logic is too complex for CHECK (e.g., referencing other rows or tables).

(iii) Transaction Rollback [6 marks]¶

Scenario: In the schedule below, two transactions — Course Registration and Course Transfer — are executing concurrently. Describe what takes place if Course Transfer fails and is rolled back on its final write operation.

Detailed rollback process:

When Course Transfer fails on its final write operation and must be rolled back, the DBMS performs the following steps:

1. Failure Detection: The DBMS detects the failure at the point of the write operation (e.g., a constraint violation, deadlock, system error, or explicit abort signal). The transaction is marked for abort.

2. Undo Operations (Rollback): The DBMS reverses all modifications made by Course Transfer since it began. For each WRITE operation performed by Course Transfer, the before-image (the original value of the data item before the write) is retrieved from the undo log and written back to the database, restoring the data item to its state prior to Course Transfer's modifications. This proceeds in reverse order of the writes to maintain consistency.

3. Release Locks: All locks held by Course Transfer are released. This includes:

• Exclusive (X) locks on data items that Course Transfer wrote.
• Shared (S) locks on data items that Course Transfer read. Releasing these locks allows other transactions (including Course Registration, if it was blocked waiting for any of these locks) to proceed.

4. Transaction State Update: Course Transfer's state is updated to ABORTED in the system catalog. The transaction is removed from the active transaction table.

5. Recovery of Blocked Transactions: If Course Registration (or any other transaction) was blocked waiting for a lock held by Course Transfer, it is now unblocked and can resume execution. The DBMS scheduler re-evaluates the schedule to determine whether Course Registration can proceed or must wait for other resources.

6. No Commit Required: Because Course Transfer failed, no COMMIT is issued for it. The database does not apply any of Course Transfer's changes permanently. The effects of Course Registration (if it has already committed) remain intact.

Key principle: Rollback ensures atomicity (one of the ACID properties) — the transaction's effects are completely undone, as if it never executed. This failure is a transaction error or system error (per the course), and the rollback mechanism ensures the database is returned to a consistent state.

(iv) Concurrent Data Consistency [6 marks]¶

Scenario: Two transactions — Course Registration and Course Transfer — are executing concurrently. Describe in detail what takes place in terms of data consistency.

Detailed analysis:

When two transactions execute concurrently, the DBMS must ensure that the resulting schedule is equivalent to some serial execution of those transactions. Without proper concurrency control, the following anomalies can arise:

1. Concurrency Anomalies:

2. Concurrency Control — Lock-Based Protocol (Two-Phase Locking — 2PL):

To prevent these anomalies and maintain data consistency, the DBMS employs the Two-Phase Locking (2PL) protocol:

• Growing Phase: Transactions acquire locks (read/shared locks for reads, write/exclusive locks for writes) but cannot release any locks.
• Shrinking Phase: Transactions can release locks but cannot acquire new ones.
• Strict 2PL (most common): Exclusive locks are held until the transaction commits or aborts, preventing dirty reads and lost updates.

Theorem: Any schedule resulting from 2PL is conflict-serializable — meaning it produces the same result as some serial execution of the transactions.

3. Serializability:

The DBMS ensures serialisability — the guarantee that concurrent execution produces the same result as if the transactions had executed one after another in some order. Two schedules are conflict equivalent if the order of any two conflicting operations (operations from different transactions, accessing the same item, with at least one being a write) is the same in both schedules.

4. Outcome:

If the DBMS uses a serialisable protocol (e.g., strict 2PL), the concurrent execution of Course Registration and Course Transfer will produce a result equivalent to one of these serial orders:

• Course Registration executes first, then Course Transfer; or
• Course Transfer executes first, then Course Registration.

The specific outcome depends on the DBMS's concurrency control algorithm and the timing of the transactions. The key guarantee is that no inconsistency arises from the concurrent execution — the database remains in a valid state.

Question 3 — MessageMania & GDPR [25 marks]¶

(i) GDPR Issues [10 marks]¶

The scenario raises multiple serious GDPR issues. GDPR (General Data Protection Regulation, Regulation (EU) 2016/679) applies because MessageMania is based in Ireland (an EU Member State) and processes personal data of individuals in the EU.

Issue 1: Unlawful Processing / Breach of Purpose Limitation (Article 5(1)(b))

The bug causes protected messages to be automatically switched to public. Protected messages are intended to be visible only to registered followers. By making them public, MessageMania is processing personal data (the content of private messages) in a way that is incompatible with the original purpose for which the data was collected. Users consented to their messages being shared only with followers, not with the general public. This violates the purpose limitation principle.

Issue 2: Breach of Data Integrity and Confidentiality (Article 5(1)(f)) — Data Security Breach

GDPR Article 5(1)(f) requires that personal data be processed securely, with appropriate protection against unauthorised or unlawful processing and against accidental loss. The bug constitutes a security breach:

• Protected messages contain personal data (they are shared with a limited, intended audience).
• The bug exposes this data to an unintended audience (any internet user).
• This is a failure of confidentiality — one of the three pillars of information security (confidentiality, integrity, availability).

Issue 3: Mandatory Breach Notification (Article 33)

Under Article 33(1), MessageMania must notify the relevant supervisory authority (in Ireland, the Data Protection Commission — DPC) of a personal data breach without undue delay and, where feasible, not later than 72 hours after becoming aware of it.

Jessica became aware of the issue on 26 December 2018. GDPR entered into force on 25 May 2018 and became directly applicable on 25 May 2018. Therefore, GDPR was in force when Jessica discovered the bug. The 72-hour notification clock started ticking on 26 December 2018. If MessageMania did not notify the DPC within 72 hours, they are in violation of Article 33.

Issue 4: Notification to Data Subjects (Article 34)

Under Article 34, when a breach is likely to result in a high risk to the rights and freedoms of natural persons, the data controller must communicate the breach to the affected data subjects without undue delay.

Protected messages being exposed to the public is likely to pose a high risk to users' rights and freedoms (privacy violation, potential for harassment, reputational damage, etc.). Therefore, MessageMania should have notified all affected users whose protected messages were exposed. Failure to do so is a further GDPR violation.

Issue 5: Lawfulness, Fairness and Transparency (Article 5(1)(a))

Users were not informed that their protected messages were being exposed. This violates the transparency principle — data subjects must be informed in clear and plain language about how their data is processed. The fact that "to the best of her knowledge, the company itself was also unaware" means users were neither given a privacy notice describing this risk nor informed when it occurred.

Issue 6: Accountability (Article 5(2))

GDPR places the burden on the data controller to demonstrate compliance with all data protection principles. The fact that a bug existed that silently changed message privacy settings — and that the company was unaware of it — suggests inadequate technical and organisational measures. This violates the accountability principle.

Issue 7: Data Protection by Design and by Default (Article 25)

Article 25 requires that data protection measures be built into the design of processing systems and that by default, only data necessary for each specific purpose be processed. The bug — where changing an email address inadvertently changes message visibility — indicates that data protection was not designed into the system. A well-designed system would ensure that a profile update (email change) has no effect on message visibility settings.

Issue 8: Right to Rectification (Article 16) and Right to Erasure (Article 17)

Affected users have the right to request correction of inaccurate data (their messages were incorrectly classified as public) and the right to request erasure. If users are unaware of the breach, they cannot exercise these rights, compounding the violation.

Issue 9: Lawful Basis for Processing (Article 6)

The original lawful basis for processing (likely consent or legitimate interest) may no longer be valid once messages are exposed beyond the intended audience. Processing becomes unlawful because it exceeds the scope of the user's consent.

Issue 10: Potential Fines (Article 83)

GDPR allows fines of up to €20 million or 4% of total worldwide annual turnover (whichever is higher) for the most serious violations. The violations here — particularly the failure to notify the supervisory authority and data subjects — could attract significant penalties.

(ii) Internal Reporting [8 marks]¶

Who Jessica should inform:

Jessica, as a programmer who discovered a potential GDPR breach, should first inform her line manager or direct supervisor (if one exists and is trustworthy). However, given the seriousness of the issue (a data security breach affecting 2 million users), she should also consider reporting to the company's Data Protection Officer (DPO).

Under GDPR Article 38, if MessageMania employs a DPO (which is likely given the scale of processing — 2 million users and systematic monitoring of behaviour through message visibility settings), Jessica should report the issue to the DPO. The DPO is responsible for monitoring GDPR compliance and is the designated contact point for supervisory authorities.

Who that individual should inform:

The DPO (or line manager, if no DPO exists) should then inform:

1. The Data Protection Commission (DPC) — Ireland's supervisory authority — within 72 hours of confirmation that a breach has occurred (Article 33). The DPO is specifically tasked with this notification under Article 39(1)(b).

2. The Board of Directors / Senior Management — so that the company can make strategic decisions about:

3. Whether to notify affected data subjects (Article 34).
4. Whether to engage legal counsel.
5. How to communicate with users and the public.

6. What remedial technical measures to implement.

7. Affected Data Subjects (if the breach poses a high risk) — The DPO or senior management must assess whether the breach is likely to result in a high risk to users' rights and freedoms. If so, all affected users must be notified without undue delay (Article 34).

Escalation path:

Jessica (programmer)
    → Line Manager / Direct Supervisor
        → Data Protection Officer (DPO)
            → Data Protection Commission (DPC) [within 72 hours]
            → Board of Directors / Senior Management
                → Affected data subjects [if high risk]

If the DPO or management fails to act:

If Jessica reports the issue to the DPO or management and they fail to take appropriate action, GDPR does not provide a specific whistleblower mechanism at the EU level, but Irish employment law and the Protected Disclosures Act 2014 (as amended) provide protections for employees who report wrongdoing in good faith. Jessica could escalate externally to the DPC directly if internal channels fail.

(iii) Company Impact [7 marks]¶

The impact of this issue on MessageMania is likely to be significant and multifaceted:

1. Regulatory Fines and Penalties

The DPC can impose fines under Article 83 of GDPR. Given the scale (2 million users affected), the severity of the breach (private messages exposed to the public), and the failure to notify within 72 hours, the fine could be substantial — potentially in the range of millions of euros. The DPC considers:

• The nature, gravity, and duration of the infringement.
• The number of data subjects affected.
• Whether the breach was deliberate or negligent.
• Any measures taken to mitigate the damage.
• The level of cooperation with the supervisory authority.

2. Reputational Damage

MessageMania's reputation as a trusted platform would be severely damaged. Users who had protected messages exposed may:

• Delete their accounts and migrate to competing platforms.
• Publicly criticise the company on social media and review platforms.
• Lose trust in the platform's ability to protect their privacy.

For a social media platform, trust is the core product. Reputational damage could lead to significant user churn and reduced user acquisition.

3. User Churn and Business Impact

With 2 million users, even a small percentage leaving the platform would have a material impact:

• Reduced advertising revenue (if the business model is ad-supported).
• Reduced network effects (social platforms become less valuable with fewer users).
• Increased customer support costs (handling user complaints and inquiries).

4. Legal Liability

Affected users may bring civil claims against MessageMania for:

• Breach of statutory duty (GDPR).
• Breach of contract (the terms of service likely include promises about data protection).
• Negligence in handling personal data.
• Invasion of privacy (under Irish common law or the European Convention on Human Rights, Article 8).

5. Mandatory Remediation Costs

MessageMania would need to invest in:

• Fixing the bug (engineering resources).
• Auditing the database to identify all affected users and messages.
• Implementing stronger data protection controls.
• Engaging external security auditors.
• Providing data protection training to staff.

6. Increased Regulatory Scrutiny

The DPC may place MessageMania under enhanced monitoring, requiring regular compliance reports and audits. This increases ongoing operational costs and limits operational flexibility.

7. Insurance Implications

If MessageMania has cyber insurance, the breach may lead to increased premiums or exclusions. Some policies may not cover breaches resulting from negligence (failure to implement adequate security measures).

8. Competitive Disadvantage

Competitors could capitalise on MessageMania's misstep by marketing their own platforms as more privacy-focused, accelerating user migration.

9. Potential for Class Action

If a significant number of users are affected, a class action lawsuit could be brought, with damages based on the distress and inconvenience caused by the breach. Irish law allows for compensation under Article 82 of GDPR for material or non-material damage.

Question 4 — Enhanced ER & SQL Triggers [25 marks]¶

Part A — IrelandTalentProductions¶

(i) Enhanced ER Model [8 marks]¶

Superclass and Subclasses (ISA Hierarchy)¶

Superclass: Entertainer

• Attributes: entertainerID (PK), name, dateOfBirth, contactAddress, phone

Subclasses (using ISA relationships):

ISA Constraint Interpretation:

• The problem does not state whether the subclasses are disjoint or overlapping. In talent management, it is common for entertainers to have multiple roles (e.g., a comedian who also broadcasts). We assume overlapping — an entertainer can be a member of multiple subclasses.
• We also assume partial — not every entertainer must be classified as a comedian, musician, or broadcaster (some may be general entertainers).

Multivalued Attributes and Relationships¶

Comedian:

• kindOfComedian — single-valued (e.g., "Standup Comedian").
• preferredVenues — multivalued (e.g., "physical events", "TV", "Radio", "Film"). A comedian can have several preferred venue types, and a venue type can apply to several comedians. This is an M:N relationship between Comedian and Venue.

Musician:

• instruments — multivalued (a musician can play multiple instruments). M:N relationship between Musician and Instrument.
• ageRanges — multivalued (a musician can be suited to multiple audience age ranges). M:N relationship between Musician and AgeRange.
• genres — multivalued (a musician can perform multiple genres). M:N relationship between Musician and Genre.

Broadcaster:

• programmeKind — The problem states there are exactly five kinds: current affairs, daytime casual, news, documentary, and sports. This can be modelled as a single-valued attribute with a CHECK constraint, or as a relationship to a ProgrammeKind entity. Given the fixed enumeration, a single-valued attribute with domain restriction is appropriate.

ER Diagram (Textual Representation)¶

                                ┌──────────────────────────┐
                                │       Entertainer        │
                                ├──────────────────────────┤
                                │ *entertainerID           │
                                │   name                   │
                                │   dateOfBirth            │
                                │   contactAddress         │
                                │   phone                  │
                                └──────────┬───────────────┘
                                           │ ISA (partial, overlapping)
                                    ┌──────┼──────┐
                                    │      │      │
                         ┌──────────┘      │      └──────────┐
                         ▼                 ▼                 ▼
                  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐
                  │   Comedian  │  │  Musician   │  │ Broadcaster │
                  ├─────────────┤  ├─────────────┤  ├─────────────┤
                  │ *entertainer│  │ *entertainer│  │ *entertainer│
                  │   ID (FK)   │  │   ID (FK)   │  │   ID (FK)   │
                  │ kindOfComed-│  │ programmeKind│  │             │
                  │   ian       │  └──────┬──────┘  └─────────────┘
                  └──────┬──────┘         │
                         │                │ M:N
                         │                │
                    ┌────┴────┐    ┌──────┴───────┐
                    │         │    │              │
                    ▼         ▼    ▼              ▼
             ┌──────────┐ ┌───────┐  ┌────────┐ ┌──────────┐
             │  Venue   │ │Instr. │  │ AgeRange│ │  Genre   │
             ├──────────┤ ├───────┤  ├──────────┤ ├──────────┤
             │ *venue   │ │ *inst │  │ *ageRange│ │ *genre   │
             └──────────┘ └───────┘  └──────────┘ └──────────┘

Relationships for multivalued attributes:

Comedian ──M:N── PreferredVenue ──N:1── Venue
   │                          │
   ▼                          ▼
entertainerID              *venue

Musician ──M:N── PlaysInstrument ──N:1── Instrument
Musician ──M:N── PerformsForAge ──N:1── AgeRange
Musician ──M:N── PerformsGenre ──N:1── Genre

Assumptions Made¶

1. ISA is partial and overlapping — An entertainer need not be a comedian, musician, or broadcaster; and an entertainer can belong to multiple subclasses simultaneously.
2. entertainerID is the shared primary key across the superclass and all subclasses (implemented in the relational model as FKs that are also PKs in the subclass tables).
3. kindOfComedian is single-valued — each comedian instance has one primary kind, even though they may perform as several kinds (the attribute captures the primary classification).
4. preferredVenues, instruments, ageRanges, and genres are multivalued — requiring M:N relationships to separate entities.
5. programmeKind is single-valued with a fixed domain — one of five values, enforceable via a CHECK constraint.
6. Broadcaster has no additional multivalued attributes — only the programme kind.

(ii) Relational Model [8 marks]¶

Table Definitions¶

-- Superclass table
Entertainer(
    entertainerID    VARCHAR(20)  PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    dateOfBirth      DATE         NOT NULL,
    contactAddress   VARCHAR(255) NOT NULL,
    phone            VARCHAR(20)
);

-- Subclass: Comedian
Comedian(
    entertainerID    VARCHAR(20)  PRIMARY KEY,
    kindOfComedian   VARCHAR(50),
    -- FK referencing Entertainer (also PK, ensuring each comedian is an entertainer)
    CONSTRAINT fk_comedian_entertainer
        FOREIGN KEY (entertainerID) REFERENCES Entertainer(entertainerID)
);

-- Subclass: Musician
Musician(
    entertainerID    VARCHAR(20)  PRIMARY KEY,
    -- FK referencing Entertainer
    CONSTRAINT fk_musician_entertainer
        FOREIGN KEY (entertainerID) REFERENCES Entertainer(entertainerID)
);

-- Subclass: Broadcaster
Broadcaster(
    entertainerID    VARCHAR(20)  PRIMARY KEY,
    programmeKind    VARCHAR(20)  NOT NULL,
    -- FK referencing Entertainer
    CONSTRAINT fk_broadcaster_entertainer
        FOREIGN KEY (entertainerID) REFERENCES Entertainer(entertainerID),
    -- Domain constraint for the five programme kinds
    CONSTRAINT chk_programme_kind
        CHECK (programmeKind IN ('current affairs', 'daytime casual',
                                  'news', 'documentary', 'sports'))
);

-- Multivalued: Comedian preferred venues
PreferredVenue(
    entertainerID    VARCHAR(20),
    venueType        VARCHAR(50),
    PRIMARY KEY (entertainerID, venueType),
    CONSTRAINT fk_pv_comedian
        FOREIGN KEY (entertainerID) REFERENCES Comedian(entertainerID)
);

-- Multivalued: Musician instruments
MusicianInstrument(
    entertainerID    VARCHAR(20),
    instrument       VARCHAR(50),
    PRIMARY KEY (entertainerID, instrument),
    CONSTRAINT fk_mi_musician
        FOREIGN KEY (entertainerID) REFERENCES Musician(entertainerID)
);

-- Multivalued: Musician age ranges
MusicianAgeRange(
    entertainerID    VARCHAR(20),
    ageRange         VARCHAR(30),
    PRIMARY KEY (entertainerID, ageRange),
    CONSTRAINT fk_mar_musician
        FOREIGN KEY (entertainerID) REFERENCES Musician(entertainerID)
);

-- Multivalued: Musician genres
MusicianGenre(
    entertainerID    VARCHAR(20),
    genre            VARCHAR(50),
    PRIMARY KEY (entertainerID, genre),
    CONSTRAINT mg_musician
        FOREIGN KEY (entertainerID) REFERENCES Musician(entertainerID)
);

Explanation of Design Choices¶

Part B — MyAssistant Virtual Assistant¶

(a) [2 marks] Constraint: no bookings before 7 am or after 9 pm¶

ALTER TABLE Booking
ADD CONSTRAINT valid_booking_hours
CHECK (
    EXTRACT(HOUR FROM StartDateAndTime) >= 7
    AND EXTRACT(HOUR FROM StartDateAndTime) < 21
    AND EXTRACT(HOUR FROM StopDateAndTime) <= 21
);

Explanation: The constraint ensures that both the start time and end time of a booking fall within the allowed hours (7 am to 9 pm). EXTRACT(HOUR FROM ...) is standard SQL; some DBMS may use DATEPART(HOUR, ...) (SQL Server) or HOUR(...) (MySQL). The constraint is applied at the table level via ALTER TABLE, ensuring it is enforced from the time the database was first created onward. Checking both StartDateAndTime and StopDateAndTime ensures the entire booking falls within the permitted window.

(b) [3 marks] View of all room bookings for room '101', with StartDateAndTime and attendees¶

CREATE VIEW Room101Bookings AS
SELECT   b.StartDateAndTime, ba.MeetingAttendees
FROM     Booking b
         JOIN BookingAttendees ba ON b.BookingID = ba.BookingID
WHERE    b.RoomNum = '101';

Explanation: The view joins Booking with BookingAttendees on BookingID to associate each booking with its attendees. The WHERE clause filters to room '101' only. The view returns the start date/time and the list of meeting attendees for each booking in that room.

© [4 marks] Trigger: insert into MeetingAdmin when booking has more than 20 attendees¶

CREATE TRIGGER trg_meeting_admin
AFTER INSERT OR UPDATE ON Booking
FOR EACH ROW
WHEN (
    (SELECT COUNT(*)
     FROM   BookingAttendees
     WHERE  BookingID = NEW.BookingID) > 20
)
INSERT INTO MeetingAdmin (BookingID, MeetingAttendeeNumbers)
VALUES (NEW.BookingID,
        (SELECT COUNT(*)
         FROM   BookingAttendees
WHERE  BookingID = NEW.BookingID))
ON CONFLICT (BookingID) DO UPDATE
SET MeetingAttendeeNumbers = EXCLUDED.MeetingAttendeeNumbers;

Explanation:

• AFTER INSERT OR UPDATE ON Booking — The trigger fires after a new booking is inserted or an existing booking is updated. This matches the question wording: "when any meeting booking has been inserted or updated."
• FOR EACH ROW — The trigger fires once per affected booking row.
• WHEN clause — The trigger body only executes if the total number of attendees for the booking (counted from BookingAttendees) exceeds 20.
• INSERT into MeetingAdmin — When the condition is met, a row is inserted with the BookingID and the total number of attendees (computed via a subquery).
• ON CONFLICT DO UPDATE — If a MeetingAdmin row already exists for the BookingID, this clause updates the existing row rather than attempting to insert a duplicate, handling the case where the attendee count changes after the initial insertion.

Why fire on Booking rather than BookingAttendees? The question asks for a trigger on meeting bookings (the Booking table), not on individual attendee records. Firing on Booking ensures the trigger responds to booking-level operations as specified.