MGMT 430 Business Database Systems

1. MGMT 430 Business Database Systems Sree Nilakanta Management Information Systems

2. Entities, Attributes, Relationships & Keys • Example Customers 1 place N Orders M place N Products

3. Entities & Attributes phone cname zip cid city Customers 1 place N Orders ord_date oid M place N Products pid descr name

4. phone cname zip cid city Customers 1 place N Orders ord_date oid M place N Products pid descr name Relationships • Incomplete model • Relationships have information • Where to keep

5. phone cname zip cid city Customers 1 place N Orders ord_date oid M place N Products pid descr name Applying attributes • Qty applies to Orders and Products rel. • “places” may become an entity • it is a “weak” entity Qty

6. Revised E-R Model phone cname zip • LineItems is the new “weak” entity • Can’t exist on its own • Depends on Orders & Products cid city Customers 1 place N Orders ord_date oid 1 Qty N LineItems place N place Products 1 pid descr name

7. Converting E-R to relational Model • Every entity becomes a relation • A relation is a 2-D table of rows & columns • Attributes are columns (fields) • Rows are records • Every row must be unique • All columns are single valued

8. Converting E-R Model • Customers entity • Customers (cid, cname, phone, zip, city) • cid is the primary key

9. Relations • Customers(cid, cname, phone, zip, city) • Orders(ord_id, date) • Products(pid, name, descr)

10. Applying cardinality • Parent primary key is shadowed in child • Weak entities use parent key in primary key • Foreign key • Foreign key enforcement

11. Relations revisited • Customers(cid, cname, phone, zip, city) • Orders(ord_id, date, cid) • Products(pid, name, descr) • LineItems(oid, pid, Qty)

12. Summary • Entities are the major players in the drama • Relationships define rules of the play • Near one-to-one correspondence with implementation