Normalisation

Normalisation

Before we start…. • The best database systems are carefully planned and designed by the mean of: • • • • • •

Analyse; Purpose of creating a new system Design Test planed Implemented Tested Documented

Normalisation • Normalisation is mainly linked in Design process • Information is taken from the analyse report • All database system requires an normalisation process • Once all the fields and tables have been identified though the normalisation process, A Relationship can be identified.

Define Normalisation • A process of breaking a single large flat table into several smaller tables • Creating new tables and identifying their relationship • Make database design more flexible to up date later • Eliminate redundancy • If data exists in more than one place or one table. Not in different tables.

• Eliminates repetition of data within a table • Repetition is represented in another linked table.

Normalisation Steps 1st Normal Form Eliminate any Repeating Groups 2nd Normal Form Splitting Tables 3rd Normal Form Eliminate Columns Not Dependent on Key Continue

1st Normal Form Eliminate any Repeating Groups • List all the fields in a single table. • Then separate into individually logical group/table and provide a primary Key in each group

2 Normal Form nd

Splitting Tables • If the field in a table is not fully dependant on the key, take it out and place it in a separate table. • The new table must have a reasonable name and a new primary key to define the table

3 Normal Form rd

Eliminate Columns Not Dependent on Key • If the field does not contribute to a description of the key, the field must be removed to a separate table. • Each field must be dependant on the key and independent of one another.

Primary Key • Primary key is defined or introduced to every new table. • The main purpose of this is to Identify a record uniquely. • Can not contain same data in the primary key field. • • • •

Books; ISBN Students; Student ID No. Customer; Cust_ID, Member_ID, etc Complaint; Complaint_ID, Enq_ID

• The field to which the primary key refers to in another table is called Foreign Key • The Primary Key and the foreign Key has to be same data type. (apart from auto number an Number)

What do I Mean???

Primary Key; Staff_id as Auto number

Staff_id in TblLog table is classified as number

Data Dictionary

Data Dictionary???? • All the tables Identified? • All the Fields within the table identified? • Well done……. • Now describe them……….give definition Table

Field name

Description

Field Type Field Size Any other

Example Customer Table to hold customerdetails

Must be identified when designing a database

order o Field

Field

Type

Cust_id f_name s_name add1 Town county tel_no dob

AutoNum Text Text Text Text Text Text date

Size

15 16 20 15 16 10 8

Description primary Key; Uniq no given to each customer Customer first name Customer surname First line of address town name of the county customer tel no customer date of birth

Hold information about the order placed by customer Type

Order No AutoNum Cust_id Number Total items Number Total cost Curency Total payed Curency outstanding Curency

Size

Description primary Key; Uniq no given to each order placed Customer Id No, no decimal total items orderd, no decimal Total cost of item orderd, 2 decimal Total payedof item orderd, 2 decimal Out standing payment on this order, 2 decimal, totalcost-total payed

What have we learnt today • Brief into Relationship (database) • Normalisation • Worked on example

• Primary Key • Data Dictionary

Design • • • • • • •

Data dictionary Normalisation of Tables/Fields ER Diagram Must Mustbe bedone doneas aspaper paperbase base Form Layout/Design totoget getaapicture pictureof ofhow howthe the actual actualsystem systemwill willlook looklike like Query Design Makes Makesthings thingseasier easierwhen when Report Layouts actually actuallyimplementing implementingthe the system Test Strategies system