Functional Dependency in DBMS with Examples

Published April 26, 2025

Database Management Systems (DBMS) rely on well-structured relationships between data elements to maintain integrity and efficiency. Among the most important concepts in relational database design is functional dependency. Whether you're a database administrator, developer, or student learning database fundamentals, understanding functional dependencies is crucial for creating normalized, efficient database schemas. In this post we will cover functional dependency in DBMS

What is Functional Dependency in DBMS?

Functional dependency is a relationship between attributes in a database relation where one attribute's value determines the value of another attribute. In simpler terms, if you know the value of attribute A, you can determine the value of attribute B.

The formal notation for functional dependency is:

X → Y

This means attribute X functionally determines attribute Y, or Y is functionally dependent on X. For any two tuples t1 and t2 in a relation, if t1[X] = t2[X], then t1[Y] = t2[Y].

Real-World Example

Consider a university database with student information:

• StudentID
• Name
• Department
• DepartmentHead

In this relation, StudentID → Name because each student ID uniquely determines a student name. Similarly, Department → DepartmentHead because each department has exactly one department head.

Types of Functional Dependency in DBMS

Understanding the different types of functional dependencies helps in proper database normalization. Here are the primary types:

1. Trivial Functional Dependency

A functional dependency X → Y is trivial if Y is a subset of X. This means that knowing X automatically gives you Y because Y is contained within X.

Example:

• {StudentID, Name} → StudentID
• {CourseID, CourseName} → CourseID

2. Non-trivial Functional Dependency

A functional dependency X → Y is non-trivial if Y is not a subset of X. This means Y contains at least one attribute that is not in X.

Example:

• StudentID → Name
• CourseID → CourseDescription

3. Fully Functional Dependency in DBMS

A functional dependency X → Y is a fully functional dependency if removing any attribute from X results in the dependency no longer holding. This concept is particularly important in achieving 2NF (Second Normal Form).

Example: Consider a relation with attributes {StudentID, CourseID, Grade}:

• {StudentID, CourseID} → Grade is a fully functional dependency because neither StudentID nor CourseID alone can determine the Grade.

4. Partial Functional Dependency

A functional dependency X → Y is a partial dependency if there's a proper subset Z of X such that Z → Y. In other words, only part of the composite key is needed to determine the dependent attribute.

Example: In a relation with {StudentID, CourseID, StudentName, Grade}:

• {StudentID, CourseID} → StudentName is a partial dependency because just StudentID → StudentName holds true.

5. Transitive Functional Dependency

A transitive dependency occurs when X → Y and Y → Z, which implies X → Z (but Y is not a subset of X and Z is not a subset of Y).

Example:

• StudentID → DepartmentID (each student belongs to one department)
• DepartmentID → DepartmentName (each department has one name)
• Therefore: StudentID → DepartmentName (transitive dependency)

Dependency in DBMS: Beyond Functional Dependencies

While functional dependencies are the most common type of dependencies in database design, there are other important dependencies to understand:

1. Multivalued Dependency

A multivalued dependency occurs when an attribute depends on another attribute but is independent of other attributes in the relation. Denoted as X →→ Y, it means that for each value of X, there's a set of values of Y independent of other attributes.

2. Join Dependency

A join dependency asserts that a relation can be reconstructed by joining its projections. This is important for higher normal forms like 5NF.

3. Inclusion Dependency

Inclusion dependencies ensure referential integrity between relations, specifying that values in one attribute must appear in another attribute in a different relation.

Why Functional Dependencies Matter in Database Design

Understanding functional dependencies is essential for:

1. Normalization: Properly identifying dependencies helps eliminate redundancy through normalization.
2. Data Integrity: By understanding dependencies, constraints can be implemented to maintain data accuracy.
3. Query Optimization: Knowledge of dependencies can help in query planning and optimization.
4. Schema Design: Dependencies guide the creation of efficient, well-structured database schemas.

Identifying Fully Functional Dependency in DBMS

Fully functional dependencies are particularly important for achieving 2NF. To identify a fully functional dependency:

