11 NORMALISATION

1. And Franchise Colleges • 11 NORMALISATION • By MANSHA NAWAZ Section 11 : Normalisation

2. 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

3. DFD DDS Normalised Tables E-R Model DBMS LOGICAL VIEW OF DATA FORMS PHYSICAL VIEW OF DATA TABLES Section 11 : Normalisation

4. DATAFLOW DIAGRAMS DataStores, DataFlows and Data Dictionary DATA MODEL TABLE SET NF E-R Model DBMS PHYSICAL VIEW OF DATA TABLES LOGICAL VIEW OF DATA FORMS Database connectivity via websites www .net technology Macromedia Dreamweaver MS Visual Studio Database connectivity via desktop DBMS such as MS Access Database connectivity via programming languages such as MS Visual Basic Area of Interest Section 11 : Normalisation

5. 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

6. 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 EntityRelationship Modelling. • Proposed processes that operate on the data and/or produce data (process view) • Making sure these tally (event/transaction view?) Section 11 : Normalisation

7. 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

8. 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

9. 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

10. 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

11. 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

12. 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

13. Document : Design Specification • Data Dictionary lecture 08 • Data Stores - Structures & Elements lecture 08 • Data Flow - Structures & Elements lecture 08 • Data Stores and Flow Usage lecture 09 • Process Descriptions lecture 10 • NORMALISATION lecture 11 • Database Tables derived from Data Store Descriptions • ONF - Logical View of Datastores • 1NF • 2NF • 3NF - Physical View of Tables Section 11 : Normalisation

14. Further Reading Supplement Notes on Why Normalisation or ER Modelling? www Online Tutorial Normalisation Section 11 : Normalisation