04 CHAPTER FOUR - INTEGRITY CONSTRAINTS AND NORMALIZATION.pptx

database beginners slide

1. MICROLINK IT COLLEGE DEPARTMENT OF COMPUTER SCIENCE Fundamental Database Systems Compiled By: G/SLASSIE E. 1

2. CHAPTER FOUR INTEGRITY CONSTRAINTS,FUNCTIONAL DEPENDENCY AND NORMALIZATION 2

3. 3 CHAPTER FOUR: INTEGRITY CONSTRAINTS AND NORMALIZATION Relational Integrity Constraints Domain Integrity Constraint Entity Integrity Constraint Referential Integrity Constraint Normalization of Relational Database Tables Modification Anomalies (Insertion, Update, Delete ) Normalization Steps First Normal Form (1NF) Second Normal Form ( 2NF) Third Normal Form ( 3NF) BCNF (Boyce-Code Normal Form )

4. Cont. Relational Integrity Constraints The various types of constraints that are placed on a database contribute to the degree of reliability , consistency and accuracy of the data that is stored in the database. Data integrity constraints refer to the accuracy and correctness of data in the database. Data integrity provides a mechanism to maintain data consistency for operations like INSERT , UPDATE , and DELETE . The different types of data integrity constraints are Domain , Entity and Referential Integrity …etc. 4

5. Cont. Domain Integrity Constraint Domain Integrity - No value of the attribute should be beyond the allowable limits . Domain refers to the set of all possible values that attribute/column can take. The domain specifies its own Name , Data Type , and Logical Size . Note : The domain integrity constraint specifies that each attribute must have values derived from a valid range . Example : The age of the person cannot have any letter from the alphabet. The age should be a numerical value. And the sex of the applicant should be either male (M) or female (F) . Any entry other than M or F violates the domain constraint . 5

6. Cont. Entity Integrity Constraint Entity integrity implies that a primary key cannot accept null value . The primary key of the relation uniquely identifies a row in a relation, it means no data redundancy . In a base relation, no attribute of a Primary Key can assume a value of NULL. Entity Integrity - No primary key attribute may be null . All primary key fields MUST have data . Hence , a key attribute value that was null would be a contradiction. E.g.: ID is the key attribute of Student entity. 6

7. Cont. Referential Integrity Constraint The referential integrity states that a database must not contain any unmatched foreign key values with primary key. In other words, a referential integrity is each foreign key value must match primary key value or the foreign key value can be Null . In the relational data model, associations between tables are defined through the use of foreign keys . Enterprise Integrity : Additional rules specified by the users or database administrators of a database are incorporated. E.g . A Restriction on a number of workers in a given branch office (branch as a relation) 7

8. Cont. Example: which of the rules are violated and how? 8 SID (Text) PK Name (Text) Year (Number) DID (Text) FK 1214 Abebe 2 D01 1215 Kebede One D02 1214 Rahel 2 Null NULL Seble Two D04 DID (Text) PK DName (Text) DLoc (Text) DNo (Text) D01 CS MK 1 D02 CS AA Two Null Mgt. MK 2 Null Acc. AA Three

9. Cont. Entity Integrity : Null value for key attribute is assigned so this rule is violated. (Null Value for “SID”) and (Null Value for “DID ”) Domain Integrity : if the data type for “Year” is integer the rule is violated. (if it is text, the rule is not violated). Referential Integrity : this rule is violated b/c the student “DID” does not match with department “DID”. 9

10. Cont. Why Normalize? Normalization intended to Eliminate redundancy Organize data efficiently Reduce the potential for anomalies What is Normalization? Decompose a relation into a set of smaller relations A relation is in a specific Normal Form (NF) if it Satisfies requirements of all previous NFs Satisfies requirements of the current NF Data/Database normalization is a series of steps followed to obtain a database design that allows for consistent storage and efficient access of data in a relational database. 10

11. Cont. Formal definition NORMALIZATION is the process of identifying the logical associations between data items and designing a database that will represent such associations but without suffering the modification anomalies which are; Insertion anomalies, Deletion anomalies and Update anomalies. Normalization may reduce system performance since data will be cross referenced from many tables . Thus De-normalization is sometimes used to improve performance , at the cost of reduced consistency guarantees. All the normalization rules will eventually remove the modification anomalies that may exist during data manipulation after the implementation 11

12. Cont. De-normalization Doesn't always make sense for data to be normalized Some applications work better with de-normalized data Usually those that rely on lots of read only operations Reading Assignment : The why, when and how of de-normalization 12

