闪电代写 -代写CS作业_CS代写_Finance代写_Economic代写_Statistics代写_代码代做_IT代写_加急帮助

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

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 with height 2. Alternative 3: Clustered B+ tree index on with height 2.

Alternative 4: Unclustered B+ tree index on with height 3.

Alternative 5: Unclustered Hash index on . Assume 1.2 Disk I/Os to get has bucket on average.

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.