=========================preview======================
(COMP334)midterm01F_sol.pdf
Back to COMP334 Login to download
======================================================
Midterm Solution
Question 1:
You are hired by a big bank. It has a head quarter and a number of branches over Hong Kong. Each of those branches has its own databases managed by a DBMS. Some of them use Oracle and some of them use SQL Server. You are asked to design a distributed database system for the bank so that a branch can access data at other branches and officers a the header quarter can access data at all branches.
a) (5%) You propose that the system will have no global schema. List two reasons that you can use to justify your decision.
Solution:
Reasons for the proposal:
1.
The DBMS is heterogeneous. Therefore, providing global schema lead to high cost and complexity.
2.
Whenever a new branch is set up, it creates its own schema and then shares it with all other branches. It is much more costly to define the global schema again and figure out the new local schema for the new branch in a top-down approach. Without the global schema, we can do it in a bottom-up approach.
b) (5%) You are expecting your colleagues to challenge you. List two reasons that your colleagues may use to against your proposal.
Solution:
Reasons against the proposal:
1.
Update of a data may not lead to an update of the same data in other branches since there is no global schema. It needs to synchronization / consistency / integrity problem.
2.
With help of the integrated view provided by the global schema, database users do not need to know where the data is. This provides higher level of transparency and hence eases the implementation of front end system.
Question 2: (30%)
A supply-part database consists of three relations as shown below.
SUPPLIER
SNO SNAME COUNTRY
S1 SN1 USA
S2 SN2 INDIA
S3 SN3 CHINA
S4 SN4 CHINA
S5 SN5 INDIA
S6 SN6 USA
SUPPLY
SNO PNO QTY
S1 P1 60
S1 P3 70
S2 P2 60
S3 P3 55
S3 P4 96
S4 P2 65
S6 P2 70
S6 P4 96
PARTS
PNO PNAME PRICE
P1 PC 10000
P2 CAMERA 8000
P3 VIDEO 5000
P4 HI-HI 3000
There are three applications:
Q1: Print SNO of suppliers who supply parts with price less than 6000.
Q2: For each supplier in USA, print SNAME, and PNO of the parts that s/he
supplies. Q3: For each supplier, print SNO, SNAME, the number of parts s/he supplies.
a) (10%) Determine a set of simple predicates that is complete minimal. Justify your answer.
Solution:
Change to application statement to SQL:
Q1: select SNO from PARTS, SUPPLY where PARTS.PNO = SUPPLY.PNO and PARTS.PRICE < 6000
Q2: select SNAME, PNO from SUPPLIER, SUPPLY where SUPPLIER.SNO = SUPPLY.SNO and SUPPLIER.COUNTRY = USA
Q3: select SNO, SNAME, COUNT(*) from SUPPLIER, SUPPLY where SUPPLIER.SNO = SUPPLY.SNO group by SUPPLIER.SNO
Simple predicate from application:
Q1: price < 6000
Q2: country = USA
Q3: null
Since price < 6000 is related to relation PARTS and country = USA i