13. Cont. Modification Anomalies Normalization is technique to eliminate unwanted redundancies in relational tables. Redundancy can cause Insert , Update , and Delete Operations to produce unexpected side effects known as Modification Anomalies . Modification Anomalies : a good database design avoids modification anomalies by eliminating redundancies or that user can change the content of a database without unexpected side effects. Insertion Anomaly : this occurs when a new extra data is to insert to database. Delete Anomaly : this occurs when deleting which causes other data to be deleted. Update Anomaly : this occurs when a data is to be modified. 13

14. Cont. E.g .: Let’s see a poorly design database. Insertion Anomaly : to insert a new course, it is necessary to know student SID and OfferNo , b/c both are PK of this table and it is not also possible to the part of the PK to contain Null Value. Delete Anomaly : if we delete a student from enrollment O3 ( offerNo ), which is S2 (SID), We lose data of C3 (Courses). Update Anomaly : if we change the Admission of S1 from Regular to Extension, two rows are changed, b/c S1 enrolls in two Admission. 14 SID(PK) City Admission OfferNo (PK) Courses OfferYear S1 Mekelle Regular O1 C1 2011 S1 Mekelle Regular O2 C2 2011 S2 Mekelle Regular O3 C3 2011 S2 Mekelle Regular O2 C4 2011

15. Cont. After converting the ER-Diagram to Relational you need to normalize the table. Normal Forms is a property of a relational database. When a relation is Non-Normalized (that is, does not satisfy a normal form ), then it presents redundancies and produces undesirable behavior during Insertion , update and Delete operations. Normalization is a procedure that allows the non-normalized schemas to be transformed into new schemas for which the satisfaction of a normal form is guaranteed. In other words, it reduces data redundancies and helps eliminate the data anomalies . 15

16. Cont. Goals of Normalization Eliminate Redundancies Caused Avoid Anomalies in Insertion, update, and Delete Represent accurately the items being modeled Simplify maintenance and Recovery of information To make it possible to represent any relation in the database Benefits of Normalization Facilitates data integration Reduce data redundancy Provides a good architecture for retrieving and maintaining data Reduce the chances of data anomalies occurring Compliment of the relational data modeling 16

17. Cont. Various steps in normalization process are described in the subsection that follows:- 1. First Normal Form (1NF) A relational table must not contain repeating groups are in 1NF . If a table is not in 1NF we have normalize it using decomposition . Decomposition is dividing a non normal table in to two/more normal tables without losing any meaning . 1NF describes the tabular format in which: All the key attributes are defined . There are no repeating groups in the table. All attributes are dependent on the primary key . 17

18. Cont. E.g. :- Give the following table to show that it is in 1NF we have to look for any repeating groups . Normal Tables in 1NF Non-normal table = 2 normal tables in meaning they are the same. 18 N/B B.ID(PK) B.Title Publisher 1 B01 Computer Networking Natan, James 2 B02 Fundamental Database Gorge 3 B03 Advanced Database Jams 4 B04 Computer maintenance Chirs , Lewes Table 1   N/B B.ID(PK) B.Title 1 B01 Computer Networking 2 B02 Fundamental Database 3 B03 Advanced Database 4 B04 Computer maintenance Table 2   B.ID(FK) Publisher B01 Natan B01 James B02 Gorge B03 Jams B04 Chirs B04 Lewes

19. Cont. 2. Second Normal Form (2NF) A relational table is in 2NF, if every Non-key attributes depends on the Whole-key ( two or more keys ). Functional Dependency can be expressed by: X  Y : Y is functional dependent on X , or X determines Y . X&M  Z : Z is functional dependent on X&M , or X&M determines Z . If Non-key determines another Non-key violates the 2NF. To show the table in 2NF, it should be:- It should be in 1NF Whole-key  Non-key 19

20. Cont. E.g.:- Show that the following STUDENT_COURSE table is in 2NF. 20 SID(PK) Name Sex Age Admission CCODE ( PK) CTitle Ch / Hr Offered Year Grade S01 Amanuel M 29 Regular C01 FDBs 4 2012 A+ S02 Asefa M 27 Regular C01 FDBs 4 2012 B S03 Henok M 28 Regular C02 ADBs 4 2013 B+ S04 Bereket M 30 Regular C02 ADBs 4 2013 A The table is in 1NF . Whole-key (SID, CCODE)  Non-key (Name, Sex, Age, Admission, CTitle , Ch / hr , Offered Year, Grade) SID  Name, Sex, Age, Admission ( Not in 2NF ) CCODE  CTitle , Ch / hr , Offered Year ( Not in 2NF ) SID , CCODE  Grade ( In 2NF )

