Project 4 — E/R Diagrams

SOLUTIONS

 

For the following business artifacts, do the following:

· Draw an E/R diagram

· Reduce the diagram to relation schemas

· List all functional dependencies, i.e, all the primary keys and which of the other attributes are determined by them. ( student.id => name, tot_cred)

 

In your E/R diagram be sure to include the following (when applicable) :

Entity Sets

Attributes

  Atomic

  Composite

  Multi-valued

  Derived

  Primary Keys

Relationships

  Roles

  Participation

  Cardinality

Weak Entity Sets

  Discriminators

  Identifying Entity Sets

 

Moonlight Café—Receipt (25 points)

 

Relations

Shop(name, address, tax)

Till(number, person, shop_name(+) )

Receipt(date_time, subtotal, taxtotal, cash, return, till_number(+) )

Item(receipt_date_time(+), quantity, product_name(+) )

Product(name, price)

 

Functional Dependencies

Shop        name => address, tax

Till           number   => person, shop_name

Receipt    date_time => subtotal, taxtotal, cash, return, till_number

Item         receipt_date_time, quantity =>product_name

Product    name => price

 

Moonlight Café—Price List (25 points)

 

Relations

Shop(name, address, web_site)

Price_list(start_date, end_date, shop_name(+) )

Price(product_name(+), product_size(+), amount )

Product( name, size, type(+) )

Group(type)

 

Functional Dependencies

Shop          name => address, web_site

Price_list     start_date, end_date => shop_name

Price           product_name, product_size => amount

Product       name, size => type

 

 

Mulch Dispatch and Delivery System (50 points)

Mulch Delivery and Dispatch System Description

 

Mulch Order Data

Mulch Order Reports

 

Student Questions and Clarifications

 

 

DESIGN

1. List all functional dependencies and constraints that you see in the system

2. Build an ER diagram to include all database structures to support the functional requirements

3. Reduce the diagram to relation schemas

 

 

 

Clock( time )

Area( area_id , name, zipcode)

Truck( name, capacity, time_left, area_id(+), mulch_type_id(+) )

Mulch( mulch_type_id, mulch_name, bags_per_pallet, buying_price_per_bag, selling_price_per_bag )

Order(order_id, customer_name, customer_address, total_amount_due, area_id(+), seller_name(+) )

Mulch_Item( order_id(+), mulch_type_id(+), num_ordered )

Seller( seller_name )

 

 

Area                area_id => name, zipcode

Truck               name => capacity, time_left, area_id, mulch_type_id

Mulch              mulch_type_id => mulch_name, bags_per_pallet, buying_price_per_bag, selling_price_per_bag

Order              order_id => customer_name, customer_address, total_amount_due, area_id, seller_name

Mulch_Item      order_id, mulch_type_id => num_ordered