Nova Southeastern University - MSIT 630Final_MSIT630_Winter2018. 100% Corrected.

Document Content and Description Below

Final Exam MSIT 630 Database Systems (Winter 2018) Total: 50 points Name: Marlon Johnson 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). True Explain:This true because both are used in the AQL language. DDL is used to build and modify tables and indexes while DML is used to insert, update and delete. 2, View is not updatable. False Explain: This is false because some views are updateable. 3, For Character data type, value NULL is equivalent to empty string ‘’. False Explain:This is false because all nulls are handled the same way. 4, A table has only one primary key, but it can have more than one foreign keys. True Explain:True because a table can be linked with multiple tables, thus multiple foreign keys but, a table only has one unique primary. 5, In SQL, an asterisk (*) can be used to express “all columns”. True Explain:The (*) represents the cartesian product, making it able to express all attributes in rows and columns. 6, If a schema satisfies BCNF, then it also satisfies 3NF. True Explain: BCNF is a superior form to 3NF, as such, 3NF has to be satisfies before reaching BCNF 7, A secondary index can be either dense or sparse. False Explain: A secondary index can only be dense. A cluster key however, may be sparse 8, Given a hash function h and two search-key values, Ki and kj, if ki≠kj, then h(ki)≠h(kj). False Explain: h is h(ki)=h(kj) and h is very unlikely to to perform a one-to-one mapping. 9, ΠLname, Fname, Salary(σsalary>30000(Employee)) is equivalent to σsalary>30000(ΠLname, Fname, Salary(Employee)). True This study resource was shared via CourseHero.comExplain: These two queries would produce similar results. 10, since Natural-join operations are associative, (E1 E2) E3 = E1 (E2 E3), the ordering of natural-join is not important for query optimization. False 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) The underlined attribute(s) in each relational schema is primary key. Employee contains employee details, Super_ssn is the SSN of supervisor, which is a foreign key that refers to Ssn in table Employee, and Dno is the department number, which is another foreign key that refers to Dnumber in table Department. Department contains department details and Mgr_ssn is SSN of the department manager, which is a foreign key that refers to Ssn in table Employee. Project contains project details, and Dnum is a foreign key which refers to Dnumber in table Department. Works_on records who work on which project, Essn is a foreign key that refers to Ssn in table Employee, and Pno is another foreign key that refers to Pnumber in table Project. 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’. Ans: select bdate, address From employee 2, Retrieve the name and address of all employees who work for the ‘Research’ department. Ans: select fname, lname, address from employee, department where dnumber = dno and dname = ‘Research’; 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. Ans: select M. fname, M. lname, J. fname, J. lname from employee as M, employee as J where M. superssn = J.ssn; This study resource was shared via CourseHero.com4, Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary. Ans: select sum(salary), max(salary), min(salary), avg(salary) from employee 5, For each project, retrieve the project number, the project name, and the number of employees who work on that project. Ans: select pnumber, pname, count(*) from project, employee, works_on where pnumber = pno and ssn = essn group by pnumber, pname; 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; Ans: π fname, lname, sex, Address(Employee) Describe: The SQL equivalent of this returns the first name, last name, sex and address from the table Employee. 7, Select Fname, Lname From Employee Where Sex=’M’; Ans: π fname, lname(σ sex = M (Employee)) Description: The SQL equivalent of this statement will result in the first name and last of the employees who are male. Describe what the following relational algebra expressions return and give the equivalent SQL statements. (2 points each) 8, ΠLname, Fname, Salary(σsalary>30000(Employee)) Ans: Select Lname, Fname, Salary From Employee Where salary > 30000 Description: The algebraic expression equivalent of this displays the first name, last name and salary of the employees who earn more than 30000. 9, ΠDname, Lname, Fname(Department Mgr_ssn=SsnEmployee) Ans: Select Dname, Lname, Fname From Employee join Department Where Mgr_ssn = Ssn This study resource was shared via CourseHero.comDescription: The algebraic expression equivalent of this displays the department name, last name and first name of the manager and which department that manger is a part of. Part 3: Questions 1, (4 points) 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. Ans: a → b (given) aa → ab (augmentation rule) a → ab (union rule) a → c (given) ab → cb (augmentation) a → bc (transitivity) This is what was to be shown. 2, (6 points) 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. Ans: 1. Search the first level index, follow the second pointer to the next Level 2. search the next level. Follow the first pointer to the next level. 3. Search the leaf node; then follow the first pointer with key value 19. b. Find records with a search-key value which is between 5 and 19, inclusively. This study resource was shared via CourseHero.comAns: 1. Search the first level index, 5 is not found. Since 5 is less than 19 Go left, following the first pointer to the next level. 2. search the next level. Follow the second pointer to the next level. 3. search the leaf node; then follow the first pointer with key value 5 4. after access return to the leaf node 5. follow the pointer to the next leaf block in chain. The contents of this leaf block is 11 and 17, no 19, return. Follow the pointer to the next leaf block in chain. 6. Follow the first pointer to the records with the key value 19 [Show More]

Last updated: 2 years ago

Preview 1 out of 5 pages