Logical Database Design & Relational Model – Review Flashcards

A comprehensive set of question-and-answer flashcards covering key concepts, definitions, rules, and examples from the lecture on logical database design, relational modeling, integrity constraints, mapping rules, normalization, and view integration.

What is the primary goal of logical database design?

To transform a conceptual data model into a logical data model that meets user needs for data sharing, flexibility, and ease of access while being compatible with a specific DBMS.

Which data model is most commonly used in logical database design and why?

The relational data model, because of its widespread adoption, its principles’ applicability to other models, and its compatibility with SQL.

Who introduced the relational data model and in what year?

E. F. Codd in 1970.

Name the two early prototype systems that demonstrated the relational model’s feasibility.

IBM System R and University of California, Berkeley Ingres.

What are the three core components of the relational data model?

Data structure (tables), data manipulation (relational algebra/SQL), and data integrity (constraints).

What is a relation in the context of the relational model?

A table with rows and columns that represents a set of tuples of the same type.

State five key properties every relation must satisfy.

1) Unique table name; 2) Each cell contains a single (atomic) value; 3) Each row is unique; 4) Each column name is unique; 5) Row and column order are insignificant.

Define primary key.

An attribute or combination of attributes that uniquely identifies each row of a table.

What is a composite primary key?

A primary key that consists of two or more attributes (e.g., EmpID + DependentName).

Define foreign key.

An attribute in one table that is the primary key of another table, used to represent relationships between tables.

What is entity integrity?

A rule requiring that every relation have a primary key and that primary key values are non-null and unique.

Explain referential integrity.

A constraint that ensures every non-null foreign key value matches a valid primary key value in the related table, or is null if the relationship is optional.

Give three possible actions when a parent row with dependents is deleted.

Cascading delete, prohibit deletion, or set the foreign key to null.

What is a domain in relational design?

The set of allowable values for a given attribute, defined by name, meaning, data type, size, and possibly range or list of values.

Why must multivalued attributes be removed from relations?

Because each cell must hold a single value; multivalued attributes cause violations of 1NF and introduce anomalies.

What is a well-structured relation?

A relation that contains minimal redundancy and allows insertion, update, and deletion without causing anomalies.

List three common data anomalies caused by poor design.

Insertion anomaly, deletion anomaly, and modification (update) anomaly.

What technique is used to convert poorly structured relations into well-structured ones?

Normalization.

Name the first three normal forms in order.

First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF).

What condition must a relation satisfy to be in 1NF?

All attributes contain only atomic (single) values; no repeating groups.

When is a relation in 2NF?

It is in 1NF and has no partial functional dependencies—every nonkey attribute depends on the whole primary key.

State the requirement for 3NF.

The relation is in 2NF and has no transitive dependencies—nonkey attributes depend only on the primary key.

What problem does Boyce-Codd Normal Form (BCNF) address?

Anomalies that occur when non-key attributes determine part of a composite candidate key or when multiple overlapping candidate keys exist.

Which higher normal form removes multivalued dependencies?

Fourth Normal Form (4NF).

Define functional dependency.

A relationship in which one attribute (or set) uniquely determines another attribute (A → B).

What is a determinant?

The attribute (or set of attributes) on the left side of a functional dependency that determines another attribute.

Differentiate between a candidate key and a primary key.

A candidate key uniquely identifies rows and is minimal; a primary key is the candidate key chosen by the designer to serve as the main identifier.

Why is a candidate key always a determinant?

Because knowing the candidate key uniquely determines all other attributes in the row.

Give an example of a partial functional dependency.

In INVOICE(OrderID, ProductID, OrderedQuantity, OrderDate…), OrderDate depends only on OrderID, part of the composite key (OrderID, ProductID).

Give an example of a transitive dependency.

CustomerName depends on CustomerID, which depends on OrderID; thus OrderID → CustomerID → CustomerName.

What is view integration (merging relations)?

