Business Database Management Systems

1
Business Database Management Systems

• Class 8
• Translating From Conceptual to Logical Database
  Models
• DSC 544/444 ?Fall 2006

2
Reminders

• Test 1. Wednesday, Oct. 25. Weight 25
• Conceptual modeling concepts
• Understanding/drawing E-R diagram notation
• Translating from conceptual to tables
• Todays main topic
• Upcoming Presentations
• Today CostCo.com
• October 23 AllMusic.com
• October 30 Burton.com

3
Agenda

• Review revisit both complete E-R diagrams from
  HW2
• Discuss the mapping between the conceptual model
  (E-R diagram) and logical (relational) models
• Practice E-R to relational conversions
• HW3 will have you practice creating relational
  models
• Team presentation

4
Snowboarding club
Name
1
Ski Area
M
SID
Phone
wishes
Date
Date
MID
M
M
interest
attend
M
M
Member
Meeting
M
M
M
M
driver
Date Out
Speaker Name
Talk Title
passenger
rental
drives
Date In
Brand
EID
Equipment
Model
Date Acquired
Length
Tip Width
Gender
Min Weight
Binding
Boot
Snowboard
Waist Width
Size
Max Weight
Tail Width
5
Charitable fundraising org.regarding s

• How much flexibility?
• Assume charity percentages are fixed for each
  event
• Or, allow each donor to specify their own
  percentages with each donation
• Org. must decide, and we must deal with their
  decision when defining the information
  architecture

6
Volunteer club with s fixedfor each event
Address
Percentage
donates to
M
Contact
Charity
Phone
Name
CharityID
M
Name
1
Event
holds
M
Amount
Received
EID
Date
Chairperson
CID
M
1
on
handles
M
M
Member
Committee
1
M
1
M
Provisionalmember
Full member
Committee Name
Treasurer
mentors
1
Name
DID
Donor
Address
Phone
7
Volunteer club with customized s for each
donation
Address
Contact
Charity
M
Phone
Name
CharityID
Percentage
donates to
Name
1
Event
holds
M
Amount
Received
EID
Date
Chairperson
CID
M
1
on
M
handles
M
M
Member
Committee
1
M
1
M
Provisionalmember
Full member
Committee Name
Treasurer
mentors
1
Name
DID
Donor
Address
Phone
8
Homework 3 Assignment

• Part 1
• Define table signatures (i.e., just the headers,
  with no data) for
• the snowboarding club, and
• the charitable org. assuming fixed s for each
  event
• So that we all will have a common starting point,
  base your work on the two corresponding ER
  diagrams within these notes
• Part 2 Normalization (will discuss Mon.)
• Due in class Monday Oct. 30. Part 1 is good
  practice for some of the concepts on Test 1.

9
Agenda

• Review revisit both complete E-R diagrams from
  HW2
• Discuss the mapping between the conceptual model
  (E-R diagram) and logical (relational) models
• Practice E-R to relational conversions
• Team presentation

10
Mapping from Conceptual to Logical an Overview

• After creating the conceptual data model, you
  then define the logical model
• For a relational database, that means we have to
  define tables
• If we were designing for an object-oriented
  database, we would need to define objects
• Relational databases are far more common than
  object-oriented databases, so we will only
  concern ourselves with the relational (logical)
  model

11
Entities

• Basic structure of an E-R diagram entities and
  their relationships
• Each entity (both strong and weak) implies a
  table in the relational model.
• Entity name becomes table name

12
Identifiers for Tables

• Just as we need a means to identify each entity
  instance, we likewise need a means to uniquely
  distinguish each row of a table.
• What we call an entity identifier in the
  conceptual model we call a table primary key in
  the logical model.
• Every table must have a primary key that will
  make each row of a table unique from all the
  other rows in that table
• Recall that we sometimes need multiple attributes
  to define an identifier for an entity.
• Likewise, a tables primary key can span multiple
  attributes.

13
Converting Identifying Attributes

• Strong entity
• Create a (primary key) column for each
  identifying attribute of the entity
• Weak entity
• Create a (primary key) column for each
  identifying attribute of every entity that this
  weak entity has an identifying relationship with.
• Create an additional (primary key) column for
  each discriminator attribute of the weak entity

14
Converting Non-identifying Attributes

