Database Design

1
Database Design

• Lessons 2 3Database Models, Entities,
  Relationships

2
Database Design

• Conceptual
• Analysis
• What
• Logical
• Design
• How
• Physical
• Build
• Data
• Information

3
An Entity

• Something of significance to business about
  which data must be known
• A name for the things that you can list
• A single name of noun
• Entities have Instances
• Occurrences of entities
• Rows

4
Entities vs. Instance

• Entities can be
• Tangible, like Person or Product
• Nontangible, like skill level
• An event, like concert, graduation, wedding
• Instance examples
• Animal entity instances like, Dalmatian, Siamese
  cat, cow, tiger
• Car entity instances like, sedan, station wagon,
  SUV, convertible

5
Examples

• Entity Product
• New York instance
• Director can be either an entity or instance
  context is important

6
Attributes

• Entities have Attributes
• Single-value property, detail of an entity
• Piece of information that describes, qualifies,
  quantifies, classifies and/or specifies an entity
• Property of an entity
• Attributes have a data type

7
Attributes

• Describe an entity
• Attribute vs. attribute value
• color vs. blue
• animal type vs. dog
• Can have one and only one value at a given point
  in time
• One or more attributes must be defined as a
  unique identifier (UID)

8
Unique Identifier (UID)

• Used to distinguish one instance of an entity
  from another
• Example Student ID as a UID for student entity
• part number as a UID for product entity
• Social security number (UID) for employee
• Denote with a

9
Attributes

• Must be a single-values at any point in time
• Should be stored in one and only one entity
• Values have data type
• Example entity CAR may have attributes model
  color (values of beetle, green)
• An attribute may change over time

10
Attribute

• Volatile may change with time, like age
• should look for non-volatile attributes like
  birth date rather than age
• Mandatory vs. Optional
• email address mandatory for EMPLOYEE if modeling
  email application
• email address optional for CUSTOMER is modeling
  an online catalog

11
Entity relationship diagram (ERD)

• Visual way to display business requirements
• Tool used in design stage
• Used to react to, validate, and correct data in
  database
• Entities should be implemetation-free

12
Relationship

• Represents something significant to a business
• Expresses how entities are mutually related
• Always exist between entities
• Always have two perspectives
• Is named at both ends
• Between two entities (or one entity and itself)

13
Conventions

• Entities appear as all capital letters and
  singular
• Relationships are italicized
• Entities are placed in soft boxes (rounded
  corners)
• Examples
• EMPLOYEE hold JOBs
• JOBs are held by EMPLOYEEs
• PRODUCTs are classified by a PRODUCT TYPE
• PRODUCT TYPE classifies a PRODUCT

14
Optionality of relationships

• A relationship adds a link between entities
• Relationships come from business rules

15
Optionality of relationships

• Are either Mandatory or Optional
• Mandatory value is a REQUIRED field
• Use MUST to describe
• Denoted with an and a solid line
• Optional value may be supplier or not
• Use MAY to describe
• Denoted with a and a dashed line
• Example
• Each DEPARTMENT must have one or more EMPLOYEEs
• Each DEPARTMENT may have one or more EMPLOYEEs

16
Identifying Relationships

• Cardinality or Degree of relationship
• Describes how many?
• Use one and only one or one or more
• use crow foot to denote one or more in ERD
• Examples
• Each DEPARTMENT may have one or more EMPLOYEEs
• Each EMPLOYEE must be assigned to one and only
  one DEPARTMENT
• See ERD on next slide

17
ERD

• Entities use soft boxes

DEPARTMENT ID o name o location
EMPLOYEE ID first name last name o telephone
number o salary job
hire
assigned to
18
Examples

• Each SEAT may be sold to one or more PASSENGERs
• this example accounts for overbooking
• Each PASSENGER may purchase one and only one SEAT

19
Entity naming

• Name must be unique
• Create a description of the entity (be explicit)
• Be aware of homonyms
• Market 16 to 25 years
• Market Europe, Asia etc.
• Avoid reserved words
• Remove the relationship name from the entity name

20
ERD conventions - summary

• Entities go in soft boxes
• Entity names are singular and written in all
  capital letters
• Attributes go under Entity
• is a UID (unique identifier Key)
• mandatory attribute
• o optional attribute

21
ERD conventions - summary

• Relationships are lines
• solid are mandatory
• dashed are optional
• Lines terminations express cardinality
• single toe denotes one and only one
• crows foot denotes one or more

22
Example

• Each HAIRSTYLIST may work on one or more CLIENTs
• Each CLIENT must be assigned to one and only one
  HAIRSTYLIST
• See next slide to ERD

23
ERD diagram

• List entity and attributes

• HAIRSTYLIST
• id
• first name
• last name
• address
• phone number
• social-security number
• salary

• CLIENT
• client number
• first nameo last nameo phone number

work on
assigned to
24
Conventions

• Not a strict requirement (can reverse)

this crow is flying east
this crow is flying south
25
Matrix Diagram

• 3.4.4

26
ERD
COUNTRY
TRAVELER
visit
visited by
the location of
located in
have seen
seen by
LANDMARK
27
Previous ERD

• Note ERD included optionality and cardinality
• Note there are several MM relationship. This is
  a valid relationship, but discussed in later
  chapters