MSIT 630 Database Systems FINAL EXAM.

Document Content and Description Below

MSIT 630 Database Systems (Winter 2015) FINAL EXAM Final Exam MSIT 630 Database Systems (Winter 2015) Total: 50 points Name:... ___Solaiman Hossain_____ 8:00AM, 4/23/2015 – 10:00PM, 4/23/2015 Student-ID:____N01652504____ Part 1: True or False, please explain why. (2 points each) 1- SQL (Structured Query Language) is both DDL (Data Definition Language) and DML (Data Manipulation Language). 2- View is not updatable. 3- For Character data type, value NULL is equivalent to empty string. 4- A table has only one primary key, but it can have more than one foreign keys. 5- In SQL, an asterisk (*) can be used to express “all columns”. 6- If a schema satisfies BCNF, then it also satisfies 3NF. 7- A secondary index can be either dense or sparse. 8- Given a hash function h and two search-key values, Ki and kj, if ki≠kj, then h(ki)≠h(kj). 9- ΠLname, Fname, Salary(σsalary>30000(Employee)) is equivalent to σsalary>30000(ΠLname, Fname, Salary(Employee)). 10- Since Natural-join operations are associative, (E1 E2) E3 = E1 (E2 E3), the ordering of natural-join is not important for query optimization. Part 2: Case study The following tables form part of a database held in a relational DBMS: Employee(Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno) Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date) Project(Pname, Pnumber, Plocation, Dnum) Works_on(Essn, Pno, Hours) Based on the schema defined above, write SQL statements to answer the following queries: (2 points each). 1- Retrieve the birth date and address of the employee(s) whose name is ‘John B. Smith’. Answer: 2- Retrieve the name and address of all employees who work for the ‘Research’ department. Answer: 3- For each employee, retrieve the employee’s first name and last name and the first name and last name of his or her immediate supervisor. Answer: 4- Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary. Answer: 5- For each project, retrieve the project number, the project name, and the number of employees who work on that project. Answer: Describe what the following SQL statements return and give the equivalent relational algebra expressions. (3 points each) 6- Select Fname, Lname, Sex, Address From Employee; Answer: Fname, Lname, Sex, Address(Employee); 7- Select Fname, Lname From Employee Where Sex=’M’; Answer: Fname, Lname(Sex(Employee)); Describe what the following relational algebra expressions return and give the equivalent SQL statements. (2 points each) 8- ΠLname, Fname, Salary(σsalary>30000(Employee)) SELECT Lname, Fname, Salary FROM Employee WHERE salary>30000; 9- ΠDname, Lname, Fname(Department Mgr_ssn=SsnEmployee) Part 3: Questions 1- Use Armstrong’s axioms to prove the soundness of the union rule. Union rule: If ab holds and ac holds, then abc holds. 2- From the hint, we derive,  a b [as given in rule]  aa ab [augmentation rule]  a ab [union of identical sets]  a c [as given in rule]  ab cb [augmentation rule]  a bc [transitivity rule & set union commutativity] 3- For B+ Tree below, show the steps involved in the following queries: a- Find records with a search-key value which is great than 18. b- Find records with a search-key value which is between 5 and 19, inclusively. - [Show More]

Last updated: 2 years ago

Preview 1 out of 6 pages