ICT321: When Converted to a Relational Schema How many Foreign Keys will Exist: Database Design & Modelling Assignment, SUSS, Singapore

Section A: Database Analysis: Modelling Data

Consider the following ER diagram.

Tasks:

1. When converted to a Relational Schema
   • How many Foreign Keys will exist in the Task relation?
   • How many Foreign Keys will exist in Project relation?
2. Consider the Project Relation
   • What is the name of the Foreign Key in this relation?
   • Would this foreign key be allowed to have Null values?
3. Consider the Task Relation
   • What is the name of the Foreign Key in this relation?
   • Would this foreign key be allowed to have Null values?
4. Imagine that you now want to build the database based on the Relational Schema
   • Which table would you have to create first? Leader, Project, or Task?
   • Which table would you have to create the second? Leader, Project, or Task?
   • Which table would you have to create last? Leader, Project, or Task?
5. Explain the relationship between
   • Task and Employee by reading Left to
   • Task and Employee by reading Right to

Section B: Database Design: Logical Database Design And SQL

Consider the following ER diagram.

Tasks:

1. Convert this ERD to a Relation/Relational Schema. Indicate all PKs and FKs.
2. What is the PK of ENROLMENT?
3. Write the SQL statement to create a student: STU101, Henry Bloggs
4. Write the SQL statement to create a subject: DDMG, Database Design, and Modelling?
5. Write the SQL statement to enroll Henry into Database Analysis and Design?
6. Write the SQL statement for the following scenarios:
   • After doing very poorly in the Final Exam, Henry scores an “N” (a fail), update his record.
   • Six months later, Henry sits another exam for HIT1402 and obtains a “D” (a Distinction)
7. Can this database design record the fact the Henry scored an “N” in one semester and then a “D” in the next semester? Why?

Section C: Case Study – Normalization

The PSB Sudoku Championships

“The PSB Sudoku Championships” are about to take place. A number of competitors will compete in the championships. Each competitor has a unique competitor id and a name. A number of Sudoku Puzzles have been generated for the championships. Each puzzle has a unique number and a Grade (such as Easy, Hard, Diabolical).

E.g. Puzzle No: P222 Grade: Hard

During the championships, a puzzle is simultaneously given to a number of competitors. As each competitor completes a puzzle his/her time is recorded. The organizers of the championships want to be able to produce reports such as these:

The organizers of the championships provide you with the following sample report:

Tasks:

1. Create an ERD solution based on the above narrative.
2. Convert the ERD to a relational schema.