Contents Components of Relational Model Functional Dependencies Redundant

Contents ØComponents of Relational Model ØFunctional Dependencies ØRedundant Relations and Anomalies ØNormalization 1

Main Components of Relational Model ØStructure (Tables/ Relations) ØManipulation Language (SQL) ØIntegrity Constraints 2

Relation Scheme ØMap all the Entity Types in ERD to Relational scheme as below: ØSTUDENT(Stdt. Id, Stdt. Name, Stdt. City, Stdt. State, Stdtphone_no) ØDefine the domain and data types in the relational model (domain and data type are interdependent) 3

A Few Mapping Considerations ØOne-to-Many ØIf Minimum cardinality is compulsory then make FK Not Null otherwise set it Null. ØOne-to-One ØPK of compulsory side is included in the optional side 4

Difference Between ERD and Relational Model’s Core Concept Normalization Ø An ERD is a top-down approach of analysing user requirements and data requirement of any organization Ø Normalization is a bottom-up analysis of Relational Data Model Ø In Normalization we analyze association between attributes and based on those analysis group the attribute in tables to form tables and relationships 5

Functional Dependency Ø It is an important concept that describes the relationship among attributes (Maier, 1983) Ø Definition of FD: Ø Describes the relationship between attributes in a relation. For example, if a and B are attributes of relation R, B is functionally dependent on a (denoted A B), if each value of A is associated with exactly one value of B. (A and B may each consist of one or more attributes. ) OR For any valid instance of A, the value uniquely determines the value of B Its not mathematical, B cannot be computed from A. It may depend on more than one attribute. 6

Functional Dependency Ø It is the property of semantics of attributes in a relation, it defines how the attribute relate to each other Ø If we know the value of A attribute in any tuple we can find the value of B. Ø A determines the value B Ø Determinant: The attribute on the left hand side of arrow. 7

Functional Dependency - Example Ø staff. No Functionally determines position but opposite is not true, position does not determine staff. No, there may be many staff members working as Managers 8

Functional Dependency - Example Ø Functional dependency that holds for all time: staff. No s. Name 9

Redundant Relations - Problem A partial dependency exists when there is a functional dependence in which the determinant is only part of the primary key (remember we are assuming there is only one candidate key). For example, if (A, B) → (C, D), B → C, and (A, B) is the primary key, then the functional dependence B → C is a partial dependency because only part of the primary key (B) is needed to determine the value of C. Partial dependencies tend to be rather straightforward and easy to identify. 10

Redundant Relations - Problem ØRedundant Relations have problems called update anomalies which is classified as ØInsertion Anomaly ØDeletion Anomaly ØModification Anomaly 11

Redundant Relations - Example We will proceed with this example in defining the concept of anomalies 12

Insertion Anomaly Ø Insertion anomaly has two main types: Ø To insert the name of new staff member, we need to enter the correct, valid and consistent branch number, in case of failure we may have inconsistent data Ø To insert the detail of new branch that has no staff till now, we need to insert NULLs in the staff detail attributes, however, if the staff. NO is PK then we cannot enter NULL in the Primary Key field which will prevent us to enter any data for branch. 13

Deletion Anomaly Ø If we delete the record of all the members of a branch for rerecruitment then the record of branch is also lost Ø This is an anomaly, since the branch data must be separately handles 14

Modification Anomaly Ø If we want to change one attribute of any particular branch, we must update all the tuples of staff located at that branch, failure which the data will become inconsistent and it would have different address of branch suppose for different staff 15

Another Example of Redundant Data with Anomalies Ø Ø The database is designed keeping in view all the constraints. A Student can have one major A student may have many courses A single course is taught by only one professor Name Course Phone_no Major Professor Grade Kashif CS-203 1234567 Comp Sc Ali A Imtiaz CS-303 5464364 Chemistry Javed B Kashif CS-328 1234567 Comp Sc Tousef B Naveed CS-303 7654657 Physics Adnan A Naveed CS-503 7654657 Physics Imran In Kashif CS-492 1234567 Comp Sc Atif In Saad CS-379 4575435 English Shahzeb C 16

Problem ØRedundancy ØPhone_no, Major & Professor teaching course is repeating many times ØAll three anomalies (Insertion, Deletion, Modification) 17

Problem ØUpdate ØPhone_no has to be changed at many places ØInsertion ØCS-500 is offered by Prof. Atif, but the information can not be inserted in this table as no student has taken that course so far. 18

Problem ØDeletion ØCS-503 delete, the Professor teaching that course will also be deleted 19

Role of KEYS in Normalization ØCandidate Key: An attribute or combination of attribute that uniquely identify a row in a table. ØCandidate key also becomes Primary key, however, if a table holds more than one candidate keys, one of them is the primary key while others are called secondary keys. 20

