database question I need to Perform the following steps for the STU_DORM and EMP

Question

database question I need to Perform the following steps for the STU_DORM and EMPLOYEE relations below:

Identify all candidate keys.

Look for the functional dependencies in the relation.

Ask if any determinants exist that are not candidate keys.

Place the columns of the functional dependency in a table of their own.

Make the determinant the primary key of the new table.

Leave the determinant as a foreign key in the original table.

Create a referential integrity constraint.

Normalize the following STU_DORM relation:

Hint: The resulting STUDENT and DORM relations will be:

Normalize the following EMPLOYEE relation:

Hint: The resulting EMPLOYEE and DEPARTMENT relations will be:

StudentNumber LastName FirstName DormName DormCost 100 200 300 400 500 600 700 Smith Johnson Terry Jeff AbernathySusan Smith Wilcox Webber Simon Susan John Carl Carol Stephens Alexander Horan Alexander Stephens Horan Stephens 3,500.00 3,800.00 4,000.00 3,800.00 3,500.00 4,000.00 3,500.00 Copyright ©2013 Pearson Education, publishing as Prentice Hall

Explanation / Answer

/*creating a primary table of dorm with primary key*/

create table dorm(dormname nvarchar(40) constraint p_k_dorm primary key,dormcost money)

/*creating another table student with foregin key relation with dorm table in dorm column*/

create table student(student_number nvarchar(30),

lastname nvarchar(30),firstname nvarchar(30),dormname nvarchar(40) constraint f_k_stu foreign key references dorm(dormname))

/*creating a primary table of department with primary key*/

create table department(department nvarchar(30) constraint p_k_dept primary key,deptphone int)

/*creating another table employee with foregin key relation with department table in department column*/

create table employee(employee_number int,lastname nvarchar(30)

,department nvarchar(30)constraint f_k_emp foreign key references department(department)

,email nvarchar(30))

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.