Chapter 4

Question 1

Data Structure

Answer 1

Data are organized in two-dimensional tables (also called relations) with columns and rows

Question 2

Data Manipulation

Answer 2

Data stored in the tables may be manipulated through the use of a command language (Structured Query Language – SQL – was developed expressly for this purpose)

Question 3

Data Integrity

Answer 3

Business rules may be defined that maintain the integrity of the data that is manipulated

Question 4

Relational Database Model Constraints–Domain Integrity

Answer 4

constrains allowable values for columns(e.g., data type, column size, maximum value, etc.)

Question 5

Relational Database Model Constraints–Policy Integrity

Answer 5

constrains data operations to business rules (e.g., only managers may place vendor orders)

Question 6

Relational Database Model Constraints–Entity Integrity

Answer 6

prohibits null values for primary key column

Question 7

Relational Database Model Constraints–Referential Integrity

Answer 7

constrains a foreign key value to match a primary key value in a related table

Example: For every value of CustomerID in the Order table there must be a matching value of CustomerID in the Customer table

Question 8

Properties of Relational Tables–table (relation)

Answer 8

Each table (relation) in a given database has a unique name

Question 9

Properties of Relational Tables-column (attribute)

Answer 9

within a given table has a unique name

• -Every column (attribute) is single-valued
• -Thus, multivalued attributes require special teatment when designing relational tables

Question 10

Properties of Relational Tables–row (tuple)

Answer 10

Every row (tuple) in a table is unique

Question 11

Primary Key (PK) (analogous to entity identifier)

Answer 11

A column (or columns) whose value uniquely identifies or differentiates each row in a table(e.g., EmployeeID)

Question 12

Composite Key

Answer 12

a primary key made up of more than one column

e.g., FirstName + MiddleName + LastName

Question 13

Foreign Key (FK)

Answer 13

A column in one table that serves as the primary key of another table in the same database (thus serving as a link between the two tables)

Question 14

Candidate Key

Answer 14

If a table has more than one column that provides a way of uniquely identifying the rows of the table, then they are each called a candidate key

When there is more than one candidate key, one of them must be chosen to be the primary key of the table

Question 15

alternate key

Answer 15

A candidate key that is not chosen to be the primary key of a table

Question 16

Foreign Keys– additional rules

Answer 16

The foreign key column in one table must have the same domain of values as the primary key column in the linked table

–Two columns have the same domain of values if the columns have values of the same type (e.g., integer numbers; see previous slide)

Question 17

Logical Database Design

Answer 17

The process of arranging the entities and attributes of the conceptual data model of the business environment into the tables and columns of a relational database structure to serve that business in an information system

The goal is to create well-structured tables (i.e., free of anomalies) that properly reflect the organization’s business environment

Question 18

Anomalies

Answer 18

are problems that are experienced when attempting to manipulate stored data

Question 19

Well-Structured Tables

Answer 19

Tables that contains minimal redundancy and that allow users to insert, delete, and modify table rows without errors or inconsistencies are considered to be well-structured

When designing relational database tables, we seek to eliminate anomalies through the use of normalization

Question 20

Insertion Anomalies

Answer 20

are experienced when we attempt to store a value for a column but cannot because the value of another column is unknown

–e.g., cannot add a new customer’s information until an order number is ready to be entered (because OrderID column serves as the primary key for the table and cannot have null values)

Question 21

Deletion Anomalies

Answer 21

are experienced when a value for a column we wish to keep is unexpectedly removed when a value for another column is deleted

e.g., cannot delete the sole order for a customer without deleting the only copy of the customer’s information also

Question 22

Update Anomalies

Answer 22

are experienced when changes to multiple rows of a table are needed to effect an update to a single value of a column
e.g., cannot completely update a customer’s address without changing it for every order placed by that customer

Question 23

Map Binary Relationships/schemas–One-to-Many

Answer 23

Primary key on the one side becomes a foreign key on the many side

Question 24

Map Binary Relationships/schemas–Many-to-Many

Answer 24

• Create a new table; the primary key of the new table is typically a CPK comprised of (at least) the primary keys of the two entities involved in the relationship

Question 25

Map Binary Relationships/schemas–One-to-One

Answer 25

Primary key on the mandatory side becomes a foreign key on the optional side (if optionalities are asymmetric)

Question 26

Unary (Recursive) Relationships–One-to-Many

Answer 26

Recursive foreign key in the same table (also true for unary One-to-One)

Question 27

Unary (Recursive) Relationships–Many-to-Many

Answer 27

(e. g., bill of materials): Two tables result:
- -One for the entity type
- -One for an associative relation in which the primary key has two fields, both taken from the identifier of the original entity

Question 28

Unary (Recursive) Relationships–Ternary (and n-ary) Relationships

Answer 28

• -One table for each original entity and one for the common relationship (associative entity) (i.e., a ternary relationship maps to a total of four tables)
• -Table representing the associative entity has foreign keys to each entity in the relationship
• -PK of the table formed for the associative entity is typically a composite PK composed of (at least) the primary keys of the three entities

Question 29

Map Supertype/Subtype Structures

Answer 29

• -Create a separate table for the supertype and each of the subtypes
• -Assign common attributes, including subtype discriminator, to the supertype table
• -Assign to the subtype tables those attributes unique to each subtype
• -Assign to the subtype tables the primary key of the supertype table (which also functions as a FK referencing the supertype)

Question 30

Data Normalization

Answer 30

• -A formal process for grouping attributes into tables
• -A tool to validate and improve logical designs so that they satisfy certain constraints to avoid unnecessary duplication of data
• -The process of decomposing a table with anomalies into two or more, smaller, well-structured tables

Question 31

Functional Dependency

Answer 31

The value of one attribute (the determinant) determines the value of another attribute

Question 32

A—>B

Answer 32

We say here that “A determines B” or

“B is functionally dependent on A”

Question 33

First Normal Form

Answer 33

Table has no multivalued attributes

• -a table that has multivalued attributes is unnormalized
• -in this context, a multivalued attribute is sometimes referred to as a repeating group

Identify the functional dependencies in the table and draw the arrows

Question 34

Second Normal Form

Answer 34

Table is in 1NF and has no partial functional dependencies (that is, every nonkey attribute is fully functionally dependent on the entire primary key)

Question 35

what is the Solution to get to 2NF

Answer 35

The solution to the problem with the previous table is to break it into two related tables to achieve 2NF (at least)

Question 36

Third Normal Form

Answer 36

Table is in 2NF and no transitive dependencies (functional dependencies between nonkey attributes)
–This means that no nonkey attribute should be able to determine another nonkey attribute

Question 37

how to remove transitive dependancies?

Answer 37

–For each nonkey attribute that is a determinant in a table, create a new table; that attribute becomes the primary key of the new table

–Move all of the attributes that are functionally dependent on that determinant attribute from the old table to the new table

–Leave the attribute that serves as the primary key in the new table in the old table to serve as a foreign key to allow the tables to be related