Formal Relational Database Design

1
Formal Relational Database Design
2
Topics

• RDBMS definitions
• Relational database modeling
• E-R diagrams

3
Definitions

• Table Two dimensional structure made up of rows
  and columns
• Relation Formal name for a table
• Tuple Formal name for a row
• Attribute Formal name for a column
• Schema The structure of the database system
  described in a formal language supported by the
  DBMS. It defines the tables, fields, and
  relationships. The schema is defined in a
  text-based language, but the word schema is also
  used to refer to a graphical depiction of the
  structure.

4
Definitions

• Entity
• A person, place, thing, or event for which you
  want to store data. For example, John Jones the
  employee is an entity, and his hire date,
  address, and salary are characteristics of this
  entity.
• Each row describes the characteristics of an
  entity
• Relationship
• An association between entities
• Examples found in, one-to-one, one-to-many,
  many-to-many
• Domain
• Set of values from which one or more columns draw
  their actual values
• Examples certain names in the Name column
  legitimate dates from the Startdate and
  Lastpaydate columns

5
Characteristics

• Rows
• Each is unique (easy to locate)
• Order not important
• Columns
• Order not important
• Each column has unique name called attribute

6
Basic Modeling Concepts

• Database design is both art and science.
• A data model is the relatively simple
  representation, usually graphic, of complex
  real-world data structures. It represents data
  structures and their characteristics,
  relationships, and transformations.
• The database designer usually employs data models
  as communications tools to facilitate the
  interaction among the designer, the applications
  programmer, and the end user.
• A good database design is the foundation for good
  applications.

7
Models

• Conceptual model
• Global view
• Independent of software
• Internal model
• An adaptation (implementation) of the conceptual
  model that is software dependent
• For example, using MS Access graphical design
  tools, or SQL text-based commands

8
Conceptual Model

• The conceptual model represents a global view of
  the data. It is an enterprise-wide representation
  of data as viewed by high-level managers.
• Entity-Relationship (E-R) diagram is the most
  widely used conceptual model.
• The conceptual model forms the basis for the
  conceptual schema.
• The conceptual schema is the visual
  representation of the conceptual model.
• The conceptual model is independent of software
  (software independence).

9
College Example
10
A Conceptual Schema for the College Example
Symbols Rectangle Entities or
relations Diamonds Relationships
11
Common Shorthand for Relations

• Write relation (table) name followed by its
  attributes (columns) in parentheses
• Underline the attributes that represent the
  primary key
• Identify the foreign keys immediately after the
  relation

12
Example

• State (StateAbbrev), StateName,
  EnteredUnionOrder, StateBird, StatePopulation)
• Capital (CapitalName, StateAbbrev,
  YearDesignated, PhoneAreaCode, CapitalPopulation)
• Foreign key StateAbbrev to State relation
• City (StateAbbrev, CityName, CityPopulation)
• Foreign key StateAbbrev to State relation
• Crop (CropName, Exports, Imports)
• Production (StateAbbrev, CropName, Quantity)
• Foreign key StateAbbrev to State relation
• Foreign key CropName to Crop relation
• Shipping (StateAbbrev, OceanShoreline,
  ExportTonnage, ImportTonnage)
• Foreign key StateAbbrev to State relation

13
The Entity-Relationship (E-R) Diagram

• Another common shorthand for describing relations
  is the E-R diagram
• Shows the entities (relations) and the
  relationships among them in a symbolic (visual)
  way.
• Translates different views of data among
  managers, users, and programmers to fit into a
  common framework.
• Defines data processing and constraint
  requirements to help us meet the different views.
• Helps implement the database.

14
The Entity Relationship (E-R) Diagram

• E-R Diagram Components
• Entities
• In E-R diagrams, an entity refers to a relation
  (table).
• An entity is represented by a rectangle
  containing the entitys name.
• Attributes
• Attributes (columns) are represented by ovals and
  are connected to the entity with a line.
• Each oval contains the name of the attribute it
  represents.
• Attributes have a domain -- the attributes set
  of possible values.
