CPSC 304

Project Part II -Logical Design

Solutions

1. First set of Tables

The following is a list of the tables obtained from the ERD-to-Tables process described in the notes and in the description of part 2.

Item Hierarchy :  This is a total disjoint hierarchy. We should check if we can keep one level and not the other.  Item and Book participate in different associations. Therefore  we need to keep both levels of the hierarchy:

Item (upc, taxable, selling_price, stock, item_type)

• Represents: entity set item.
• Primary key: upc
• Constraints:
• each item  is supplied by some supplier
• an item can either be a  Nonbook or a Book

Nonbook (upc, name, category, manufacturer)

• Represents: entity set nonbook
• Primary key: upc
• Category is one of {"stationary", "electronics", software", "arts", "misc" }
• Foreign Keys:
• (upc)  references Item

Book (upc, title, publisher, category)

• Represents: entity set book
• Primary key: upc
• Category is one of {"general", "text" }
• :Foreign Keys:
• (upc)  references Item
• Constraints:
• every book must have some author

Author (name)

• Represents the author entity set
• Primary key: name
• Constraints: each author must be associated with a book

HasAuthor (upc, name)

• Represents the has-author relationship set
• Primary key: upc, name
• Foreign Keys:
• (upc) references Book
• (name) references Author

Text (upc)

• Represents: entity set text
• Primary key: upc
• :Foreign Keys:
• (upc)  references Book
• Constraints
• each text must be used in some course

Course (dept, course#)

• Represents the course entity set
• Primary key: (dept, course# )

UsedIn (upc, dept, course#)

• Represents the used-in relationship set
• Primary key: (upc,  dept, course# )
• Foreign Keys:
• (upc)  references Book
• (dept, course# ) references Course

Supplier (name, address, city, status)

• Represents: entity set supplier
• Primary key: (name)
• status is one of { "active", "inactive" }

Supplies (name, upc, sup_price)

• Represents: relationship set supplies
• Primary key: (name, upc)
• Foreign Keys:
• (name) reference Supplier
• (upc) references Item

Account (id, holder, dept, amount)

• Represents: entity set account
• Primary key: (id )

 

Transaction Hierarchy :  This is a total disjoint hierarchy. We should check if we can discard any level.  Entity set transaction does not participate in any relationship. Therefore we can discard it.  Similarly, the entity sets purchase and return can be discarded and keep the lower level sets, as long as we are certain that each query for such a transaction will  always specify the type of the transaction (i.e. cash, card, or account). On the other hand, if we expect queries that refer to just a purchase or a return without specifying the type, we should keep the purchase and return tables and discard the lower tables. Therefore the tables for this hierarchy would be as following:

 

Shipment (tid, date, name, upc, quantity)

• Represents:  the entity set shipment and the relationship set records-shipment (relationship is one-to-many and total on shipment)
• Primary key: tid
• Foreign Keys:
• (name) references Supplier
• (upc) references Item
• Constraints:
• tid  must be unique among the Shipment, Purchase, and Return entities.

 

1st Approach for Purchase/Return:   Assume that every query about the a purchase or return transaction will always specify the type of the transaction (i.e. cash, card, or account). So, we keep the lower tables and discard the higher ones:

CashPurchase (tid, date, upc, quantity, amount, receiptId)

• Represents: entity set cash_purchase and relationship set records_cash_purchase
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• Constraints:

CardPurchase (tid, date, upc, quantity, amount, card_type, card#, receiptId)

• Represents: entity set card_purchase and relationship set records_card_purchase
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• Constraints:

AccountPurchase (tid, date, upc, quantity, amount, type, id, receiptId)

• Represents: entity set acount_purchase and relationship set records_acount_purchase
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• (type, id) references Account
• Constraints:

CashReturn (tid, date, upc, quantity, amount, purchase_tid)

• Represents: entity set cash_return and relationship sets records_cash_ return and based_on
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• (purchase_tid) references CashPurchase(tid)
• Constraints:

CardReturn (tid, date, upc, quantity, amount, card_type, card#, purchase_tid)

• Represents: entity set card_ return and relationship sets records_card_ return and based_on
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• (purchase_tid) references CardPurchase(tid)
• Constraints:

AccountReturn (tid, date, upc, quantity, amount, id, purchase_tid)

• Represents: entity set acount_ return and relationship sets records_acount_ return and based_on.
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• (id) references Account
• (purchase_tid) references AccountPurchase(tid)
• Constraints:
• tid  must be unique among the Shipment, Purchase, and Return entities.
• account type must match item’s type.

 

2nd Approach  for Purchase/Return:   Assume that most  queries about the a purchase or return transaction will not specify the type of the transaction (i.e. cash, card, or account). So, we keep the higher tables and discard the lower ones. Attributes pur_type to distinguish the cash, the card and the account type of purchase. Her is an idea :

Purchase (tid, date, upc, quantity, amount, pur_type, id, receiptId)

• Represents: entity sets purchase, cash_purchase, card_purchase, account_purchase, and relationship sets records_cash_purchase, records_card_purchase, records_account_purchase.
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• pur_type is one of { "cash", "ms", "visa", "ae", "account" )
• Constraints: 
• The  table values should satisfy the following:
• if pur_type is "cash", id is null
• if pur_type is "ms" or "visa" or "ae", acc_id stores the card number
• if pur_type is "account",  id stores the UBStore account number.

Return (tid, date, upc, quantity, amount, pur_type, id, purchase_tid)

• Represents: entity sets return, cash_ return, card_ return, account_ return, and relationship sets records_cash_ return, records_card_ return, records_account_ return and based_on.
• Primary key: tid
• Foreign Keys:
• (upc) references Item
• (purchase_tid) references Purchase(tid)
• pur_type is one of { "cash", "ms", "visa", "ae", "account" )
• Constraints: 
• tid  must be unique among the Shipment, Purchase, and Return entities.
• The  table values should satisfy the following:
• if pur_type is "cash", id is null
• if pur_type is "ms" or "visa" or "ae", id stores the card number
• if pur_type is "account",  id stores the UBStore account number.

 

  

2. Functional Dependencies

There are three source of the functional dependencies:

• Primary keys of the entity sets. The primary key determines all the other attributes. These keys have been identified from statements in the problem description when the ER was created.
• One-to-one and many-to one  associations.
  If there is a one-to-one association between entity sets E1 and E2, with primary keys A and B respectively then the FD's A-->B and B-->A hold.
  If there is a many-to-one association from entity sets E1 to E2, whose primary keys are A and B respectively,  then the FD A-->B holds.
• Other properties of the problem domain which are described in the problem description and cannot been shown in the ER diagram.

The following is a list of FD's identified from the ER diagram for the UBStore enterprise for each table. We take the 2nd approach for Purchase/Return.

Item (upc, taxable, selling_price, stock, item_type)

•  upc --> taxable, selling_price, stock, item_type          (key)

Nonbook (upc, name, category, manufacturer)

• upc --> name, category, manufacturer          (key)

Book (upc, title, publisher, category)

• upc --> title, publisher, category          (key)

Text (upc)

• none

Author (name)

• none

HasAuthor (upc, name)

• none

Course (dept, course#)

• none

UsedIn (upc, dept, course#)

• none

Supplier (name, address, city, status)

• name -->address, city, status       (key)
• name, city  --> status                   (AFD3)

Supplies (name, upc, sup_price)

• name, upc -->sup_price           (key)
• upc -->sup_price                     (AFD2)

Account (id, holder, dept, amount)

•  id -->holder, dept, amount    (key)
• holder --> dept                      (AFD1 )

Purchase (tid, date, upc, quantity, amount, pur_type, id, receiptId)

• tid --> date, quantity, upc, amount, pur_type, id, receiptId          (key)

Return (tid, date, upc, quantity, amount, pur_type, id, purchase_tid)       

• tid --> date, quantity, upc, amount, pur_type, id, purchase_tid        (key)

Shipment (tid, date, name, upc, quantity)

• tid --> date, name, upc, quantity        (key)

 

3. Tables after Normalization

We observe that in all table of part (1) except  Supplier, Supplies, and Account the dependencies are key dependencies. Therefore  all tables except those three are in BCNF .

The extra dependency (AFD3) in Supplier is implied by the key dependency. The LHS of (AFD3) includes the key. Therefore Supplier is in BCNF.

Supplies is not in BCNF. We need to split it into

• ProductPrice( upc, sup_price)
• Supplies1( name, upc)

Similarly, Account is not in BCNF.

We need to split it into

• HorderDept( holder, dept)
• Account1(  id, holder, amount )

   

4. Final Refinements

Now, we must check ( using the problem statement) whether there is information in the tables that it is not needed. By checking the UBStore  problem description we conclude the following:

1.      We never insert any author without the book they author. Therefore the author table is not needed. We can get all the information about authors from the HasAthor table. Therefore the Author table is deleted.

2.      Similarly, we don’t keep information about courses, unless they have a text assigned (we never insert or delete courses alone).  The used-in relation is total on text. This means that we will never keep a text if it is not used in a course. Moreover, text has not any additional attributes. Therefore we can delete the tables Text and Course and retain only the UsedIn table that contains all the information about the texts and the courses in which they are used. 

3.  Combine tables with the same keys when this action does not  introduce redundancies. In our case, tables Item and ProductPrice can be combined into:

        Item (upc, taxable, selling_price, stock, item_type, sup_price)

4.    OPTIONAL:  If we want our Item table to be more efficient we can combine item, book and nonbook into one table as following:

a.       Add to Item an attribute category with values "book",  "equipment", software", "arts", or "misc". This includes all the categories of nonbook items.

b.      Add to Item attributes name and producer. For a nonbook, these attributes will represent the name and the manufacturer of the item. For a book, these attributes will represent the title and the publisher of the book.

c.       Now we can delete the tables Nonbook and Book and keep the new Item table.