Schema Refinement: Functional Dependencies and Normal Forms in Database Design

What is the primary goal of schema refinement?

To improve database design by addressing issues such as anomalies and ensuring data integrity.

What are functional dependencies (FDs)?

FDs express the relationship where a set of attributes uniquely determines another set of attributes within a relation schema.

What is a lossless-join decomposition?

A decomposition of a relation that allows the original relation to be reconstructed without any loss of information.

What does dependency preservation mean in schema design?

It ensures that all functional dependencies are maintained in the decomposed relations.

What is Boyce-Codd Normal Form (BCNF)?

A normal form that requires every functional dependency X → Y to have X as a superkey.

What are the design principles for relational databases?

Relations should have semantic unity, avoid information repetition, minimize anomalies, and limit null values.

What is an example of a change anomaly?

Update problems, such as changing a supplier's name, can lead to inconsistencies if not managed properly.

What is the purpose of normalization in database design?

Normalization aims to improve the design by organizing data to reduce redundancy and dependency.

What is a superkey?

A set of attributes that can uniquely identify tuples in a relation.

What is a candidate key?

A minimal superkey; it is a set of attributes that uniquely identifies tuples without any unnecessary attributes.

What is the significance of integrity constraints in database design?

Integrity constraints help identify regularities in database instances that can indicate anomalies.

What is a schema's semantic unity?

It refers to the logical coherence of the data within a relation, ensuring that all attributes contribute to a meaningful whole.

What are spurious joins?

Joins that produce incorrect or unintended results due to improper schema design.

What is the role of the ER model in database design?

The ER model provides a conceptual framework for designing the database schema before mapping it to a relational model.

What does it mean for a schema to be good?

A good schema minimizes redundancy, avoids anomalies, and accurately represents the relationships between data.

What is the impact of excessive null values in a database?

Excessive null values can lead to confusion and complicate data retrieval and integrity.

How can schema decompositions avoid anomalies?

By breaking down relations into smaller, more manageable parts while retaining all necessary information.

What is the relationship between functional dependencies and keys?

Keys are derived from functional dependencies; a candidate key is a minimal set of attributes that functionally determines all other attributes.

What is an example of a functional dependency?

In a relation schema, if SIN determines EName, it can be expressed as SIN → EName.

What are the potential problems with a single-table schema?

It can lead to update, insert, and delete anomalies, as well as increased space requirements.

What does the term 'anomaly' refer to in database design?

An anomaly refers to inconsistencies or issues that arise during data operations like insertion, deletion, or updating.

What is the purpose of evaluating schemas?

To detect anomalies and assess the overall quality and effectiveness of the database design.

What is the significance of the parts/suppliers database example?

It illustrates the importance of schema design in managing relationships between parts, suppliers, and pricing.

What is the difference between a relation schema and an instance of a relation?

A relation schema defines the structure of the data, while an instance is a specific set of data that conforms to that structure.

What is a primary key?

A primary key is a candidate key chosen by the Database Administrator (DBA).

Define a superkey.

A superkey is a set of attributes such that no two tuples in an instance agree on their values for those attributes.

What is the relationship between superkeys and functional dependencies?

If K is a superkey for relation schema R, then the dependency K → R holds on R.

What does the closure of a set of functional dependencies F (denoted F +) represent?

F + is the set of all functional dependencies satisfied by every relational instance that satisfies F.

What are Armstrong's axioms in the context of functional dependencies?

Armstrong's axioms are reflexivity, augmentation, and transitivity, which are used to derive logical implications.

What does reflexivity state in Armstrong's axioms?

If Y is a subset of X, then X → Y.

What does augmentation state in Armstrong's axioms?

If X → Y, then XZ → YZ for any Z.

What does transitivity state in Armstrong's axioms?

If X → Y and Y → Z, then X → Z.

What is schema decomposition?

Schema decomposition is the process of breaking down a relation schema R into a collection of relation schemas R1, R2, ..., Rn such that R = R1 ∪ R2 ∪ ... ∪ Rn.

