CSCC43代做、R设计编程代写

日期：2023-11-27 08:23

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.