[Solved] CSE3055 Homework #8

• Consider the unnormalized relation R with six attributes ABCDEF and the following functional dependencies:

AB CDE

• F
• D

1. What is the key(s) for the relation?
2. What is the normal form of this relation? Explain it.
3. Decompose R into 3NF relations step by step if it is not in 3NF.

• Consider the following normalized relations from a database in a large retail chain:

STORE (StoreID, Region, ManagerID, SquareFeet)

EMPLOYEE (EmployeeID, WhereWork, EmployeeName, EmployeeAddress)

DEPARTMENT (DepartmentID, ManagerID, SalesGoal)

SCHEDULE (DepartmentID, EmployeeID, Date)

What opportunities might exist for denormalizing these relations when defining the physical records for this database? Under what circumstances would you consider creating such denormalized records?

• [14 pts] Consider the following two relations for Millennium College:

STUDENT (StudentID, StudentName, CampusAddress, GPA)

REGISTRATION (StudentID, CourseID, Grade)

Following is a typical query against these relations:

SELECT Student.StudentID, StudentName, CourseID, Grade

FROM Student, Registration

WHERE Student.StudentID = Registration.StudentID AND GPA > 3.0 ORDER BY StudentName;

1. On what attributes should indexes be defined to speed up this query? Give the reasons for each attribute selected.
2. Write SQL commands to create indexes for each attribute you identified in part a.

• You have a STUDENT table that has SID, Name, and Age columns. Which data pages are accessed to execute the queries below, under situations given at (a) and (b)? (Assume that index seek is used whenever possible)

1. [18 pts] The table has a clustered index on SID column, and no other indexes. The index structure and data is stored on data pages as the following:

1. Query 1: select Name from STUDENT where SID < 11

1. Query 2: select * from STUDENT where Age = 16

• Query 3: select * from STUDENT where SID = 7

1. The table has a non-clustered index on Age column, and no other indexes. The index structure and data is stored on data pages as the following:

1. Query 1: select Age, Name from STUDENT where SID < 9

1. Query 2: select Age from STUDENT where Age < 8

• Query 3: select * from STUDENT where Age = 8