• Attributes may share a domain.
• Primary keys are underlined.
• Relationships
• Appear in diamonds.
• A verb describing the relationship appears inside
  the diamond.

15
The Attributes of the STUDENT Entity
16
Basic E-R Diagram Entity Presentation
17
The Entity-Relationship (E-R) Diagram

• Classes of Attributes
• A simple attribute cannot be subdivided.
• Examples Age, Sex, and Marital status
• A composite attribute can be further subdivided
  to yield additional attributes.
• Examples
• ADDRESS ? Street, City, State, Zip
• PHONE NUMBER ? Area code, Exchange number

18
The Entity-Relationship (E-R) Diagram

• Classes of Attributes
• A single-valued attribute can have only a single
  value.
• Examples
• A person can have only one social security
  number.
• A manufactured part can have only one serial
  number.
• Multivalued attributes can have many values.
• Examples
• A person may have several college degrees.
• A household may have several phones with
  different numbers
• Multivalued attributes are shown by a double line
  connecting to the entity.

19
The Entity-Relationship (E-R) Diagram

• Multivalued Attribute in Relational DBMS
• The relational DBMS cannot implement multivalued
  attributes.
• Possible courses of action for the designer
• Within the original entity, create several new
  attributes, one for each of the original
  multivalued attributes components.
• Create a new entity composed of the original
  multivalued attributes components.

20
Splitting the Multivalued Attributes into New
Attributes
21
A New Entity Set Composed of Multivalued
Attributes Components
22
The Entity-Relationship (E-R) Diagram

• A derived attribute is not physically stored
  within the database instead, it is derived by
  using an algorithm.
• Example AGE can be derived from the data of
  birth and the current date.

23
The Entity-Relationship (E-R) Diagram

• Relationships
• A relationship is an association between
  entities.
• Relationships are represented by diamond-shaped
  symbols.

24
The Entity-Relationship (E-R) Diagram

• A relationships degree indicates the number of
  associated entities or participants.
• A unary relationship exists when an association
  is maintained within a single entity.
• A binary relationship exists when two entities
  are associated.
• A ternary relationship exists when three entities
  are associated.
• See next slide

25
The Entity-Relationship (E-R) Diagram
26
The Implementation of a Ternary Relationship
27
The Entity-Relationship (E-R) Diagram

• Connectivity
• The term connectivity is used to describe the
  relationship classification (e.g., one-to-one,
  one-to-many, and many-to-many).

28
The Entity-Relationship (E-R) Diagram

• Cardinality
• Cardinality expresses the specific number of
  entity occurrences associated with one occurrence
  of the related entity.

29
The Entity-Relationship (E-R) Diagram

• Relationship Participation
• The participation is optional if one entity
  occurrence does not require a corresponding
  entity occurrence in a particular relationship.
• An optional entity is shown by a small circle on
  the side of the optional entity.

30
CLASS is Optional to COURSE
COURSE and CLASS in a Mandatory Relationship
31
The Entity-Relationship (E-R) Diagram

• Recursive Entities
• A recursive entity is one in which a relationship
  can exist between occurrences of the same entity
  set.
• A recursive entity is found within a unary
  relationship.

32
(No Transcript)
33
The Implementation of the MN RecursivePART
Contains PART Relationship
34
Implementation of the MN COURSE Requires
COURSE Recursive Relationship
35
Implementation of the 1M EMPLOYEE Manages
EMPLOYEE Recursive Relationship
36
The Entity-Relationship (E-R) Diagram

• Composite Entities
• A composite entity is composed of the primary
  keys of each of the entities to be connected.
• The composite entity serves as a bridge between
  the related entities.
• The composite entity may contain additional
  attributes.

37
Converting the MN Relationship Into Two 1M
Relationships
38
The MN Relationship Between STUDENT and CLASS
39
End

• References
• New Perspectives on Microsoft Access 2000,
  Introductory, by Adamski, Hommel, and Finnegan,
  Course
• Technology, 1999.
• Access Database Design and Programming, Third
  Edition, by Roman, OReilly, 2002.
• Database Systems Design, Implementation, and
  Management, by Rob Coronel, Boyd Fraser, 1995.