6: Relational Databases

What are the three main skills emphasized in Chapter 6?

Understanding relations, keys, and constraints; identifying normalization problems and decomposing relations; and mapping ER/EER models to relational schemas.

Who formalized the relational model, and when?

Edgar F. Codd in 1970.

What mathematical foundations support the relational model?

Set theory and first-order predicate logic.

What is a relation?

A set of tuples, commonly displayed as a table.

What is a tuple?

One record containing attribute values.

What is a relational attribute?

A named property or column.

What is a cell value?

The value for a particular tuple and attribute.

What is a domain?

The set of admissible values for an attribute.

What can an ER entity type map to in the relational model?

A relation.

Can a relation represent an ER relationship rather than an entity type?

Yes. Mapped relationships can have their own relations.

What ER concept roughly corresponds to a tuple?

An entity instance.

What ER concept corresponds to a relational column?

An attribute type.

Can a formal relation contain duplicate tuples?

No. A relation is a set.

Do tuples in a formal relation have an inherent order?

No.

What is the degree of a relation?

The number of attributes.

What is the cardinality of a relation?

The number of tuples.

A relation has five columns and 200 rows. What are its degree and cardinality?

Degree 5 and cardinality 200.

What does it mean that a relation is a subset of the Cartesian product of its domains?

It contains some of the possible combinations of values from those domains.

Must a relation contain at least one tuple?

No. A relation can be empty.

Do SQL tables and query results automatically behave like mathematical sets?

No. Duplicates may occur unless prevented by constraints or removed.

What does NULL represent in this chapter?

Missing, unknown, irrelevant, or inapplicable information.

Is NULL equivalent to zero or an empty string?

No. Zero and an empty string are actual values.

What does the domain constraint require?

Each attribute contains one atomic, single-valued value from its domain, subject to permitted NULLs.

Why does storing two separate course names in one cell violate the chapter’s atomicity requirement?

The cell contains a collection of values instead of one atomic, single-valued value.

What is a superkey?

A set of attributes that uniquely identifies a tuple, possibly including unnecessary attributes.

What is a candidate key?

A minimal superkey.

What does “minimal” mean for a candidate key?

No attribute can be removed without losing uniqueness.

Does minimal mean choosing the candidate key with the fewest attributes among all candidates?

No. Each candidate key must have no removable attributes, even if another candidate key has fewer attributes.

What is a primary key?

The candidate key selected as the principal identifier.

What is an alternative key?

A candidate key not selected as the primary key.

What is a foreign key?

Attributes that reference a key in another relation or in the same relation.

How does a candidate key differ from a superkey?

A candidate key has no unnecessary attributes; a superkey may contain them.

How does a primary key differ from an alternative key?

Both are candidate keys, but only the primary key is selected as the principal identifier.

If StudentNr is unique, is {StudentNr, Name} a superkey?

Yes, because StudentNr already uniquely identifies the tuple.

If StudentNr alone is unique, is {StudentNr, Name} a candidate key?

No. Name is unnecessary, so the set is not minimal.

If StudentNr and Email are individually unique and required, what are the candidate keys?

{StudentNr} and {Email}.

If StudentNr is selected as primary key and Email is another candidate key, what is Email?

An alternative key.

What is a prime attribute?

An attribute belonging to at least one candidate key.

What is a non-prime attribute?

An attribute belonging to no candidate key.

Does prime mean belonging only to the selected primary key?

No. Membership in any candidate key makes an attribute prime.

StudentNr and Email are candidate keys, while Name may repeat. Which attributes are prime?

StudentNr and Email.

StudentNr and Email are candidate keys, while Name may repeat. Which attribute is non-prime?

Name.

What are the four relational constraints discussed in this chapter?

Domain, key, entity integrity, and referential integrity.

What does the key constraint require?

Keys uniquely identify tuples.

What does entity integrity require?

No component of the primary key may be NULL.

Can one component of a composite primary key be NULL?

No. Entity integrity applies to every component.

What does referential integrity require?

A non-NULL foreign-key value must match a referenced key value.

A purchase order contains a non-NULL supplier number that does not exist in SUPPLIER. Which constraint is violated?

Referential integrity.

A primary-key component is NULL. Which constraint is violated?

Entity integrity.

Is a foreign key automatically unique?

No. Many tuples may reference the same parent.

Is a foreign key automatically NOT NULL?

No. NOT NULL must be required separately when participation is mandatory.

Can a foreign key reference its own relation?

Yes. This is a self-referencing foreign key.

What do the slides say about the domains of foreign keys and referenced keys?

Their domains must match.

In practical SQL systems, must foreign keys always reference the selected primary key?

No. They can also reference qualifying unique keys.

If every purchase order must have a supplier, what additional constraint is needed on its supplier FK?

NOT NULL.

How do FK, UNIQUE, and NOT NULL constraints differ?

