7 Basic Relational Database Design Free Online FlashCards

What is the first step in relational database design?

Translate requirements into entities, their attributes, relationships, and rules the data must follow.

What do tables, rows, and columns represent?

A table represents a kind of thing or association; a row is one instance, and a column is one attribute.

How should a many-to-many relationship be represented?

Use an associative table to connect the two entities. For orders and products, the `order_items` table represents which products belong to which orders.

What does a primary key guarantee?

A primary key is a column or set of columns that uniquely identifies each row and cannot be null.

What integrity rule does a foreign key enforce?

A foreign key references a primary or unique key in another table, enforcing that the referenced row exists.

What is a composite key?

A composite key uses multiple columns together to identify a row, such as `(order_id, product_id)` for an order line.

What does the `NOT NULL` constraint enforce?

`NOT NULL` makes a value required; it prevents a column from being left null.

What does the `UNIQUE` constraint prevent?

`UNIQUE` prevents duplicate values or duplicate combinations of values in a column or set of columns.

What kind of rule does a `CHECK` constraint express?

`CHECK` limits a value to an allowed range or set, such as requiring a quantity to be positive.

What does first normal form (1NF) require?

First normal form requires each cell to contain one value of its column’s kind; avoid lists in a cell and repeating columns.

What does second normal form (2NF) add to 1NF?

Second normal form requires 1NF and requires every non-key attribute to depend on the whole key, not just part of a composite key.

What dependency rule defines third normal form (3NF)?

Third normal form requires 2NF and requires non-key attributes to depend on the key, not on other non-key attributes.