Solutions to Redundancy and Anomalies ØNormalization, however, consider these facts in mind ØLossless Join ØAfter joining the relations the resulting relation shall be of original one ØDependency Preservation ØFunctional dependency holds 21

Normalization Ø Normalization: A step by step process for producing a set of relations with desirable attributes, given the data requirements of an organization Ø Purpose of Normalization: Ø Minimal number of attributes to support data requirements of organization Ø Attribute with close logical relationship (FD) are found in the same relation Ø Minimal redundancy of attribute, with the exception of FK Ø Advantages of Normalization: Ø Update is achieved with minimal number of operations Ø Reduce Data inconsistency Ø Reduction in the file storage space 22

1 st Normal Form Ø Also called 1 NF Ø There are no Multi-valued attributes and describes atomic value for each tuple / also called removing the repeating groups Ø A Database is in 1 NF iff every relation in the database is in 1 NF. Ø Steps for 1 NF Ø 1. There are no repeating groups in the relation (thus, there is a single fact at the intersection of each row and column of the table). 2. A primary key has been defined, which uniquely identifies each row in the relation. 23

1 st Normal Form - Example 24

1 st Normal Form - Example Invoice Table 25

1 st Normal Form - Example Ø Select Functional Dependencies and then Primary Key on the basis of organization rules. All possible FDs in given table. . . Now Look for a suitable primary key Ø Order. ID, Product. ID is the most suitable PK, as the combination of these keys identify each row uniquely --- But it the table still holds Redundancy and Anomalies 26

2 nd Normal Form Ø Based on the Concept of Fully Functional Dependency Ø A functional dependency X Y is fully functional dependency if removal of any attribute A from X, and the FD doesn’t hold anymore. Ø A functional dependency X Y is partial dependency if removal of any attribute A from X, and the FD still holds Ø A relation is said to be in 2 NF if it is in 1 NF and every non-key attribute is fully functionally dependent on key attribute(s) Ø In other words, the relation is in 1 NF and does not hold any Ø 2 nd NF is particularly applicable when FD’s left hand are composite key, and part of PK, Ø if PK is made up of single attribute 2 nd is not applicable 27

2 nd Normal Form Ø Steps for 2 NF Ø 1. Create a new relation for each primary key attribute (or combination of attributes) that is a determinant in a partial dependency. That attribute is the primary key in the new relation. 2. Move the non-key attributes that are dependent on this primary key attribute (or attributes) from the old relation to the new relation. 28

2 nd Normal Form - Example 29

2 nd Normal Form - Example 30

2 nd Normal Form - Example Ø The table is in 1 NF Ø Following Partial FD exist in the table. Ø Order. ID Ø Product. ID is the most suitable Pk for these relations --- But the table still holds Redundancy and Anomalies (Removed in 3 NF) 31

2 nd Normal Form - Example 32

2 nd Normal Form - Example EMPLOYEE Employee. ID Emp. Skill Emp. Work. Place Kashif Java Lahore Kashif C# Lahore Haris PHP Karachi Hassan C++ Multan Hassan Perl Multan Hassan Ruby on Rails Multan 33

2 nd Normal Form - Example EMPLOYEE_SKILL Employee. ID Emp. Skill Kashif Java Kashif C# Haris PHP Hassan C++ Hassan Perl Hassan Ruby on Rails EMPLOYEE_WORKPLACE Employee. ID Emp. Work. Place Kashif Lahore Haris Karachi Hassan Multan 34

3 rd Normal Form Ø Transitive Dependency: A functional dependency between Primary Key and other non-key attribute(s) that are dependent on PK via another non-key attribute Ø A relation is said to be in 3 NF if it is in 2 NF and every non-key attribute is functionally dependent on only Primary Key attribute(s) 35

3 rd Normal Form Ø Steps in 3 NF Ø 1. For each non-key attribute (or set of attributes) that is a determinant in a relation, create a new relation. That attribute (or set of attributes) becomes the primary key of the new relation. 2. Move all of the attributes that are functionally dependent on the primary key of the new relation from the old to the new relation. 3. Leave the attribute that serves as a primary key in the new relation in the old relation to serve as a foreign key that allows you to associate the two relations. 36

3 rd Normal Form - Example 3 NF Transitive Dependency Not in 3 NF as this relation has transitive dependency 37

3 rd Normal Form - Example 38

3 rd Normal Form A non-key attribute is defining another attribute Ø Another Scenario Roll_No Name Department Year 1 Nadeem Physics 2 Imtiaz Chemistry Hostel Quaid e Azam 1 Hostel 3 Saad Mathematics 2 Iqbal Hostel 4 Rehan Botany 2 Iqbal Hostel 5 Mohsin Geography 3 New Hostel 6 Atif Zoology 3 New Hostel Student_Dept ------------------Year_hostel(year, Hostel) 39

