Ch 4. Relational Database Design

What is a primary key?

An identifier (often a character, number, or combination of both) that applies to one relevant record in a database and must be entirely unique.

What is the main structural purpose of a primary key?

To ensure data is kept separate from other collections of information and provide an absolute reference to a record on a row.

What is the standard SQL syntax to define an integer primary key?

id INT PRIMARY KEY

What is a foreign key in SQL?

A field that directly identifies another table by holding the same value as the primary key of that related table.

Does a foreign key have to be unique?

No, they do not have to be unique, nor are they always used strictly for organization.

How are primary keys visually identified in a database diagram?

They have a little key symbol displayed to the left of them.

Can a database table have more than one primary key?

A table can technically have more than one, but usually, we just use one.

What is an intersection table used for?

A table used to join other tables together, which helps reduce duplicate records between them.

What is a composite key?

A configuration where two values combined will create a unique key rather than using a single primary key.

In database table terminology, what does the word 'schema' refer to?

The actual name of the table itself.

What are the strict best-practice naming conventions for columns?

Columns must be lowercase, separated using underscores, and use the term 'ID' when referring to a unique identifier.

Do database tables require rows to exist?

No, database tables do not require any rows to exist.

What is a field?

The individual value located at the exact intersection of a tuple (row) and an attribute (column).

Does every field in a database table require data?

No, not all fields require data. Database language is used to indicate which fields can be left blank or must contain data.

What are the 5 sequential steps to design a relational database?

1. Determine the purpose of the database 2. Data collection (listing entities) 3. Creating tables and identifying primary keys 4. Creating table relationships 5. Choosing column data types.

What real-world example does the lesson use to explain an entity and its attributes?

A National Identity Card (the card is the entity; the descriptive details printed on it are the attributes).

What basic data types are commonly chosen for table columns?

Date/times, integers, strings, decimals, and booleans.

What is a Normal Form?

The specific stage at which a table is organized.

What are the strict requirements for First Normal Form (1NF)?

Columns must have single values, columns must have unique names, values down an attribute must share the same data type, and no two records can be identical.

What are the strict requirements for Second Normal Form (2NF)?

The table must be in 1NF, and there can be no partial dependencies of any column on the primary key.

What are the strict requirements for Third Normal Form (3NF)?

The table must be in 2NF, all non-primary fields must depend entirely on the primary field, and all transitive dependencies are completely removed.