Combining separately developed relations or views into a unified relational schema, resolving redundancies and inconsistencies.

List two naming problems encountered during view integration.

Synonyms (different names, same meaning) and homonyms (same name, different meaning).

How are many-to-many (M:N) binary relationships mapped to relations?

Create a new associative relation whose primary key is the combination of the primary keys of the two participating entities (plus any nonkey attributes).

Describe how a 1:M binary relationship is mapped.

Place the primary key of the ‘one’ side as a foreign key in the relation on the ‘many’ side.

How is a 1:1 binary relationship usually implemented?

Include the primary key of one entity as a foreign key in the other entity’s relation, typically in the optional side to avoid nulls.

What is a weak entity and how is it mapped?

An entity type that depends on another for its identity; mapped by combining its partial identifier with the owner’s primary key as a composite primary key and foreign key.

When is a surrogate key recommended?

When the natural key is long, composite, recycled, or otherwise cumbersome, or when simplifying complex composite primary keys.

Explain mapping of multivalued attributes.

Create a separate relation that contains the multivalued attribute plus the primary key of the parent entity as a composite primary key.

How are associative entities with their own identifier mapped?

The identifier becomes the primary key of the associative relation; the primary keys of participating entities become foreign keys.

Describe the mapping of a unary 1:M relationship.

Add a recursive foreign key in the same relation that references the relation’s primary key (e.g., ManagerID → EmployeeID).

How is a unary M:N relationship implemented?

Create an associative relation with two foreign keys both referencing the primary key of the original entity.

What is the strategy for representing supertype/subtype relationships in relations?

Create one relation for the supertype with the common attributes and a relation for each subtype containing its unique attributes and a primary key identical to the supertype’s key.

What attribute in a supertype is usually used as the subtype discriminator?

One or more attributes in the supertype relation that indicate to which subtype each instance belongs (e.g., EmployeeType).

Why is it important to reflect surrogate keys added during logical design back into the conceptual model?

To keep the conceptual and logical models consistent and avoid confusion during maintenance and future development.

What is the three-schema architecture useful for during database development?

It supports both top-down (conceptual to logical) and bottom-up (view integration, normalization) approaches by separating external, conceptual, and internal views.

Define insertion anomaly with an example.

Inability to add data because other data are missing; e.g., cannot insert a new course in EMPLOYEE2 without simultaneously entering an employee.

Define deletion anomaly with an example.

Unintended loss of data when other data are deleted; e.g., deleting an employee in EMPLOYEE2 removes records of courses they completed.

Define modification anomaly with an example.

Need to change the same fact in multiple places; e.g., updating a product price in every invoice row where that product appears.

Why are enterprise keys (object identifiers) sometimes preferred over business keys?

Enterprise keys remain unique across the entire database and simplify evolution, avoiding ripple effects when business keys change.

What command is used in SQL to enforce referential integrity?

FOREIGN KEY … REFERENCES clause in CREATE TABLE or ALTER TABLE statements.

In CREATE TABLE, how do you ensure an attribute cannot be null?

Add the NOT NULL constraint after the attribute definition.

Why might NoSQL solutions be considered despite RDBMS dominance?

They handle unstructured or highly scalable data requirements well, though they currently lack the widespread adoption and tooling of RDBMSs.

What role do CASE tools play in logical design?

They automate transforming EER diagrams into relational schemas but may need manual refinement for complex constructs like ternary relationships or supertype/subtype hierarchies.

During normalization, when would you stop at 3NF instead of progressing to 4NF or 5NF?

When the design meets requirements without multivalued or join dependencies and further decomposition offers little practical benefit.

What shortcut can designers take to go directly to 3NF?

Identify all determinants, make each a primary key of its own relation, and assign dependent attributes accordingly, ensuring no overlapping dependencies.

Why is documenting sample data for a relational schema beneficial?

It helps test design assumptions, check accuracy, improve user communication, and facilitate prototyping and query testing.