The Need & Rationale Spreadsheet syndrome Goal: reduce - PowerPoint PPT Presentation

Introduction Good Relational Design Normalization The Need & Rationale Spreadsheet syndrome Goal: reduce redundancies and inconsistencies e ffi cient updates eliminate anomalies Normalization solves these problems Relational Database Design

Introduction Good Relational Design Normalization Normal Forms Criteria for safety against anomalies & inconsistencies Usually 3 to 7 normal forms ( 1NF → 7NF ) First 3: how non-key attributes relate to key 4th & 5th: many-to-one, many-to-many Must be satisfied progressively May combine several in one step Highest Normal Form ( HNF ) Applied to individual tables Relational Database Design

Introduction Good Relational Design Normalization First Normal Form A relation is a relation: no repetition in a tuple one (at least) unique key no nullable attribute (optional) Atomic attribute values For e.g., decompose multi-valued attributes Atomicity can be extreme: date , strings Relational Database Design

Introduction Good Relational Design Normalization Second Normal Form Conditions: Relation is in 1NF No non-prime attributes functionally depend on subset of PK Relational Database Design

Introduction Good Relational Design Normalization Second Normal Form Conditions: Relation is in 1NF No non-prime attributes functionally depend on subset of PK Not in 2NF: books ( book name , author name , review ) skills ( employee name , skill , address ) Relational Database Design

Introduction Good Relational Design Normalization Second Normal Form Conditions: Relation is in 1NF No non-prime attributes functionally depend on subset of PK Not in 2NF: books ( book name , author name , review ) skills ( employee name , skill , address ) Solution: separate into multiple relations Relational Database Design

Introduction Good Relational Design Normalization Second Normal Form Conditions: Relation is in 1NF No non-prime attributes functionally depend on subset of PK Not in 2NF: books ( book name , author name , review ) skills ( employee name , skill , address ) Solution: separate into multiple relations For e.g.: employee name , skill and employee name , address Both are in 2NF Relational Database Design

Introduction Good Relational Design Normalization Second Normal Form Conditions: Relation is in 1NF No non-prime attributes functionally depend on subset of PK Not in 2NF: books ( book name , author name , review ) skills ( employee name , skill , address ) Solution: separate into multiple relations For e.g.: employee name , skill and employee name , address Both are in 2NF single primary key ⇒ 2NF Relational Database Design

Introduction Good Relational Design Normalization Second Normal Form Conditions: Relation is in 1NF No non-prime attributes functionally depend on subset of PK Not in 2NF: books ( book name , author name , review ) skills ( employee name , skill , address ) Solution: separate into multiple relations For e.g.: employee name , skill and employee name , address Both are in 2NF single primary key ⇒ 2NF Can have anomalies in 2NF AuthorAwards ( award name , award year , author name , date of birth ) Relational Database Design

Introduction Good Relational Design Normalization Third Normal Form Conditions: Relation is in 2NF Every non-key is directly dependent on the key “every non-key attribute must provide a fact about the key, the whole key, and nothing but the key.” Relational Database Design

Introduction Good Relational Design Normalization Third Normal Form Conditions: Relation is in 2NF Every non-key is directly dependent on the key “every non-key attribute must provide a fact about the key, the whole key, and nothing but the key.” AuthorAwards ( award name , award year , author name , date of birth ) Relational Database Design

Introduction Good Relational Design Normalization Third Normal Form Conditions: Relation is in 2NF Every non-key is directly dependent on the key “every non-key attribute must provide a fact about the key, the whole key, and nothing but the key.” AuthorAwards ( award name , award year , author name , date of birth ) date of birth intransitively dependent on key ( author name , award year ) → author name → date of birth Solution: separate into multiple relations AuthorAwards ( award name , award year , author name ) & Author ( author name , date of birth ) Other examples: Employees ( Empl id , Empl name , Dept name , Dept floor ) : Relational Database Design

Introduction Good Relational Design Normalization Third Normal Form Conditions: Relation is in 2NF Every non-key is directly dependent on the key “every non-key attribute must provide a fact about the key, the whole key, and nothing but the key.” AuthorAwards ( award name , award year , author name , date of birth ) date of birth intransitively dependent on key ( author name , award year ) → author name → date of birth Solution: separate into multiple relations AuthorAwards ( award name , award year , author name ) & Author ( author name , date of birth ) Other examples: Employees ( Empl id , Empl name , Dept name , Dept floor ) : Employees ( Empl id , Empl name , Dept id ) and Department ( Dept id , Dept name , Dept floor ) 3NF: free of update, insertion, and deletion anomalies Relational Database Design

Introduction Good Relational Design Normalization Fourth Normal Form Only many-to-one and many-to-many Relational Database Design

The Need & Rationale Spreadsheet syndrome Goal: reduce - PowerPoint PPT Presentation

Introduction Good Relational Design Normalization The Need & Rationale Spreadsheet syndrome Goal: reduce redundancies and inconsistencies e ffi cient updates eliminate anomalies Normalization solves these problems Relational Database

RATIONALE RATIONALE Prevalence of diabetes in Malaysia has increased from 8.3% (NHMS

a Classroom Setting Teaching Functional Skills Rationale Toileting Rationale Competent

Agricultural Integrated Survey (AGRIS) Rationale and Methodology Technical Session 2 Mohamed

Rationale The HIV self-test development process requires to take into account the needs and

22t0712015 Rationale for change - DSD perspective .Evaluation of current strands, .Planned new

Listing presentation September 2017 AGENDA Purpose Rationale for the unbundling and

Acquisition of GT Energy Creating a diversified energy group The Transaction Rationale and

RDR Organizational Self Assessment A Tool For Change PREPARED FOR Agenda The Rationale

29/7/19 The rationale: why do this? Writing a PhD proposal o Needs to be a contribution to

RT-GCG Cost Recovery Framework Status Update to TP September 13, 2016 Agenda 1. Rationale for

Decomposing the Rationale of Code Commits: The Software Developers Perspective Khadijah Al

Track European Public Management GOVERN- MENT Source: Porter, Michael E. (1990). The competitive

Enpr-EMA PAEDIATRIC ANTIBIOTIC WORKING GROUP Rationale and outlook Laura Folgori Clinical

Enpr-EMA PAEDIATRIC ANTIBIOTIC WORKING GROUP Rationale and outlook Laura Folgori Clinical

Objectives Rationale for IV-tPA in acute ischemic stroke The expanding role for IV-tPA in

Intensive Care Unit Rationale - Problem Failure rates at the end of the fall 2013 semester:

An In Introduction The project concept and objectives Introduction Rationale Goals and

November 14, 2018 Agenda Review rationale for revision Overview of major changes Public comment

Numerical Tools at CERN Rationale of a choice Presented by L.Bruno Experimental Areas, Targets

Understanding epigenetics: The potential rationale for BET inhibition in management of CVD

HPS, A New Microarchitecture: Rationale and Introduction Yale N. Patt, Wen-mei Hwo, and Michael

BNP Paribas Fortis Industrial Plan December 1 st , 2009, Brussels Strategic Rationale and

Rationale Studies Second International Science Study Third International Mathematics and

Specialty Treatment for Athletes with Eating Disorders Now that we have the rationale, what can