Download to read offline
Relational Database Management System Unit 2
- 1. UNIT-2: FUNCTIONAL DEPENDENCIES& NORMALIZATION FOR RELATIONAL DATABASES
- 2. Stud_ID Name Dept 101ALex CS 102 Rita IT 103 Ben BBA FUNCTIONAL DEPENDENCY A functional dependency is a link between attributes that establishes how one uniquely identifies another. It occurs when the value of one attribute uniquely determines the value of another attribute in a relation. • If attribute A determines attribute B, it is written as A → B. • Where: A is the determinant, and B is the dependent attribute. Example: In a Student(Stud_ID, Name, Dept, Phone) table, Stud_ID uniquely determines the Name and Department, It can be written as Stud_ID → Name, Dept, Phone
- 3. TYPES OF FUNCTIONALDEPENDENCY 1. Full Functional Dependency 2. Partial Functional Dependency 3. Transitive Functional Dependency 4. Multivalued Functional Dependency 5. Trivial and Non-Trivial Functional Dependency
- 4. FULL FUNCTIONAL DEPENDENCY A dependencyX → Y is a full functional dependency if Y is dependent on the whole of X, and not just a part of it. • Mostly applies when the primary key is composite. Example: Relation: Student_Course(Stud_ID, Course_ID, Grade) (Stud_ID, Course_ID) → Grade (Grade depends on the full composite key, not just Stud_ID or Course_ID). Stud_ID Course- ID Grade 101 C001 A 102 C001 B 103 C007 B+
- 5. PARTIAL FUNCTIONAL DEPENDENCY •A dependency X → Y is a partial functional dependency if Y is dependent on part of X i.e If an attribute is determined by only a part of a composite key . • Example: ⚬ Relation: Student_Course(Stud_ID, Course_ID, Stud_Name) ⚬ Stud_ID → Stud_Name (depends only on part of the composite key). • This is what 2NF removes. Stud_ID Course- ID Stud_Name 101 C001 Alex 102 C001 Bob 103 C007 Ben
- 6. TRANSITIVE FUNCTIONAL DEPENDENCY •A dependency X → Z is transitive if there exists a non- prime attribute Y such that: ￭ X → Y and Y → Z. • Example: ⚬ Relation: Employee(Emp_ID, Dept_ID, Dept_Name) ⚬ Emp_ID → Dept_ID and Dept_ID → Dept_Name → so Emp_ID → Dept_Name (transitive). • This is what 3NF removes. Emp_ID Dept_ID Dept_Name 101 D001 HR 102 D002 Marketing 103 D007 IT
- 7. A multivalued dependency(X →→ Y) exists when a single value of X determines multiple independent values of Y, regardless of other attributes in the relation. • Such dependencies often arise in many-to-many relationships. • 4NF addresses these dependencies • Examples: ⚬ Student(Stud_ID, Hobby, Language) ￭ Stud_ID → Hobby (a student can have many hobbies). ￭ Stud_ID → Language (a student can know many languages). MULTIVALUE D FUNCTIONAL DEPENDENCY Stud_ID Language Hobby 101 Eng Singing 102 Hindi Dancing 103 Eng Sports
- 8. TRIVIAL AND NON-TRIVIALFUNCTIONAL DEPENDENCY Trivial Dependency • A functional dependency is trivial if the right-hand side (dependent) is already part of the left-hand side (determinant). • Example: {Stud_ID,Course_ID} → Stud_ID ⚬ Since Stud_ID is already in the left side, this dependency is trivial. Non-Trivial Dependency • A functional dependency is non-trivial if the right- hand side is not a subset of the left-hand side. • Example: Stud_ID→ Dept ⚬ Here, Dept is not part of Stud_ID, so it’s non- trivial. Stud_ID Course- ID Dept 101 C001 CS 102 C001 BBA 103 C007 AI
- 9. NORMALIZATION Normalization is aprocess of organizing data in a database to reduce redundancy and improve data integrity. It involves decomposing large tables into smaller, well-structured ones. Functional dependencies are essential to this process. NORMAL FORMS BASED ON PRIMARY KEYS First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce-Codd Normal Form (BCNF)
- 10. A relation isin 1NF if: • All attributes contain atomic (indivisible) values. • No repeating groups or multivalued attributes. FIRST NORMAL FORM (1NF) EmployeeID Skills E01 Java, Python E02 C++, Python EmployeeID Skills E01 Java E01 Python E02 Python E02 C++ Before Normalization After 1NF
- 11. A relation isin 2NF if: • It is in 1NF. • Every non-prime attribute is fully functionally dependent on the whole primary key. • It eliminates partial dependency SECOND NORMAL FORM (1NF) Before Normalization After 2NF Stud_ID Stud_Name S1 Ananya S2 Rahul Stud_ID Stud_Name C1 DBMS C2 Networks
- 12. Emp_I D Dept_ ID 101 D1 102 D2 103D1 104 D3 105 D2 Dept_ID Dept_Name D1 HR D2 Finance D3 Marketing A relation is in 3NF if: • It is in 2NF. • No transitive dependency exists (non-prime attribute depending on another non-prime attribute). THIRD NORMAL FORM (1NF) Before Normalization After 3NF Employee Table Department Table
- 13. Emp_I D Dept_ ID 101 D1 102 D2 103D1 104 D3 105 D2 Dept_ID Dept_Name D1 HR D2 Finance D3 Marketing • A relation is in BCNF if: • For every functional dependency X → Y, X must be a super key. • BCNF is stricter than 3NF. It handles anomalies not covered in 3NF. BOYCE- CODD NORMAL FORM (BCNF) Before Normalization After BCNF Employee Table Department Table
- 14. Relational Data Model The relationaldata model, is a foundational concept in Relational Database Management Systems (RDBMS), where data is organized into tables, also known as relations. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship. Basic Concepts • Relation: A relation is a table that stores data in rows and columns. • Tuple: A tuple is a single row in a relation. It represents one record and contains values for each attribute. • Attribute: An attribute is a column in a relation that represents a property or characteristic of the entity. • Domain: A domain is the set of possible values that an attribute can take. • Degree: The degree of a relation refers to the number of attributes in the table. • Cardinality: The cardinality of a relation refers to the number of tuples it contains.
- 15. Relational Keys Keys arespecial attributes (or sets of attributes) used to uniquely identify tuples in a relation. They play a vital role in maintaining uniqueness and relationships in databases. Relational Keys • Super key • Candidate key • Primary key • Alternate key • Foreign key
- 16. Relational Constraints Relational constraints arerules that ensure the accuracy and integrity of data. These rules are applied to the values stored in the database to maintain consistency and prevent invalid data.
- 17. Domain Constraints Domain constraintsdefine the allowed values for each attribute (column) in a relation. Every attribute in a table is associated with a domain, which limits the data type and value range that can be stored. Example: An attribute like Age may only accept integers between 18 and 60. You cannot insert a name or negative number into the Age column. Key Constraints Key constraints ensure that each row in a table is uniquely identifiable. This is typically enforced using Primary Keys and Candidate Keys. • Primary Key must be unique and not null. • No two rows can have the same primary key value. Example: In a STUDENTS table, no two students can have the same Student_ID.
- 18. Entity Integrity Constraints Entityintegrity constraint ensures that primary key values are never null. Since the primary key is used to uniquely identify each tuple (row), it must always contain a valid and non-null value. Example: A Student_ID field in a STUDENTS table cannot be left blank. Referential Integrity Constraints Referential integrity constraints are used to maintain consistency between two related tables. It ensures that a foreign key value in one table must match an existing primary key value in another table, or be NULL. Example: If a Course_ID in the ENROLLMENT table refers to a Course_ID in the COURSES table, then that course must exist in the COURSES table.
- 19. Relational Database Schema A RelationalDatabase Schema is the blueprint or structure that defines how data is organized in a relational database. It includes the names of tables, the attributes (columns) of each table, their data types, and the relationships between the tables. Key Components • Table Names – Each table represents an entity (like STUDENTS, COURSES, EMPLOYEES). • Attributes – Columns of each table, representing properties of that entity. • Data Types – Specifies what kind of data an attribute can hold (e.g., INTEGER, VARCHAR, DATE). • Keys – • Primary Key: Uniquely identifies each row in a table. • Foreign Key: Connects two related tables. • Relationships – Defines how tables are linked using keys (one-to-one, one-to-many, etc.).
- 20. Relational Algebra isa formal language used to query and manipulate relational databases. It provides a set of mathematical operations that take one or more relations (tables) as input and produce a new relation as output. Basic Relational Algebra Operations • Selection (σ) • Projection (π) • Union ( ) ∪ • Set Difference (−) • Cartesian Product (×) • Rename (ρ) Relational Algebra
- 21. The Selection operationretrieves rows (tuples) from a relation that satisfy a given condition. Syntax : σ<condition>(Table_Name) Example : σBranch='CSE'(STUDENT)- Selects only those students who belong to the CSE branch. Selection (σ) Roll No Name Branch Marks 001 Ravi CSE 65 003 Arjun CSE 95 Roll No Name Branch Marks 001 Ravi CSE 65 002 Meena ECE 77 003 Arjun CSE 95 004 Priya IT 89 Student Table Result
- 22. The Projection operationretrieves specific columns (attributes) from a relation. It removes duplicates automatically. Syntax : π<attribute_list>(Table_Name) Example : πName, Marks(STUDENT) – Shows only the Name and Marks of all students. Projection (π) Name Marks Ravi 65 Meena 77 Arjun 95 Priya 89 Roll No Name Branch Marks 001 Ravi CSE 65 002 Meena ECE 77 003 Arjun CSE 95 004 Priya IT 89 Student Table Result
- 23. The Union operationcombines the tuples from two compatible relations (same number of attributes and same data types) and removes duplicates. Syntax: Table1 Table2 ∪ Example: STUDENT STUDENT_NEW ∪ Union ( ) ∪ Roll No Name Branch 001 Ravi CSE 002 Meena ECE Student Result Roll No Name Branch 002 Meena ECE 003 Arjun CSE Student_New Roll No Name Branch 001 Ravi CSE 002 Meena ECE 003 Arjun CSE
- 24. The Set Differenceoperation returns tuples that are in the first relation but not in the second. Syntax: Table1 Table2 − Example: STUDENT STUDENT_NEW − Set Difference ( ) − Roll No Name Branch 001 Ravi CSE 002 Meena ECE Student Result Roll No Name Branch 002 Meena ECE 003 Arjun CSE Student_New Roll No Name Branch 001 Ravi CSE
- 25. The Cartesian Productoperation returns all possible combinations of tuples from two relations. It is also known as the cross product. Syntax: Table1 × Table2 Example: STUDENT × DEPT Cartesian Product (×) Roll No Name 001 Ravi 002 Meena Student Table Result D_ID D_Name D01 CSE D02 ECE Student_New Roll No Name D_ID D_Na me 001 Ravi D01 CSE 001 Ravi D02 ECE 002 Meena D01 CSE 002 Meena D02 ECE
- 26. The Rename operationis used to rename a relation or its attributes. This is helpful for readability or for intermediate steps in complex queries. Syntax : ρNewName(TableName) Example : ρSTU(STUDENT) Rename (ρ) Roll No Name Branch Marks 001 Ravi CSE 65 002 Meena ECE 77 003 Arjun CSE 95 004 Priya IT 89 Student Table Result Table : STU Roll No Name Branch Marks 001 Ravi CSE 65 002 Meena ECE 77 003 Arjun CSE 95 004 Priya IT 89
- 27. Relational Database Designusing ER-to-Relational Mapping The ER-to-Relational Mapping process converts an Entity-Relationship (ER) diagram into a relational schema made up of tables, keys, and relationships. This is important because the ER model designs the database conceptually, while the relational model is used to implement it in systems like MySQL or Oracle. Why is ER-to-Relational Mapping Important? • Data Integrity: Maintains accurate relationships and enforces constraints through keys. • Efficiency: Develops a schema optimized for performance and storage. • Clarity: Converts conceptual designs into practical database structures.
- 28. Steps for ER-to-Relational Mapping 1.Mapping Regular Entity Types • Each strong entity in the ER diagram becomes a table. • Attributes become columns of the table. • Primary key is underlined. Example: Conversion of regular entity ER Model to its Relational Schema
- 29. 2. Mapping WeakEntity Types • A weak entity depends on a strong entity and does not have a primary key of its own. • A new table is created with all its attributes + the primary key of the strong entity it depends on. • The primary key of this relation is a composite key (partial key + strong entity’s key). Example:
- 30. 3. Mapping BinaryOne-to-One Relationships For a 1:1 relationship between two entities, • Add the primary key of one entity as a foreign key in the other. • Place the foreign key in the entity with total participation. Example: Branch key added to Manager table.
- 31. 4. Mapping BinaryOne-to-Many Relationships In a 1:N relationship, the primary key of the "one" side is added as a foreign key to the relation representing the "many" side. Example:
- 32. 5. Mapping BinaryMany-to-Many Relationships For M:N relationships, • Create a new table for the relationship. • Include foreign keys from both entities. • Combined keys form a composite primary key. Example: Owns(custNo, acctNo, lastAccessed)
- 33. 6. Mapping MultivaluedAttributes • Create a separate table for the multivalued attribute. • Include the primary key of the main entity and the attribute. Example: FavColour(SSN, colour)
- 34. 7. Mapping N-aryRelationships (n > 2) • Create a new table including primary keys of all participating entities as foreign keys and any attributes of the relationship itself.. Example: Supply(SName, ProjName, PartNo, Quantity)