辅导MovieExec、讲解java/c++编程语言、辅导python、讲解RichExec

Assignment 2 (Due Dec 1, 2018)

There are 5 questions in this assignment.

Question 1 – Views (12 marks)

There are 3 tables in the schema as listed below.

MovieStar(name, address, gender, birthdate)

MovieExec(name, address, certNo, netWorth)

Studio(name, address, presCNo)

a) Construct the following views:

i. A view RichExec given the name, address, certificate number and net worth of all

executives with a net worth of at least HKD 10,000,000.

ii. A view StudioPres given the name, address, certificate number and net worth of

all executives who are studio presidents.

iii. A view ExecStar given the name, address, gender, birthdate, certificate number

and net worth of all individuals who are both executives and stars.

b) Which of the views in (a) are updatable?

Question 2 – Functional Dependencies (18 marks)

a) Suppose a relation has no attribute that is functionally determined by all other attributes.

Show that the relation has no nontrivial functional dependencies at all.

b) For the given relation R(A,B,C,D,E) with functional dependencies AB->C, C->D, D->B

and D->E,

i. Indicate all the BCNF violations if occur.

ii. Decompose the relation, if necessary, into collections of relations that are in

BCNF.

iii. Indicate all the 3NF violations if occur.

iv. Decompose the relation, if necessary, into collections of relations that are in 3NF.

Question 3 – Normalization (15 marks)

Convert the ERD of your answer in Question 2b of Assignment 1 into tables and perform further

normalization as needed. After the conversion, write down FDs for each table. If a table is not in

BCNF, explain why and split it into two or more tables that are in BCNF.

Question 4 – Normalization (15 marks)

For the following description of an accounting database, identify functional dependencies and

construct normalized tables. You should try to provide a set of BCNF tables as far as possible.

Justify if you need to relax some of your tables which are not in BCNF.

The primary function of the database is to record entries into a register. A user can have

multiple accounts and there is a register for each account.

Information about users includes a unique user number, a name, a street address, a city, a

province and e-mail address (optional).

Accounts have attributes including a unique number, a unique name, a start date, a last

check number, a type (checking, investment, etc.), a user number and a current balance

(computed). For checking accounts, the bank number (unique), the bank name, and the

bank address are also recorded.

An entry contains a unique number, a type, an optional check number, a payee, a date, an

amount, a description, an account number, and a list of entry lines. The type can have

various values including ATM, next check number, deposit, and debit card.

In the list of entry lines, the user allocates the total amount of the entries. An entry line

includes a category name, a description of the entry line, and an amount.

Categories have other attributes not shown in an entry line: a unique category number

(name is also unique), a description, a type (asset, expense, revenue, or liability), and a

tax-related status (yes or no).

Categories are organized in hierarchies. For example, there is a category Auto with

subcategories Auto:fuel and Auto:repair. Categories can have multiple levels of

subcategories.

Question 5 - Indexing (15 marks)

Let P denotes the number of pages for storing all records of a database, R the number of records

per page, C the cost for reading or writing a page, and H the time to evaluate a hash function.

Assume that page reads and writes and hash function evaluations are the only relevant

computational costs. Suppose we have the following file organizations and indexes:

Sorted File

Unclustered B+ tree Clustered Hash Index

Provide a worst case analysis for the computational time of the following operations for these

file organizations/indexes.

a) Equality Search (no duplicate matches)

b) Range Selection (with M matching records)

c) Insertion

Assume that for B+ tree and Hash indexes all pages are filled with 80% and that the size of a

record id is 0.1 times the size of a record.

Question 6 - Indexing (10 marks)

Consider the table StaffReport below.

ReportNo StaffName StaffID Performance

004 Tom 1001 A

003 Tom 1002 B

005 Stephen 1005 C

023 John 1007 A

012 John 1008 C

019 Cody 1004 C

007 Stephen 1010 B

038 Cody 1100 D

029 Cody 1202 C

016 John 1101 B

015 John 1031 D

045 Tom 1021 B

022 Peter 1011 C

027 Stephen 1302 A

(a) Construct a B+ tree with maximum number of keys of to 4 for StaffID of the table above.

The insertion order is the same as the row order in the table. Show steps.

(b) Show steps in finding the performance of staff with IDs between 1005 and 1031.

Question 7 - Concurrency Control (15%)

Consider the following sequence of data access requires to two data items A and B from three

transactions T1, T2 and T3. The intervals between the requests in each transaction reflect the

time laps between these requests if the transaction is the only transaction in the system.

time1 2 3 4 5 6 7 8 9 10 11 12 13

T1 R(A) R(A) R(B) W(B) Commit

T2 R(B) R(A) W(B) W(B) Commit

T3 R(A) W(A) Commit

(a) Suppose A = 2 and B = 1 initially. W(B) is B=B+A and W(A) is A = A + 1. If the three

transactions are executed serially, how many different sets of values for A and B

afterwards?

(b) Build the precedence graph of the above schedule shown in the table.

(c) Give an equivalent serial schedule. If not possible, explain why.

(d) Suppose T3 did not run at all. Would the precedence graph be different? Explain.