What are the criteria for a good schema decomposition?

A good decomposition does not lose information, complicate checking of constraints, or contain anomalies.

What condition must be met for a decomposition to be lossless?

The common attributes of the decomposed schemas must form a superkey for at least one of the schemas.

In the example of Grades decomposition, what was the issue with the natural join?

The natural join produced extra data (spurious tuples), indicating that information would be lost.

What is dependency preservation in schema decomposition?

Dependency preservation ensures that all functional dependencies can be enforced on the decomposed schema without needing to join tables.

Which decomposition is better for testing functional dependencies: D1 or D2?

Decomposition D1 is better because it allows testing of functional dependencies on individual tables without requiring joins.

What is the significance of the common attributes in a decomposition?

Common attributes must form a superkey for the decomposition to be considered lossless.

What is the implication of having a lossy decomposition?

A lossy decomposition can lead to loss of information when reconstructing the original relation from its decomposed parts.

How can additional rules for functional dependencies be derived?

Additional rules can be derived using Armstrong's axioms, such as union and decomposition.

What does the union rule state in functional dependencies?

If X → Y and X → Z, then X → YZ.

What does the decomposition rule state in functional dependencies?

If X → YZ, then X → Y and X → Z.

What does it mean if a functional dependency is sound?

It means that anything derived from F is also in F +.

What does it mean if a functional dependency is complete?

It means that anything in F + can be derived from F.

What is dependency preservation in database schema decomposition?

A decomposition is dependency preserving if there is an equivalent set of functional dependencies, none of which are interrelational.

Why is decomposition D1 preferred over D2?

Decomposition D1 allows testing functional dependencies FD1 and FD2 on separate tables R1 and R2, while D2 requires joining tables R1 and R3 to test FD2.

What is a 'good' relational database schema?

A good schema consists of independent facts in separate tables, with each relation schema having a primary key and mutually independent attributes.

What are the goals of transforming a schema into a normal form?

The goals are intuitive transformation and an anomaly-free, non-redundant representation of data.

What is the formal definition of BCNF?

A schema R is in BCNF if for every functional dependency (X → Y) in F+, either (X → Y) is trivial or X is a superkey of R.

How does BCNF avoid redundancy?

BCNF avoids redundancy by ensuring that attributes that functionally determine others are superkeys, preventing the need to repeat values.

What is the process for lossless-join BCNF decomposition?

The process involves replacing a relation Ri that violates BCNF with a new relation and adding the functional dependencies to the result until all relations satisfy BCNF.

What is a potential issue with BCNF decomposition?

There may be no efficient procedure for decomposition, and it is possible that no lossless join dependency preserving BCNF decomposition exists.

Provide an example of a schema that is not in BCNF.

R = {Sno, Sname, City, Pno, Pname, Price} is not in BCNF because Sno determines Sname and City but is not a superkey.

What are the functional dependencies in the example schema?

The functional dependencies are Sno → Sname, City; Pno → Pname; Sno, Pno → Price.

What is the result of decomposing a non-BCNF schema?

The schema can be decomposed into R1 = {Sno, Sname, City}, R2 = {Sno, Pno, Price}, and R3 = {Pno, Pname}, which is a lossless-join BCNF decomposition.

What does 1NF eliminate in a relational schema?

1NF eliminates relations within relations or relations as attributes of tuples.

What does 2NF eliminate in a relational schema?

2NF eliminates transitive functional dependencies of non-prime attributes to key attributes.

What does 3NF eliminate in a relational schema?

3NF eliminates partial and transitive functional dependencies of prime attributes to key attributes.

What is the importance of functional dependencies in schema refinement?

Functional dependencies provide clues for eliminating redundancies in a relational schema.

What is the significance of a superkey in BCNF?

A superkey is a set of attributes that can uniquely identify a tuple in a relation, ensuring no redundancy in data representation.