Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Database Design Process: From Conceptual Schema to Logical and Physical Design, Slides of Introduction to Database Management Systems

An overview of the database design process, covering requirements analysis, conceptual design, logical design, and physical design. It includes discussions on entities, relationships, attributes, and database modeling techniques such as entity-relationship (er) modeling and referential integrity. The document also touches upon topics like participation and referential constraints, cardinality, and data modeling aids.

Typology: Slides

2011/2012

Uploaded on 02/13/2012

richiey
richiey 🇨🇦

32 documents

1 / 36

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
3-1
Database Design Process
Real World
Requirements Analysis
Database
Requirements
Conceptual Des ign
Conceptual
Model
Logical Design
Physical Design
Logical
Schema
Functional
Requirements
Functional Analysis
Access
Specifications
Application Pgm Design
E-R Modeling
Choice of a
DBMS
Data Model
Mapping
3-2
Requirements Collection &
Analysis
Examples of activities:
Identification of user groups and application areas
Analysis of the operating environment and processing
requirements
Interviews
Caveat:
Users change their minds
Anticipating users’ future desires is difficult
On the one hand: Adaptive system design is good.
On the other hand: Good performance requires freezing
important system parameters.
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24

Partial preview of the text

Download Database Design Process: From Conceptual Schema to Logical and Physical Design and more Slides Introduction to Database Management Systems in PDF only on Docsity!

3-

Database Design Process

Real World

Requirements Analysis Database Requirements

Conceptual Design

Conceptual Model

Logical Design

Physical Design

Logical Schema

Functional Requirements

Functional Analysis

Access Specifications

Application Pgm Design

E-R Modeling

Choice of a DBMS

Data Model Mapping

Requirements Collection &

Analysis

 Examples of activities:

 Identification of user groups and application areas

 Analysis of the operating environment and processing

requirements

 Interviews

 Caveat:

 Users change their minds

 Anticipating users’ future desires is difficult

 On the one hand: Adaptive system design is good.

 On the other hand: Good performance requires freezing

important system parameters.

3-

Conceptual Database Design

 Conceptual Schema Design:

 Database structure, semantics, interrelationships, and

constraints.

 A stable description of the database (anticipating users’

desires).

 High-level data model may be useful:

➠ Expressiveness

➠ Simplicity

➠ Minimality

➠ Diagrammatic

Conceptual Database Design

 Design strategies

 Top-down: start from abstraction and use successive

refinements.

➠ This is the one we focus on

 Bottom-up: start from concrete designs to find

abstractions.

➠ Databases exist; the focus is on integration

 Iterative: mixed top-down and bottom-up as appropriate

3-

Conceptual Design

Real World

Requirements Analysis Database Requirements

Conceptual Design

Conceptual Schema

Logical Design

Physical Design

Logical Schema

Functional Requirements

Functional Analysis

Access Specifications

Application Pgm Design

 Top-Down Design

 Determine the entities, attributes, relationships

 Model them properly

 Map the resulting E-R model into a data model

 Conceptual

 No physical details

 Easier to detect conceptual design errors

 One of the logical database design aids

 Significant amount of research within the database

community

 Easy mappings to other data models possible

Entity-Relationship Modeling

3-

 Entity

 An object that exists in the real world, that has certain properties and

that is distinguishable from other objects

 Example

➠ Employee ➠ Project

 Relationship

 Associations between two or more entities

 Example

➠ Manage Employees manage projects ➠ Work Employees work in projects

 Attribute

 The properties of entities and relationships

 Example

➠ Employee Employee No, Name, Title, Salary ➠ Work Responsibility, Assignment duration

Entity-Relationship Modeling

 Entity type is an abstraction that defines the properties

(attributes) of a similar set of entities

 Example:

➠ Employee Name, Title, Salary ➠ Project Name, Budget, Location

 Entity instances are instantiations of types

 Example:

➠ Employee Joe, Jim, ... ➠ Project Compiler design, Accounting, ...

 An entity instance can have multiple entity types

 Example :

➠ If we also want to have an EMPLOYEE entity type, then every engineer is also an employee

 Entity class (or entity set ) is a set of entity instances that are of

the same type

 Similar arguments can be made for relationships

Entity Types and Instances

3-

 Entity identifier

 One or more of the attributes that can uniquely identify

each instance of a given entity type

 Example

➠ Employee Employee No

➠ Project Project No

 Relationship identifier

 A means of identifying each relationship instance.

 Usually a composite identifier consisting of the

identifiers of the two or more entity types that it relates

 Example

➠ Works(Employee No, Project No)

Identifiers

Entities-Attributes-Relationships

DEPARTMENT

WAREHOUSE PART EMPLOYEE

SUPPLIER PROJECT

LOCATION

Supplier No

Supplier Name Location^

Project No

Project Name Budget

Wareh. No

Wareh. Name Location^

Part No

Part Name QTY^ WGT

City Dept. No

Dept. Name Manager

Emp. No

