Relational Database Model Database Design

1
ITC114
Database Design
2
Objectives

• The objectives for this weeks lecture are
• Discuss the general process and goals of database
  design.
• Define and explain the user views.
• Define a Database Lifecycle

3
Database Design

• Question
• What is the relationship between
• Database?
• Information System?

4
Information System

• provides the tools and conditions to manage both
  the data and information.
• an IS is made up of people, H/W, S/W, database,
  application programs and procedures.

5
Database

• Data repository that serves an IS.
• A database must be designed properly if an IS is
  to be successful.

6
Systems Development Life Cycle (SDLC)

• Database design occurs within the SDLC
• initial assessment
• feasibility study
• user requirements
• logical system design
• detailed system specification
• coding, testing and debugging
• installation and fine tuning.
• evaluation, maintenance, enhancement

Planning
Analysis
Systems Design
Implement-ation
Maintenance
7
Database LifeCycle one representation

• DBLC SDLC

Initial Study
Analysis
Database Design
Detailed Design
Implemntn loading
Coding
Testing Evaluation
Testing Evaluation
Operation
Database maintenance and evaluation
Application program maintenance
8
1. Database Initial Study

• analyse the companys situation.
• define problems and constraints.
• define objectives.
• define scope and boundaries.

9
Database Design Process

• The steps involved in the database design
  include
• Conceptual Design
• ERDs
• Normalisation - data model verification
• DBMS software selection
• Logical Design
• Physical Design

10
Conceptual Model

• S/W and H/W INDEPENDENT view of the requirements
  for the database.
• It shows what is required in the database.
• Information can be gathered from
• end-user data views
• observing the current system - inputs and outputs
• from others in the systems design group.

11
So what is a crux of a database design?

• The user .
• In particular ..
• WHAT THE USER REQUIRES FOR THE SYSTEM
• User view
• A set of requirements that is necessary to
  support the operations of a particular user p.
  170. 4th edition

12
User views ..

• A user is person or group of people who will use
  the system or part of it.
• A user view is a set of requirements that are
  necessary to support the operations of a
  particular user
• Many user views of the database can therefore
  exist.
• A cumulative design for the database design will
  result from the outcome of each of the user views
  developed.
• Dont try and design the database in one hit .
  do it in user view stages.

13
What are the steps to be carried out for each
user view?

• Represent the user view as a collection of tables
  (ideally this can be done using ERD).
• Identify all keys (particularly primary and
  foreign keys).
• Normalise these tables (up to 3NF)
• Merge the results of the previous steps back into
  the ERD and update the table as appropriate.

14
Graphical representation
15
Suggested steps for drawing ERDs

• refer to user views.
• do a draft ERD.
• verify with the users.
• repeat last two steps.
• define relationships, keys, attributes.
• normalise - create new entities where required.

16
Another way to illustrate entity relationship
diagrams
17
A more formal means to represent the information.
18
Example of combining user views
19
More on combining user views
20
Completing the user view

• To the previous user view, add the following
  entity
• Orders
• The userview for this one requires
• for an order store the order number, the order
  date, the number, name and address of the
  customer that placed the order, and the number
  and the sales rep who represents the customer
• Each item within the order must store the part
  number and description, the number of the part
  that was ordered and the quoted price

21
The resulting ERD is as follows
22
Normalising the data

• Identify all the data and represent in a
  relation
• Identify the keys of the repeating data items
• Customer -gt customer number
• Part -gt part number
• Order -gt order number
• Sales representative -gt sales rep number
• 3. Add these keys in part of the relation in
  point one ? result ? data is in 1NF

23
What data is identified?
Orders ( Order Number, Order Date, Customer
Number, Last Name, First Name, Street, City,
State, Zip Code, Sales Rep Number, Part Number,
Part Description, Number Ordered, Quoted
Price) The above table has been put in 1NF Now
put it into 3NF
24
Here is the 3NF
25

• There is another example supplied in your
  textbook please ensure that you complete it at
  your leisure and seek help as required p186 -
  190.

26
Is this all we have to do?

• THE ABOVE PROCESS ONLY DETERMINES THE DATA
  REQUIREMENTS ..
• WE NEED TO CONSIDER ADDITIONAL OTHER EQUALLY
  IMPORTANT FACTORS
• DBMS software selection
• Logical Design
• Physical Design

27
2. Selection of DBMS software

• Choice of DBMS software - what are the issues?
• costs.
• choice of models.
• DBMS features and tools.
• hardware requirements.

28
3. Logical Design

• Translation of the conceptual model into
  definitions specific to a particular DBMS (this
  is S/W dependent referred to as an internal
  model).
• Converting conceptual model to internal model.
• Need to consider tables, views, transactions
• Consider the domain values (that is the data
  restrictions)
• Address access rights and views.

29
4. Physical Design

• Involves
• a. selecting the data storage (function of H/W)
• b. performance characteristics (function of the
  system DBMS).
• Relational DBMS - issues such as seek time,
  sector and block size, read/write heads etc. need
  to be considered - all are access issues
  affecting performance.
• data volumes need to considered - why?
• distributed DBMS makes the issue more complex.

30
4. Physical Design

• Before physical design - Need to evaluate the
  conceptual design
• Will the design satisfy identified performance
  requirements?
• If not then consideration needs to be made to
  improve this performance.
• So how is the performance of the database
  determined?

31
Physical Design

• To determine if performance requirements can be
  met, need to
• 1. determine size of an average transaction,
  then
• 2. calculate transaction response time by
  determining
• PROCESSING time plus
• DATA COMMUNICATIONS time plus
• DISK ACCESSING time.

32
Physical Design

• If calculated transaction response time exceeds
  desired response time - need to consider actions
  to resolve problem
• use faster data communication rate
• optimize disk access
• re-evaluate database design (denormalize)
• relax performance requirement
• higher speed disk drives

33

• 3. Implementation and Loading
• - steps to be considered
• 1. creation of database storage group.
• 2. create the database within the storage space.
• 3. assign the rights to use the database.
• 4. create the table space within the database.
• 5. create the tables within the table space.
• 6. access controls for tables and/or views.
• 7. data conversion.
• What are some of the other issues with
  implementation?

34
Other issues ..

• a. Security
• physical security
• P/W security
• access rights
• audit trails
• data encryption
• diskless workstations
• b. Backup and Recovery
• c. Concurrency controls - how to allow
  simultaneous access to data.

35
The Database Life Cycle

• 4. Testing and Evaluation
• occurs in parallel with application programming
• assessing the systems evaluation criteria
• 5. Operation
• in theory it is now operational.
• 6. Maintenance and Evolution - main ones
• protective maintenance
• corrective maintenance.

36
Strategies for Database Design

• a. Top-down design
• Begins with a general database design that
  models the overall enterprise and refines the
  model until a design is achieved.
• b. Bottom-up design
• Starting from the specific user requirements to
  ultimately synthesize the design.

37
Top-down approach

• Review data gathered on all views without
  attempting to create any relations.
• Determine the basic entities of interest.
• Start a table for each entity.
• Determine and fill in a primary key for each
  table
• Add foreign keys as necessary

38
Other Issues that can affect the design process?

• 1. Centralised design issues
• productive when data components are simple?
• 2. Decentralised design issues
• typical with more complex systems.
• the problem is spread across a number of team
  members.
• The above are only suggestions - other factors
  need to be considered .......

39
Database Life Cycle - Summary

• DBLC - the history of the database procedures.
  It is composed of six iterative phases
• Initial study,
• Database design,
• Implementation loading,
• Testing evaluation,
• Operation,
• Maintenance evolution
• Good database design and implementation are a
  critical component to a successful project.