Fourth Normal Form and Multi-Value Dependencies, Slides of Database Management Systems (DBMS)

Download Fourth Normal Form and Multi-Value Dependencies and more Slides Database Management Systems (DBMS) in PDF only on Docsity! 4TH NORMAL FORM Docsity.com REVIEW OF NFs • 1NF  All values of the columns are atomic. That is, they contain no repeating values. • 2NF  it is in 1NF and every non-key column is fully dependent upon the primary key. Docsity.com 4th Normal Form Cont… This is best discussed through mathematical notation. Assume the following relation R(a:pk1, b:pk2, c:pk3) Recall that a relation is in BCNF if all its determinant are candidate keys, in other words each determinant can be used as a primary key. Because relation R has only one determinant (a, b, c), which is the composite primary key and since the primary is a candidate key therefore R is in BCNF. Docsity.com 4th Normal Form Cont… Now R may or may not be in fourth normal form. 1. If R contains no multi value dependency then R will be in Fourth normal form. 2. Assume R has the following two-multi value dependencies: a --->> b and a --->> c In this case R will be in the fourth normal form if b and c dependent on each other. However if b and c are independent of each other then R is not in fourth normal form and the relation has to be projected to following two non- loss projections. These non-loss projections will be in fourth normal form. Docsity.com 4th Normal Form Cont… Many-to-many relationships Fourth Normal Form applies to situations involving many-to-many relationships. In relational databases, many-to-many relationships are expressed through cross- reference tables. Docsity.com Example 1 Consider a case of class enrollment. Each student can be enrolled in one or more classes and each class can contain one or more students. Clearly, there is a many-to-many relationship between classes and students. This relationship can be represented by a Student/Class cross- reference table: {StudentID, ClassID} Docsity.com Example 1 Cont… • The key for this table is the combination of StudentID and ClassID. To avoid violation of 2NF, all other information about each student and each class is stored in separate Student and Class tables, respectively. • Note that each StudentID determines not a unique ClassID, but a well-defined, finite set of values. This kind of behavior is referred to as multi-valued dependency of ClassID on StudentID. Docsity.com Example 2 • Consider another example with two many-to-many relationships, between students and classes and between classes and teachers. Students Classes * * Also, a many-to-many relationship between students and teachers is implied. Classes Teachers * * Docsity.com 4th Normal Form and anomalies Cont… Case 1: Assume the following relation: Employee (Eid:pk1, Language:pk2, Skill:pk3) No multi value dependency, therefore R is in fourth normal form. Docsity.com case 2: Assume the following relation with multi-value dependency: Employee (Eid:pk1, Languages:pk2, Skills:pk3) Eid --->> Languages Eid --->> Skills Languages and Skills are dependent. This says an employee speak several languages and has several skills. However for each skill a specific language is used when that skill is practiced. 4th Normal Form and anomalies Cont… Docsity.com Thus employee 100 when he/she teaches speaks English but when he cooks speaks French. This relation is in fourth normal form and does not suffer from any anomalies. Eid Language Skill 100 English Teaching 100 Kurdish Politic 100 French Cooking 200 English Cooking 200 Arabic Singing Docsity.com Insertion anomaly: To insert row (200 English Cooking) we have to insert two extra rows (200 Arabic cooking), and (200 English Singing) otherwise the database will be inconsistent. Note the table will be as follow: Eid Language Skill 100 English Teaching 100 Kurdish Politics 100 English Politics 100 Kurdish Teaching 200 Arabic Singing 200 English Cooking 200 Arabic Cooking 200 English Singing Docsity.com • Deletion anomaly: If employee 100 discontinue politic skill we have to delete two rows: (100 Kurdish Politic), and (100 English Politic) otherwise the database will be inconsistent. Eid Language Skill 100 English Teaching 100 Kurdish Politics 100 English Politics 100 Kurdish Teaching 200 Arabic Singing 200 English Cooking 200 Arabic Cooking 200 English Singing Docsity.com More anomalies • Update anomaly: If employee 200 changes his skill from singing to dancing we have to make changes in more than one place. Docsity.com