Data Model

Introduction

Entity-Relationship Model (ER)

• Requirements
• Analysis
• Conceptual Design
• Implementation
• Physical DB Design
• (DB Design should follows the same thing as software engineering design - the "waterfall" model)

The Big Picture

Miniworld

|

REQUIREMENTS COLLECTION AND ANALYSIS ---> Functional Requirements ---> FUNCTIONAL ANALYSIS ---> High-level Transaction Specification

|

Database Requirements

|

CONCEPTUAL DESIGN

|

Conceptual Schema (In a high-level data model)

|

LOGICAL DESIGN (DATA MODEL MAPPING)

DBMS-independent ----------------------------------------------------------------------------------------

DBMS-specific ---------------------------------------------------------------------------------------------

|

Logical (Conceptual) Schema (In the data model of a specific DBMS)

|

PHYSICAL DESIGN <--- High-level Transaction Specification ---> APPLICATION PROGRAM DESIGN ---> TRANSACTION IMPLEMENTATION ---> Applicationi Programs

|

Internal Schema (For the same DBMS)

Figure: Simplified phases of database design

Overview of ER

Learn by example:

Design a DB for a company to store employee data

Requirement

Company has:

• Employees
• Depts
• Projects

Charasteristies

A company has various depts

Each dept has:

• a unique name
• a unique number
• info on employee who manages it
• start date as manager
• controls several projects

Figure xxx

Each project has a unique name number and a location

More

Each employee has

• SSN
• name
• addr
• salary
• sex
• bday

"Employee" vs "employee"

Dependents

• name
• sex
• bday
• relationship

Figure xxx

Each employee is assigned to one dept but may work on several projects.

Figure xxx

Detail P43 and P59

Where do we start?

Entity - an object with conceptual existence

Attributes - properties of an entity. e.g. name, age, ID, ...

Value - assigned to an attribute

For example: Employee entity, ei and company entity ci

Syntax

Simple Attributes are not divisible

==> atomic (Age = 5, GPA = 3.5)

Composite is divided into subparts

e.g. addr = street#, street name, city, state

Hierachy of Composite Attributes

Single vs Multivalued Attributes

Single - one value for a particular entity (e.g. age)

Multivalue - > 1 value or (lbound/lower bound, ubound/upper bound)
e.g. (fr, soph, jr, grad, openu)

Stored vs Derived Attributes

s = start-date

c = current-date

employment-length = c - s
(Derived from c & s)

e.g. Number of employee = counting num of emp in Dept Entity

Special Value

Null:

• Not applicable
• Unknown
• Missing

Example

Student <- Entity Type

------
Name
------
ID
------
Sex
------

Entity Set (Extension)

More Terms

Key

• attribute with a distinct (unique) value for each individual entity ei
• no 2 entries have the same value
• can have more than one (key)

Domain of Attribute

• Range of valid values

  e.g. month = (1, 12)

Relationship

Figure xxx

e1 and ej works for dk

Degree of ri = 2

Degree of Relationship

Binary:

---- O ----

Tenary:

----- |
---- O ----

Cardinality Ratio

Shows # of relationship instances an entity can participate in

(M : 1)

1 : 1 = one-to-one

1: N = one-to-many

M:N = many to many

Weak Entity Type

• With no key attributes of their own
• Use double //

For example:

DEPENDENT --- name, [ ]

Two dependent has same name but belong to two different employment

OR

A Employee has two same name dependent

(When look at weak entity, we cannot unique identity who that is, e.g. dependent <- need to know the employee first).

In other words, Employee entity owns the dependent entries that are related to it.

Page 59 for all box labels

HW: What is Identifying Relationship Type means

Figure xxx: Employee, Supervision relationship

Need ER Model Homework