FK requires a valid referenced value; UNIQUE prevents repeated values; NOT NULL requires a value.

Why normalize relations?

To reduce unnecessary redundancy and the insertion, deletion, and update anomalies it causes.

What is an insertion anomaly?

A design prevents recording one fact independently without supplying unrelated or missing information.

What is a deletion anomaly?

Deleting one fact unintentionally removes another fact that should be retained.

What is an update anomaly?

A repeated fact must be changed in multiple rows, creating a risk of inconsistent copies.

How can repeated supplier details cause an insertion anomaly?

A supplier cannot be recorded independently without creating or leaving missing supply information.

How can repeated supplier details cause a deletion anomaly?

Deleting the supplier’s last supply row also deletes the only stored supplier details.

How can repeated supplier details cause an update anomaly?

Changing an address requires updating multiple rows, and missed rows retain the old address.

What are goals of good relational design?

Clear meaning for each relation, fewer repeated facts, fewer unnecessary NULLs, and reduced risk of inconsistent updates.

Is normalization simply the process of making tables smaller?

No. It is driven by dependencies and business rules.

What does the functional dependency X → Y mean?

If two tuples agree on X, they must agree on Y.

What is the determinant in X → Y?

X, the attributes on the left side.

Does X → Y imply that X uniquely identifies an entire tuple?

Not necessarily. X determines Y but may not determine every attribute in the relation.

Does X → Y automatically imply Y → X?

No. Functional dependencies are directional.

What does SSN → EmployeeName mean?

Each SSN determines one employee name.

What does ProjectNr → ProjectName mean?

Each project number determines one project name.

What does {SSN, ProjectNr} → Hours mean?

Hours are determined by the employee–project combination.

Where should functional dependencies come from?

Business rules governing all valid states of the relation.

Why is a small data sample insufficient to establish a functional dependency?

The apparent dependency may be accidental and fail for other valid records.

What is a full functional dependency?

Every attribute in the determinant is needed to determine the dependent attributes.

What is a partial functional dependency?

A proper subset of the determinant still determines the dependent attributes.

Why is {SSN, ProjectNr} → ProjectName partial when ProjectNr → ProjectName?

SSN is unnecessary for determining ProjectName.

What is a transitive dependency in the normalization examples?

A key determines a non-key fact through an intermediate non-key determinant.

What does SSN → DeptNr → DeptName illustrate?

A transitive dependency of DeptName on SSN.

What is a trivial functional dependency?

A dependency whose right side is a subset of its left side.

Why is {SSN, Name} → SSN trivial?

SSN is already included in the determinant.

What is the rule for first normal form (1NF) in this chapter?

Every attribute value is atomic and single-valued.

How should a modeled composite name be represented when mapping it to atomic components?

Use separate component attributes, such as FirstName and LastName.

How should multiple department locations be represented instead of storing a list in one cell?

Create DEPT_LOCATION(DeptNr, Location), with one row per department–location pairing.

What is the primary key of DEPT_LOCATION(DeptNr, Location) under the chapter’s assumptions?

(DeptNr, Location).

What is the foreign key in DEPT_LOCATION(DeptNr, Location)?

DeptNr references DEPARTMENT.DeptNr.

What does one row of DEPT_LOCATION represent?

One department–location pairing.

What is the rule for second normal form (2NF)?

The relation is in 1NF, and every non-prime attribute depends on the whole of every candidate key.

What problem does 2NF eliminate?

Partial dependencies of non-prime attributes on candidate keys.

Does 2NF consider only the selected primary key?

No. It considers every candidate key.

Can a 1NF relation whose candidate keys are all single-attribute keys have an ordinary partial-key dependency problem?

No.

WORK has key (SSN, ProjectNr), and ProjectNr → ProjectName. Why does ProjectName violate 2NF?

It is a non-prime attribute determined by only part of the candidate key.

How should WORK(SSN, ProjectNr, ProjectName, Hours) be decomposed when ProjectNr determines ProjectName?

WORKS_ON(SSN, ProjectNr, Hours) and PROJECT(ProjectNr, ProjectName).

What is the primary key of WORKS_ON(SSN, ProjectNr, Hours)?

(SSN, ProjectNr).

What is the primary key of PROJECT(ProjectNr, ProjectName)?

ProjectNr.

Which foreign key connects WORKS_ON to PROJECT?

WORKS_ON.ProjectNr references PROJECT.ProjectNr.

Why does Hours remain in WORKS_ON after the 2NF decomposition?

It depends on the full employee–project combination.

What is the simplified 3NF rule for the straightforward examples in this chapter?

Be in 2NF and remove transitive dependencies of non-prime attributes on the key.

What is the precise 3NF test?

For every non-trivial dependency X → A, X must be a superkey or A must be prime.

EMPLOYEE has key SSN, with SSN → DeptNr and DeptNr → DeptName. What violates 3NF?

DeptName depends transitively on SSN through DeptNr.