21. Cont. Student Table (Not in 2NF)   SID(PK) Name Sex Age Admission S01 Amanuel M 29 Regular S02 Asefa M 27 Regular S03 Henok M 28 Regular S04 Bereket M 30 Regular 21 Course Table (Not in 2NF)   CCODE(PK) CTitle Ch / Hr Offered Year C01 FDBs 4 2012 C01 FDBs 4 2012 C02 ADBs 4 2013 C02 ADBs 4 2013 Grade Table (In 2NF)   SID(FK ) CCODE(FK ) Grade S01 C01 A+ S02 C01 B S03 C02 B+ S04 C02 A

22. Cont. 3. Third Normal Form (3NF) A relational table is in 3NF, if every Non-key attributes depends only on the Primary-key only . A Functional Dependency in which one Non-key determines another Non-key violates the 3NF. To show the table in 3NF, it should be:- It should be in 2NF Primary-key only  Non-key 22

23. Cont. E.g.:- Show that the following STUDENT_COURSE table is in 3NF . 23 SID(PK) Name Sex Age Admission CCODE ( PK) CTitle Ch / Hr Offered Year Grade S01 Amanuel M 29 Regular C01 FDBs 4 2012 A+ S02 Asefa M 27 Regular C01 FDBs 4 2012 B S03 Henok M 28 Regular C02 ADBs 4 2013 B+ S04 Bereket M 30 Regular C02 ADBs 4 2013 A It should be in 2NF , the table is in 2NF, b/c :- Whole-key (SID, CCODE)  Non-key (Name, Sex, Age, Admission, CTitle , Ch / hr , Offered Year, Grade) Primary-key only  Non-key SID  Name, Sex, Age, Admission ( In 3NF ) CCODE  CTitle , Ch / hr , Offered Year ( In 3NF ) SID, CCODE  Grade ( Not in 3NF )

24. Cont. Student Table (In 3NF )   SID(PK) Name Sex Age Admission S01 Amanuel M 29 Regular S02 Asefa M 27 Regular S03 Henok M 28 Regular S04 Bereket M 30 Regular 24 Course Table (In 3NF )   CCODE(PK) CTitle Ch / Hr Offered Year C01 FDBs 4 2012 C01 FDBs 4 2012 C02 ADBs 4 2013 C02 ADBs 4 2013 Grade Table (Not i n 3NF )   SID(FK ) CCODE(FK ) Grade S01 C01 A+ S02 C01 B S03 C02 B+ S04 C02 A

25. Cont. 4. BCNF (Boyce-Code Normal Form) A table is in BCNF, if every determinant is a Key . It is used to normalize 2NF and 3NF at a single step . BCNF is simpler because it doesn’t refer 2NF . Violation of BCNF involves Functional Dependencies in which the determinant (left hand side) of the keys is not primary-key . 25

26. Cont. E.g.:- Show that the following STUDENT_COURSE table is in BCNF. 26 SID(PK) Name Sex Age Admission CCODE ( PK) CTitle Ch / Hr Offered Year Grade S01 Amanuel M 29 Regular C01 FDBs 4 2012 A+ S02 Asefa M 27 Regular C01 FDBs 4 2012 B S03 Henok M 28 Regular C02 ADBs 4 2013 B+ S04 Bereket M 30 Regular C02 ADBs 4 2013 A SID  Name, Sex, Age, Admission ( In BCNF ) CCODE  CTitle , Ch / hr , Offered Year ( In BCNF ) SID, CCODE  Grade ( In BCNF )

27. Cont. Student Table (In BCNF)   SID(PK) Name Sex Age Admission S01 Amanuel M 29 Regular S02 Asefa M 27 Regular S03 Henok M 28 Regular S04 Bereket M 30 Regular 27 Course Table (In BCNF)   CCODE(PK) CTitle Ch / Hr Offered Year C01 FDBs 4 2012 C01 FDBs 4 2012 C02 ADBs 4 2013 C02 ADBs 4 2013 Grade Table (In BCNF)   SID(FK ) CCODE(FK ) Grade S01 C01 A+ S02 C01 B S03 C02 B+ S04 C02 A