Midterm 1 - CPSC 304 (2024T1)

Problem 1

For each of the following statements, clearly indicate whether it is true or false by putting a check mark in the associated box. Consider each statement individually and with regards to the materials covered in class.

Statement	True/False
An entity set can have more than one primary key, provided that each of them can uniquely identify an entity in the entity set.	False
The number of candidate keys and the number of superkeys for a relation can be the same.	True
Based on the rules of ER diagrams discussed in lecture, it is impossible for the same pair of entity sets to have two different relationships between them.	False
A participation constraint of entity set A in relationship C with another entity set B states that if you know an entity a in entity set A, then you know the other entity b in entity set B.	False
If an attribute has both UNIQUE and NOT NULL constraints in the SQL DDL statement, it is a candidate key for the relation.	True
For any combination of attributes in R(A,B,C,D), if its closure includes A, B, C, and D, it is a candidate key for R.	False

Problem 2

For Each of the following Statements, Clearly Indicate whether it is True or False by Putting a Check Mark in the Associated Box. Consider Each Statement Individually and with regards to the rules/constraints Discussed in Class

Statement	True/False
The number of entities in entity set A can be the same as the number of entities in entity set C.	True
PQ is a candidate key of the relationship set G.	True
The number of entities in entity set H must always equal the number of entities in entity set J.	False
The number of entities in entity set I must always equal the number of relationships in relationship E.	True
Different entities in the entity set C can have the same value for attribute M.	True
The number of relationships in relationship set D is always greater than or equal to the number of entities in entity set A.	False
If there is one entity in entity set C, there must be at least one entity in entity set I.

Problem 3

Assume that you have the following:

a1, a2, and a3 are the only entities of A
c1 and c2 are the only entities of C
h1 and h2 are the only entities of H
i1 and i2 are the only entities of I
j1, j2, and j3 are the only entities of J

Which of the following relationship sets for B, D, E, or G are possible according to the diagram, where B={(a1,c1)} means that a relationship between a1 and c1 exists in relationship set B. To extend this to the aggregation E, E{(c1,G{h1, i1, j1})} means that a relationship between c1 and the relationship G(h1, i1, j1}} exists in the relationship set E.

Assume that any relationships not explicitly listed in the question are correct according to the diagram. Clearly indicate which is possible by putting a check mark in the associated box. Ambiguous, blank, or unreadable answers will be counted as incorrect.

Question	True/False
B = {(a1, c1), (a1, c2)}	True
D = {(a1, a2), (a2, a3), (a1, a3)}	False
G = {(h1, i1, j1)}	False

Problem 4

To answer this question, complete the table two pages ahead. We recommend using the space on the next page to first fully translate the ER diagram to the relational model. As you are going, if you had to make any choices where there are other plausible options, you may find it helpful to keep track of these choices.

When possible, use a single relation to represent more than one entity or relationship. Use the FIRST appropriate row in the table to represent the combined relation. For example, if you want to combine X and Y, use the row for X to represent the combined relation XY, and write “combined” in row Y’s first column and write “N/A” in every remaining cell. For the rows that represent a relation or a combined relation, complete the row by filling in appropriate attributes from the relation. If a relation does not have any attribute for an entry, write “N/A” in the cell.

For relationship D, use K1 and K2 to distinguish two entities from entity set A.

PLEASE ALPHABETIZE (I.E., PUT IN ALPHABETICAL ORDER) THE ATTRIBUTES IN A GIVEN CELL. EVERY BLANK CELL WILL BE MARKED AS INCORRECT.

Relation Containing	Attributes	Primary Keys	Foreign Keys	NOT NULL Constraints	UNIQUE Constraints
Entity Set A	k1, k2, l	k1	k2	N/A	N/A
Relationship Set B	k, m, n	k, m	k	N/A	N/A
Entity Set C	(Combined with B)	N/A	N/A	N/A	N/A
Relationship Set D	(Combined with A)	N/A	N/A	N/A	N/A
Relationship Set E	p, q, t, o, s, k, m	s	p, q, t, m, k	p, q, t, m, k	p, q, t
Relationship Set G	(Combined with E)	N/A	N/A	N/A	N/A
Entity Set H	p, q, r	p, q	N/A	N/A	N/A
Entity Set I	(Combined with G)	N/A	N/A	N/A	N/A
Entity Set J	t, u	t	N/A	N/A	N/A

Problem 5

Consider a relation with the following FDs:

(a). Briefly Explain why relation R is or is not in Bcnf. if R is not in Bcnf, then Decompose the relation into Bcnf

is already in BCNF.
is not in BCNF as is not a candidate key so we decompose into and .
no relation has these attributes anymore so we ignore it.
is already in BCNF.
is already in BCNF.

We've now exhausted all functional dependencies so we get the final solution and .

(b). List All the Keys for the New Relations Resulting from the Decomposition. by Writing the Relations and Underlining the Attributes that Are Keys (e.g., ). if R Was Initially in Bcnf, List All Keys for R

(c). Can You Choose a Different Fd to Decompose relation R if You Were Told to Redo Question A? if So, Decompose relation R with a Different Fd. if Not, why Not? if R Was Initially in Bcnf, Say “N/A”

No we can not as is the only relationship not in BCNF already.

Problem 6

Consider the relation and the following FDs:

Find all the minimal keys for . Write your final answer in the box, but show your work below. Please list the attributes in alphabetical order within a key and the keys in alphabetical order.

Left	Middle	Right
A, G, H	B, C, E, F	D

The minimal keys for are , , and .

Problem 7

Consider the relation and the following FDs. Follow the 3-step process discussed in class to find a minimal cover of the set of FDs. Perform each step in the appropriate box below:

Step 1:

Step 3:

can be removed because we obtain it from the closure through and .

Problem 8

Consider the relation with the following minimal cover:

(a). If You Were Asked to Use the Lossless Join Method and Follow the order of Functional Dependencies in the Minimal Cover from top to Bottom to Decompose into 3NF Which Fd Would not Be Preserved during the Decomposition Process? Mark the Letter Corresponding to Your Choice and only that Letter inside the Box

A.
B.
C.
D. None of the above

is lost using the lossless join method and following the original order.

(b). If You Were Asked to Use the Synthesis Method to Bring into 3NF, Which relation Would You Need to Add to the Set of Decomposed Relations to Preserve Lossless Joins? Mark the Letter Corresponding to Your Choice and only that Letter inside the Box

A.
B.
C.
D. None of the above

The answer is as is evident from the final solution of (a).

Problem 9

Consider the relations from the in-class relational algebra exercises about a symphony:

Person(_email_, name, age)

This relation stores anyone who has signed up for our mailing list. Tuples in this relation may not be listed in Purchase.

Show(_id_, year, month, date, showing, attendanceNumber)

showing describes whether a show was during morning, afternoon, or evening

Song(_composer_, _title_)

SongsPerformed(_showId_, _composer_, _title_)

showId is a foreign key referring to Show
composer and title are foreign keys referring to attributes of the same name in Song

Purchase(_email_, _showID_, price)

email is a foreign key referring to the email attribute in Person
showID is a foreign key referring to Show

Musician(_id_, name, instrument, position, nationality)

PerformedIn(_id_, _showID_)

id refers to the attribute of the same name in Musician
showID is a foreign key referring to Show