Homework 3 Solution

1. Assume the following tables for this problem:




ComputerProduct(manufacturer, model, price)




Desktop(model, speed, ram, hdd)




Laptop(model, speed, ram, hdd, weight)




A computer product is either a desktop or a laptop.




Using a CHECK constraint on the Laptop table, express the constraint that a laptop cannot have weight larger than 5kg. You do not need to show the entire CREATE TABLE statement. Show only the CHECK constraint part in the CREATE TABLE statement.



2. You are the DBA for the VeryFine Toy Company and create a relation called Employees(ename, dept, salary). For authorization reasons, you also de ne views EmployeeNames(ename) and DeptInfo(dept, avgsalary). The second column lists the average salary for each department.




Show the view de nition statements for EmployeeNames and DeptInfo.



You want to authorize your secretary, Mike, to re people (you will probably tell him whom to re, but you want to be able to delegate this task), to check on who is an employee, and to check on average department salaries. What is the minimum set of privileges you should grant to Mike?



Continuing with the preceding scenario, you do not want your secretary to be able to look at the salaries of individuals. Does your answer to the previous question ensure this? Be speci c: Can your secretary possibly nd out salaries of some individuals (depending on the actual set of tuples), or can your secretary always nd out the salary of any individual he wants to?



(d) You decide to go on an extended vacation, and to make sure that emergencies can be handled, you want to authorize your boss Joe to read and modify the Employees relation and the Employ-eeNames relation (and Joe must be able to delegate authority, of course, since he is too far up the management hierarchy to actually do any work). Show the appropriate SQL statements. Can Joe read the DeptInfo view?







You decide to go on an extended vacation, and to make sure that emergencies can be handled, you want to authorize your boss Joe to read and modify the Employees relation and the Employ-eeNames relation (and Joe must be able to delegate authority, of course, since he is too far up the management hierarchy to actually do any work). Show the appropriate SQL statements. Can Joe read the DeptInfo view?
























1



We want to store the table created by the following SQL statement into a disk.



CREATE TABLE Class(




dept CHAR(2),




cnum INTEGER,




sec INTEGER,




unit INTEGER,




year INTEGER,




quarter INTEGER,




title CHAR(30),




instructor CHAR(20)




)




We need to store tuples for 1,000 classes that have been o ered so far. 10 classes are o ered every year. The tuples are stored in random order (i.e., they are not sequenced by any attribute). A disk of the following parameters is used for storing the table.




3 platters (6 surfaces) 10,000 cylinders




500 sectors per track 1024 bytes per sector




6,000 RPM rotational speed 10ms average seek time




What is the capacity of this disk?



What is the average time to read a random sector from the disk?



Assume one disk block corresponds to one disk sector. How many disk blocks are needed to store the above table with 1,000 tuples?



We want to run the following query by scanning the entire table.



SELECT * FROM Class WHERE year = 2005




Assuming that all blocks for the table is allocated sequentially, how long will it take to run the query? Assume that the disk head is not on the same track where the rst block of the table is stored.




Now assume that due to frequent updates to the table, disk blocks are allocated such that, on average, sequentiality is broken every three blocks. That is, the table is stored in 24 randomly located \clusters" of 3 consecutive blocks. Assuming that we scan the entire table to execute the above query, how long will it take?






















































2