CH 3- Database Processing

Question 1

Entity

Answer 1

• An entity is some identifiable thing that users want to track:

– Customers
– Computers
– Sales

Question 2

Relation

Answer 2

-Relational DBMS products store data about entities in relations, which are a special type of table

Question 3

Characteristic of relations

Answer 3

-Is a two-dimensional table

                     characteristics:

1) Rows contain data about an entity.
2) Columns contain data about attributes of the entities.
3) All entries in a column are of the same kind.
4) Each column has a unique name.
5) Cells of the table hold a single value.
6) The order of the columns is unimportant.
7) The order of the rows is unimportant.
8) No two rows may be identical.

Question 4

Domain Integrity Constraint

Answer 4

-The requirement that all of the values in a column are of the same kind

domain= grouping of data that meets a specific type definition.

**Columns in different relations may have the same name.

Question 5

Relations as defined by Codd

Answer 5

1) The rows of a relation must be unique.
2) There is no requirement for a designated primary key.

• The requirement for unique rows implies that a primary key can be designated.
• In the “real world,” every relation has a primary key.

Question 6

Functional Dependencies

Answer 6

-occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s):
CookieCost = NumberOfBoxes x $5
NumberOfBoxes → CookieCost

-The attribute on the left side of the functional dependency is called the determinant

Functional dependencies may be based on equations:

ExtendedPrice = Quantity X UnitPrice
(Quantity, UnitPrice) → ExtendedPrice

• Function dependencies are not equations!

Question 7

Composite Determinants

Answer 7

-determinant of a functional dependency that consists of more than one attribute

(StudentNumber, ClassNumber) → (Grade)

Question 8

Functional Dependency Rules

Answer 8

If A → (B, C), then A → B and A→ C.
– This is the decomposition rule.

• If A → B and A → C, then A → (B, C).
– This is the union rule.

• However,
– if (A , B) → C, then neither A nor B determines C by
itself.

Question 9

What Makes Determinant Values Unique

Answer 9

-determinant is unique in a relation if and only if it determines every other column in the relation.

Question 10

keys

Answer 10

-a combination of one or more columns that are used to identify particular rows in a relation

Question 11

composite key

Answer 11

key that consists of two or more columns.

Question 12

candidate key

Answer 12

key that determines ALL of the other columns in a relation

Question 13

primary key

Answer 13

candidate key selected as the primary means of identifying rows in a relation.

– There is only one primary key per relation.
– The primary key may be a single key or a composite key

Question 14

Entity Integrity Constraint

Answer 14

• the primary key must have unique data values inserted into every row of the table.
• The phrase unique data values implies that this column is NOT NULL, and does not allow a NULL value in any row.

Question 15

Surrogate Keys

Answer 15

-an artificial column added to a relation to serve as a primary key.

– DBMS supplied
– Short, numeric, and never changes—an ideal primary key
– Has artificial values that are meaningless to users
– Normally hidden in forms and reports

Question 16

Foreign Keys

Answer 16

• is a column or composite of columns that is the primary key of a table other than the one in which it appears.
• The term arises because it is a key of a table foreign to the one in which it appears as the primary key

Note: The primary keys of the relations are underlined and any foreign keys are in italics in the relations below:

Question 17

Referential Integrity Constraint

Answer 17

-is a statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation:

Question 18

Database Integrity

Answer 18

3 constraints:

1) The domain integrity constraint- Columns with values of the same kind
2) The entity integrity constraint- No null values in PK, unique values in PK
3) The referential integrity constraint- Limit values of FK to those existing in the corresponding PK.

• The purpose of these three constraints, taken as a whole, is to create database integrity, which means that the data in our database will be useful, meaningful data.

Question 19

Types of Modification Anomalies

Answer 19

1) Insertion anomaly -The structure of the table forces us to enter facts about two entities when we just want to enter facts about one.
2) Deletion anomaly -When we delete one row, the structure of this table forces us to lose facts about two different things
3) Update anomaly -changes can produce data inconsistencies.

Question 20

First Normal Form (1NF)

Answer 20

criteria:
1) Meets the set of conditions for a relation

2)Has a defined primary key- expand the PK to represent the repeating group.

Question 21

Second Normal Form (2NF)

Answer 21

-A relation is in 2NF if, and only if, it is in 1NF and all non-key attributes are determined by the entire primary key.

• Remove Partial Dependencies.
• For example, consider the STUDENT_ACTIVITY relation:

STUDENT_ACTIVITY (StudentID, StudentName, Activity,
ActivityFee, AmountPaid)

StudentID → StudentName
Activity → ActivityFee
StudentID,Activity →AmountPaid

Question 22

Third Normal Form (3NF)

Answer 22

-A relation is in 3NF if, and only if, it is in 2NF and there are no non-key attributes determined by another non key attribute.

**This is also known as a Transitive Dependency.

Question 23

Boyce-Codd Normal Form (BCNF)

Answer 23

A relation is in BCNF if, and only if, it is in 3NF and every determinant is a candidate key.

Question 24

Eliminating Anomalies from Functional Dependencies with BCNF

Answer 24

1. Identify every functional dependency.
2. Identify every candidate key.
3. If there is a functional dependency that has a determinate that is not a candidate key:Note: In step 3, if there is more than one such
   functional dependency, start with the one with the
   most columns.

       A. Move the column of that functional 
           dependency into a new relation.
       B. Make the determinant of that functional 
           dependency the primary key of the new 
           relation.
      C. Leave a copy of the determinant as a foreign 
           key in the original relation.
      D. Create a referential integrity constraint 
           between the original relation and
           the new relation.

4. Repeat step 3 until every determinant of every relation is a candidate key.

Question 25

Multivalued Dependencies

Answer 25

occurs when a determinant is matched with a particular set of values:

Employee → → Degree
Employee → → Sibling
PartKit → → Part

• The determinant of a multivalued dependency can never be a primary key.

Question 26

Fourth Normal Form (4NF)

Eliminating Multivalued Anomalies

Answer 26

• Multivalued dependencies are not a problem if they are in a separate relation, therefore:

– Always put multivalued dependencies into their own
relation