Question

Create a Crow’s Foot Notation Entity Relationship Diagram (ERD) to support the following business operations:

The local city youth league needs a database system to help track children who sign up to play soccer. Data needs to be kept on each team, the children who will play on each team, and their parents. Also, data needs to be kept on the coaches for each team and matches.

You need to store the

Team ID

Team name

Player ID

Player first name,

Player last name, and

Player age of all players

Each team may or may not have a Player. A Player must have a Team and should belong to only a one team.

You also need to store the

Coach ID

Coach’s first name,

Coach’s last name,

Coach’s home phone number.

A Team may have many Coaches or may not have a Coach. A Coach must have only one team assigned to him or her.

For each parent, the

Parent’s ID

last name,

first name,

Home phone number, and

Home address need to be kept in the system.

A Player may have many Parents. A Parent may have only one Player.

Teams play matches. For each match we need to keep track of the following:

Match ID

The date on which the game is played

The final result of the match

Host team ID (HID) and Guest team ID (GID)

a) Identify 4 business rules which describes relationships between entities and write the type of the relationship as given below, based on the description of operations given in the question.

Example: A Player may have many Parents: One –to- many relationship between Player and Parent. [2 Marks]

b) Determine the functional dependencies (FD). [3 Marks]

Eg. StudentID  Fname, address, ......, ......

c) Normalise these relations to 3rd normal form, ensuring that the resulting relations are dependency-preserving. You need to explain each step clearly. [10 Marks]

MN405 - Data and Information Management - Final Assessment Trimester 1, 2020

Page 4 of 8

d) ER diagram

a. Represent the structure of your database visually by using the entity-relationship (ER) diagram. You are required to:

i. Draw the ER diagram on a paper and include image in the Word document.

ii. Name all entities including your name.

 For example : If you have Entity name Student, you will name it as “Yourname:Student”. If “Sam Smith”is drawing the ER diagram he will name “Student” entity as “Sam_Smith:Student”.

iii. If you make any assumptions about data that are not explicitly given in the problem, these must be described.

[15 Marks]

NB:

This should be your own individual work. If not, it is considered as cheating and you will get zero marks for the whole exam.