Posted: October 17th, 2013

Database

Name:

Course:

Date:

Database

#16.20

The third normal form (3NF) of database relations specifies that references to columns, which are not dependent on the primary key, be eliminated. 3NF therefore requires the use of foreign keys to reference other separate tables. No columns in the referenced table should originate from the parent table. The relation schemas produced by Algorithm 16.6 can be in 3NF if they satisfy the following conditions. The relational table (R) must be in 2NF or 1NF. All columns should be fully dependent on the primary key. To determine whether the relation schemas are actually in 3NF we will have to prove that they satisfy the above conditions.

The algorithm in 16.6 comprises of Universal Relation R and a set of FDs (Functional Dependencies) F that are attributes of the universal relation. These FDs are in the form of sets R_{1}, R_{2} and R_{3} and are given as follows:

R_{1} (Empssn, Esal, Ephone, Dno)

R_{2} (Pno, Pname, Plocation)

R_{3} (Empssn, Pno)

R_{1} is a functional dependency of the relation schema that contains a set of attributes, which include; the Employee social security number (Empssn), employee salary (Esal), employee phone number (Ephone) and the (Dno). Therefore R_{1} or FD_{1}: Empssn → Esal, Ephone, Dno. The same is applied to the other functional determinants as follows:

R_{2} or FD_{2}: Pno → Pname, Plocation

R_{3} or FD_{3}: Empssn → Pno

For explanation purposes, the above attributes will be abbreviated with the first one or two letters in the case where attributes share the first letter. For example, Empssn will be Em, so the functional dependencies can be merged to form the universal set R as represented below;

F: {Em → EsEpD, Pno→PnPl, Em→ Pno}

F therefore represents the complete relation schema with all the attributes incorporated from the FDs. To prove that this relation schema is in the third normal form we first identify whether it is in second normal form. F is in Third normal form because there is a trivial dependency Em → Pno. This satisfies the requirement of 2NF. The second requirement is to identify if the non-prime attribute is dependent on the super keys of R. The Employee social security number (Empno) is the super key in this universal relation. The Pno also acts as a key in one of the functional determinants (i.e. R_{2}). The Pno is the non-prime attribute. Pno exhibits functional dependency in which the Empno indirectly determines the Pno.

# 16.21

Template dependencies are methods used to represent constraints among relation databases that are difficult to describe. This is done by describing the constraints using a template. In our case, the template will be made of hypothesis tuples and conclusion. The relation schema is made up of functional dependencies that can be represented in a dependency template as shown below.

R: {Em, Pno, Es, Ep, D, Pn, Pl}

**Hypothesis:**

Em_{1} |
Pno_{1} |
Es_{1} |
Ep_{1} |
D_{1} |
Pn_{1} |
Pl_{1} |

Em_{2} |
Pno_{2} |
Es_{2} |
Ep_{2} |
D_{2} |
Pn_{2} |
Pl_{2} |

X = {Em, Pno}

Y= {Es, Ep, D, Pn, Pl}

The above template is made up of several tuples. This template shows that if the tuples Es_{1}…Pl_{1} (the antecedents) appear in the database, then the corresponding tuples Es_{2}…Pl_{2} must also appear in the same database.

**Conclusion: **

Es_{1}=Es_{2}, Ep_{1}=Ep_{2}, D_{1}=D_{2}, Pn_{1}=Pn_{2}, Pl_{1}=Pl_{2} |

The above templates show the template dependencies among various tuples in the relation schema. The employee number is the super key and all subsequent tuples are dependant on the super key. The relation between these attributes is illustrated using the template above.

#16.22

Relation schema 3.5 has several inclusion dependencies. This relation schema (R) has the following sets of attributes; Employee, department, dept_locations, project, works_on and dependent which will be given as R_{1}, R_{2}, R_{3}, R_{4}, R_{5} and R_{6} respectively. Therefore;

R_{1} :{ Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno}

R_{2}: { Dname, Dnumber, Mgr_ssn, Mgr_start_date }

R_{3}: {Dnumber, Dlocation }

R_{4}: { Pname, Pnumber, plocation, Dnum }

R_{5}: { Essn, Pno, Hours }

R_{6}: { Essn, Dependent_name, Sex, Bdate, Relationship }

To find out the cases of inclusion dependency we must follow the following rules;

Reflexivity: X < X, in this case, their must be a functional dependency between R. X and its subsets. In this case, R_{1} is the superset given as R_{1}:{ Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno} the Ssn attribute is the relation attribute that links the employee table to other tables. The superset satisfies the reflexivity rule since it shows direct functional dependency with all the subsets. Another set of attributes that shows reflexivity is the department FD R_{2}: { Dname, Dnumber, Mgr_ssn, Mgr_start_date }.

Attribute correspondence: FD. X<FD. Y, the attributes in the superset should correspond to those in the set in which there is inclusion dependence. From the superset, we can now generate the R.Y subsets that have corresponding attributes. The relationship between the superset and the subset can therefore be written as R_{1} < R_{5} this two functional dependencies show attribute correspondence. For the case of R_{2} this can be represented as R_{2} < R_{3}.

Transitivity: when X<Y and Y<Z, then automatically X<Z. this rule states that if Y which is a subset of the superset X has another subset Z, then automatically Z becomes a subset of X. The relations above R_{1} < R_{5} can reflect transitivity when a subset of R_{5} is introduced, in this case R_{6}. From the transitivity rule therefore, R_{1} < R_{6} this relation therefore satisfies all the rules for inclusion dependency. In the other case R_{3 }< R_{4} therefore R_{2} < R_{4.} The above examples represent the inclusion dependencies found in the relation schema.

Place an order in 3 easy steps. Takes less than 5 mins.