Transcript Database Systems: Design, Implementation, and Management

1
Database Systems: Design,
Implementation, and Management
CHAPTER 6
Database Design
The Systems Development Life Cycle



The Systems Development Life Cycle (SDLC) provides a
methodology for developing an IS.
Database design takes place within the confines of an IS.
Five phases of SDLC:
 Planning
 Analysis
 Design
 Implementation
 Maintenance

SDLC is an iterative process
2
Systems Development Life Cycle
Figure 6.2
3
Database Lifecycle (DBLC)
4
Figure 6.3
This is also an iterative process like SDLC
Database Design

Divided into four tasks
 Conceptual
design
 DBMS software selection (if required)
 Logical design
 Physical design



Conceptual design is independent of software and hardware
Logical design is DBMS (software) dependent
Physical design is dependent on both software and hardware
5
Conceptual Design


The goal is to capture and model user requirements
Four Steps:
 Data
analysis and requirements
 Entity relationship modeling and normalization
 Data model verification
 Distributed database design
6
Conceptual Design

Data analysis and requirements
 The
focus is on identifying user requirements
 This can be gathered through various mean



observing and analyzing the current system
user interviews
questionnaire surveys
 Capture

User data views describe the data used by the user


and document user data views and business rules.
Example
Business rules describe policies and procedures followed by the company

Example: (EZS)
 An item may be procured from many vendors
 Purchase price of an item is negotiated with each supplier.
7
Conceptual Design

ER Modeling and Normalization
 User







requirements are modeled using E-R diagrams
Identify main entities based on user requirements data
Define relationships between the entities
Define attributes, primary keys, and foreign keys for each of the entities.
Normalize the entities.
Complete the initial E-R diagram.
Verify the E-R model against the data, information, and processing
requirements.
Modify the E-R diagram, if necessary
 Documentation
process must be standardized to avoid
miscommunication
8
Conceptual Design

Data model verification
 Ensure
that user data views can be supported by the data model
 All business transactions (select, insert, update, delete, user
queries) can be supported by the model

Distributed database design
 Data
requirements and processing requirements may vary from
one location to another
 Decision may be made about allocating data to different
locations
9
DBMS Selection


10
This step is required only if you plan to acquire a new DBMS
Common factors affecting the decision:
 Cost
-- Purchase, maintenance, operational, license, installation,
training, and conversion costs.
 DBMS features and tools.
 Underlying model.
 Portability -- Platforms, systems, and languages.
 DBMS hardware requirements.
Logical Design




Logical design translates the conceptual design into the
internal model for a selected DBMS.
It includes the design of tables, indexes, views, transactions
Access authorities (who can access what) are also decided.
The ER model is translated into relational schema
11
Logical Design

12
Translating ER Model into Relational Schema
 After
normalizing the E-R diagram we are left with only two
types of relationships


One-to-one
One-to-Many
 For
every one-to-one relationship, reexamine the possibility of
merging the two entities into a single entity by combining their
attributes.
 Entities participating in a one-to-one relationship are linked
through a foreign key.
 Supertype-subtype relationships are usually implemented as
one-to-one relationships. Both entities share a common primary
key, which also becomes a foreign key in the subtype entity.
13
Logical Design
1
Employee
1
(0,1)
(1,1)
Employee
Driver
1
Emp_Id
Emp_Name
Emp_Salary
Driver
May be a
Primary and Foreign
Key
1
Emp_Id
License Nbr
Lic Exprn. Date
Example of translating a 1:1 relationship into a relational schema
14
Logical Design

Translating ER Model into Relational Schema
 One-to-many
relationships are implemented by adding the
primary key of the first entity as the foreign key of the second
(many side) entity.
Example:
1
M
Professor
Class
teaches
(0,N)
(1,1)
Professor
Class
1
Prof_Id
Prof_Lname
Prof_Phone
M
Class_Code
Class_Section
Class_Days
Class_Time
Prof_Id
Foreign Key
Example - Logical Design
15
PROF_ID
Is a valid professor identification number.
Type: numeric
Range: low value = 1,000
high value =2,000
Display format: 9999
Length: 4
PROF_LNAME
Is a valid professor last name.
Type: character
Display format: XXXXXXXXXXXXXXX
Length: 15
PROF_PHONE
Is a valid phone number.
Type: character
Display format: 999-999-9999
Length: 12
CLASS_CODE
Is a valid class code.
Type: numeric
Range: low value = 1,000
Display format: 9999
Length: 4
high value =1,999
Example - Logical Design
CLASS_SECTION
Is a valid is a valid class section number.
Type: numeric
Range: low value = 10
high value = 99
Display format: 99
Length: 2
CLASS_DAYS
Is a valid day code.
Type: character
Valid entries: MWF, TTh, M, T, W, Th, F
Display format: XXX
Length: 3
CLASS_TIME
Is a valid time.
Type: character
Display format: 99:99 (24-hour clock)
Display range: 00:01 to 24:00
Length: 5
16
Physical Design





17
Select data storage and data access characteristics (indexes) of
the database.
It affects location of the data in the storage device(s) and
system performance.
Physical design is more complex with distributed databases.
Relational databases are more insulated from physical layer
details than hierarchical and network models.
Chapters 7 and 8 describe an excellent case study of
database design
Physical Organization
18
Figure 6.12
19
DB Design Strategy Notes

Top-down
 1)
Identify data sets
 2) Define data elements

Bottom-up
 1)
Identify data elements
 2) Group them into data sets
Figure 6.14