Suppose you have been given the table structure and data given below, which was imported from an Excel spreadsheet. Attribute Name Sample Value Sample Value Sample Value Sample Value

EMP$\_$NUM

$123$

$104$

$118$

$120$

PROF$\_$RANK

Professor

Asst. Professor

Assoc. Professor

Assoc. Professor

EMP$\_$SALARY

$$150,000$

$$130,000$

$$130,000$

$$130,000$

EMP$\_$NAME

Ghee

Rankin

Ortega

Smith

DEPT$\_$CODE

CIS

CHEM

CIS

ENG

DEPT$\_$NAME

Computer Info. Systems

Chemistry

Computer Info. Systems

English

PROF$\_$CLASSES

Organic Chemistry

Computer Basics, Management Information Systems

English Literature

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

The data reflect following rules:

$1.$

A professor can have multiple advisees, but an advisee can only be assigned one employee only.

$2.$

A professor can serve on multiple committees, and a committee can have multiple professors on it$.$

$3.$

A professor can edit more than one journal together with some other professors.

$4.$

A Professor can teach more than one classes but a class is taught by one professor only. A professor might be doing research only.

$5.$

In addition, the college intends to extend the database by incorporating student data including student information $($personal information, course information$)$ and information related to classes $($Teachers$,$ rooms, timings$)$

Given the scenario,

$1.$

Draw the dependency diagram for the Table $1$ only $($requirements given in $1,2,3$ and $4).$

ICT$911$ Database Management Systems, S$320233$

$2.$

Identify the multivalued dependencies.

$3.$

Eliminate the multivalued dependencies by converting the affected table structures to $4$NF

$4.$

Draw the Crow$$s Foot ERD to reflect the dependency diagrams you drew and additional requirements specified $(1,2,3,4$ and $5).$ You might have to create additional attributes to complete tables and define the proper PKs and FKs$.$ Make sure that all of your attributes conform to the naming conventions. Make sure that all relationships are correctly represented and labelled. Any M:N relationships must be resolved using bridge$/$composite entities. Any additional attributes for bridge entities should also be identified. For example, the number of hours a professor worked on a particular journal must be identified in the bridge entity. All design principles with identification of mandatory$/$optional participation, weak$/$strong relationships must be clearly identified.

$5.$

Create database and tables identified in the ERD, with correct data types, constraints and insert data $($there are only $4$ sample values, extend it to minimum of $10$ records for each major table$).$

$6.$

Apply Following queries$/$ Generate reports on your database and submit the screenshot $($queries plus results$).$

a$.$

Display name of professors working in the department of chemistry

b$.$

Display the names of students that are being advised by Professor Ghee and committees he has been working on$.$

c$.$

Display the number of professors working on each committee $($Using Group By statement$)$

d$.$

Alter the table EMPLOYEE and add an additional column called Cumulative$\_$ Salary. The cumulative$\_$Salary column will contain values for teaching staff only. The column should have the values populated with the help of a query. The value will be annual salary of the professor summed up with the remunerations that the professor has worked on editing a journal $($A professor is paid $$50/$hour paid for editing a journal$)$

e$.$

Delete an employee record where employee$\_$no is $104.$ Explain in report what happens when you try to delete the record.

f$.$

The name of the department Computer Info. System has been updated to Computer Systems. Reflect the change in the database