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
