Relational Databases Chapter 14

Storing the same information multiple times within different tuples, leading to wasted storage space.

Unintended or unexpected problems that arise when trying to update data in a database.

A type of update anomaly where it's challenging to add a new tuple without having complete information about a related entity. For example, adding a new employee to a department without the department's complete details.

A type of update anomaly where deleting a tuple unintentionally removes information about related entities. For example, deleting the last employee from a department removes all information about the department.

A type of update anomaly that occurs when updating one tuple unexpectedly causes other tuples to become inconsistent. For example, changing an employee's department inadvertently changes the information associated with all employees in the department.

A relation schema where the meaning of attributes is clear and concise. Each tuple (row) represents a single entity or relationship instance.

Foreign keys are used to connect entities and relationships. For example, a dept_id foreign key in the employee relation would connect an employee to their department.

Attributes from different entities should not be mixed together within the same relation. Keep the data consistent and organized.

Attributes of entities and relationships should be stored in separate tables whenever possible. Entity attributes belong in the main entity table, while relationship attributes belong in the relationship table.

A relation must have a clear, unambiguous meaning. Each table should tell a story.

Avoid having unnecessary duplicates in the table data. This minimizes storage space and potential data inconsistencies.

Aim to minimize NULL values in tuples (rows). NULLs can indicate missing data and can lead to inconsistencies.

Design the schema so that it's impossible to create incorrect or nonsensical data combinations.

A relationship between two sets of attributes where the first uniquely determines the second.

When updating a value, you need to update multiple tuples to maintain consistency.

The process of restructuring tables to eliminate data redundancy and anomalies.

A table is in 1NF if all attributes are atomic, meaning they cannot be further divided.

A table is in 2NF if it is in 1NF and all non-key attributes are fully dependent on the primary key.

A table is in 3NF if it is in 2NF and all non-key attributes are independent of each other.

Normalizing tables to eliminate anomalies and improve data integrity.

A set of attributes in a relation schema where no two tuples have the same values for all attributes in the set. It guarantees uniqueness and consistency for identifying each tuple.

A superkey where removing any attribute would make it no longer a superkey. It's the smallest possible superkey.

Multiple keys that can uniquely identify tuples in a relation.

A specific normal form where a relation is free of partial dependencies. This means no non-key attribute is dependent on only part of a composite key.

A normal form where a relation is free of both partial dependencies and transitive dependencies. It means no non-key attribute is dependent on another non-key attribute.

A normal form requiring that every determinant is a candidate key. It eliminates redundant information and helps maintain data integrity.

The process of converting a lower normal form relation into a higher normal form to improve data integrity and reduce redundancy.

The intentional lowering of a relation's normal form for better performance or efficiency. It might introduce redundancy but can speed up queries.

A property that is part of a table's primary key.

A functional dependency where removing any attribute from the left side of the dependency causes the dependency to fail.

A functional dependency where a non-key attribute depends only on a part of the primary key.

A functional dependency where an attribute indirectly depends on the primary key. It is derived from two other dependencies, one from the primary key to an intermediate attribute and another from the intermediate attribute to the dependent attribute.

A functional dependency (FD) describes a relationship between attributes in a table. It states that if two tuples have the same value for attribute X, they must also have the same value for attribute Y. This means that the value of attribute X determines the value of attribute Y.

An FD constraint is an rule that defines a relationship between attributes in a table. It sets a rule for how data is linked within a table. If a constraint is violated, the database will prevent the change from being made.

FDs are derived by examining the actual data in the table and identifying patterns. If a certain data point always leads to a specific other data point, it suggests a potential FD. If there are any inconsistencies, the proposed FD is invalid.

This constraint indicates that knowing an employee's Social Security Number (SSN) allows you to determine their name. We can write this as SSN → Employee Name.

This constraint indicates that a project's number (PNUMBER) determines both its name (PNAME) and location (PLOCATION). We can write this as PNUMBER →→ {PNAME, PLOCATION}.

A composite dependency involves multiple attributes on the left side of the arrow. This FD means that the combination of an employee's Social Security Number (SSN) and a project number (ProjectNum) determines the hours worked (HOURS). We can write this as {SSN, PNUMBER} →→ HOURS.

This FD may exist because if we know a particular Text, we may be able to determine the corresponding Course. The data supports this possibility.

These FDs are ruled out because the available data shows that the same Teacher can teach different Courses and different Texts, and the same Course can be taught using different Texts. This means a Teacher cannot uniquely determine either the Course or Text, and the same for Course and Text.