Normal forms in dbms ppt

all normal forms in dbms with examples pdf and normal forms in theory of computation ppt
Prof.EvanBaros Profile Pic
Prof.EvanBaros,United Kingdom,Teacher
Published Date:26-07-2017
Your Website URL(Optional)
Comment
Lecture 5 Sec/on 1 1. Normal forms & funconal dependencies 4 Lecture 5 Sec/on 1 What you will learn about in this secon 1.  Overview of design theory & normal forms 2.  Data anomalies & constraints 3.  Funconal dependencies 4.  ACTIVITY: Finding FDs 5 Lecture 5 Sec/on 1 Overview Design Theory • Design theory is about how to represent your data to avoid anomalies. • It is a mostly mechanical process •  Tools can carry out rou=ne por=ons • We have a notebook implemen0ng all algorithms •  We’ll play with it in the ac0vi0es Lecture 5 Sec/on 1 Overview Normal Forms st • 1 Normal Form (1NF) = All tables are flat nd • 2 Normal Form = disused DB designs based on • Boyce-Codd Normal Form (BCNF) func0onal Our focus in dependencies, this lecture intended to prevent + next one rd • 3 Normal Form (3NF) data anomalies th th • 4 and 5 Normal Forms = see text books Lecture 5 Sec/on 1 Overview st 1 Normal Form (1NF) Student Courses Student Courses Mary CS145 Mary CS145,CS229 Mary CS229 Joe CS145,CS106 Joe CS145 … … Joe CS106 st Violates 1NF. In 1 NF 1NF Constraint: Types must be atomic Lecture 5 Sec/on 1 Data anomalies & constraints Data Anomalies & Constraints Lecture 5 Sec/on 1 Data anomalies & constraints Constraints Prevent (some) Anomalies in the Data A poorly designed database causes anomalies: Student Course Room Mary CS145 B01 If every course is in Joe CS145 B01 only one room, contains redundant Sam CS145 B01 informaon .. .. .. Lecture 5 Sec/on 1 Data anomalies & constraints Constraints Prevent (some) Anomalies in the Data A poorly designed database causes anomalies: Student Course Room If we update the Mary CS145 B01 room number for one tuple, we get Joe CS145 C12 inconsistent data = Sam CS145 B01 an update anomaly .. .. .. Lecture 5 Sec/on 1 Data anomalies & constraints Constraints Prevent (some) Anomalies in the Data A poorly designed database causes anomalies: Student Course Room .. .. .. If everyone drops the class, we lose what room the class is in = a delete anomaly Lecture 5 Sec/on 1 Data anomalies & constraints Constraints Prevent (some) Anomalies in the Data A poorly designed database causes anomalies: Student Course Room Similarly, we can’t Mary CS145 B01 reserve a room Joe CS145 B01 without students Sam CS145 B01 = an insert anomaly … CS229 C12 .. .. .. Lecture 5 Sec/on 1 Data anomalies & constraints Constraints Prevent (some) Anomalies in the Data Is this form beXer? Student Course Course Room Mary CS145 •  Redundancy? CS145 B01 •  Update anomaly? Joe CS145 •  Delete anomaly? CS229 C12 Sam CS145 •  Insert anomaly? .. .. Today: develop theory to understand why this design may be beXer and how to find this decomposi/on… Lecture 5 Sec/on 1 Func/onal dependencies Funconal Dependencies Lecture 5 Sec/on 1 Func/onal dependencies Funconal Dependency Def: Let A,B be sets of aXributes We write A à B or say A func/onally determines B if, for any tuples t and t : 1 2 t A = t A implies t B = t B 1 2 1 2 and we call A à B a funconal dependency A-B means that “whenever two tuples agree on A then they agree on B.” Lecture 5 Sec/on 1 Func/onal dependencies A Picture Of FDs Defn (again): Given aXribute sets A=A ,…,A and 1 m B = B ,…B in R, 1 n A … A B … B 1 m 1 n Lecture 5 Sec/on 1 Func/onal dependencies A Picture Of FDs Defn (again): Given aXribute sets A=A ,…,A and 1 m B = B ,…B in R, 1 n A … A B … B 1 m 1 n The func/onal dependency Aà B on t i R holds if for any t ,t in R: i j t j Lecture 5 Sec/on 1 Func/onal dependencies A Picture Of FDs Defn (again): Given aXribute sets A=A ,…,A and 1 m B = B ,…B in R, 1 n A … A B … B 1 m 1 n The func/onal dependency Aà B on t i R holds if for any t ,t in R: i j t j tA = tA AND tA =tA AND … i 1 j 1 i 2 j 2 AND tA = tA i m j m If t1,t2 agree here.. Lecture 5 Sec/on 1 Func/onal dependencies A Picture Of FDs Defn (again): Given aXribute sets A=A ,…,A and 1 m B = B ,…B in R, 1 n A … A B … B 1 m 1 n The func/onal dependency Aà B on t i R holds if for any t ,t in R: i j t j if t A = t A AND t A =t A AND i 1 j 1 i 2 j 2 … AND t A = t A i m j m If t1,t2 agree here.. …they also agree here then t B = t B AND t B =t B i 1 j 1 i 2 j 2 AND … AND t B = t B i n j nLecture 5 Sec/on 1 Func/onal dependencies FDs for Relaonal Schema Design • High-level idea: why do we care about FDs? 1.  Start with some rela=onal schema 2.  Find out its func0onal dependencies (FDs) 3.  Use these to design a beHer schema 1.  One which minimizes the possibility of anomalies Lecture 5 Sec/on 1 Funconal Dependencies as Constraints Student Course Room A funcBonal dependency is a form Mary CS145 B01 of constraint Joe CS145 B01 •  Holds on some instances not Sam CS145 B01 others. .. .. .. •  Part of the schema, helps define a Note: The FD Course - valid instance. Room holds on this instance Recall: an instance of a schema is a mul/set of tuples conforming to that schema, i.e. a table Lecture 5 Sec/on 1 Funconal Dependencies as Constraints Note that: Student Course Room •  You can check if an FD is violated by examining a single Mary CS145 B01 instance; Joe CS145 B01 Sam CS145 B01 •  However, you cannot prove that an FD is part of the .. .. .. schema by examining a single instance. However, cannot prove •  This would require checking that the FD Course - every valid instance Room is part of the schema

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.