• Single-valued (standard attribute)
• Create a table column for each
• Derived
• Omit these values are not stored in our tables
• Later, we can produce these values using a query
• Multi-valued
• Relational model does not directly support!
• However, as we have discussed, a multi-valued
  attribute can be conceptualized as a new (weak)
  entity, thus implying a separate table.

15
Converting Binary Relationships

• One-to-many relationships
• Consider the 2 associated entity tables.
• Within the many side entitys table, we need to
  have foreign key column(s) referring to the
  related one side entity instances
• We use the identifier of the related entity to
  define the foreign key column(s)
• In other words, we copy the primary key column(s)
  of the related table
• Note those column(s) may already be included
  within the many side table (we will see this
  later in Example 3)

16
Converting Binary Relationships

• One-to-one relationships
• Consider the 2 associated entity tables.
• The foreign key column(s) can be with either
  entity
• As before, copy the primary key column(s) of the
  related table
• Note in a 11 relationship, the two entities
  often use the same identifier, in which case the
  existing primary key columns serve the dual
  role of both primary and foreign keys
• A separate foreign key column is then
  unnecessary!

17
Converting Binary Relationships

• Many-to-many relationships
• Relational model does not directly support!
• However, each many-to-many relationship can be
  conceptualized as a new (associative) entity,
  thus implying a separate table.
• The identifier for the associative entity is the
  combination of the identifiers for the two
  related entities.
• Thus, for the separate table we create for an MM
  relationship, its primary key columns include the
  primary key columns for both of the related
  tables.

18
Agenda

• Review revisit both complete E-R diagrams from
  HW2
• Discuss the mapping between the conceptual model
  (E-R diagram) and logical (relational) models
• Practice E-R to relational conversions
• Team presentation

19
ER?Relational Practice

• Example 1
• Resulting Tables
• Player(PlayerID, Name, TeamID)
• Team(TeamID, Name)

Name
Name
has
M
1
Player
Team
TeamID
PlayerID
Foreign key column
20
ER?Relational Practice

• Example 2
• Resulting Tables
• Team(TeamID, Name)
• Jersey(TeamID, FrontText, BackText)
• Note that here each TeamID column serves a dual
  role as both primary and foreign key

Name
FrontText
1
has
1
Team
TeamID
BackText
21
ER?Relational Practice

• Example 3
• Resulting Tables
• Player(PlayerID, Name, TeamID)
• Guardian(PlayerID, GuardianName, Phone)

GuardianName
Name
M
has
1
Player
PlayerID
Phone
Foreign key to Team table
Dual role also acts as foreign key to Player
table
22
ER?Relational Practice

• Example 4
• Resulting Tables
• Player(PlayerID, Name, TeamID)
• Guardian(GuardianID, GuardianName, Phone)
• PlayerHasGuardian(PlayerID, GuardianID)

Name
GuardianID
has
M
M
Guardian
Player
PlayerID
GuardianName
Phone
Foreign key to Team table
23
Ternary and n-ary Relationships

• Recall that ternary (or n-ary) relationships
  imply a simultaneous connection between three (or
  more) entity instances.
• Just as with the MM scenario, the relational
  model does not directly support!
• Again, we can conceptualize these complex
  relationships via a new (associative) entity,
  thus implying a separate table.
• The identifier for the associative entity is the
  combination of the identifiers for the related
  entities.

24
Example

• How do we convert the following conceptual model
  E-R diagram to the relational model?
• First, lets redraw the E-R diagram showing the
  associative entity interpretation of the n-ary
  relationship.

Game
Team
Referee
Field
25
ER?Relational Practice

• Conceptualize using an associative entity and
  home/away relationships
• Resulting Tables
• Team(TeamID, Name)
• Referee(RefereeID, Name)
• Field(FieldID, Name, Street, City, ZIP)
• Game(GameID, RefereeID, FieldID, HomeTeam,
  AwayTeam)
• Note we might have been able to create these
  tables directly from the prior diagram, but
  showing the associative entity helps clarify the
  information architecture specifics.

assigned
home
1
M
Team
Referee
Game
1
M
1
M
M
for
away
1
Field
26
Summary

• We have discussed the linkages between the
  conceptual and relational models
• Entities become tables, with attributes as
  columns
• Entity instances become data rows
• No direct relational support for implementing MM
  or ternary/n-ary relationships
• Create associative entity (and thus a new table)
  to handle such complex relationships