Test 2 Study Guide

Question 1

Define “relation”.

Answer 1

A relation is a named, two-dimensional table of data.

Question 2

What are the requirements for a table to qualify as a relation?

Answer 2

It must have a unique name.
Every attribute value must be atomic (not multivalued).
Every row must be unique.
Attribute columns must have unique names.
The order of the rows and columns must be irrelevant.

Question 3

What do relations correspond to in the E-R model?

Answer 3

Relations correspond with entity types and with M:N relationship types.

Question 4

What do rows correspond to in the E-R model?

Answer 4

Rows correspond with entity instances and with M:N relationship instances.

Question 5

What do columns correspond to in the E-R model?

Answer 5

Columns correspond with attributes.

Question 6

How are primary keys notated in a relational schema?

Answer 6

Primary keys are underlined with a solid line.

Question 7

How are foreign keys notated in a relational schema?

Answer 7

Foreign keys are underlines with a dotted line.

Question 8

Define “domain constraint”.

Answer 8

A domain constraint is a way to limit what data values can be placed in an attribute (such as integers, max digits, etc).

Question 9

Define “entity integrity”.

Answer 9

Entity integrity is when no primary key attributes are allowed to be null, and all primary key fields have data.

Question 10

Define “referential integrity constraint”.

Answer 10

Referential integrity constraint is where any foreign key value (on the “many” side) must match a primary key value on in the relation of the “1” side.

Question 11

When converting from an ERD to a relational schema, what happens to composite attributes?

Answer 11

Composite attributes are split into their individual component attributes.

Question 12

When converting from an ERD to a relational schema, what happens to multivalued attributes?

Answer 12

Multivalued attributes are split off into their own relation with a foreign key relating back to the superior entity.

Question 13

When converting from an ERD to a relational schema, what happens to weak entities?

Answer 13

Weak entities are broken off into a separate relation, with a foreign key taken from the strong entity. The primary key of this relation is a partial identifier of the weak entity, and the primary key of the strong entity.

Question 14

When converting from an ERD to a relational schema, what happens to a 1:M relationship?

Answer 14

The primary key on the 1 side of the relationship becomes a foreign key on the many side.

Question 15

When converting from an ERD to a relational schema, what happens to a M:N relationship?

Answer 15

A new relation is created with a primary key made of the primary keys of each side. These do not act as foreign keys.

Question 16

When converting from an ERD to a relational schema, what happens to a 1:1 relationship?

Answer 16

The primary key on the mandatory side becomes a foreign key of the optional side.

Question 17

When converting from an ERD to a relational schema, what happens to an associative entity?

Answer 17

If the identifier of the associative entity isn’t assigned, then it defaults to the a composite primary key made of the primary keys of the two entities. Otherwise, the primary keys of the two many entities become foreign keys.

Question 18

When converting from an ERD to a relational schema, what happens to a unary relationship?

Answer 18

A 1:M unary relationship becomes a recursive foreign key in the same relation (so, two EMP_IDs, although with different names).
A M:N unary relationship becomes two relations, one for the entity type, and one for an associative relation, in which the primary key has two attributes, one taken from the primary key of the entity, the other created for the relation. So, if you have an item that can have multiple components (also items), it would be broken into two relations, ITEM and COMPONENT. Component would have its primary keys set as Item_No and Component_No.

Question 19

When converting from an ERD to a relational schema, what happens to ternary and N-ary relationships?

Answer 19

Ternary and N-ary relationships have a relation created for each entity, and one for the associative entity. The associative entity has a foreign key to each entity in the relationship.

Question 20

When converting from an ERD to a relational schema, what happens to supertype/subtype relationships?

Answer 20

The supertype is converted to a relation, and so is each subtype, each keeping their respective attributes. Each subtype inherits its parent’s primary key, with the determinator character prepended to it.

Question 21

Define “data normalization”.

Answer 21

Data normalization is the process of decomposing relations with anomalies to produce smaller, well-structured relations. It’s a tool to validate and improve logical designs, so that they avoid unnecessary data duplications.

Question 22

What are the three types of data anomalies?

Answer 22

There are three types of data anomalies:
Insertion anomalies: Adding new rows forces the user to create duplicate data.
Deletion anomalies: Deleting rows may cause a loss of data that would be needed for other future rows.
Modification anomalies: Changing data in a row forces changes in other rows due to duplication.

Question 23

What is the “rule of thumb” for good normalization?

Answer 23

The general rule of thumb is that a table should never pertain to more than one entity type.

Question 24

What is functional dependency?

Answer 24

Functional dependency is when the value of one attribute (also called the determinant) determines the value of another.

Question 25

Define “determinant”.

Answer 25

A determinant is an attribute that defines the value of another in a functional dependency.

Question 26

Define “candidate key”.

Answer 26

A candidate key is a unique identifier. These are ideal to become primary keys. Any non-key attribute is functionally dependent on every candidate key.

Question 27

What does 1st normal form look like?

Answer 27

In 1st normal form, or 1NF, there are no multivalued attributes. That’s all.
By default, all relations are in 1NF.

Question 28

What does 2nd normal form look like?

Answer 28

In 2nd normal form, or 2NF, it is the same as 1NF, with the addition that every non-key attribute is fully-functionally-dependent on the entire primary key. If there is an attribute that is unrelated to the entire primary key, it is not in 2NF.

Question 29

What are the steps to convert from 1NF to 2NF?

Answer 29

Create a new relation for each primary key attribute that is a determinant in a partial dependency. That attribute becomes that relation’s primary key.
Move the non-key attributes dependent on the new primary key to the new relation.

Question 30

What does 3rd normal form look like?

Answer 30

In 3rd normal form, or 3NF, it is the same as 2NF, but with the additional stipulation that there are no transitive dependencies. That means that there are no functional dependencies on non-primary-key attributes.

Question 31

What is a transitive dependency?

Answer 31

A transitive dependency is a functional dependency, once removed; that is, a functional dependency that is dependent on a non-primary key, which is in turn dependent on a primary key.

Question 32

What are the steps to convert from 2NF to 3NF?

Answer 32

Non-key determinants with transitive dependencies go into a new relation as a primary key, but also stays as a foreign key in the old relation. It’s dependents go along with it.

Question 33

What are the three types of functional dependency?

Answer 33

Full - Where the attribute is dependent on the entire primary key.
Partial - Where the attribute is dependent on only part of a composite primary key.
Transitive - Where the attribute is dependent on a non-key attribute, which in turn is dependent on a key attribute.

Question 34

What is denormalization?

Answer 34

Denormalization is the process of reversing normalization. It’s useful in that there are some situations where an unnormalized table is a better choice for a database. Normalization of a table usually creates more tables, which requires joins, which are tricky as far as system speed.