Mail-Order Company Database

1. Mail-Order Company Database A small mail-order company must maintain the following information: • The company must keep track of all its customers with their names, addresses, and the dates of their first orders. A unique customer number is assigned to each customer. • Each order placed by a customer may contain multiple order-lines. Each order-line is used to order one kind of product for some quantity. The date of the order must be recorded. Each customer assigns order numbers to her orders. Orders from different customers may have identical order numbers. • Each product has a product number, a product name, and a unit retail price.

2. Mail-Order Company Database (cont'd) • Each product may have multiple suppliers. A unique supplier number is assigned to each supplier. Different suppliers may offer the same product at different wholesale prices. A supplier may supply multiple products. The names and addresses of the suppliers must be recorded. • The company owns multiple warehouses. Each warehouse is identified by the name of the city where it is located. The telephone number of each warehouse must be recorded. The number of each product stocked at each warehouse must be recorded. A warehouse can stock different products. However, each product is stocked at most at one warehouse.

3. Mail-Order Company Database: E-R Diagram address C# Customers name since 1 name place S# city telephone # address M Suppliers O# Warehouses Orders date M 1 M quantity quantity for store supply M M M wholeSalePrice Products name P# retailPrice

4. Weak Entities • The keys of weak entities are uniquie only among their respective parents. Such keys are referred to as weak keys. • In referring to a weak entity, we must use the combination of the weak key of the weak entity and the primary key of the parent entity. • If the order numbers are assigned by customers, the order numbers are weak keys, and hence each order must be identified with (C#, O#). • Weak entities can exist only when their parent entities exist.

5. Generating Relational Tables • Provide a table for each entity type. The attributes of the entity type become the table columns. • Provide a table for each M : M relationship type. The primary keys of the entity types associated by the relationship type become the foreign keys in the table. The combination of those foreign keys becomes the primary key of the table. Also, the attributes of the relationship type need be added to the table. • A M:1 or 1:M relationship type does not require a new table. The primary key of the entity type on the 1-side can be added as a foreign key to the table representing the entity type on the M-side.

6. Mail-Order Company Database: Relational Schema (1) Customers(C#, name, address, since) (2) Place(C#, O#) (3) Orders(C#, O#, Date) (4) For(C#, O#, P#, quantity) (5) Products(P#, name, retailPrice) (6) Suppliers(S#, name, address) (7) Supply(S#, P#, wholeSalePrice) (8) Warehouses(city, telephone#) (9) StoredAt(P#, city, quantity) Relation 2 is subsumed by relation 3. Relations 5 and 9 can be merged: Products(P#, name, retailPrice, city, quantity)

7. Converting a M:M Relationship Types intoTwo 1:M Relationship Types. O# Orders O# date Orders 1 date M quantity M quantity for Orderline M M Products 1 Products name P# retailPrice name P# retailPrice

8. Consider M:M Relationships as Entities

9. M:M Relationships as Entities • An edge representing a relationship is an entity. • Each relationship is connected to exactly one participating entity on each side. • Each participating entity is connected to multiple relationships. • Therefore, we have a 1:M relationship type on one side and an M:1 relationship type on the other side.

10. Bug-Reports Tracking System: E-R Diagram M Customers reported-to 1 Packages make 1 M M have Workarounds Reports received by M M 1 M M Versions Employees have M for for 1 1 1 M M M perform have Problems M M M M for verified by Changes M M M made by Verifications

11. (Entity) Type Hierarchy • Is a forest of (entity) types • Indicated a supertype-subtype relationship • Is also called an IS-A hierarchy (or relationship) Example: • A car is a vehicle • A truck is a vehicle • A dump truck is a truck • A trailer-truck is a truck

12. (Entity) Type Hierarchy: Extended ER Schema license# owner Vehicle color nPassengers Car Truck weight style loadWeight DumpTruck TrailerTruck nTrailers

13. (Entity) Type Hierarchy: Vehicle Database • For each vehicle, record its license number, owner, and color. • For each car, record the number of passengers and style (sedan, convertible, etc.). • For each truck, record the weight of the truck itself. • For each dump truck, record the maximum weight of the load. • For each trailer-truck, record the number of the trailers (1 - 3).

14. Relational Schema for Type Hierarchy I • Store all the information on one entity in one table. • A similar method is used by object oriented programming languages such as C++ and Java. Vehicle(license#, owner, color) Car(license#, owner, color, nPassengers, style) Truck(license#, owner, color, weight) DumpTruck(license#, owner, color, weight, loadWeight) TrailerTruck(license#, owner, color, weight, nTrailers)

15. Relational Schema for Type Hierarchy I Vehicle(license#, owner, color) Car(license#, owner, color, nPassengers, style) Truck(license#, owner, color, weight) DumpTruck(license#, owner, color, weight, loadWeight) TrailerTruck(license#, owner, color, weight, nTrailers) Get all information on all trailertrucks. select * from TrailerTruck;

16. Relational Schema for Type Hierarchy I Vehicle(license#, owner, color) Car(license#, owner, color, nPassengers, style) Truck(license#, owner, color, weight) DumpTruck(license#, owner, color, weight, loadWeight) TrailerTruck(license#, owner, color, weight, nTrailers) Get license# and owner of all vehicles. • select licence#, owner, color from Vehicle • union • select licence#, owner, color from Car • union • select licence#, owner, color from Truck • union • select licence#, owner, color from DumpTruck • union • select licence#, owner, color from TrailerTruck;

17. Relational Schema for Type Hierarchy II Vehicle(license#, owner, color) Car(license#, nPassengers, style) Truck(license#, weight) DumpTruck(license#, loadWeight) TrailerTruck(license#, nTrailers) • The information on one entity is stored in multiple tables • When a class hierarchy is relatively flat, query statements in SQL become simpler

18. SQL Queries for Type II Class Hierarchy Vehicle(license#, owner, color) Car(license#, nPassengers, style) Truck(license#, weight) DumpTruck(license#, loadWeight) TrailerTruck(license#, nTrailers) Get all information on all trailertrucks. • select v.licence#, owner, color, weight, nTrailers • from Vehicle v, Truck t, TrailerTruck tt where v.license# = t.licemse# and t.license# = tt.license#

19. SQL Queries for Type II Class Hierarchy Vehicle(license#, owner, color) Car(license#, nPassengers, style) Truck(license#, weight) DumpTruck(license#, loadWeight) TrailerTruck(license#, nTrailers) Get license# and owner of all vehicles. select licens#, owner from Vehicle;

20. Inheritance support by PostgreSQL create table vehicle ( license# char(8) primary key, owner char(20), color char(10), ); create table truck ( weight float, ) inherits (vehicle); create table dump_truck ( load_weight float, ) inherits (truck);

21. Field Service Support System: E-R Diagram 1 1 1 District has Depot Supplier 1 1 M has makes has M stores M M Customer Shipment 1 Employee 1 M M has 1 makes for M gets Technician M Computer M M 1 Report Part 1 has M 1 fixes for M M M M Problem in CPU Memory Disk

22. Ternary Relationship Type PJ# (key) Name Project P# (key) S# (key) supply Supplier Part Name City Price Name Weight

23. Table Created from a Ternary Relationship Type Table Supplier-Project-Part

24. Is This Equivalent to the One Ternary Relationship Type? supply Supplier Part participate use Project We cannot know which supplier supplied which part with respect to which project.