After this lecture, you should be able to:

1
More on E-R Diagrams

• After this lecture, you should be able to
• Use E-R Model effectively to design a database
  for a mail order company.
• Know more features of ER Diagram
• Weak Entity
• Converting a MM Relationship Types intoTwo 1M
  Relationship Types
• Ternary Relationship Type
• Build Extended ER digram to represent Entity Type
  Hierarchy (supertype-subtype relationship).
• Work on Assignment 4 and be prepared for Midterm
  II.

2
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 his/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.

3
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.

4
What Should an Entity Be?

• SHOULD BE
• An object that will have many instances in the
  database
• An object that will be composed of multiple
  attributes
• An object that we are trying to model
• SHOULD NOT BE
• A user of the database system
• An output of the database system (e.g., a report)

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

• The keys of weak entities are unique 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.

7
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 M1 or 1M 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.

8
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)
9
Mail-Order Company Database Relational Schema
(1) Customers(C, name, address, since) (2)
Orders(C, O, Date) (4) For(C, O, P,
quantity) (5) Products(P, name, retailPrice,
city, quantity) (6) Suppliers(S, name,
address) (7) Supply(S, P, wholeSalePrice) (8)
Warehouses(city, telephone)
10
Converting a MM Relationship Types intoTwo 1M
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
11
Ternary Relationship Type
PJ (key)
Name
Project
P (key)
S (key)
supply
Supplier
Part
Name
Name
Weight
City
Price
12
Table Created from a Ternary Relationship Type
Table Supplier-Project-Part
13
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.
14
Convert Ternary To Binary Relationship Type
PJ (key)
Name
Project
P (key)
S (key)
Part
Shipment
Supplier
Name
Name
Weight
City
Price
15
(Entity) Type Hierarchy

• Indicated a supertype-subtype (superclass-subclass
  ) 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

16
(Entity) Type Hierarchy Extended ER Schema
license
owner
Vehicle
color
nPassengers
Car
Truck
weight
style
DumpTruck
TrailerTruck
loadWeight
nTrailers
17
(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).

18
Relational Schema for Type Hierarchy I

• Store all the information on one entity in one
  table.

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)

• A similar method is used by object oriented
  programming languages such as C and Java.

19
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
20
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

21
Relational Schema for Type Hierarchy II
Vehicle(license, owner, color) Car(license,
nPassengers, style) Truck(license,
weight) DumpTruck(license, loadWeight) TrailerTru
ck(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

22
SQL Queries for Type II Class Hierarchy
Vehicle(license, owner, color) Car(license,
nPassengers, style) Truck(license,
weight) DumpTruck(license, loadWeight) TrailerTru
ck(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.license
• and t.license tt.license

23
SQL Queries for Type II Class Hierarchy
Vehicle(license, owner, color) Car(license,
nPassengers, style) Truck(license,
weight) DumpTruck(license, loadWeight) TrailerTru
ck(license, nTrailers)
Get license and owner of all vehicles.
select licens, owner from Vehicle
24
Ex Convert ER Diagram to Relational Schema

• A G are entity types. P, Q, and R are
  relationship types. Entity types B and D are
  subtypes of A, and entity type C is a subtype of
  B. Attributes are shown as a1, a2, ... Primary
  key attributes are marked with . F is a weak
  entity-type of E. Attribute f1 of F marked with
  is a weak-entity key. Construct the
  relational schema using minimum number of tables.

25
Ex Convert ER Diagram to Relational Schema
E(e1, e2) F(e1,f1, f2) G(g1, g2) R(e1, f1,
g1, r1)
A(a1, a2) B(a1, b1, b2) C(a1, c1, c2) D(a1,
d1, e1, f1, q1)