=========================preview======================
(ielm230)[2002](s)final~sluoaa^_791.pdf
Back to IELM230 Login to download
======================================================
IEEM 230 NAME:
Exam II, Fall 2002, Dec 14 SID:
Time: 2 Hours, Max Score: 100

1. Normal Forms [10+10 = 20]


You are hired to design a database for storing Exam schedules in a college. The attributes of the schema, and the FDs that are true are given below. Assume that the schema is in 1NF.
EXAM( CourseNo, Section, Dept, CourseType, InstructorID, Semester, Year, Schedule, Room}

{CourseNo} {Dept, CourseType}
{CourseNo, Section, Semester, Year} {Schedule, Room, InstructorID}
{Room, Schedule, Semester, Year} {InstructorID, CourseNo, Section}

(a) Find all the candidate keys for the schema.
(b) Select one of the candidate keys as the primary key, and use it to convert the schema into 3NF.
2. Relational Algebra (RA) [5x5 = 25]


Use the attached Employee-Department-Projects Database. Write RA expressions to:
(a) Report the Last name of Employees who do not work on any project in Stafford.












(b) Report SSN and Last name of all females whose supervisor is female.
(c) Report the Last names of employees who work on all the projects in Houston.












(d) Report SSN and Last name of the manager of the department that controls the Computerization project.

(e) Report the Last name of managers who do not supervise any employee.

3. SQL [7*5 = 35]


Use the attached Employee-Department-Projects Database. Write SQL queries to:
(a) Report the last name of employees whose supervisor is younger than them. [Hint: assume that you can use > to compare dates].











(b) Report the name of the department that controls the largest project (i.e. the project that has the most number of people working in it).
(c) Report the First and Last names of Employees who have a spouse as a dependent, but no children.












(d) Report the Last name and SSN of Employees who work only on project(s) located in Houston.

(e) Report the SSN, Last name and Salary of the highest income employee of each department.












(f) Ramesh Narayan will replace the manager of the Research Department. Write the SQL command to make this change. [Note: the current manager of that department will still remain as an employee of that department].

(g) Report the SSN, Last name and number of dependents for each employee


4. Indexes [20]



Given below are the attributes and data types of a table storing records of books in a database:
create table titles (
title_id char(20), title char(80), category char(12), pub_id char(4), price money,
total_sales int, notes char(150), pubdate datetime)

Assume that the storage requirement for different data types is as follows:
char: 1 byte; money: 8 bytes, int: 4 bytes, datetime: 8 bytes.
All field separators are 1 byte, and record separators are 3 bytes.

The average time for the h