Lecture 2

Data Modeling using the Entity

Relationship Model
 Overview

◉ We learned that:

1. Database is a collection of related data that represents a real-world

2. A DBMS will allow us to create and manage databases.

3. We can query the database (retrieve data from tables in the database).

4. We can update the database (change, add, or delete data from tables in the
database).

5. We can use SQL to perform operations on the database

Overview

◉Database State
Overview

◉Database Schema
 This chapter

◉ How to design a database using the Entity-Relationship Model ( ER-Model )

◉ Three major components:

1. Entities
2. Attributes
3. Relationships
This chapter
 Database Design

◉ Objective: Decide on the structure of the database

◉ Three main steps:

1. Requirements collection and analysis
2. Conceptual design
3. Logical and physical design

1. Requirements Analysis 2. Conceptual Design 3. Logical and Physical Design

 1. Requirements collection and analysis

◉ Decide on the requirements of the database system

◉ Ask questions such as:

1. What is going to be stored?
2. How is it going to be used?
3. Who should access the data?

◉ Result: data requirements

◉ Functional requirements of the application
 2. Conceptual design

◉ A high-level description of the database

◉ Detailed so that technical people can understand it

◉ But, not too complicated that non-technical people can’t understand it

◉ From a set of requirements to a conceptual design.

Using the ER-Model

Many data models have been developed in the past:

➢ The Entity-Relationship Model (ER Model)

➢ The Hierarchical Data Model
➢ The Network Data Model
➢ The Relational Data Model
➢ The Object-Oriented Data Model
Examples of Data Models (1)
The Entity-Relationship Model

m 1
Employees Works_In Departments
SSN Name Name Location
 Examples of Data Models (2)

The Hierarchical Data Model

Departments Name Location

Employees SSN Name

Examples of Data Models (3)
The Relational (Tables) Data Model

Employees SSN Name Dept-Name

Departments Name Location

Examples of Data Models (4)

The Object-Oriented Data Model

Employees Departments
SSN Name
Name Location
Department
 3. Logical and Physical design

◉ Logical design or data-model mapping:

◉ Result is a database schema in implementation data model of DBMS

◉ Physical design phase:

◉ Internal storage structures, file organizations, indexes, access paths, and
physical design parameters for the database files specified
Part 2: Main components of the
ER-Model
ER-Diagram example
 ER-Model components

◉ Three major components:

1. Entities

2. Attributes

3. Relationships
 1. Entities

◉ Basic objects in ER model

◉ A “thing” in the real world with independent existence

◉ Physical existence: person, car, house, employee, …

◉ Conceptual existence: company, job, course, …

Each entity must have a set of attributes

 1. Entities, entity type, and entity sets

◉Entities (records): The individual objects, which are members of entity

sets
Ex: A specific person or product

◉Entity type: A collection of entities that have the same attributes.

Should be a noun

◉Entity sets: Represent the sets of all possible entities

 1. Entities

◉ Entities example:
 1. Entities

◉ Entities, entity type and entity set example:

 2. Attributes

◉ Properties used to describe entities

◉ Example #1:
◉ Entity Name: EMPLOYEE
◉ Attributes:
◉ name, age, address, salary, job

◉ Example #2:
◉ Entity Name: CAR
◉ Attributes:
◉ name, maker, VIN number
 2. Attributes

◉ Properties used to describe entities

◉ A particular entity will have a value for each of its attributes

name: “John Smith”

age: 55
address: 2311 Kirby, Houston, TX 77001
salary: $2000
job: Accountant

◉ The values are the actual data stored in the database

 Entities and Attributes

Entity
Graphical representation
Man Woman First Last Date of
Birth
Name Name

Child Person
Baby

An entity is represented by a rectangle

An attribute is represented by an Oval
Old person
 2. Attributes

◉ Types of attributes:

1. Simple (Atomic) Vs. Composite Attributes

Composite attributes can be divided into smaller subparts
◉ Subparts represent basic attributes with independent meanings
◉ Ex. Address (Building no., street, destrict, city, country )
◉ Ex. FullName (1st name, middle, last or family name)
◉ Ex. Telephone (002055….)

Simple attributes cannot be divided

Ex. City, ZipCode, FirstName, LastName, …
 2. Attributes

◉ Types of attributes:

2. Single-valued Vs. Multi-valued Attributes

Single-valued attributes have a single value for a particular entity
◉ Ex. Age, Color, Name, …

Multi-valued attributes may have a number of values for a particular entity

◉ Ex. Skill, CollegeDegrees,
 2. Attributes

◉ Types of attributes:

3. Complex Attributes
A combination of composite and multivalued attributes
Ex. Telephone Number (002055……; 002011….)
 2. Attributes

◉ Types of attributes:

4. Stored Vs. Derived Attributes

In some cases, one attribute (or more) is related to another
Ex. Age and BirthDate
A derived attribute can be determined from a stored attribute
Ex. BirthDate is a stored attribute, while Age is a derived attribute
 2. Attributes

◉ Types of attributes:

5. Null Values
Used in cases where attributes may not have values for a specific entity
1. Because the value is not applicable
Ex. ApartmentNo, CollegeDegrees, …

2. Or because the value is unknown

Ex. PhoneNumber (may not exist)
Ex. Height of a person (exists)
 2. Attributes

◉ Key attribute:
• Key attribute: An attribute that has to be unique and can be used to identify
an entity (Primary Key in the database)

• Value sets (or domain of values):

• Specifies set of values that may be assigned to that attribute for each
individual entity
 Entities and Attributes

Name Address price name ID

Company Product

◉ Is there a relationship between company and product?

 3. Relationship

Name Address price name ID

Company Makes Product

◉ Is there a relationship between company and product?

◉ Yes, a company makes products
ER-Diagram example
 ER-Diagram example

More than
Key attribute one
branch

Driven attribute
 ER-Diagram example

??

Weak entity
 3. Relationship

◉ A relationship between two (or more) entities

1. A relationship can have attributes

 3. Relationship: A relationship can have attributes

Name Data of birth Release date name ID

Person Watch Movie

◉ A person can watch a movie more than once

◉ What if we want to save the date each time a person watches a
movie?
 3. Relationship: A relationship can have attributes

Name Data of birth Release date name ID

Person Watch Movie

date
ER Diagram Notations

Entity Attribute

Weak Entity Key Attribute

Relationship Derived attribute

Identifying Relationship Multivalued

ER Diagram Notations

Composite attributes