=========================preview======================
(IELM230)asgt5.pdf
Back to IELM230 Login to download
======================================================
IELM 230 Industrial Data Systems, Fall 2005
Assignment 3. Max score: 25 [ 2+3+3+4+2+3+8]. Due Date: Dec 8, 2005

SQL and Indexes

For each of the following questions, please use the Employee-Department-Projects Database tables that you also used in your lab. In each question, write the SQL query(s) to give the required answer. No need to show expected output (unless specifically asked).

Q1. Report the Last Name and Address of Employees who are not in Dept 5.

Q2. Report the SSN and Last Name of Employees who do not have a Son as a dependent.

Q3. Report SSN and Last name of all employees whose supervisor is female.

Q4. Report the SSN and Last name of employees who work on all projects.
[Hint: you can use the double-NOT EXISTS for this query, as discussed in class].

Q5. Create a view called EmpWorksOn with the following fields: SSN, Lname, Fname, Dept (of Employee), Project No, Project Name, Project Location Hours per week.

Q6. Using the view created in Q5, write a SQL query to report SSN of those employees of Research Dept who spend more than 30 Hours per week on projects in Stafford.

Q7. [Indexes] The HKUST ARR maintains the records of 30,000 students, past and present. Assume that the table containing this data was created using the following command:

CREATE TABLE Student (
Name char(50),
admitDate datetime,
Program int,
Department int,
FTorPT char(2),
Sex char(1),
HomePhone char(12),
HomeAddress char(150),
MobilePhone char(8),
SID char(10),
GraduationDate datetime,
PRIMARY KEY (SID)
)

The database file is stored in a hard disk formatted with 1024 Byte blocks. The DBMS uses 2 byte end-of-field separators, and 2-byte end-of-record separators. An int requires 4 Bytes, and a datetime requires 16 Bytes.
The main file operation delays include hard disk seek time = 10 msec, read/write time = 4 msec, and transfer time= 1 msec. The CPU time for transactions is negligible.

A primary index was created using SID, and a secondary index was created using Name.

(a) Estimate the worst case time to modify the HomeAddress of a student given his/her Name.
(b) Estimate the worst case time in searching the data of a student, given the MobilePhone.
(c) Estimate the worst case time to modify the GraduationDate, given the SID.