Chapter 2 - The Relational Model

Question 1

What is an Entity?

Answer 1

Something of importance to the user that needs to be represented in a database. It can be represented by a single table.

Question 2

What is a Relation?

Answer 2

A two-dimensional table consisting of rows and columns

Question 3

What are the Characteristics of a Relation? (1-4)

Answer 3

1. Rows contain data about an entity
2. Columns contain data about attributes of the entity
3. Cells of the table hold a single value
4. All entries in a column are of the same kind

Question 4

What are the Characteristics of a Relation? (5-8)

Answer 4

5. Each column has a unique name
6. The order of the columns is unimportant
7. The order of the rows is unimportant
8. No two rows may hold identical sets of data values

Question 5

What are some examples of Poorly-Formed Relations?

Answer 5

Multiple entries per cell
The order of the rows affects the table
Different kinds of column entries
Variable-length column values

Question 6

How do we Notate Relation Structures?

Answer 6

Relation names are in ALL CAPS (and always singular; if two words, separate by an underscore)
A column name is with the first letter capitalized (if two words, run together LikeThis)
The primary key is underlined
All the attributes of a table are listed in parentheses
Foreign keys are in italics

Question 7

What is a database schema?

Answer 7

A database schema is the design on which a database and its associated applications are built.

Question 8

What are the Synonyms for Tables, Rows, and Columns?

Answer 8

Tables = Files = Relations
Rows = Records = Tuples
Columns = Fields = Attributes

Question 9

What is a Key?

Answer 9

One or more columns of a relation that is used to identify a row. A key can be unique (primary) or nonunique (foreign)

Question 10

What are Composite and Candidate Keys?

Answer 10

A composite key contains two or more attributes.

A candidate key uniquely identifies each row in a relation.

Question 11

What is a Primary Key (PK)?

Answer 11

A candidate key that is chosen as the key that the DBMS will use to uniquely identify each row in a relation. The PK will be underlined.

Question 12

What is a Surrogate Key?

Answer 12

A column with a unique, DBMS-assigned identifier that has been added to a table to be the primary key.

Example, with an address it takes street, city, zip, etc. to identify a unique row. A surrogate key might add in a PropertyID to function as a unique identifier in a single row.

Question 13

What is a Foreign Key?

Answer 13

A primary key of another table that has been placed in the current table to represent a relationship between the tables.

Question 14

What is the Referential Integrity Constraint with Foreign Keys?

Answer 14

A referential integrity constraint states that every value of a foreign key must match a value of an existing primary key.

Question 15

What is a Null Value? What are some Issues with having Null Values?

Answer 15

A null value is a missing value in a cell in a relation.

The problem with null values is that it is ambiguous. Is it that no value is appropriate? Or is the value known, but not entered? Or is the value unknown, and therefore not entered?

Question 16

How can you Prevent Null Values from appearing in the Database?

Answer 16

You can eliminate null values by requiring an attribute value.

Question 17

What is a Functional Dependency?

Answer 17

A functional dependency occurs when a candidate key determines all the other attributes (columns) in a relation.

Question 18

What is Normalization?

Answer 18

The process or set of steps for breaking a table with more than one theme into a set of tables that each has only one.

Question 19

What is First Normal Form (1NF) and when is it Achieved?

Answer 19

First normal form means each cell only has one value, and all entries in a column are of the same kind.

1NF is achieved when a table has all 8 characteristics of a relation, a defined PK, and has no repeating groups.

Question 20

What are the Steps of the Normalization Process? (1-4)

Answer 20

1. Identify all the candidate keys of a relation
2. Identify all the functional dependencies in the relation.
3. Examine the determinants of the functional dependencies. If any determinant is NOT a candidate key, then the table is not well formed. (More in a separate card)
4. Repeat step 3 until every determinant of every relation is a candidate key.

Question 21

What are the Steps of the Normalization Process? (Step 3 expanded)

Answer 21

3A. Place the columns of the functional dependency in a new relation of their own.
3B. Make the determinant of the functional dependency the PK of the new relation
3C. Leave a copy of the determinant as the foreign key in the original relation
3D. Create a referential integrity constraint between the original and the new realtions

Question 22

What is the Boyce Codd Normal Form (BCNF)?

Answer 22

It’s a relation in 3NF (Third Normal Form) in which every determinant is a candidate key.

Question 23

What is a Multivalued Dependency?

Answer 23

A multivalued dependency occurs when a determinant is matched with a particular set of values.

Solution: Make both the determinant and the other field a composite PK.

Question 24

What are the Normal Forms? (1-4)

Answer 24

1. First normal form (1NF) - each cell has only one value, and all entries in a column are the same kind
2. 2NF - No partial dependencies (all non-key attributes are determined by the entire PK)
3. 3NF - No transitive dependencies (no non-key attributes are determined by another non-key attribute)
4. BCNF - each table is in 3NF and all determinants are candidate keys