team work
CS304 Database System Concepts
Assignment 3
Due: April 16, 2013
(Please submit hard copies to class or to Zheng on due date.)
Name: Matric No:
Q1. (10 points) Consider the E-R diagram in Figure 1, which models an online bookstore. a. List the entity sets and their primary keys b. Suppose the bookstore adds music cassettes and compact disks to its collection. The same music item may be present in cassette or compact disk format, with differing prices. Extend the E-R diagram to model this addition, ignoring the effect on shopping baskets. c. Now extend the E-R diagram, using generalization, to model the case where a shopping basket may contain any combination of books, music cassettes, or compact disks.
Figure 1
Note: Please only include necessary parts of the original diagram.
Q2. (10 points)Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused database with data baste. Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lives, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like.
Draw the ER diagram for the database and give the schema for this database
Q3. (20 point)Consider the following set F of functional dependencies for relation schema R
R=(A, B, C, D, E)
F={, , , } a) Compute the closure of F; b) Compute the canonical cover ; c) List candidate keys for R;
If R is decomposed to and , show that d) this decomposition is a lossless decomposition e) this decomposition is NOT a dependency-preserving decomposition
Q4. (20 points) Consider the following relational schema:
Sale(clerk, store, city, date, item, size, color) // a clerk sold an item on a particular day
Item(item, size, color, price) // prices and available sizes and colors for items
Make the following assumptions, and only these assumptions, about the real world being modeled:
-- Each clerk works in one store.
-- Each store is in one city.
-- A given item always has the same price, regardless of size or color.
-- Each item is available in one or more sizes and one or more colors, and each item is available in all combinations of sizes and colors for that item.
Sale does not contain duplicates: If a clerk sells more than one of a given item in a given size and color on a given day, still only one tuple appears in relation Sale to record that fact.
(a) Specify a set of completely nontrivial functional dependencies for relations Sale and Item that encodes the assumptions described above and no additional assumptions.
(b) Are the schemas Sale and Item in Boyce-Codd Normal Form? If not, decompose them into BCNF.
Q5. (10 points)Given a relational schema r (A, B,C, D), does A →→ BC logically imply
A→→ B and A→→C? If yes prove it, else give a counter example.
Q6. (10 points) Exercise 7.12 on page 307 of Text book.
Q7. (10 points) Explain why 4NF is a normal form more desirable than BCNF.
Q8. (10 points) 3NF decomposition can be done in polynomial time. Verifying whether a given decomposition is in 3NF is hard(NP complete). Why is decomposition polynomial while verification exponential?
Assignment 3
Due: April 16, 2013
(Please submit hard copies to class or to Zheng on due date.)
Name: Matric No:
Q1. (10 points) Consider the E-R diagram in Figure 1, which models an online bookstore. a. List the entity sets and their primary keys b. Suppose the bookstore adds music cassettes and compact disks to its collection. The same music item may be present in cassette or compact disk format, with differing prices. Extend the E-R diagram to model this addition, ignoring the effect on shopping baskets. c. Now extend the E-R diagram, using generalization, to model the case where a shopping basket may contain any combination of books, music cassettes, or compact disks.
Figure 1
Note: Please only include necessary parts of the original diagram.
Q2. (10 points)Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused database with data baste. Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lives, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like.
Draw the ER diagram for the database and give the schema for this database
Q3. (20 point)Consider the following set F of functional dependencies for relation schema R
R=(A, B, C, D, E)
F={, , , } a) Compute the closure of F; b) Compute the canonical cover ; c) List candidate keys for R;
If R is decomposed to and , show that d) this decomposition is a lossless decomposition e) this decomposition is NOT a dependency-preserving decomposition
Q4. (20 points) Consider the following relational schema:
Sale(clerk, store, city, date, item, size, color) // a clerk sold an item on a particular day
Item(item, size, color, price) // prices and available sizes and colors for items
Make the following assumptions, and only these assumptions, about the real world being modeled:
-- Each clerk works in one store.
-- Each store is in one city.
-- A given item always has the same price, regardless of size or color.
-- Each item is available in one or more sizes and one or more colors, and each item is available in all combinations of sizes and colors for that item.
Sale does not contain duplicates: If a clerk sells more than one of a given item in a given size and color on a given day, still only one tuple appears in relation Sale to record that fact.
(a) Specify a set of completely nontrivial functional dependencies for relations Sale and Item that encodes the assumptions described above and no additional assumptions.
(b) Are the schemas Sale and Item in Boyce-Codd Normal Form? If not, decompose them into BCNF.
Q5. (10 points)Given a relational schema r (A, B,C, D), does A →→ BC logically imply
A→→ B and A→→C? If yes prove it, else give a counter example.
Q6. (10 points) Exercise 7.12 on page 307 of Text book.
Q7. (10 points) Explain why 4NF is a normal form more desirable than BCNF.
Q8. (10 points) 3NF decomposition can be done in polynomial time. Verifying whether a given decomposition is in 3NF is hard(NP complete). Why is decomposition polynomial while verification exponential?