A Demo of Logical Database Design

1
A Demo of Logical Database Design
2
Aim of the demo

• To develop an understanding of the logical view
  of
• data and the importance of the relational
  model
• To consider entities, attributes, and tables
  within the
• relational view
• Learn basic and advanced ER modelling techniques

3
Entities

• Relational database design starts by defining the
  required entities
• Entities are anything you want to store data
  about
• people (students, customers, employees, etc.)
• places (resorts, cities, countries, etc.)
• things (restaurants, products, invoices, movies,
  paintings, books, buildings, contracts, etc.)
• events (elections, presentations, earthquakes,
  hurricanes, etc.)

4
Entity Sets / Entity Types

• Entity sets are collections of related entities.
  Entities are related by their classification
• student entities are related by the fact that
  they are all students
• invoice entities are related by the fact that
  they are all invoices
• car entities are related by the fact that they
  are all cars

5
Entity Sets/Types and Entities

• Unfortunately, database designers almost always
  use the two terms as synonyms.
• When database designers refer to entities, they
  really are referring to entity sets or entity
  types.
• Therefore, when you see a reference to an
  EMPLOYEE entity in a database design, remember
  that EMPLOYEE actually represents an entity
  set/type that contains a collection of employee
  entities and each instance of this type is one
  single entity.
• Entity Set (type) Class
• Entity Object

6
Attributes

• Properties of entities or relationships
• This is the data stored about an entity
• E.g. student entity could have attributes such
  as name,
• address, DOB etc.

7
Types of attributes

• Simple each entity has a single atomic value
  for the attribute. Cannot be subdivided
• Age, sex, marital status
• Composite (composed of several components) Can
  be subdivided into additional attributes
• Address into street, city, zip
• Name(FirstName, MiddleName, LastName)
• Single-valued Can have only a single value
• Person has one social security number
• Multi-valued an entity may have multiple values
  for that attribute
• Person may have several college degrees
  (qualifications, skills)
• Color of a car
• Derived Can be derived with algorithm
• Age can be derived from date of birth

8
Overview of Database Design

• Conceptual design (ER Model is used at this
  stage.)
• What are the entities and relationships in the
  database?
• What information about these entities and
  relationships should we store
  in the database?
• What are the integrity constraints or business
  rules that hold?
• A database schema in the ER Model can be
  represented pictorially (ER diagrams).
• Can map an ER diagram into a relational schema
  by hands or tools.

9
ER Diagram Two Most Commonly Used Notations
10
ER Diagrams - The Basics
11
ER Diagram Basics - Cardinalities
12
Classification of Cardinalities

• Minimum cardinality - relationship participation
• Mandatory (existence dependent) Entity
  occurrence requires corresponding occurrence in
  the related entity. Offering cannot exist without
  being related to a course.
• Optional Entity occurrence does not require a
  corresponding occurrence in the related entity. A
  course does not necessarily need an offering to
  exist.
• Maximum cardinality based
• 1-M
• M-N
• 1-1

13
Weak Entities Identifying Relationship

• Identifying relationship
• One entity is existence-dependent on another
• PK of related entity contains PK component of
  parent entity

14
Weak Entities

• A weak entity is an entity that
• Is existence-dependent i.e. weak entities cannot
  exist without the owner entity AND
• Has a primary key that is partially or totally
  derived from the parent entity in the
  relationship. They do not have the PK of their
  own
• However not every existence dependency results in
  a weak entity type.
• Eg Driver License cannot exist unless it is
  related to PERSON entity even though it has its
  own key (Licence number) and hence is not a weak
  entity.

15
M-N Relationships with Attributes

• Replace M-N relationship with
• Associative entity type
• Two identifying 1-M relationships

16
Associative Entity Type Example
17
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.

18
Entity Supertypes and Subtypes

• Generalization hierarchy
• Depicts relationships between higher-level
  supertype and
• lower-level subtype entities
• Supertype has shared attributes
• Subtypes have unique attributes
• A subtype entity inherits its attributes and
  its relationships
• from the supertype entity
• The supertype and its subtype(s) maintain an
  is-a relationship

19
Generalization Hierarchies
20
Disjointness and Completeness of Subtypes

• Disjointness D means intersection is empty
• Overlap No symbol means intersection is not
  empty Faculty and Student. Subtypes have common
  entities
• Completeness C means union of subtype entities
  is the set of supertype entities
• Nothing supertype can have free standing
  entities ((not in any subtype). Union does not
  provide supertype

21
Entity Vs Attribute

• Should address be an attribute of Employees or an
  entity (connected to Employees by a
  relationship)?
• Depends upon the use we want to make of address
  information, and the semantics of the data
• If we have several addresses per employee,
  address must be an entity (since attributes
  cannot be set-valued).
• If the structure (city, street, etc.) is
  important, e.g., we want to retrieve employees in
  a given city, address must be modelled as an
  entity (since attribute values are atomic).

22
Summary

• A data model is the relatively simple
  representation, usually graphic, of complex
  real-world data structures. It represents data
  structures and their characteristics, relations,
  constraints, and transformations.
• Different type attributes have different
  implications in a database. Some of these need to
  be considered carefully.

23
Comprehensive Example