Design by Decomposition

at
Categories
Tags

 

Consider the following relational schema:

Sale(salesman, store, commission_rate, city, date_sold, product, style, color) // a salesman sold a product on a particular day

Product(product, style, color, price) // prices, available styles and colors for products

Make the following assumptions, and only these assumptions, about the real world being modeled:

— Each salesman works in one store and has one fixed commission rate.

— Each store is in one city.

— A given product always has the same price, regardless of style or color.

— Each product is available in one or more styles and one or more colors, and each product is available in all combinations of styles and colors for that product.

Sale does not contain duplicates: If a salesman sells more than one of a given product in a given style 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 Product that encodes the assumptions described above and no additional assumptions.

(b) Based on your functional dependencies in part (a), specify all minimal keys for relations Sale and Product.

(c) Is the schema in Boyce-Codd Normal Form (BCNF) according to your answers to (a) and (b)? If not, give a decomposition into BCNF.

(d) Now consider your decomposed relations from part (c), or the original relations if you did not need to decompose them for part (c). Specify a set of nontrivial multivalued dependencies for relations Sale and Product that encodes the assumptions described above and no additional assumptions. Do not include multivalued dependencies that also are functional dependencies.

(e) Are the relations you used in part (d) in Fourth Normal Form (4NF) according to your answers for (a)-(d)? If not, give a decomposition into 4NF.

Sample Solution

(a) Functional Dependencies

Given the assumptions, we can identify the following functional dependencies:

Sale:

  • salesman → store, commission_rate, city (Each salesman works in one store and has one fixed commission rate.)
  • product, style, color → price (A given product always has the same price, regardless of style or color.)

Product:

  • product → price (A given product always has the same price.)

(b) Minimal Keys

Sale:

  • salesman, product, style, color

Product:

  • product

(c) BCNF

The schema is not in BCNF. The functional dependency product → price is not a superkey of Product, violating the BCNF condition.

Decomposition into BCNF:

  • Product1(product, price)
  • Product2(product, style, color)

(d) Multivalued Dependencies

  • product →→ style, color (Each product is available in one or more styles and one or more colors.)

(e) 4NF

The relations Product1 and Product2 are not in 4NF. The multivalued dependency product →→ style, color in Product2 is not implied by any functional dependency, violating the 4NF condition.

Decomposition into 4NF:

  • Product1(product, price)
  • Product2(product, style)
  • Product3(product, color)

Note: The decomposition into 4NF assumes that a product cannot have the same style or color combination with different prices. If this assumption is not valid, a further decomposition might be necessary.

This question has been answered.

Get Answer