Transcript Database Concepts - Syracuse University

Logical Database Design and
Entity-Relationship Mapping
“To Err is Human; to Forgive, Divine.”
~ Alexander Pope ~
What exactly is a Logical Model?
• A Blueprint of your database.
• It is DBMS independent, and does not rely on
an specific product.
• It is a technical communications tool.
• Concepts from relational theory are reintroduced (PK, FK, Table, Column, data type)
• Any person knowledgeable in SQL should be
able to read a logical model and create a real
database implementation from it.
Concept Map: Conceptual vs. Logical
CONCEPTUAL
LOGICAL
ERD
Relational Diagram
Entity
Relation (Table)
Attribute
Column
Relationship
Foreign Key (FK)
N/A
PK, Constraints
Important: Rows (sample data) MUST
be considered in the Logical Model !!!
Relational Notation is Simple
• Table or relation is a box. Name of
relation is at the top.
• Columns / Attributes are placed below
the relation name in the box.
• Primary Key columns are Underlined
and labeled with PK
• Foreign Key columns are labeled with
FK
• A PK/FK Relationship between tables
are established with an arrow (),
which always POINTS TO THE PRIMARY
KEY of the relationship.
Students
SUID PK
Lname
Fname
Ht
Wt
CreditCards
CardNo PK
SUID FK
CardType
ExpDate
Mapping
Mapping is the process of
transforming a conceptual model
into a logical model.
Logical
Conceptual
Basic Attribute Mapping
• Table name is plural
• Use Naming
conventions
• Assign data types
• PK for each table
• Break down composite
into simple
• Set required to not null
• Set Unique and check
constraints
Logical
Conceptual
Mapping 1-M Relationships
• FK goes on the
many side
• If required on
the one side
(aka Weak
Entity), then set
the FK to not
allow null
Logical
Conceptual
Mapping Multi-Valued Attributes
• Similar to 1-M:
Place [m] attributes in
new table. PK from
original table is FK in the
new table.
Logical
Conceptual
Mapping Multi-Valued Attributes
• Similar to 1-M:
Place [m] attributes in
new table. PK from
original table is FK in the
new table.
Logical
Conceptual
Mapping 1-1 Relationships
• Similar to 1-M:
Since the FK goes
to the optional
side of the
relationship, but
the FK is set to PK.
Logical
Conceptual
Mapping M-M Relationships
• Make a “bridge
table” place
FKs in bridge
table and set
as composite
PK
Logical
Conceptual
Mapping Weak Entities
• Make sure the
foreign key
does not allow
null
Advanced Mapping
Edge cases which are variations on
the same themes. Examples provided
for reference.
Conceptual
Ex: Mapping a Unary 1-M Relationship
Employee
EmpID
EName
Phone
HireDate
Manages
Logical
Employee
EmpID PK
EName
Phone
HireDate
ManagerEmpID FK
The FK inside the
table forms the
recursive
relationship
Conceptual
Ex: Mapping a Unary N-M Relationship
Item
ItemNo
ItmName
UnitCost
Logical
Item
ItemNo PK
ItmName
UnitCost
Contains
Bridge Table with both FK’s
relating back to the same
table PK
Component
ItemNo PK FK
ComponentItemNo PK FK
Qty
Ex: Mapping Sub/Super Type
Conceptual
Logical
Employee
Employee
EmpNo
EName
HireDate
EmpNo PK
EName
HireDate
EmpType
Discriminator
EmpHourly
EmpSalary
EmpHourly
EmpSalary
HourlyRate
AnnualRate
StockOptns
EmpNo PK FK
HourlyRate
EmpNo PK FK
AnnualRate
StockOptns
Logical Database Design and
Entity-Relationship Mapping
Questions?