Table 4-4 shows a relation called GRADE REPORT for a university. Your assignment is as...

Functional dependency:

• An association between two attributes or two sets of attributes in the same relational database table which is having some constraints is known as functional dependency.

• In a table, one attribute is functionally dependent on another attribute to take one value.

• Functional dependencies for the relation are numbered and illustrated below.

Functional dependency 1:

• In this relation, student name, campus address, and course major are functionally dependent on student ID.

Functional dependency 2:

• Course title, instructor name, and location are functionally dependent on the course ID of the student.

Functional dependency 3:

• Student’s grade is functionally dependent on his/her course ID and student ID.

Functional dependency 4:

• Instructor name is functionally dependent on instructor location.

(b) Normal form of the relation:

• The relation is provided in the.

• It gives the rational dependency of the anomaly. It provides precise relationships. There are no discrepancies made.

• The relation does not contain any repeated attributes.

(c) Convert the relation to 3NF (Normal Form)

Third normal form:

• For the third normal form, the first and basic requirement is that the model should be in the first and second normal form.

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.

o If any non-primary key attributes depend on any other non-primary key attribute, then it should moved or deleted.

o It is termed as transitive dependency.

• In this, new relations should be created from the above relation, which is used to connect the old and new entities to provide the relationship.

• The relations created here are STUDENT, REGISTRATION, COURSE, and INSTRUCTOR.

• By assumption and using some constraints the attributes are allocated to each relation.

The third normal form shows relation, which is created from the GRADE REPORT shown below:

• In the STUDENT relation,

o A relation does not have any repeating attributes.

o The non-key attributes are dependent on primary key StudentID.

o There is only functional dependency and no transitive dependency.

o Therefore, the relation is in 3NF.

• In the REGISTRATION relation,

o A relation does not have any repeating attributes.

o The attribute Grade inside the relation does not depend on StudentID, and CourseID.

o There is no transitive dependency

o Therefore, the relation is in 3NF.

• In the COURSE relation,

o A relation does not have any repeating attributes.

o The non-key attributes are dependent on primary key CourseID.

o There is only functional dependency and no transitive dependency.

o Therefore, the relation is in 3NF.

• In the INSTRUCTOR relation,

o A relation does not have any repeating attributes.

o The attributes inside the relation are InstructorName is functionally dependent on InstructorLocation.

o There is only functional dependency and no transitive dependency.

o Therefore, the relation is in 3NF.

(d) Relational schema for 3NF relations with referential integrity:

Referential integrity:

• Referential integrity is supported by RDBMS (Relational Database Management System).

• Relational database is basically a collection of the tables, where every table has a specific key assigned to it, known as primary key.

• The values in each row of a table are unique. Two tables are related to each other with the help of foreign key.

• Referential integrity is a special feature of RDBMS, which ensures that the data entered by the user is valid and consistent.

• It is done with the help of primary and foreign key, which links the tables together and it checks the whether the values are correctly synchronized.

The diagram to describe the third normal form relations with the relational integrity constraints is as given below:

Explanation:

• In the STUDENT relation,

o The attributes inside the relation are StudentID, StudentName, CampusAddress and Major.

o StudentID is unique in the relation and it acts as the primary key.

• In the REGISTRATION relation,

o The attributes inside the relation are StudentID, CourseID and Grade.

o StudentID and CourseID are the foreign key attributes in the relation, which are used to refer STUDENT and COURSE relations respectively.

• In the COURSE relation,

o The attributes inside the relation are CourseID, CourseTitle and InstructorName.

o CourseID is unique in the relation and it acts as the primary key.

o InstructorName is the foreign key attribute in the relation, which is used to refer INSTRUCTOR relation.

•

o The attributes inside the relation are InstructorName and InstructorLocation.

o InstructorName is unique in the relation and it acts as the primary key.

(e) 3NF relations using Microsoft Visio

Third normal form:

• For the third normal form, the first and basic requirement is that the model should be in the first and second normal form.

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.

o If any non-primary key attributes depends on any other non-primary key attribute, then it should moved or deleted.

o It is termed as transitive dependency.

• In this a new entity should be created, which is used to connect the old and new entities by adding of the relationship.

The third normal form for the GRADE REPORT relation using Microsoft Visio is shown below:

Cardinality notations in the relationship:

Explanation:

• In the above ER diagram, the relationship between entities STUDENT and REGISTRATION,

o One-to-many relationship is denoted here.

o A student can be zero or one, who can register for many or zero courses.

• The relationship between entities COURSE and REGISTRATION,

o Many-to-one relationship is denoted here.

o A student can register for zero or many courses, but a student can take only one course from that.

• The relationship between entities COURSE and INSTRUCTOR,

o Many-to-one relationship is denoted here.

o Many or zero courses can be taught by one or zero instructor.