Functional Dependency second lesson.pptx

DB Functional dependency

1. To identify and use functional dependencies in the process of normalization, you can follow these steps: Step 1: Identify Functional Dependencies Examine Relationships Between Attributes: Look at each attribute in your table and determine if it is dependent on another attribute. For example, in a table with columns like StudentID , StudentName , and Course, check if StudentName is uniquely determined by StudentID . If so, StudentID → StudentName is a functional dependency. List All Functional Dependencies: Write down all the functional dependencies you identify. This list will be crucial as you move through the normalization process.

2. Step 2: Apply Normalization Normalization is typically done in stages, each corresponding to a "normal form" (NF). First Normal Form (1NF): Objective: Ensure that the table has no repeating groups. Action: If you have attributes that can contain multiple values (e.g., a list of phone numbers), split them into separate rows or columns. Using Functional Dependencies: Use the identified dependencies to ensure that the table is organized so that each attribute contains atomic (indivisible) values.

3. Step 2: Apply Normalization 2.Second Normal Form (2NF): Objective: Eliminate partial dependencies. Action: Move any non-key attribute that is only partially dependent on a composite primary key to a new table. Using Functional Dependencies: Identify attributes that depend on part of a composite key and create new tables to eliminate these partial dependencies. 3. Third Normal Form (3NF): Objective: Eliminate transitive dependencies. Action: Move attributes that are dependent on other non-key attributes (rather than the primary key) to a new table. Using Functional Dependencies: Ensure that all non-key attributes depend only on the primary key.

4. Example: Given a table: Columns: StudentID , StudentName , Course, Instructor Functional Dependencies: - StudentID → StudentName . - Course → Instructor. Unnormalized Form: The table might include repetitive data (e.g., multiple courses for the same student listed in a single row). 2.1NF: Ensure that each course for a student is in a separate row.

5. Example: 3. 2NF: If the primary key is composite (e.g., StudentID, Course), and StudentName only depends on StudentID, separate the table into two: Table 1: StudentID, StudentName Table 2: StudentID, Course, Instructor 4. 3NF: If Instructor depends on Course rather than the combination of StudentID and Course, create a new table: Table 3: Course, Instructor

6. By the end of the normalization process, your database will be structured to minimize redundancy and avoid anomalies, while the functional dependencies will guide how the data is split across tables.