Emp. Name AddrTitle Salary

are supplied by supply

are supplied by

supply

are used by

use

work on employ

manage

3-

Entity types and instances

Attributes

Relationships

E-R Notation

E-R Diagrams

EMPLOYEE PROJECT

Responsibility

Duration

Budget

Project Employee No Project No Name Employee Name

Title Salary

WORKS ON

Address

Apt. # City

Street #

NoEmp

Location

3-

 Each instance of one entity class E1 can be

associated with at most one one instance of another

entity class E2 and vice versa.

 Example :

 Each employee can work in at most one project and each

project employs at most one employee.

One-to-One Relationship

EMPLOYEE PROJECT

Responsibility

Duration

Budget

Project Name

Employee No EmployeeName Project No

Title Salary

WORKS ON

One-to-One Relationship

WORKS_ON

Relationship Instances

EMPLOYEE Set PROJECT Set

3-

 Each instance of one entity class E1 can be

associated with zero or more instances of another

entity class E2, but each instance of E2 can be

associated with at most 1 instance of E1.

 Example :

 Each employee can work in at most one project; each

project can employ many engineers.

Many-to-One Relationship

EMPLOYEE PROJECT

Responsibility

Duration

Budget

Project Employee No Project No Name Employee Name

Title Salary

WORKS ON

N 1

Many-to-One Relationship

WORKS_ON

Relationship Instances

EMPLOYEE Set PROJECT Set

3-

 An entity instance of type T 1 is in a relationship

with another entity instance of type T 1.

 It assumes multiple roles.

Recursive Relationships

EMPLOYEE

MANAGES

1 N

Manager Subordinate

PART

CONTAIN

M N

Is part of Consists of

Multiple Relationships

EMPLOYEE PROJECT

Duration Responsibility

Budget

Project Employee No Project No Name Employee Name

Title Salary

WORKS ON

N M

MANAGES

3-

A relationship can link more than one type of entity.

Higher-Order Relationships

SUPPLIER PROJECT

Duration Responsibility

Budget

Project Name

Supplier No SupplierName Project No

Credit Location

SUPPLY

N M

PROVIDE

N M

PART

Part No L

Part Name

Qty

Wgt

Constraints

 Referential integrity

 When there is a 1:1 or M:1 relationship R between

entity types E1 and E2, if one and exactly one instance

of E2 has to exist for a given instance of E1, a

referential integrity constraint exists

 Participation constraint

 Determines whether instances of a given entity can

exist without participating in a relationship

 Cardinality constraint

 Relationship types (1:1, M:1, M:N) and their

refinement where the exact number is specified

3-

Strong entities: The instances of

the entity class can exist on their

own, without participating in

any relationship.

 Also called non-obligatory

membership.

Weak entities: Each instance of

the entity class has to participate

in a relationship in order to

exist. Keys are imported from

dependent entity.

 Also called obligatory

membership.

 Special type of total participation

Strong and Weak Entity Sets

PROJECT

Balance

Budget Line Expenses

RECORDS

BUDGET

Partial key

 Be careful in defining and interpreting relationships.

 For example, consider the following diagram.

 Can we find, for any given employee, which department he

is in?

 Conversely, can we find, for a given department, which

employees are in that department?

Connection Traps

DIVISION

DEPARTMENT EMPLOYEE

N

N

INCLUDES

DEPT

INCLUDES

EMP

3-

One solution is to change the relationship definition.

Connection Traps

DIVISION

DEPARTMENT

EMPLOYEE

N 1

N

INCLUDES

DEPT

INCLUDES

EMP

What will happen if some employees are connected

with divisions (e.g., as consultants to division heads),

but are not included in any department?

Connection Traps

DIVISION

DEPARTMENT

EMPLOYEE

N 1

N

INCLUDES

DEPT

INCLUDES

EMP

CONSULTS

M FOR^ N

3-

EMPLOYEE WORKS ON PROJECT

N M

 Treat the relationship as an entity class. Define suitable

relationships among three entities.

 This simplification is not necessary for mapping into the

relational model, but is important for mapping into other models.

Many-to-Many Simplification

EMPLOYEE PROJECT

EMP-EMP

M

EMP-PROJ

EMPLOYMENT M

A relationship can link more than one type of entity.

Higher-Order Relationships

SUPPLIER PROJECT

Duration Responsibility

Budget

Project Supplier No Project No Name Supplier Name

Credit Location

SUPPLY

N M

PROVIDE

N M

PART

Part No L

Part Name

Qty

Wgt

3-

Higher-Level Relationships

Create an intermediate weak entity type

SUPPLIER SUPPLY PROJECT

N M

ORD-PART

N M

PART

L

SUP-ORD ORD-PROJ

ORDER

Specialization

 An entity type E1 is a specialization of another

entity type E2 if E1 has the same properties of E

and perhaps even more.

 E1 IS-A E

MANAGER

EMPLOYEE

MANAGER

EMPLOYEE

or isa

Specialization

Generalization