Transcript Normalisation

And Franchise Colleges
• 11 NORMALISATION
By MANSHA NAWAZ
Section 11 : Normalisation
1
SAD Overview
• Systems development undertaken by using Case Tools
– used early in development life cycle
– Provide requirements and design in terms of Systems Model and Data Model
• Case Tools for Systems Modelling:
– Dataflow Diagrams (DFD)
– Data Dictionary (DD)
• Case Tools for Data Modelling:
– Normalisation (NF)
• A rules based technique undertaken to produce the data model for a system
– Entity Relationship Model (ER)
• A diagrammatic based technique undertaken to produce the data model for a
system
• Data Model
– Can be implemented on a DBMS such as MS SQL Server, MS Access, etc
– Consists of logical and physical view of the proposed systems data.
Section 11 : Normalisation
2
DFD
DDS
Normalised
Tables
E-R Model
Conceptual Model
DBMS
PHYSICAL VIEW OF DATA
LOGICAL VIEW OF DATA
TABLES
FORMS
Section 11 : Normalisation
3
DATAFLOW DIAGRAMS
DataStores, DataFlows and Data Dictionary
DATA MODEL
E-R Model
NF
TABLE SET
DBMS
PHYSICAL VIEW OF DATA
LOGICAL VIEW OF DATA
TABLES
FORMS
Database connectivity via websites
www .net technology
Macromedia Dreamweaver
MS Visual Studio
Database connectivity via
desktop DBMS such as
MS Access
Section 11 : Normalisation
Database connectivity via
programming languages
such as MS Visual Basic
Area of Interest
4
Systems Analysis and Design
– DFDs
• can provides the base for database development
• Used to derive our data model
– Datastores
• Decompose into a number of related tables
• provide the TABLE views for the database
• Physical View of data (Internal Schema)
– Datastores & Dataflows
• Decompose into a number of related tables
• provide the FORM views
• Logical View of data (External Schema)
– Process
• reports, queries, functions
& :procedures
Section 11
Normalisation
5
Data Model
– Also referred to as Conceptual Model
– A Data Model is the representation of a proposed systems database
requirements
– Data Model provides
• a full set of related tables and data
• Able to derive physical views (internal schema) of data
• Able to derive logical views (external schema) of data
– Data Model produced by Case Tools techniques of Normalisation
and/or Entity Relationship Modelling.
– Proposed processes that operate on the data and/or produce data
(process view)
– Making sure these tally (event/transaction view?)
Section 11 : Normalisation
6
• Logical View - for User
–
–
–
–
External Schema View
Form or Screen views provided to the end user.
Datastore & Dataflows represent as logical views in Data Dictionary
Contained in Data Dictionary - Structure and Element view
• Physical View - for Designers
–
–
–
–
–
–
Internal Schema View
database structure view that a DBMS requires.
in terms of Tables, Attributes (data fields) and Types (data defn.)
linkage between tables via primary and foreign keys
Datastore decomposed to a set of normalised tables
Normalised datastore represent physical view
• Normalisation
– takes the logical view and produces the physical view
– Take the datastore and produce the tables
Section 11 : Normalisation
7
Normalisation (NF).
– Provide us with a Bottom Up approach to producing the database
tables
– Technique covered in other modules
Entity Relationship Modelling (ERM).
– Covered in HSQ Databases & SQL Module
Section 11 : Normalisation
8
• Logical View - for User
– Form or Screen views provided to the end user.
– Datastore structure and element view
• Physical View - for Designers
– database structure view that a DBMS requires.
– in terms of Tables, Attributes (data fields) and Types (data
defn.)
– linkage between tables via primary and foreign keys
– Datastore decomposed to a set of normalised tables
• Normalisation takes the logical view and produces
the physical view
• Take the datastore and produce the tables
Section 11 : Normalisation
9
Normalisation Rules
• 0NF Zero Normal Form or Unnormalised data
– Data Dictionary Structure and Elements of a datastore
– List datastore data: identify key(s) and repeating group of data
– represents the logical form view of a datastore
• 1NF first Normal Form or first normalised data
– Remove repeating group(s) to new table(s)
• 2NF second Normal Form or second normalised data
– Remove partial key dependency data to new table(s)
• 3NF third Normal Form or third normalised data
– Remove non key dependency data to new table(s)
– represents the physical tables view of a datastore
Section 11 : Normalisation
10
Normalisation Rational
• The normalisation process also ensures
–
–
–
–
–
there must be no repeating groups of data in a table
all attributes in a table must be atomic
cannot be broken down into any smaller components
all primary keys must remain unique
every foreign key must have a matching primary key in its related table.
• Normalisation is a process that reduces errors due to
badly designed data structures (entities, attributes, and
relationships).
• Normalisation can be carried out at various levels of
complexity.
• You will need to understand the purpose of normalisation
and the methods used to normalise each datastore
Section 11 : Normalisation
11
Summary
•
•
•
•
Rules to assist in the creation of a DATA MODEL
A step by step technique which restructures the data of a
system into a more efficient and desirable form.
Takes logical datastore view to physical table view
Makes improvements in terms of :
NO DUPLICATION
 NO REDUNDANT
 NO NULL
 REDUCTION IN PHYSICAL SIZE
 QUICKER INFORMATION RETRIVAL
 LEADS TO A FULLY OPTIMISED SET OF TABLES

Section 11 : Normalisation
12
Document : Design Specification
– Data Dictionary
•
•
•
•
Data Stores - Structures & Elements
Data Flow - Structures & Elements
Data Stores and Flow Usage
Process Descriptions
– NORMALISATION
lecture 08
lecture 08
lecture 08
lecture 09
lecture 10
lecture 11
• Database Tables derived from Data Store Descriptions
– ONF - Logical View of Datastores
– 1NF
– 2NF
– 3NF - Physical View of Tables
Section 11 : Normalisation
13
Further Reading
Supplement Notes on
NORMALISATION
Why Normalisation
or ER Modelling?
www Online Tutorial
Normalisation
- Worked Example - Problems - Answers > Normalisation Video
Section 11 : Normalisation
14