Normalisation
Normalisation
Value: 15%
Submission method options: Alternative submission method
Task
back to top
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
back to top
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
back to top
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
back to top
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.
The post Normalisation appeared first on superioressaypapers.
Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"
