COMP3350 Advanced Databases
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
School of Information and Physical Sciences
SAMPLE EXAMINATION
COMP3350 Advanced Databases
Question 1.
(a.) Map the following EER model to relational model
(b.) Normalise the following relational model to BCNF. LecId is the primary key.
LecturerInfo(lecId, lecName, salary, dno, dname, dpone)
The following functional dependencies exists:
FD1: lecId lecName, salary, dno
FD2: dno dname, dphone
Question 2.
Consider the following schema which contains information of employees:
CREATE TABLE Employee(
eid INT PRIMARY KEY, -- Employee id
ename VARCHAR(100), -- Employee name
salary FLOAT, -- Employee salary
manager INT REFERENCES Employee) -- Employee id of manager
go
Marcel, a team member, has written the following code for the specifications given. The code is giving the incorrect results.
You need to review and state any errors or omissions. State the line numbers where you find any error(s) or where you would insert any T-SQL code. Specify any T-SQL code you will insert.
Note: There are no syntax errors in the T-SQL code below.
(a.) Print all employees under Henry including his subordinates. Note that Henry's employee id is 3.
1. With CTE_Hierarchy(employeeNo, employeeName, level) AS
2. (select e.eno, cast(e.ename as nvarchar(255))
3. from Employee e
4. where e.eno = 3
5. union all
6. select e.eno, e.ename
7. from Employee e, CTE_Hierarchy m
8. where e.manager = m.employeeNo
9. )
10. select employeeNo, employeeName from CTE_Hierarchy
11. where e.eno = 3
(b.) Enforce the following business rule:
An employee’s salary is always less than his/her manager.
1. CREATE TRIGGER tr_Verify_BR1
2. ON Employee
3. FOR UPDATE
4. AS
5. BEGIN
6. IF ((SELECT COUNT(*)
7. FROM Employee e, Employee m
8. WHERE e.manager = m.eid AND e.salary >= m.salary)>0)
9. BEGIN
10. RAISERROR ('Employee should have a salary less than his/her
manager', 9, 1)
11. END
12. END
Question 3.
(a.) State major differences between OLTP and Data Warehousing systems. (b.) What is the goal of query optimization? Why is optimization important?
(c.) Consider the following table
Product(productNo, productName, color, size, manufacturer,
description)
productNo is the primary key in the Product table. There are 10,000,000 products in the table distributed equally in 100,000 pages on disk.
A popular query which obtains the product name for a given product number. This query is shown in SQL below:
SELECT productName
FROM Product
WHERE productNo = ‘
To improve the performance of the above workload, you are given the choice of the following alternatives:
Alternative 1: No indexes on Account table
Alternative 2: Unclustered B+ tree index on
Alternative 4: Unclustered B+ tree index on
Alternative 5: Unclustered Hash index on
Which Alternative would you choose? Why? Justify your answer by estimating the cost (in terms disk I/Os) by considering query plans with different alternatives above.
Question 4.
(a.) Briefly describe a transaction in the database context and its properties.
(b.) Explain the conflicts that may cause anomalies. Give examples of anomalies that are caused by such conflicts.
(c.) What is meant by an unrecoverable schedule? Give an example of a schedule with an unrecoverable schedule. How does Strict 2PL protocol ensure recoverable schedule
(d.) Consider the following schedule which uses Strict 2PL protocol.
i. Determine whether a deadlock exists in the above schedule. Explain how you obtained at your answer.
ii. If a deadlock has occurred, how does the DBMS resolve the deadlock?
Question 5
(a.) Discuss the terms:
i. scaling out:
ii. scaling up:
(b.) Give reasons for the development of NoSQL technologies given that mature relational database technology already existed.
(c.) Describe the main types of NoSQL systems. Give examples of products in each type.
(d.) What is the CAP theorem? Which of the three properties are most important in NoSQL systems.
Question 6.
(a.) What ACID properties does Recovery Manager guarantee?
(b.) Briefly explain the following terms:
- Steal
- Force
(c.) Why is No Steal - Force approach trivial but not practical and Steal - No force approach desirable?
(d.) Explain how ARIES algorithm performs a transaction abort using an example.
2023-07-22