BCNF Ø A Relation is in BCNF if it is in 3 NF and Ø if every determinant is a candidate key Ø There is a determinant which is not a candidate key Ø OR Ø A relation has more than one candidate keys, if there is only one candidate key then relation is in 3 NF and BCNF Ø Candidate keys overlap Ø A stronger form of 3 NF Ø Also called 3. 5 NF Ø A Stronger Version of 3 NF 40

BCNF Ø Steps in BCNF Ø In the first step, the relation is modified so that the determinant in the relation that is not a candidate key becomes a component of the primary key of the revised relation. Ø The attribute that is functionally dependent on that determinant becomes a non-key attribute. This is a legitimate restructuring of the original relation because of the functional dependency. 41

BCNF - Example 2. 1. Rules determines that even if a student May have more than one major , there is Functional Dependencies in BCNF: Exactly one supervisor for each major, hence, SID, Major Advisor, Maj. GPA Major is functionally dependent on Advisor, Advisor Major Each advisor advises exactly one major However, Advisor is not a candidate key, yet it determines Major 3. Not a transitive dependency, as in the TD a non-key attribute is dependent upon another non-key Attribute, while in this case major is a part of key 42

BCNF - Example The relationship is in 3 NF, however, it still has anomalies, Update: Advisor change, Insertion: Charles Advice CS, however, there must be CS student, Deletion: Deletion of SID 123 also delete the Advisor data. 43

BCNF - Example The Advisor which determines the Major is become part of the PK. While Major becomes Non-key, this is a legitimate restructuring. However, This does not solve the problem, as major is fully functionally dependent On Advisor only. This restructured relation has partial dependency. Thus this restructured Relation is in 2 NF. 44

BCNF - Example Relation in BCNF 45

BCNF - Another Example Relation with overlapping candidate keys This relation has two candidate keys, {SID, Course. ID} and {SName, Course. ID} Course. ID appears in both candidate keys, hence, candidate keys overlap We cannot enter Course Data untill SID and Sname is entered 46

BCNF - Another Example Solution 1: OR Solution 2: BCNF Relations 47

4 th Normal Form ØA relation is said to be in 4 NF if it is in 3 NF and BCNF and all the Multi-Valued dependencies (MVD) are removed 48

4 th Normal Form Ø Multi-Valued dependencies (MVD) ØA multi-valued dependency in a Relation, where two sets of attributes are independent of each other. Ø For example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, the set of values for B and C are independent of each other. Ø Represented as. Ø A —>-> B Ø A —>-> C 49

4 th Normal Form • MVD X->-> is an assertion that if two tuples of a relation agree on all attribute of X then their components in the set of Y may be swapped and the result will be two tuples that are also in the relation. A multi-valued dependency can be further defined as being trivial or nontrivial. • A MVD A • —>-> B in relation R is defined as being trivial if • (a) B is a subset of A • OR • (b) A U B = R • A MVD is defined as being nontrivial • if neither (a) nor (b) are satisfied. A trivial MVD does not specify a constraint on a relation; a nontrivial MVD does specify a constraint. 50

4 th Normal Form Course Teacher Text. Book Database Systems Muhammad Hafeez Fundamentals of Database Systems Nadeem Zafar Modern Database Management Database Systems: A Practical Approach Object Oriented Programming Muhammad Safyan Object-Oriented Programming. Atif Ishaq Detiel & Detiel Programming in Java 51

4 th Normal Form Course Teacher Text. Book Database Systems Muhammad Hafeez Fundamentals of Database Systems Muhammad Hafeez Modern Database Management Database Systems Muhammad Hafeez Database Systems: A Practical Approach Object Oriented Programming Muhammad Safyan Object-Oriented Programming. Detiel & Detiel Object Oriented Programming Muhammad Safyan Programming in Java Object Oriented Programming Atif Ishaq Object-Oriented Programming. Detiel & Detiel Object Oriented Programming Atif Ishaq Programming in Java 52

4 th Normal Form • Definition: • A relation R is in 4 NF if whenever x->->Y is a non-trivial MVD, then X is a superkey • Non-trivial means • 1. Y is not a subset of X, and • 2. X and Y are not, together, all the attributes in a relation R. 53

4 th Normal Form COURSE_TEACHER Course Teacher Database Systems Muhammad Hafeez Database Systems Nadeem Zafar OOP Muhammad Safyan OOP Atif Ishaq COURSE_TEXTBOOK Course Textbook Database Systems TEXT 1 Database Systems TEXT 2 Database Systems TEXT 3 OOP TEXT 1 OOP TEXT 2 54