CSCC43 – Fall 2023 University of Toronto Scarborough
Intro to Databases – Assignment #3
Nov18, 2023
This assignment may be completed in a max group of 2 .
Please provide detailed answers to the following exercises.
Read the note on academic Integrity.
Instructions:
There are two parts in this assignment.
• Part 1 (3 questions – 40 points)
o Requires you to design ER Diagram given specification using any online drawing tool.
o Requires conversion from ER diagrams to relational schemas and improved version of
ER diagrams.
o Requires you to design an ER diagram given a relational schema.
o Note: Handwritten solutions/diagrams will not be accepted.
• Part 2 (6 questions with subparts - 60 points)
o Requires you to answer questions on functional dependencies and normal forms.
Submission: Quercus
For this assignment, you need to submit all typed solutions in a single PDF named
Assignment3.pdf. You can use LaTeX or any other word processing software. ER Diagrams should
be computer generated as well and should not be hand drawn.
Note: Late submissions with 10% penalty will be accepted until up to December 4th 11:59 pm.
No submissions will be accepted after that.
Assignment #3
Total: 100 points
Due Date: Dec 2nd, 2023 11:59pm
CSCC43 – Fall 2023 University of Toronto Scarborough
Part 1: Entity Relationship Model [40 marks]
Question 1 [25 marks]
For this question, you will design Entity Relationship Diagram and a database schema for
an Ice Hockey Tournament.
System Functional Requirements:
The administrator of tournament website and the database is the primary user who manages
the data associated with the tournament that includes information about players, teams,
coaches, matches, referees, etc.
The administrator wants following features included in your design and describes these
features as follows:
For each match, we store the series and the tournament day on which it takes place, which
match it is, (e.g., first match, second match, etc.) the date with day, month, year, the teams
involved in the match with the name of the city and the coach, and finally for each team
whether that team played at home. Each team can have multiple coaches and for each coach,
we record their name, salary, and city of birth.
We store the name and the surname of each player in each team with his date of birth and
main position. We store, for each day, how many points each team has, and we also store
for each match, the player of each team who played and in which position each player played
(the position can change from one game to another). For each match, we store the referee,
with first name, surname, city, and region of birth.
The match played as scheduled must be distinguished from those postponed. For a
postponed match, we store the date in which it is actually played. We also identify the
matches played in a city other than that of the home team; for each of these, we store the
city in which it took place, as well as the reason for the variation venue. For each player, we
are interested in the city of birth.
We also record the contracts between players and teams including the past contracts with
the date of beginning and end of the contracts for each player in each team. It is possible that
a player can have different contracts with the same team in different periods. For all contracts
we wish to know the date of commencement. Similarly, we also record contracts between
coach and teams.
Question 1.1 [15 marks]:
Design and draw an ER schema that captures the information given above. Your schema
should model explicitly entities and relationships in the domain of Ice Hockey Tournament,
also their attributes, generalization relationships, keys, and cardinality constraints.
Make necessary assumptions in order to complete your schema and state those assumptions
along with the diagram.
You may use domain knowledge to complete information missing in the given specifications.
CSCC43 – Fall 2023 University of Toronto Scarborough
Question 1.2 [10 marks]:
Remove any generalizations (subclass), unnecessary data and structural redundancies,
multi-valued attributes, optional relationships, and irregularities from your model.
If you did modify your ER schema, show the improved version of ER Diagram in this stage.
By removing weak entity sets (if any), translate your ER diagram into Relational schema
following the PostgreSQL syntax.
Question 2 [15 marks]
We wish to carry out a reverse engineering operation. That is, given a relational database,
we wish to construct its conceptual representation using the E-R model. The database is for
an application concerning trains and railway stations and is made up of the following
relations:
o STATION (Code, Name, City) with a referential constraint between the attribute
City and the CITY relation;
o CITY (Code, Name, Region);
o ROUTE (From, To, Distance), with referential constraints between the attributes
From and the relation STATION and between the attribute To and the relation
STATION; this relation contains all and only the pairs of stations connected
directly by a route (that is without intermediate stations);
o TRAINTIMETABLE (Number, From, To, DepartureTime, ArrivalTime) with
referential constraints between the attributes From and the relation STATION
and between the attribute To, and the relation STATION;
o TRAINROUTE (TrainNumber, From, To) with referential constraints between the
attribute TrainNumber and the relation TRAINTIMETABLE and between the
attributes From and To and the relation STATION;
o STOPTIME (TrainNumber, Station, Arrival, Departure) with referential constraints
between the attribute TrainNumber and the relation TRAINTIMETABLE and
between the attribute Station and the relation STATION;
o ACTUALTRAIN (TrainNumber, Date, DepartureTime, ArrivalTime) with a
referential constraint between the attribute TrainNumber and the
TRAINTIMETABLE relation;
o ACTUALSTOP (TrainNumber, Date, Station, Arrival, Departure) with a referential
constraint between the two attributes TrainNumber and Station and the
STOPTIME relation.
Question 2.1 [15 marks]
For the above schema, construct its conceptual representation using the E-R model.
Recall that derived redundancy can be present in terms of attribute, entity, or relationship.
Find out any such derived redundancy in this model.
CSCC43 – Fall 2023 University of Toronto Scarborough
Part 2: Functional Dependencies and Normalization [60 marks]
Question 1 [5 marks]
Consider a relation S with six attributes A, B, C, D, E, and F. You are given the following
dependencies: AB → C, BC → D, D → E, CF → B.
a) What are all the non-trivial functional dependencies that follow from the given
functional dependencies? Make sure that the functional dependencies you list have
exactly one attribute on the right hand side.
b) What are all the candidate keys of S?
c) What are all the superkeys of S that are not candidate keys?
Question 2 [5 marks]
Consider a relation R with five attributes A, B, C, D, and E. You are given the following
dependencies: A → B, BC → E, and ED → A.
a) List all the candidate keys for R.
b) Is R in 3NF? Explain why or why not.
c) Is R in BCNF? Explain why or why not.
Question 3 [15 marks]
For all of the parts below, assume you are given a relation R with four attributes A, B, C, and
D. In each part you are also given a set of functional dependencies, assume those are the
only dependencies that hold for R and do the following:
(i) Identify the candidate key(s) for R.
(ii) Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).
(iii) If R is not in BCNF, give a lossless-join decomposition of R into BCNF.
a) C → D, C → A, B → C
b) B → C, D → A
c) AB → C, AB → D, C → A, D → B
CSCC43 – Fall 2023 University of Toronto Scarborough
Question 4 [15 marks]
In each part below you are given a relation with attributes as well as a set of functional
dependencies that hold for that relation. For each part, do the following:
(i) List all the candidate keys for the relation.
(ii) Give a lossless-join and dependency-preserving decomposition of the relation
into 3NF.
a) R(A, B, C, D, E); {A → BC, CD → E, B → D, E → A}
b) S(A, B, C, D, E, F); {C → A, CD → E, A → B, D → F}
c) T(A, B, C, D, E); { A → CD, B → CE, E → B}
Question 5 [10 marks]
For each of the following relation schemas and dependencies do the following:
(i) Find all the 4NF violations.
(ii) Decompose the relations into a collection of relation schemas in 4NF.
a) R(A, B, C, D) with MVD's A àà B and B àà CD.
b) R(A, B, C, D, E) with MVD's A àà B and AB àà C and FD's A à D and AB à E.
Question 6 [10 marks]
Give counterexample relations to show why the following rules for MVD's do not hold. Hint:
apply the chase test and see what happens.
a) If A àà BC, then A àà B.
b) If A àà B, then A à B.