Transcript Vehicle(license#, owner, color)

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 M:M Relationship Type into
Two 1:M Relationship Types
 Ternary Relationship Type


Data Model 2
Build Extended ER digram to represent Entity
Type Hierarchy (supertype-subtype
relationship).
Work on Assignment 4.
1
Mail-Order Company Database
A small mail-order company must maintain the following
information:



Data Model 2
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 orderlines. 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.
2
Mail-Order Company Database (cont'd)


Data Model 2
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
What Should an Entity Be?
“An entity is an object in the real world that is
distinguisable from other objects”
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)
Data Model 2
4
Mail-Order Company Database: E-R Diagram
address
C#
Customer
name
since
1
name
S#
place
address
M
Supplier
Order
M
M
O#
date
1
quantity
M
M
wholeSalePrice
telephone #
Warehouse
for
supply
quantity
store
M
Product
P#
Data Model 2
city
name retailPrice
5
Weak Entities




Data Model 2
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.
6
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.
Data Model 2
7
Mail-Order Company Database: E-R Diagram
address
C#
Customer
name
since
1
name
S#
place
address
M
Supplier
Order
M
M
O#
date
1
quantity
M
M
wholeSalePrice
telephone #
Warehouse
for
supply
quantity
store
M
Product
P#
Data Model 2
city
name retailPrice
8
Mail-Order Company Database:
Relational Schema
(1)
(2)
(3)
(4)
(5)
(6)
(7)
Data Model 2
Customer(C#, name, address, since)
Order(C#, O#, Date)
For(C#, O#, P#, quantity)
Product(P#, name, retailPrice, city, quantity)
Supplier(S#, name, address)
Supply(S#, P#, wholeSalePrice)
Warehouse(city, telephone#)
9
Converting a M:M Relationship Types into
Two 1:M Relationship Types.
O#
O#
Order
M
Order
1
date
quantity
M
for
1
Product
retailPrice
P#
Data Model 2
Ol#
M
Product
name
quantity
Orderline
M
P#
date
name
retailPrice
10
Mail-Order Company Database:
Relational Schema
(1)
(2)
(4)
(5)
(6)
(7)
(8)
Data Model 2
Customer(C#, name, address, since)
Order(C#, O#, Date)
Orderline(Ol#, C#, O#, P#, quantity)
Product(P#, name, retailPrice, city, quantity)
Supplier(S#, name, address)
Supply(S#, P#, wholeSalePrice)
Warehouse(city, telephone#)
11
Ternary Relationship Type
PJ#
(key)
Project
S#
(key)
Supplier
Name
Data Model 2
Name
City
supply
Price
P#
(key)
Part
Name
Weight
12
Table Created from
a Ternary Relationship Type
Table Supplier-Project-Part
Data Model 2
S#
PJ#
P#
Price
S1
PJ1
P1
10
S1
PJ1
P2
20
S1
PJ2
P1
12
S2
PJ1
P1
15
S2
PJ3
P2
22
S3
PJ2
P3
30
13
Is This Equivalent to
the One Ternary Relationship Type?
Supplier
supply
Part
Project
We cannot know which supplier supplied
which part with respect to which project.
Data Model 2
14
Convert Ternary To Binary Relationship Type
PJ#
(key)
Project
S#
(key)
Supplier
Name
Data Model 2
Name
City
Shipment
Price
P#
(key)
Part
Name
Weight
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
Data Model 2
16
(Entity) Type Hierarchy: Extended ER Schema
Vehicle
license#
owner
color
nPassengers
Car
Truck
style
loadWeight
DumpTruck
weight
TrailerTruck
nTrailers
Data Model 2
17
(Entity) Type Hierarchy: Vehicle Database





Data Model 2
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)
Data Model 2
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;
Data Model 2
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
union
select
union
select
union
select
union
select
Data Model 2
licence#, owner, color from Vehicle
licence#, owner, color from Car
licence#, owner, color from Truck
licence#, owner, color from DumpTruck
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)
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
Data Model 2
22
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.license#
and t.license# = tt.license#
Data Model 2
23
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;
Data Model 2
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.
Data Model 2
25
Ex: Convert ER Diagram to Relational Schema
A(a1,
B(a1,
C(a1,
D(a1,
Data Model 2
a2)
b1, b2)
c1, c2)
d1, e1, f1, q1)
E(e1, e2)
F(e1,f1, f2)
G(g1, g2)
R(e1, f1, g1, r1)
26