Computer Architecture > EXAM > MSIT 630 Database Systems FINAL EXAM. (All)

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: 1 year ago

Preview 1 out of 6 pages

Add to cart

Instant download


Buy this document to get the full access instantly

Instant Download Access after purchase

Add to cart

Instant download

Reviews( 0 )


Add to cart

Instant download

Can't find what you want? Try our AI powered Search



Document information

Connected school, study & course

About the document

Uploaded On

Jan 18, 2021

Number of pages


Written in



Member since 3 years

340 Documents Sold

Additional information

This document has been written for:


Jan 18, 2021





Document Keyword Tags

More From SuperSolutions©

View all SuperSolutions©'s documents »
What is Browsegrades

In Browsegrades, a student can earn by offering help to other student. Students can help other students with materials by upploading their notes and earn money.

We are here to help

We're available through e-mail, Twitter, Facebook, and live chat.
 Questions? Leave a message!

Follow us on

Copyright © Browsegrades · High quality services·