Data Modeling Using EntityRelationship Diagrams ERDs

1
Lecture-3

• Data Modeling Using Entity-Relationship Diagrams
  (ERDs)

2
Introduction

• In a given set of requirements for a database, we
  can generally assume the following implications
• Nouns ? Entities/Entity types
• Nouns describing entity types ? Attributes
• Verbs (or even nouns) relating entity types
  together ? Relationships/Relationship types

3
Requirements of a COMPANY Database

• The database will be used to keep track of the
  companys employees, departments, and projects
• The company is organized into departments. Each
  department has a unique name, unique number and a
  particular employee who manages the department.
  We keep track of the start date of the department
  manager. A department may have several
  locations..
• Each department controls a number of projects.
  Each project has a unique name, unique number and
  is located at a single location. In addition, we
  would like to know the number of employees in any
  department company

4
Requirements of a COMPANY Database

• We store each employees name, social security
  number, address, salary, sex, and birth date.
  Each employee works for one department but may
  work on several projects which are not
  necessarily controlled by the same department. We
  keep track of the number of hours per week that
  an employee currently works on each project. We
  also keep track of the direct supervisor of each
  employee.
• Each employee may have a number of dependents
  which are stored in the database for insurance
  purposes. For each dependent, we keep track of
  their name, sex, birth date, and relationship to
  employee.

5
Preliminary design of entity types for the
COMPANY database
6
(No Transcript)
7
Entity Types and Key Attributes

• Entity type vs. Entity
• A key attribute of the entity type
• SSN of EMPLOYEE
• Uniqueness Constraint

ENTITY TYPE NAME
8
Types of Attributes

• Attributes Ovals
• Data type or domain or value set
• Composite VS Simple (Atomic)
• DOB, Name, Address vs Salary
• Brackets
• Single-valued VS Multi-valued
• GPA vs Degrees
• Curly braces
• Complex
• Compositemultivalued
• Several addresses

9
Types of Attributes

• Stored vs Derived (Physical vs Logical)
• DOB vs Age (What's wrong with storing age?)
• Key vs Non-keys
• More than 1 key (SSN, ID, etc)
• Can be composite
• Registration Plate, State

10
(No Transcript)
11
Relationships and Relationship Types

• Relationship type vs Relationship
• Read left-to-right or top-down
• Degree of a relationship type
• of participating entities
• Binary, ternary, n-ary
• Can have attributes
• Grades
• HoursPerWeek of WORKS_ON
• Intention
• Database Schema, Relationship Type, Entity Type
• Extention
• Database State, Relationship Set, Entity Set

RELATIONSHIPTYPE NAME
12
Relationships and Relationship Types

• Cardinality of a relationship type (only binary
  ones)
• One-to-one (11)
• One-to-many or many-to-one (1N or N1)
• Many-to-many (MN)

13
Constraints on Relationships

• (Entity Type) Participation in a relationship
  type
• Partial participation zero or more (optional)
• Not every EMPLOYEE MANAGES a DEPARMENT
• Total participation one or more (mandatory)
• Every EMPLOYEE WORKS_FOR a DEPARMENT