CSCD34H - Assignment #4

Due Wednesday, April 2nd, 2003

PART I.

Last Step of Your PDA (45 marks)

Your final PDA assignment is to build an interactive application program front end to your PDA using the C programming language and embedded SQL. If you wish to use a different programming language, it's up to you to ensure a DB2 interface is available.

Your program should consist of a continuous loop in which:

1.
A list of at least five alternative options is offered to the user. (an additional alternative should be quit.)
2.
The user selects an alternative.
3.
The system prompts the user for appropriate input values.
4.
The system accesses the database to perform the appropriate queries and/or modifications.
5.
Data or an appropriate acknowledgment is returned to the user.

 

 

You should include both queries and modifications among your options. For example, if your PDA were about TV shows, viewers, and networks you might offer options such as
  1. Find which network produces a given show.
  2. Find the show with the highest number of viewers.
  3. Given a viewer, find all the other viewers that like at least one show that this viewer likes.
  4. Add a new show.
  5. Delete all viewers of a particular show.
  6. Quit.
We are not expecting anything fancy in the way of user interface. For example, a menu printed via printf is fine. Given the short time available, do not spend too much effort on error handling either.

Hand in your program and a script showing the program running. Each of the options should be exercised at least once in your script.
 

PART II.

1. (15pts.) Consider the relation R=(A,B,C,D,E,F,G), and the functional dependencies:AF--->CD, BC----->G, G------>A,A------>B, C------>EF.

(a) Find all candidate keys. 

(b) Show that R is not in 3NF. 

(c) Give a 3NF decomposition of R. Does this decomposition verify BCNF?

(d) Given adecomposition of R in the three following relations: R1 (A,B,C),R2 (C,D,E),R3 (E,F,G).Is this a lossless join decomposition?. Is it adependency-preserving decomposition?

2. (15 pts.) Consider a relation scheme R =(A,B,C,D,E,F,G,H) and the following functional dependencies: 

                     ABC --> E,FD --> A, AG --> E, H------->G,BC---->F,A---->H,and F --> D.

(a) Find all candidate keys. 

(b) Find a minimal cover of the set of dependencies.

(c) Compute (AF)+

(d) Prove that AF --> E follows from the given set of functional dependencies by 

deriving it using the inference rules given in class.

(e) Show that R is not in BCNF. 

(f) Give a BCNF decomposition of R. 

3.(25pts.)  Let us consider  the  relations R(A,B,C); S(C, D,E); T(E,F,G); the number of tuples of each relation is:   TR = 100,000; TS = 20,000; TT = 200,000. Assume each attribute is 10 bytes long. Each  system page  may hold   4 Kbytes of information.  Suppose the following query:

                 SELECT R.A, S.D
                 FROM R,S,T
                 WHERE R.C = S.C AND S.E = T.E AND T.F = 12 AND R.B = 100 AND S.C = 20

a - Define one clustered index and one non-clustered index for each relation.
b-  Give an execution plan, and compute the cost of the query  You should give reasonable values to the images(# of different values) of  each attribute in the relations.
c - Compare the cost of the query, with the cost of the nested loops strategy (without indexes).