Database Design

1
Database Design
2
Database Design

• The process of developing database structures
  from user requirements for data
• a structured methodology
• Structured Methodology - a number of ordered
  formal
• processes with known inputs and expected outputs
• Objectives
• 1. derive relationships
• 2. evolve to meet user requirements
• 3. Do it right the first time!

3
Database Design Goals

• Reduce data redundancy.
• Provide stable data structures that can be
  readily changed with changing user requirements.
• Allow users to make ad hoc requests for data.
• Maintain complex relationships between data
  elements.
• Support a large variety of decision needs

4
Database Design

• data availability
• data reliability
• data currency
• data consistency
• data flexibility
• data efficiency
• Verify these criteria are satisfied via technical
  review

5
Logical Data Modeling

• 3 types of data objects
• Entities
• Attributes
• Relationships
• ENTITIES Are persons, places, or things about
  which data is to be, or is, gathered
• ATTRIBUTES Are the properties of
  entities.Examples are Names, Tax Numbers, Age,
  Status
• RELATIONSHIPS Describe how entities relate to
  each other eg Customers BUY Products
• Persons WORK_ON Jobs

6
Entities...

• Are persons, objects or events about which
  information is, or will be, recorded in the
  Database
• The designation of a thing about which data is
  to be collected, stored or processed.
• Many of these Entities can be identified with
  Business Activities (e.g. suppliers, purchase
  orders, customer)

7
3 Steps in Design
Designing a database involves choosing 1. The
tables that belong in the database. What are the
entities? 2. The columns that belong in each
table. What are the properties? 3. How tables
and columns interact with each other. What do
they have in common?
8
Database Design
Functional Requirements
Other matters Domains, Nulls, Derived
Data, Encoded Data. Data Base Model
Information Level Design Processes
Final Information Level Design
Physical Constraints
Physical Level Design
Final Database Structure
9
Logical Data Modeling

• 2 entities can be related as
• One to One (1 1)
• The interpretation of this is that an occurrence
  of an entity A can relate to ONE and ONLY ONE
  occurrence of entity B, and an occurrence of
  entity B can relate to ONE and ONLY ONE
  occurrence of entity A
• One to Many (1 N)
• The interpretation is that ONE occurrence of
  entity A can relate to ONE or MORE occurrences
  of entity B, but an occurrence of entity B
  can relate to ONE ONLY occurrence of entity A

10
Logical Data Modeling

• Many to Many (N N) - a common business
  relationship
• The interpretation is that ONE or MORE
  occurrences of entity A can relate to ONE or
  MORE occurrences of entity B and ONE or MORE
  occurrences of entity B can relate to ONE or
  MORE occurrences of entity A

11
Entity Relationships

• Type Shown As Example
  
• One to One 11 Book
  -----gt Title
• One to Many 1N Publisher
  ----gtBooks
• Many to Many NN Books
  lt---gtAuthors

12
Normalization

• Normalization is the process of organizing data
  in a database. This includes creating tables and
  establishing relationships between those tables
  according to rules designed both to protect the
  data and to make the database more flexible by
  eliminating two factors redundancy and
  inconsistent dependency.

13
Unnormalized Table

• Student Advisor Adv-Room Class1 Class2
  Class3
• -------------------------------------------
  ------------
• 1022 Jones 412 101-07
  143-01 159-02
• 4123 Smith 216 201-01
  211-02 214-01

14
First Normal Form

• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related
  data.
• Identify each set of related data with a primary
  key.

15
No Repeating Groups

• Student Advisor Adv-Room Class
• -----------------------------------
  ----
• 1022 Jones 412
  101-07
• 1022 Jones 412
  143-01
• 1022 Jones 412
  159-02
• 4123 Smith 216
  201-01
• 4123 Smith 216
  211-02
• 4123 Smith 216
  214-01

16
Second Normal Form

• Create separate tables for sets of values that
  apply to multiple records.
• Relate these tables with a foreign key.

17
Eliminate Redundant Data

• The following two tables demonstrate second
  normal form
• Students Student Advisor
  Adv-Room
• -------------------------
  -----
• 1022 Jones
  412
• 4123 Smith
  216
• Registration Student Class
• ------------------
• 1022 101-07
• 1022 143-01
• 1022 159-02
• 4123 201-01
• 4123 211-02
• 4123 214-01

18
Third Normal Form

• Eliminate fields that do not depend on the key.

19
Eliminate data not dependant on key

• Students Student Advisor
• -------------------
• 1022 Jones
• 4123 Smith
• Faculty Name Room Dept
• --------------------
• Jones 412 42
• Smith 216 42

20
End Product of Data Base Design

• A database which will
• Accurately reflect the real world data in all
  
• required aspects
• Be responsive to Management information demands
• Reflect Business Rules and Controls
• Be capable of modification to meet changes
• in Management needs
• Be an asset to the Organization/Enterprise