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.

Normalisation

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.