10-1
Modern Systems Analysis
and Design
Jeffrey A. Hoffer
Joey F. George
Joseph S. Valacich
Chapter 9
Database Design
10-2
Learning Objectives
Define key database design terms.
Explain the role of database design in the IS
development process.
Transform E-R or class diagrams into normalized
relations.
Merge normalized relations from separate user views
into a consolidated set of well-structured relations.
Choose storage formats for fields.
Translate well-structured relations into database
tables.
Explain when to use different types of file
organizations.
Describe the purpose and appropriate use of
indexes.
10-3
10-4
Logical Database Design
• Based upon the conceptual data model
• Four key steps
1. Develop a logical data model for each known user
interface for the application using normalization
principles.
2. Combine normalized data requirements from all user
interfaces into one consolidated logical database
model (view integration).
3. Translate the conceptual E-R data model for the
application into normalized data requirements.
4. Compare the consolidated logical database design
with the translated E-R model and produce one final
logical database model for the application.
10-5
Physical Database Design
• Based upon results of logical database design
• Key decisions
1. Choosing storage format for each attribute from the
logical database model
2. Grouping attributes from the logical database model
into physical records
3. Arranging related records in secondary memory (hard
disks and magnetic tapes) so that records can be
stored, retrieved and updated rapidly
4. Selecting media and structures for storing data to
make access more efficient
10-6
Deliverables and Outcomes
• Logical database design
– must account for every data element on
a system input or output
– normalized relations are the primary
deliverable
• Physical database design
– converting relations into database tables
10-7
Relational Database Model
• Relational Database: data represented as a
set of related tables (or relations)
• Relation: a named, two-dimensional table of
data. Each relation consists of a set of named
columns and an arbitrary number of unnamed
rows
• Well-Structured Relation: a relation that
contains
– minimum amount of redundancy
– allows users to insert, modify, and delete the
rows without errors or inconsistencies
10-8
Properties of a Relation
• Entries in cells are simple.
• Entries in columns are from the same
set of values.
• Each row is unique.
• The sequence of columns can be
interchanged without changing the
meaning or use of the relation.
• The rows may be interchanged or
stored in any sequence.
10-9
Primary Keys
• Primary Key
– An attribute whose value is unique
across all occurrences of a relation.
• All relations have a primary key.
• This is how rows are ensured to be
unique.
• A primary key may involve a single
attribute or be composed of multiple
attributes.
10-10
Well-Structured Relation
No redundancy, and data pertains to a single entity,
an employee
10-11
A Poorly Structured Relation
10-12
Normalization
• The process of converting complex
data structures into simple, stable
data structures.
• First Normal From (1NF)
– Unique rows
– No multi-valued attributes
10-13
Normalization (cont.)
• Second Normal Form (2NF)
– Each nonprimary key attribute is identified
by the whole key (called full functional
dependency).
• Third Normal Form (3NF)
– Nonprimary key attributes do not depend
on each other (i.e. no transitive
dependencies).
• The result of normalization is that
every nonprimary key attribute
depends upon the whole primary key.
10-14
Normalized Relations
Redundancies
removed by
breaking into
two separate
relations
10-15
Functional Dependencies and
Determinants
• Functional Dependency
– A particular relationship between two attributes. For a
given relation, attribute B is functionally dependent on
attribute A is, for every valid value of A, that value of A
uniquely determines the value of B
• Determinant: an attribute that determines the
values of other attributes
– All primary keys are determinants
• Symbolic notation:
– AB
– A is the determinant
– B is functionally dependent on A
10-16
Identifying Functional
Dependencies and Determinants
• Functional dependency is not a
mathematical dependency.
• Instances (or sample data) in a relation do
not prove the existence of a functional
dependency.
• Knowledge of problem domain is most
reliable method for identifying functional
dependency.
10-17
Second Normal Form (2NF)
• A relation is in second normal form
(2NF) if any of the following
conditions apply:
– The primary key consists of only one
attribute
– Every nonprimary key attribute is
functionally dependent on the full set of
primary key attributes
10-18
Conversion to a Higher Normal
Form
• To convert a relation into 2NF or 3NF,
decompose the relation into new
relations using the attributes, called
determinants, that determine other
attributes.
• The determinants become the primary
key of the new relation.
10-19
1NF but not 2NF
MPLOYEE2(Emp_ID, Name, Dept, Salary, Course, Date_Completed)
unctional dependencies:
. Emp_ID Name, Dept, Salary partial key dependency
. Emp_ID, Course Date_Completed
10-20
2NF (actually, also 3NF)
EMPLOYEE1(Emp_ID, Name, Dept, Salary) EMPCOURSE(Emp_ID, Course,
Date_Completed)
Functional dependencies:
Functional dependency:
Emp_ID Customer_Name, SalesPerson
Emp_ID, Course
Date_Completed
10-21
Third Normal Form (3NF)
• A relation is in third normal form
(3NF) if it is in second normal form
(2NF) and there are no functional
(transitive) dependencies between
two (or more) nonprimary key
attributes.
10-22
2NF but not 3NF
SALES(Customer_ID, Customer_Name, SalesPerson, Region)
Functional dependencies:
1. Customer_ID Customer_Name, SalesPerson, Region
transitive
2. SalesPerson Region
10-23
Converted to 3NF
SALES1(Customer_ID, Customer_Name, SalesPerson) SPERSON(SalesPerson, Region)
Functional dependencies: Functional dependency:
Customer_ID Customer_Name, SalesPerson SalesPerson Region
10-24
Functional Dependencies and
Primary Keys
• Foreign Key
– An attribute that appears as a nonprimary key
attribute in one relation and as a primary key
attribute (or part of a primary key) in another
relation
• Referential Integrity
– An integrity constraint specifying that the value
(or existence) of an attribute in one relation
depends on the value (or existence) of the
same attribute in another relation
10-25
Foreign Key Example
The foreign key
• The foreign key establishes a one-to-many relationship between
SPERSON (one) and SALES1 (many)
• There can be no SalesPerson value in SALES1 that does not
exist in SPERSON (referential integrity)
10-26
Transforming E-R Diagrams
into Relations
• It is useful to transform the
conceptual data model into a set of
normalized relations
• Steps
– Represent entities
– Represent relationships
– Normalize the relations
– Merge the relations
10-27
Representing Entities
• Each regular entity is transformed into a
relation.
• The identifier of the entity type becomes
the primary key of the corresponding
relation.
• The primary key must satisfy the
following two conditions.
a. The value of the key must uniquely identify
every row in the relation.
b. The key should be non-redundant.
10-28
10-29
Represent Relationships
• Binary 1:N Relationships
– Add the primary key attribute (or attributes) of
the entity on the one side of the relationship as
a foreign key in the relation on the right side.
– The one side migrates to the many side.
• Binary or Unary 1:1
– Three possible options
– Add the primary key of A as a foreign key of B.
– Add the primary key of B as a foreign key of A.
– Both of the above.
10-30
Represent Relationships (cont.)
10-31
Represent Relationships (cont.)
10-32
Represent Relationships
(cont.)
• Binary M:N relationships
– Create another relation and include
primary keys of all relations as primary
key of new relation.
10-33
Represent Relationships (cont.)
10-34
Represent Relationships (cont.)
10-35
Represent Relationships
(cont.)
• Unary 1:N Relationships
– Relationship between instances of a single
entity type
– Utilize a recursive foreign key
– A foreign key in a relation that references the primary
key values of that same relation.
• Unary M:N Relationships
– Create a separate relation.
– Primary key of new relation is a composite of
two attributes that both take their values from
the same primary key.
10-36
EMPLOYEE(Emp_ID, Name, Birthdate, Manager_ID)
10-37
ITEM(Item_Number, Name, Cost)
ITEM-BILL(Item_Number, Component_Number, Quantity)
10-38
Merging Relations (View
Integration)
• Purpose is to remove redundant relations
• View Integration Problems
– Synonyms
– Two different names used for the same attribute
– When merging, get agreement from users on a single,
standard name
– Homonyms
– A single attribute name that is used for two or more different
attributes
– Resolved by creating a new name
– Dependencies between nonkeys
– Dependencies may be created as a result of view integration
– In order to resolve, the new relation must be normalized
10-39
Physical File and Database
Design
• The following information is required:
– Normalized relations, including volume
estimates
– Definitions of each attribute
– Descriptions of where and when data are
used, entered, retrieved, deleted, and
updated (including frequencies)
– Expectations or requirements for response
time and data integrity
– Descriptions of the technologies used for
implementing the files and database
10-40
Designing Fields
• Field
– Smallest unit of named application data recognized by system
software
– Attributes from relations will be represented as fields
• Data Type
– A coding scheme recognized by system software for representing
organizational data
• Choosing data types
– Four objectives
– Minimize storage space
– Represent all possible values of the field
– Improve data integrity of the field
– Support all data manipulations desired on the field
– Calculated fields
– A field that can be derived from other database fields
10-41
Methods of Controlling Data
Integrity
• Default Value
– A value a field will assume unless an explicit value is
entered for that field
• Range Control
– Limits range of values that can be entered into field
• Referential Integrity
– An integrity constraint specifying that the value (or
existence) of an attribute in one relation depends on
the value (or existence) of the same attribute in
another relation
• Null Value
– A special field value, distinct from 0, blank, or any
other value, that indicates that the value for the field
is missing or otherwise unknown
10-42
10-43
Designing Physical Tables
• Relational database is a set of related tables
• Physical Table
– A named set of rows and columns that specifies the
fields in each row of the table
• Design Goals
– Efficient use of secondary storage (disk space)
– Disks are divided into storage units that can be read in one
machine operation.
– Space is used most efficiently when the physical length of a
table row divides close to evenly with storage unit.
– Efficient data processing
– Data are most efficiently processed when stored next to
each other in secondary memory.
10-44
Denormalization
• The process of splitting or combining
normalized relations into physical tables based
on affinity of use of rows and fields
• Partitioning
– Capability to split a table into separate sections
• Optimizes certain operations at the expense of
others
10-45
When to Denormalize
• Three common situations where
denormalization may be used
1. Two entities with a one-to-one
relationship
2. A many-to-many relationship with
nonkey attributes
3.Reference data
10-46
10-47
10-48
10-49
Designing Physical Tables
• File Organization
– A technique for physically arranging the
records of a file
– Objectives for choosing file organization
– Fast data retrieval
– High performance for processing transactions
– Efficient use of storage space
– Protection from failures or data loss
– Minimizing need for reorganization
– Accommodating growth
– Security from unauthorized use
10-50
Sequential File Organization
A file organization in
which rows are stored
in a sequence
according to primary
key value
10-51
Indexed File Organization
A file organization in
which rows are stored
either sequentially or
nonsequentially and
an index is created
that allows software
to locate individual
rows
Index: A table used to
determine the
location of rows in a
file that satisfy some
condition
10-52
Hashed File Organization
A file organization in
which the address for
each row is
determined using an
algorithm
10-53
10-54
Thanks
