Data Management Quiz 2

What is a relation?

A table.

What is an attribute?

A column in a table.

What is a tuple?

A row in a table.

What does a schema describe?

A table’s name and columns.

What is a primary key (PK)?

The candidate key selected to identify each row. Its complete value must be unique, and none of its components can be NULL.

Why can CustomerID not repeat in Customers?

CustomerID is the primary key, so each customer must have a unique identifier.

What is a foreign key (FK)?

One or more columns that reference a primary key or another eligible unique key in another (or the same) table.

Why can CustomerID repeat in Orders?

Many orders may belong to the same customer, so Orders.CustomerID can repeat as a foreign key.

What is a candidate key?

A key that identifies a row with no unnecessary field.

What is a superkey?

A set of attributes that identifies a row; it may contain extra, unnecessary attributes.

What is a composite key?

A key containing two or more columns, such as OrderID + LineNo.

What is a prime attribute?

An attribute that belongs to at least one candidate key.

What is a non-prime attribute?

An attribute that belongs to no candidate key.

What is entity integrity?

The primary key must be unique and non-NULL; every record has an identity.

What is referential integrity?

A non-NULL foreign key must match an existing referenced key.

What is a domain constraint?

A rule limiting a column to allowed values, such as a data type, range, or approved category.

What is NULL?

A marker for a missing or inapplicable value; it is not zero and is not an empty text string.

When might NULL be appropriate?

When information is unknown, not yet recorded, or optional under the design, such as an unshipped order’s ShippedDate.

What does NOT NULL mean?

The field is required and cannot contain NULL.

What is normalization?

Organizing tables using relationships between columns to reduce undesirable redundancy and dependencies.

Why normalize a database?

To reduce redundancy and help prevent update, insertion, and deletion anomalies; it also improves consistency and maintainability.

Does normalization guarantee faster queries?

No. The guide says normalization does not guarantee that every query runs faster.

What is a lossless split?

A table decomposition whose joins can rebuild the original rows without losing facts or inventing new combinations.

What is a functional dependency (FD)?

X → Y means any two legal rows with the same X values must have the same Y values.

In X → Y, what is the determinant?

X; it is the attribute or set of attributes that determines Y.

What does CustomerID → CustomerName mean?

A given CustomerID fixes one current CustomerName. It does not mean names are unique.

Does a functional dependency imply causation or describe a join?

No. It states a rule about values; it does not imply causation or describe a table join.

What is the correct order of normal forms?

1NF → 2NF → 3NF → BCNF.

What is First Normal Form (1NF)?

Each cell contains one intended scalar value, not a list, and there are no numbered repeating groups such as Item1 and Item2.

Do repeated scalar values in different rows violate 1NF?

No. Repeated scalar names across rows do not by themselves violate 1NF.

What is Second Normal Form (2NF)?

The table is in 1NF, and every non-prime attribute depends on the whole of every candidate key, not just a proper subset.

What is a partial dependency?

A non-prime attribute depends on only a proper subset of a candidate key rather than the whole key.

Give the guide’s example of a partial dependency.

With key {OrderID, ProductID}, OrderID → OrderDate is partial; keep order dates in Orders and pair-specific quantities in OrderLines.

What is Third Normal Form (3NF)?

The table is in 2NF and has no disallowed transitive dependency. For every non-trivial FD X → A, X must be a superkey or A must be prime.

What is a transitive dependency in the guide’s example?

OrderID → CustomerID → CustomerName; if CustomerID does not identify an order, store CustomerName in Customers rather than repeating it in Orders.

What is Boyce–Codd Normal Form (BCNF)?

For every non-trivial functional dependency, the determinant must be a superkey.

How is BCNF stricter than 3NF?

BCNF removes the 3NF prime-attribute exception: every non-trivial FD’s determinant must be a superkey.

What does non-trivial mean for an FD?

The arrow determines a field that is not already included on its left side.

What is a proper subset of a key?

A subset that omits at least one field from the full key.

Why must you check every candidate key when testing 2NF?

An alternate composite candidate key can have a partial dependency even if the chosen primary key is single-column.

What is data redundancy?

Unnecessary repetition of the same fact. Repeating identifiers to link tables is useful; copying a customer’s current phone number onto every order is avoidable redundancy.

What is an update anomaly?

One change requires updating several copies of a fact; missing one copy can leave conflicting values.

What is an insertion anomaly?

A new fact cannot be recorded without also supplying an unrelated fact, such as needing an OrderID to add a customer who has no order.

What is a deletion anomaly?

Deleting one record unintentionally removes another fact, such as deleting a customer’s last order and losing the only stored phone number.

How does the guide repair the OrderContact redundancy problem?

Separate Customers(CustomerID, CurrentPhone) from Orders(OrderID, CustomerID). CustomerID is the customer PK and an order FK.

What are the six SDLC phases in order?

Requirements gathering → Design → Implementation → Testing → Deployment → Maintenance.

What happens during requirements gathering?

Learn what users need and document business rules: what data is stored, who uses it, and what must be unique.

What happens during database design?

Create the conceptual ER model, then plan tables, keys, constraints, and normalization.

What does ER mean?

Entity–relationship: a model of business objects and how they relate.

What happens during implementation?

Build the database structures and application, then load or migrate the required data.

What happens during testing?

Check that functions, data rules, and performance meet requirements; test invalid as well as valid inputs.

What happens during deployment?

Release the working system for real use and prepare users and support staff.

What happens during maintenance?

Fix defects, adapt to changing needs, and keep the system reliable.

Why is maintenance typically the longest SDLC phase?

It continues throughout the system’s operational life.