Assignment 3
koprm3
instructionsdata.docxNormalisation
Value: 15%
Submission method options: Alternative submission method
Task
Suppose that you have been given the table structure and data shown in Table below, which was imported from an Excel spreadsheet.
The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal.
Table 1: Sample PROFESSOR Records
|
Attribute Name |
Sample Value |
Sample Value |
Sample Value |
Sample Value |
|
EMP_NUM |
123 |
104 |
118 |
|
|
PROF_RANK |
Professor |
Asst. Professor |
Assoc. Professor |
Assoc. Professor |
|
EMP_NAME |
Ghee |
Rankin |
Ortega |
Smith |
|
DEPT_CODE |
CIS |
CHEM |
CIS |
ENG |
|
DEPT_NAME |
Computer Info. Systems |
Chemistry |
Computer Info. Systems |
English |
|
PROF_OFFICE |
KDD-567 |
BLF-119 |
KDD-562 |
PRT-345 |
|
ADVISEE |
1215, 2312, 3233, 2218, 2098 |
3102, 2782, 3311, 2008, 2876, 2222, 3745, 1783, 2378 |
2134, 2789, 3456, 2002, 2046, 2018, 2764 |
2873, 2765, 2238, 2901, 2308 |
|
COMMITTEE_CODE |
PROMO, TRAF APPL, DEV |
DEV |
SPR, TRAF |
PROMO, SPR DEV |
|
JOURNAL_CODE |
JMIS, QED, JMGT |
|
JCIS, JMGT |
|
Given the information in Table 1:
a. Draw the dependency diagram. (3 marks)
b. Identify the multivalued dependencies. (3 marks)
c. Create the dependency diagrams to yield a set of table structures in 3NF. (6 marks)
e. Draw the Crow’s Foot ERD to reflect the dependency diagrams you drew in Part c. ( Note: You might have to create additional attributes to define the proper PKs and FKs. Make sure that all of your attributes conform to the naming conventions.) (3 marks)
NOTE
For guidance on how to normalise database relations from a dependency diagram, watch this very short YouTube video: https://www.youtube.com/watch?v=vkUyuLLgmwQ
Manually drawing the dependency diagram, or the set of 3NF relations or the relational schema is NOT acceptable.
Rationale
This assessment task will assess the following learning outcome/s:
· be able to apply normalisation techniques to optimise a database design.
Marking criteria and standards
The marking criteria for this assignment is:
|
Criteria |
HD |
DI |
CR |
PS |
|
be able to apply normalisation techniques to optimise a database design. |
Analysis identifies all PKs and functional, partial and transitive dependencies. Accurately identifies all 3NF relations, including PKs, relevant attributes and referential integrity constraints. Creates an ERD that represents all 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. |
Analysis identifies PKs and functional, partial and transitive dependencies with minor omissions. Accurately identifies 3NF relations, including PKs, relevant attributes and referential integrity constraints with minor omissions. Creates an ERD that represents identified 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. |
Analysis identifies key PKs and functional, partial and transitive dependencies. Identifies key 3NF relations, including PKs and relevant attributes and referential integrity constraints. Creates an ERD that represents identified 3NF relations and includes the related entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. |
Analysis identifies key PKs and functional dependencies and draws out relevant 3NF relations. Creates an ERD that represents identified 3NF relations and includes the related entities, PKs, attributes, relationships, cardinalities and optionalities. |
Requirements
Either save your diagrams in a PDF file or embed them in a Word document.
Don't use any other file formats
Please include your student ID, name and subject code in your submission.
