Database systems

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)

f.Include your Student ID :11694692 and Full Name:Mohammad Aleemuddin  under the ERD (in Draw.io) then copy the whole ERD as an image to your Word Document