1. Determine the candidate keys of your relation.
2. Check if non-key attributes depend on the entire candidate key.
3. Verify that the dependency is not partial (doesn't depend on just part of the key).

Example of Fully Functional Dependency

Consider a StudentCourse relation with attributes:

• StudentID
• CourseID
• EnrollmentDate
• Grade

In this relation:

• {StudentID, CourseID} forms the primary key
• {StudentID, CourseID} → Grade is a fully functional dependency because Grade depends on the specific combination of student and course
• {StudentID, CourseID} → EnrollmentDate is also a fully functional dependency

Best Practices for Managing Dependencies in DBMS

1. Normalize your database to at least 3NF (Third Normal Form) to eliminate most dependency issues.
2. Document dependencies clearly in your database design documentation.
3. Use foreign keys to enforce referential integrity for inter-table dependencies.
4. Regularly review dependencies as business requirements evolve.
5. Use dependency diagrams to visualize relationships between attributes

 

 

Frequently Asked Questions: Functional Dependencies in DBMS

Function Dependencies in DBMS

Q1: What are functional dependencies in DBMS?

A: Functional dependency in DBMS is a relationship between two attributes or sets of attributes in a database relation, where the value of one attribute(s) determines the value of another attribute(s). If X and Y are attributes of relation R, a functional dependency X → Y indicates that for each unique value of X, there is precisely one value of Y associated with it. For example, in a relation with student data, if StudentID → StudentName, then knowing a student's ID allows us to determine exactly one corresponding student name.

Q2: How do functional dependencies help in database design?

A: Functional dependencies are fundamental to proper database design for several reasons:

• They help identify candidate keys and primary keys for relations
• They form the basis for normalization, which reduces data redundancy
• They guide the decomposition of relations into well-structured tables
• They help maintain data integrity by enforcing consistent relationships
• They assist in identifying and eliminating anomalies (insertion, deletion, and update)
• They optimize storage by minimizing duplicate data

Q3: What is the difference between a trivial and non-trivial functional dependency?

A:

• Trivial functional dependency: A dependency X → Y is trivial if Y is a subset of X. This means the dependency naturally follows from the definition of X. Example: {StudentID, CourseID} → StudentID is trivial because StudentID is contained within the determinant.
• Non-trivial functional dependency: A dependency X → Y is non-trivial if at least one attribute in Y is not in X. Example: StudentID → StudentName is non-trivial because StudentName is not part of StudentID.

Q4: How can I identify functional dependencies in an existing database?

A: Identifying functional dependencies in an existing database involves:

1. Data analysis: Examining actual data to find consistent patterns where one attribute's value always determines another's
2. Business rule analysis: Understanding the business domain to identify logical relationships
3. Database profiling tools: Using specialized software that can analyze data patterns
4. Normalization tests: Checking for anomalies that suggest unhandled dependencies
5. Query execution plans: Analyzing how the DBMS optimizes queries can reveal implicit dependencies
6. Primary and foreign key relationships: These often indicate functional dependencies

Q5: What are Armstrong's axioms and why are they important?

A: Armstrong's axioms are a set of inference rules used to derive all the functional dependencies from a given set of dependencies. The three primary axioms are:

1. Reflexivity: If Y is a subset of X, then X → Y
2. Augmentation: If X → Y, then XZ → YZ for any Z
3. Transitivity: If X → Y and Y → Z, then X → Z

These axioms are important because they are:

• Sound (they never generate invalid dependencies)
• Complete (they generate all possible dependencies)
• Used to compute attribute closures and determine keys
• Fundamental to dependency theory and normalization algorithms
• Essential for determining minimal covers of functional dependencies

Data Dependency in DBMS

Q1: What is data dependency in DBMS?

A: Data dependency in DBMS refers to the relationships and constraints between different data items in a database. These dependencies define how data elements relate to and affect each other. The primary types of data dependencies include:

1. Functional dependencies: Where one attribute determines another (StudentID → Name)
2. Multivalued dependencies: Where one attribute determines a set of values for another attribute, independent of other attributes
3. Join dependencies: Where a relation can be reconstructed by joining its projections
4. Inclusion dependencies: Where values of one attribute must appear as values of another attribute
5. Template dependencies: More general constraints defining valid database states

Data dependencies are fundamental to understanding relationships between data elements and are essential for proper database design.

Q2: How do data dependencies affect database normalization?

A: Data dependencies directly drive the normalization process:

• 1NF: Eliminates repeating groups and ensures atomic attributes
• 2NF: Removes partial dependencies (dependencies on part of the primary key)
• 3NF: Eliminates transitive dependencies (dependencies through another attribute)
• BCNF: Ensures every determinant is a candidate key
• 4NF: Addresses multivalued dependencies
• 5NF: Handles join dependencies

Each normal form addresses specific types of dependencies to progressively reduce redundancy and improve data integrity.

Q3: What is the difference between functional dependency and multivalued dependency?

A:

• Functional dependency (X → Y): For each X value, there is exactly one Y value. Example: StudentID → StudentName (each student ID determines exactly one name).
• Multivalued dependency (X →→ Y): For each X value, there is a set of Y values that are independent of other attributes. Example: Course →→ Textbook (a course may require multiple textbooks, and the set of textbooks is independent of other attributes like professor).

The key difference is that functional dependency establishes a one-to-one relationship between determinant and dependent, while multivalued dependency establishes a one-to-many relationship where the "many" values are independent of other attributes.

Q4: How can data dependencies lead to anomalies if not properly managed?

A: Improperly managed data dependencies can lead to several types of anomalies:

1. Insertion anomalies: Unable to insert certain data without inserting other, possibly unrelated data
2. Deletion anomalies: Deleting one piece of information inadvertently deletes other, unrelated information
3. Update anomalies: Need to update the same information in multiple places, risking inconsistency
4. Redundancy issues: Excessive duplication of data, wasting storage and complicating updates
5. Dependency conflicts: Contradictory rules about how data relates, causing integrity issues

For example, in a relation with {StudentID, CourseID, StudentName, CourseName}, the partial dependencies StudentID → StudentName and CourseID → CourseName cause redundancy since the same student name appears in multiple tuples, leading to potential update anomalies.

Q5: How do data dependencies impact query processing and optimization?

A: Data dependencies significantly impact query processing:

1. Join optimization: Knowledge of functional dependencies helps optimize join operations
2. Index selection: Dependencies guide the selection of appropriate indexes
3. Query rewriting: Queries can be rewritten based on known dependencies
4. View materialization: Dependencies determine which views should be materialized
5. Constraint enforcement: Dependencies translate to constraints that verify data integrity
6. Partition pruning: For partitioned tables, dependencies can help eliminate unnecessary partition scans

For example, if X → Y and a query filters on X but retrieves Y, the DBMS can optimize the execution plan knowing that each X value corresponds to exactly one Y value.

Dependencies in DBMS

Q1: What are the main types of dependencies in DBMS?

A: The main types of dependencies in DBMS include:

1. Functional dependency (FD): X → Y (X determines Y)
2. Fully functional dependency: Where Y is functionally dependent on X, but not on any proper subset of X
3. Partial dependency: Where Y depends on only a portion of a composite key
4. Transitive dependency: Where X → Y and Y → Z, implying X → Z indirectly
5. Multivalued dependency (MVD): X →→ Y (X multi-determines Y)
6. Join dependency (JD): A relation can be reconstructed by joining its projections
7. Inclusion dependency: Values in one attribute must appear in another attribute
8. Template dependency: General constraint defining valid combinations of values

Each type of dependency addresses different aspects of data relationships, with functional dependencies being the most commonly used in database design.

Q2: How do you identify different types of dependencies in a database schema?

A: Identifying different types of dependencies involves:

1. For functional dependencies:
   • Analyze primary keys and unique constraints
   • Look for attributes that always have the same value for a given key
   • Examine business rules that dictate one-to-one relationships
2. For partial dependencies:
   • Look for attributes that depend on only part of a composite key
   • Check if any non-key attribute can be determined by a subset of the primary key
3. For transitive dependencies:
   • Identify chains of dependencies where X → Y and Y → Z
   • Look for non-key attributes that depend on other non-key attributes
4. For multivalued dependencies:
   • Look for one-to-many relationships independent of other attributes
   • Identify attributes that have multiple values for a single value of another attribute
5. For join dependencies:
   • Analyze decompositions of relations
   • Check if the original relation can be reconstructed from projections

Expert database designers typically combine data analysis, domain knowledge, and normalization tests to identify these dependencies.

Q3: What is the role of dependencies in ensuring data consistency?

A: Dependencies play multiple crucial roles in ensuring data consistency:

1. Constraint definition: Dependencies translate into database constraints that enforce data integrity
2. Normalization guidance: They guide the normalization process that reduces redundancy and inconsistency risks
3. Key identification: They help identify primary and candidate keys that uniquely identify records
4. Referential integrity: They establish relationships between tables via foreign key constraints
5. Update control: They help manage cascading updates and deletes to maintain consistency
6. Validation rules: They form the basis for data validation rules at the application level
7. Schema evolution: They guide safe schema changes that preserve data semantics

When properly implemented, dependencies ensure that data remains consistent across all operations including insertions, deletions, and updates.

Q4: What is the difference between partial and transitive dependencies?

A:

• Partial dependency: Occurs when a non-key attribute depends on only part of a composite primary key. Example: In a relation with primary key {StudentID, CourseID} and attributes {StudentName, CourseName}, the dependency StudentID → StudentName is a partial dependency because StudentName depends only on part of the key.
• Transitive dependency: Occurs when a non-key attribute depends on another non-key attribute, which depends on the primary key. Example: In a relation with {StudentID, DepartmentID, DepartmentName}, if StudentID → DepartmentID and DepartmentID → DepartmentName, then StudentID → DepartmentName is a transitive dependency.

The key difference is that partial dependencies involve direct dependencies on part of a key, while transitive dependencies involve indirect dependencies through another non-key attribute.

Q5: How do you handle cyclical dependencies in database design?

A: Handling cyclical dependencies (where X → Y and Y → X) requires careful analysis:

1. Identify the cycle: Recognize attributes involved in circular dependencies
2. Determine equivalence classes: Attributes in cycles functionally determine each other and form equivalence classes
3. Select a representative: Choose one attribute as the representative for the equivalence class
4. Normalize appropriately: Keep only the representative in the primary relation and move other equivalent attributes
5. Use surrogate keys: Introduce artificial keys when natural keys create cycles
6. Document the relationship: Clearly document the synonymous relationship for future reference
7. Consider denormalization: In some performance-critical cases, controlled redundancy might be acceptable

For example, if SSN → EmployeeID and EmployeeID → SSN (they determine each other), choose one as the primary key and potentially store the other as an alternate key or in a separate lookup table.

Functional Dependency in DBMS with Example

Q1: Can you explain functional dependency using a university database example?

A: Let's consider a university database with a relation StudentCourses containing attributes:

• StudentID
• StudentName
• CourseID
• CourseName
• Instructor
• Grade
• Department
• DepartmentHead

The functional dependencies in this relation include:

1. StudentID → StudentName Each student ID uniquely determines one student name.
2. CourseID → CourseName, Instructor, Department Each course ID uniquely determines the course name, instructor, and the department offering it.
3. Department → DepartmentHead Each department has exactly one department head.
4. {StudentID, CourseID} → Grade A student's grade is determined by the combination of student ID and course ID.
5. StudentID → Department (if each student belongs to one department) Each student belongs to exactly one department.

From these, we can derive additional dependencies like:

• StudentID → DepartmentHead (transitive: StudentID → Department → DepartmentHead)
• CourseID → DepartmentHead (transitive: CourseID → Department → DepartmentHead)

This relation has multiple anomalies and should be normalized into:

• Students(StudentID, StudentName, Department)
• Courses(CourseID, CourseName, Instructor, Department)
• Departments(Department, DepartmentHead)
• Enrollments(StudentID, CourseID, Grade)

Q2: How would you identify the candidate keys in a relation using functional dependencies?

A: To identify candidate keys using functional dependencies, follow these steps:

For a relation R with attributes {A, B, C, D, E} and functional dependencies:

• A → B
• BC → E
• D → A

Step 1: Find attributes that don't appear on the right side of any dependency. These are called "prime attributes" and must be part of any key. In this case, C and D don't appear on the right side.

Step 2: Compute the closure of potential candidate keys starting with these prime attributes:

• For {C}:
  • Initial: C+ = {C}
  • No more attributes can be added
  • C+ ≠ {A, B, C, D, E}, so {C} is not a candidate key
• For {D}:
  • Initial: D+ = {D}
  • D → A, so D+ = {D, A}
  • A → B, so D+ = {D, A, B}
  • BC → E, and we have B and C in D+, so D+ = {D, A, B, E}
  • D+ ≠ {A, B, C, D, E}, so {D} is not a candidate key
• For {C, D}:
  • Initial: {C, D}+ = {C, D}
  • D → A, so {C, D}+ = {C, D, A}
  • A → B, so {C, D}+ = {C, D, A, B}
  • BC → E, and we have B and C, so {C, D}+ = {C, D, A, B, E}
  • {C, D}+ = {A, B, C, D, E}, so {C, D} is a candidate key

Step 3: Check for minimality by trying to remove each attribute:

• We've shown {C} alone is not a key
• We've shown {D} alone is not a key
• Therefore, {C, D} is minimal and is a candidate key

In this example, {C, D} is the only candidate key. The process generalizes to finding all minimal sets of attributes whose closures include all attributes of the relation.

Q3: Explain the concept of closure of attributes with an example.

A: The closure of a set of attributes X, denoted as X+, is the set of all attributes functionally determined by X through direct or derived functional dependencies.

Example: Consider a relation R with attributes {A, B, C, D, E} and functional dependencies:

• A → B
• B → C
• CD → E
• E → A

To find the closure of {B}, denoted as B+:

Step 1: Initially, B+ = {B} Step 2: Using B → C, add C to B+, so B+ = {B, C} Step 3: We have both C and D needed for CD → E, but D is not in B+, so we can't add E yet Step 4: No more attributes can be added, so B+ = {B, C}

To find the closure of {B, D}, denoted as {B, D}+:

Step 1: Initially, {B, D}+ = {B, D} Step 2: Using B → C, add C to {B, D}+, so {B, D}+ = {B, D, C} Step 3: Using CD → E, add E to {B, D}+, so {B, D}+ = {B, D, C, E} Step 4: Using E → A, add A to {B, D}+, so {B, D}+ = {B, D, C, E, A} Step 5: Using A → B, we get B, but it's already in the closure Step 6: No more attributes can be added, so {B, D}+ = {A, B, C, D, E}

Since {B, D}+ contains all attributes of R, {B, D} is a superkey. To determine if it's a candidate key, we would check if any proper subset of {B, D} also determines all attributes.

Q4: How do you determine if a set of functional dependencies is minimal?

A: A set of functional dependencies F is minimal (or a minimal cover) if it satisfies all of these properties:

1. Right-hand singularity: Each dependency in F has a single attribute on the right-hand side.
2. Left-hand irreducibility: No attribute can be removed from any left-hand side without changing the closure.
3. Dependency irreducibility: No dependency can be removed from F without changing the closure.

Example: Consider the set F = {A → B, A → C, AB → D, D → E}

Step 1: Check for right-hand singularity:

• All dependencies have a single attribute on the right side, so this property is satisfied.

Step 2: Check for left-hand irreducibility:

• For A → B: A cannot be reduced further
• For A → C: A cannot be reduced further
• For AB → D: Check if A → D or B → D holds through the closure of A or B.
  • A+ = {A, B, C} (does not include D)
  • B+ = {B} (does not include D)
  • Neither A nor B alone determine D, so AB cannot be reduced

Step 3: Check for dependency irreducibility:

• Try removing A → B and check if it can be derived from the remaining dependencies.
  • From the remaining {A → C, AB → D, D → E}, we cannot derive A → B
• Try removing A → C and check if it can be derived.
  • From the remaining {A → B, AB → D, D → E}, we cannot derive A → C
• Continue this process for each dependency

Since no dependency can be removed or reduced without changing the closure, F is minimal.

Q5: How do functional dependencies help in database normalization with a real example?

A: Functional dependencies guide the normalization process by identifying and eliminating problematic dependencies. Let's illustrate with a real example:

Consider an Orders relation with attributes: {OrderID, CustomerID, CustomerName, CustomerAddress, ProductID, ProductName, ProductPrice, Quantity, TotalPrice}

The functional dependencies are:

1. OrderID → CustomerID, OrderDate
2. CustomerID → CustomerName, CustomerAddress
3. ProductID → ProductName, ProductPrice
4. {OrderID, ProductID} → Quantity
5. {ProductID, Quantity} → TotalPrice (assuming TotalPrice = ProductPrice × Quantity)

Problems with this schema:

• Customer information repeats for multiple orders from the same customer
• Product information repeats for each order of the same product
• Update anomalies: Changing a product's price requires updating multiple rows

Normalization using functional dependencies:

First Normal Form (1NF):

• Already in 1NF assuming all attributes are atomic

Second Normal Form (2NF): Remove partial dependencies:

• The dependency {ProductID, Quantity} → TotalPrice is a partial dependency if {OrderID, ProductID} is the primary key
• Split into:
  • Orders(OrderID, CustomerID, OrderDate)
  • OrderDetails(OrderID, ProductID, Quantity, TotalPrice)
  • Products(ProductID, ProductName, ProductPrice)
  • Customers(CustomerID, CustomerName, CustomerAddress)

Third Normal Form (3NF): Remove transitive dependencies:

• In OrderDetails, {ProductID, Quantity} → TotalPrice is a transitive dependency through ProductPrice
• Recalculate TotalPrice during queries instead of storing it:
  • OrderDetails(OrderID, ProductID, Quantity)

Final normalized schema:

• Customers(CustomerID, CustomerName, CustomerAddress)
• Products(ProductID, ProductName, ProductPrice)
• Orders(OrderID, CustomerID, OrderDate)
• OrderDetails(OrderID, ProductID, Quantity)

This normalized design eliminates redundancy and anomalies by properly handling all functional dependencies.

Functional Dependency in DBMS Example

Q1: What is an example of a fully functional dependency?

A: A fully functional dependency occurs when an attribute depends on the entire composite key, and removing any part of the key breaks the dependency.

Example: In a university enrollment system with relation Enrollments(StudentID, CourseID, Semester, Grade):

• The primary key is the composite key {StudentID, CourseID, Semester}
• Grade depends on the combination of all three: {StudentID, CourseID, Semester} → Grade
• This is a fully functional dependency because:
  • {StudentID, CourseID} alone doesn't determine Grade (a student might take the same course in different semesters with different grades)
  • {StudentID, Semester} alone doesn't determine Grade (a student takes multiple courses in one semester)
  • {CourseID, Semester} alone doesn't determine Grade (many students take the same course in the same semester)

The Grade attribute can only be determined when all three key attributes (StudentID, CourseID, and Semester) are known, making this a fully functional dependency.

Q2: Give an example of a partial functional dependency and how to resolve it.

A: A partial functional dependency occurs when an attribute depends only on part of a composite key.

Example: Consider a relation CourseRegistration(StudentID, CourseID, StudentName, CourseName, Grade):

• The composite primary key is {StudentID, CourseID}
• The functional dependencies include:
  • StudentID → StudentName (partial dependency: depends only on part of the key)
  • CourseID → CourseName (partial dependency: depends only on part of the key)
  • {StudentID, CourseID} → Grade (fully functional dependency)

To resolve these partial dependencies and achieve 2NF:

1. Create separate relations for each part of the key and its dependent attributes:
   • Students(StudentID, StudentName)
   • Courses(CourseID, CourseName)
   • Registrations(StudentID, CourseID, Grade)
2. Establish relationships through foreign keys:
   • StudentID in Registrations references StudentID in Students
   • CourseID in Registrations references CourseID in Courses

This decomposition eliminates the partial dependencies while preserving all the original information and relationships.

Q3: Provide an example of a transitive functional dependency and how to eliminate it.

A: A transitive functional dependency occurs when a non-key attribute depends on another non-key attribute, which depends on the primary key.

Example: Consider a relation Employee(EmpID, DepartmentID, DepartmentName, DepartmentLocation):

• The primary key is EmpID
• The functional dependencies include:
  • EmpID → DepartmentID (employee works in exactly one department)
  • DepartmentID → DepartmentName (department has exactly one name)
  • DepartmentID → DepartmentLocation (department has exactly one location)

The transitive dependency is:

• EmpID → DepartmentID → DepartmentName
• EmpID → DepartmentID → DepartmentLocation

To eliminate these transitive dependencies and achieve 3NF:

1. Decompose the relation into:
   • Employee(EmpID, DepartmentID)
   • Department(DepartmentID, DepartmentName, DepartmentLocation)
2. Establish relationships through foreign keys:
   • DepartmentID in Employee references DepartmentID in Department

This decomposition eliminates the transitive dependencies, reducing redundancy and potential anomalies. Each department's name and location are stored only once, regardless of how many employees work in that department.

Q4: Give an example of functional dependencies in an e-commerce database.

A: In an e-commerce database, functional dependencies define important relationships between various entities. Here's an example:

Consider an e-commerce database with these tables:

1. Orders relation with attributes: {OrderID, CustomerID, OrderDate, ShippingAddress, TotalAmount, PaymentMethod, OrderStatus} Functional dependencies:
   • OrderID → CustomerID, OrderDate, ShippingAddress, TotalAmount, PaymentMethod, OrderStatus
   • CustomerID → ShippingAddress (assuming default address)
2. OrderItems relation with attributes: {OrderID, ProductID, Quantity, UnitPrice, Subtotal} Functional dependencies:
   • {OrderID, ProductID} → Quantity, UnitPrice, Subtotal
   • ProductID → UnitPrice (at time of order)
   • {ProductID, Quantity} → Subtotal (calculated field)
3. Products relation with attributes: {ProductID, ProductName, CategoryID, Description, Price, StockQuantity, SupplierID} Functional dependencies:
   • ProductID → ProductName, CategoryID, Description, Price, StockQuantity, SupplierID
   • CategoryID → CategoryName (if CategoryName were included)
   • SupplierID → SupplierName, SupplierContact (if included)
4. Customers relation with attributes: {CustomerID, Name, Email, Phone, DefaultAddress, AccountCreationDate} Functional dependencies:
   • CustomerID → Name, Email, Phone, DefaultAddress, AccountCreationDate
   • Email → CustomerID (assuming email is unique)

These functional dependencies guide the design of properly normalized relations in the e-commerce database, ensuring data integrity while minimizing redundancy.

Q5: Explain the concept of lossless decomposition using a functional dependency example.

A: Lossless decomposition is a process where a relation is broken down into smaller relations such that the original relation can be reconstructed by joining the smaller relations without losing or gaining any tuples.

Example: Consider a relation StudentCourse(StudentID, StudentName, CourseID, CourseName, InstructorID, InstructorName) with functional dependencies:

• StudentID → StudentName
• CourseID → CourseName
• InstructorID → InstructorName
• CourseID → InstructorID

Decomposing this into:

• Students(StudentID, StudentName)
• Courses(CourseID, CourseName, InstructorID)
• Instructors(InstructorID, InstructorName)
• Enrollments(StudentID, CourseID)

To prove this is a lossless decomposition, we need to check that:

1. There's at least one decomposed relation that contains a candidate key of the original relation, or
2. The intersection of any two decomposed relations contains a key for at least one of them

For our decomposition:

• The original candidate key {StudentID, CourseID} is preserved in the Enrollments relation
• When joining Students and Enrollments, they share StudentID which is a key for Students
• When joining Courses and Enrollments, they share CourseID which is a key for Courses
• When joining Instructors and Courses, they share InstructorID which is a key for Instructors

Therefore, this decomposition is lossless. When we join all these relations on their shared attributes, we'll get back exactly the same tuples as in the original relation, with no loss or addition of information