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 Lan
...
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]
