CSC343 Test 3 Question Fall 2020
[11 marks]
Below is an Entity-Relationship diagram for the music industry. It may or may not represent the domain well.
trackName trackID
duration (1,N)
(1,N)
features
albumID (1,N)
produces
(0, N)
name
name
(1,1) fees
manages
releaseDate name
(0,1)
phone
manager
Track
appearsOn order
role
Album
(1,1)
(1,N)
RecordCompany
Artist
name
artistID
companyID
gender
We are in the process of translating it to a relational schema. So far, we have drafted these relations; we may want to modify some of them later:
Track(trackID, trackName, duration) Album(albumID, releaseDate, name) RecordCompany(companyID, name) Artist(artistID, name, gender) Manager(phone, name)
Now it¡¯s time to think about the remaining relations. This question is about just one of them.
1. Define a new relation called appearsOn that corresponds to the relationship set appearsOn from the ER diagram. Provide its name, attributes and keys. (To indicate a key, underline all attributes that are part of the key using a single line.) If appearsOn can be collapsed into another relation, instead show the modified relation. If it should not be collapsed explain why.
2. Use relational notation to define all necessary constraints involving appearsOn, including referential integrity constraints and any other constraints needed to enforce the cardinalities in the ER diagram. You may use relational algebra if needed.
3. Suppose the five relations above have been defined as tables in SQL, and that all columns have type VARCHAR, except for releaseDate (which is a DATE) and the IDs (which are all of type INTEGER). Define appearsOn in SQL (or if you collapsed it onto another relation, define that relation in SQL). Enforce all constraints that can be enforced using ordinary keys, foreign keys, not null, and check constraints, and without defining circular constraints.
4. Exactly which constraints in the ER diagram are not expressed in your table definition? For each, explain why it cannot be expressed. If every constraint was expressed, say so.
You may type your answer or handwrite it legibly. Either way, create a pdf file called Q1-solution.pdf and submit it via MarkUs. We will not be autotesting your SQL, so submitting this way not a problem.
Copyright (c) 2020 Diane Horton