Problem 1

Using the STUDENT table structure shown in the below do the following:-

Write the relational schema and draw its dependency diagram. Identify all dependencies, including all transitive dependencies.

Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.

Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.

Draw the Crows Foot ERD.

Problem 2:-

The table structure shown below contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic.

Given the structure shown in Table P6.6, write the relational schema and draw its dependency diagram. Label all transitive and/or partial dependencies.

Draw the dependency diagrams that are in 3NF. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.)

Draw the relational diagram.

Draw the Crows Foot ERD.

Problem 3:-

Suppose you have been given the table structure and data shown in Table P6.9, 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.

Given the information in the table above:-

Draw the dependency diagram.

Identify the multivalued dependencies.

Create the dependency diagrams to yield a set of table structures in 3NF.

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

Draw the Crows 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.)

Thank you for your help and efforts!!! :-)

ATTRIBUTE NAME SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE STU NUM STU LNAME STU MAJOR DEPT CODE DEPT NAME DEPT PHONE COLLEGE NAME ADVISOR LNAME Grastrand ADVISOR OFFICE T201 ADVISOR BLDG ADVISOR PHONE 2115 STU GPA STU HOURS STU CLASS 211343 200128 199876 199876 Ortiz Marketin MKTG Marketin 4378 223456 Statistics MATH ACCT ACCT MKTG 4356 Business Admin Business Admin Business Admin Business Admin Arts & Sciences 4356 4378 3420 T201 Torre Buildi 2115 2.78 45 Sophomore Senior T228 Torre Buildi 2123 2.31 T356 Torre Buil 2159 3.45 113 Chen 331 lones Buildin 3209 3.58 87 lunior Torre Buildi 3.87 75 unior