Q1 Preliminaries
1 Point
The allowed time for the exam is 50 minutes. Be sure to pay attention to time and to budget your time accordingly!
The exam is open pre-prepared hardcopy 2-sided cheat sheet but closed everything else. You are not allowed to communicate with or otherwise interact with other students (or friends) during the course of the exam, and this includes your HW brainstorming buddy. This exam is to be a solo effort! While taking the exam, the only window open on your computer should be a browser with one tab - this one. (Go ahead and close everything else now if you have not already done so.)
Read each question carefully, in its entirety, and then answer each part of the question. If you don't understand something, please just make your best educated guess and proceed accordingly.
Acknowledgement: I certify that I am taking this exam myself, on my own, with honesty and integrity, without interaction with others during the exam, following the rules outlined on Piazza and above, and without having obtained any
information about the exam's content from any source prior to taking it.
Q2 To E-R is Human
True False
33 Points
Based only on the E-R model pictured below, examine the model carefully and indicate whether each of the following statements are True or False. (This E-R diagram will be repeated every five questions or so for your convenience.)
Q2.1
2 Points
Two different studios may have the same name but different addresses.
Q2.2
2 Points
A movie will have either zero or one genres (e.g., mystery, comedy, action, or ...).
True False
True False
Q2.3
2 Points
A cartoon may have a weapon.
Q2.4
2 Points
A star cannot have several roles (and hence role names) in a given movie.
Q2.5
2 Points
Every murder mystery has a known title and year.
Q2.6
2 Points
True False
True False
True False
Being a star requires starring in at least one movie.
Q2.7
2 Points
A murder mystery can be co-owned by several studios.
Q2.8
2 Points
A star may have a starring role in a cartoon without providing a voice for that cartoon.
True False
True False
True False
Q2.9
2 Points
A crew can be uniquely identified by just specifying its crew number.
Q2.10
2 Points
A cartoon can have an associated genre (e.g., comedy).
Q2.11
2 Points
The number of stars in a given movie is computable from other information directly stored in the movie entity.
True False
True False
Q2.12
2 Points
Every movie involves the use of at least one crew.
Q2.13
2 Points
Every movie must have a studio that owns it.
Q2.14
2 Points
Every studio must own one or more movies.
Q2.15
2 Points
Two crews that work for a given studio may have different chiefs.
True False
True False
True False
True False
The next few questions indicate a new constraint that we might wish to change or enforce on the given schema. For each one, indicate whether or not the E-R modeling concepts and tools that we have covered in class and used in the homework can capture the given constraint by modifying the current schema in some way.
Q2.16
1 Point
A cartoon cannot be a murder mystery.
Q2.17
1 Point
A given movie can have no more than three stars.
True False
E-R-model-able
not E-R-model-able
Q2.18
1 Point
A star can appear in multiple roles in the same movie.
Q3 To E-R --> R is Divine
33 Points
Consider again the earlier E-R model (repeated again below for your
convenience). For this question, your job is to translate this E-R model into an appropriate relational schema that can capture as many of the model's features and constraints as possible. Use the delta-table approach for the movie
hierarchy aspect of the model. Be sure to include ON DELETE clauses for all of your FOREIGN KEYs. Use table and column names that correspond to the E-R diagram's artifact names wherever possible, and use good naming conventions otherwise. You may introduce additional tables where needed to fully capture the model. Also answer each of the questions that follow about the overall relational mapping for this E-R schema.
Q3.1
19 Points
Write a complete SQL CREATE TABLE DDL statement (or statements, if any supporting tables are needed) to model the Movie entity. While doing so:
Use your best judgement when choosing the data type for each column.
You can omit the attribute numstars (rather than defining a view) for this exercise. *
E-R-model-able
not E-R-model-able
E-R-model-able
not E-R-model-able
Your answer must include any relationship-merging needed to avoid having more tables than what's really necessary for this schema overall.
Movies are central to this database, so you should make sure that, as studios come and go, movies will first have to be transferred to some other studio (and cannot be accidentally removed).
Put your DDL statement(s) in the box below.
CREATE TABLE Movie ( mid integer, title text NOT NULL, year integer NOT NULL, owner_name text NOT NULL, PRIMARY KEY (mid), FOREIGN KEY (owner_name) REFERENCES Studio ON DELETE NO ACTION );
CREATE TABLE Movie_genre ( mid integer, genre text,
PRIMARY KEY (mid, genre), FOREIGN KEY (mid) REFERENCES Movie ON DELETE CASCADE );
Q3.2
5 Points
Now write the complete SQL CREATE TABLE DDL statement to model the Cartoon entity.
Q3.3
3 Points
In addition to what the given schema says, you have just gotten a late-breaking update (in a text message from your boss!) that says "Please make sure that no two movies in a given year can have the same title!" What additional line would you need to add to the movie table definition in question 3.1 in order to keep your boss happy?
UNIQUE (year, title)
Q3.4
2 Points
How many tables will you need in total for a relationship-optimized, delta-table based relational schema that captures the entire given E-R model?
CREATE TABLE Cartoon ( mid integer, PRIMARY KEY (mid), FOREIGN KEY (mid) REFERENCES Movie ON DELETE CASCADE );
Q3.5
2 Points
The primary keys for Studio and Star will be a fields called name (and of type text), and you have already figured out what the primary keys should be for most of the other tables. But what about Crew...? What should the PRIMARY KEY clause in the Crew CREATE TABLE statement be?
PRIMARY KEY (name, number)
Q3.6
2 Points
Consider an instance of the movie database in which the Movie delta table has V rows and the MurderMystery delta table has M rows. Which of the following inequalities is a correct description of the relationship, one that holds in general (for all possible database instances), of V and M?
Q4 Relational DB Design Theory
33 Points
Consider an instance of a relation R with the following contents:
6
7
8
9
10
11
V = M
V >= M
V <= M
Q4.1
5 Points
Which of the following are possible FDs, based on what you can tell from R's current contents?
Q4.2
5 Points
Which of the possible FDs from above can be inferred to be true from R's current contents?
snm -> szp
snm -> mid anm -> aph
myr -> mti
mid -> mti
Q4.3
9 Points
One of your predecessors in your new job, who recently left the company, had time to document the schema of R and to get its list of functional dependencies by interviewing the application stakeholders for this database. Unfortunately, they left before they normalized R. Here are the functional dependencies that they left you:
snm -> sst snm -> szp mid -> mti, myr mid, anm -> rnm anm -> aph mid -> snm
What attributes are in the attribute closure of attribute mid?
snm -> szp snm -> mid anm -> aph myr -> mti mid -> mti
None of the above
Q4.4
5 Points
Which of the following are among the candidate keys for R?
Q4.5
snm
sst szp
mid
mti
myr rnm anm aph
(snm, mid) (snm, mid, anm) (mid, anm)
(mid, anm, rnm) mid
3 Points
Given its FDs, which of the following issues is keeping the initial relation R from being in a better normal form?
Q4.6
3 Points
As a reminder, the initial set of dependencies that you were given for the relation R (snm, sst, szp, mid, mti, myr, rnm, anm, aph) was:
snm -> sst snm -> szp mid -> mti, myr mid, anm -> rnm anm -> aph mid -> snm
What is the highest normal form that the initial relation R satisfies?
Q4.7
3 Points
You just received a text message from an unknown number suggesting that R should be decomposed as follows:
It has trivial dependencies
It has partial dependencies
It has transitive dependencies
It has overlapping candidate keys
1NF
2NF
3NF
BCNF
R1 (mid, mti, myr, snm) R2 (snm, sst, szp) R3 (anm, aph)
Which of the following statements are true of the proposed decomposition?
It is a decomposition of *R
It is a lossless-join decomposition of R
It is a dependency-preserving decomposition of R None of the above
UNGRADED
Midterm 1 (3 PM)
13 DAYS, 21 HOURS LATE
STUDENT
Unknown Student (removed from roster?)
TOTAL POINTS
- / 100 pts
QUESTION 1
Preliminaries 1 pt
QUESTION 2
To E-R is Human 33 pts
2.1 (no title) 2 pts
2.2 (no title) 2 pts
2.3 (no title) 2 pts