Chapter 4

Question 1

What is data structure?

Answer 1

Tables (relations), rows, columns

Question 2

What is data manipulation?

Answer 2

Powerful SQL operations for retrieving and modifying data.

Question 3

What is data integrity?

Answer 3

Mechanisms for implementing business rules that maintain integrity of manipulated data

Question 4

What is a name, two-dimensional table of data?

Answer 4

A relation

Question 5

What consists of rows (records) and columns (attribute or field)?

Answer 5

A table

Question 6

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

Answer 6

• Must have a unique name
• Every attribute value must be atomic (not multivalued, not composite)
• Every row must be unique (can’t have two rows with exactly the same values for all their fields)
• Attributes (columns) in tables must have unique names.
• The order of the columns must be irrelevent.
• The order of the rows must be irrelevent.

Question 7

What must all relations be in?

Answer 7

1st Normal form

Question 8

How do relations correspond with the E-R model?

Answer 8

• Relations (tables) correspond with entity types and with many-to-many relationship types.
• Rows correspond with entity instances and with many-to-many relationship instances
• Columns correspond with attributes.

Question 9

Are relation (in relational database) and relationship (in E-R model) the same?

Answer 9

No, they are not the same.

Question 10

What are special fields that serve two main purposes (Primary and Foreign)?

Answer 10

Keys

Question 11

What two main purposes do keys serve?

Answer 11

• Primary Key
• Foreign Key

Question 12

What is a primary key?

Answer 12

Unique identifiers of the relation.

Example: Employee numbers, SSN etc.

This guarentees that all row are unique

Question 13

What are Foreign keys?

Answer 13

Identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship)

Question 14

How are the fields in keys defined?

Answer 14

Simple (a single field) or composite (more than one field)

Question 15

How are keys usually used?

Answer 15

As indexes to speed up the response to user queries.

Question 16

Keys (Visual example)

Answer 16

Question 17

What are domain constraints?

Answer 17

Allowable values for an attribute

Example: setting restrictions for what can be input for an attribute.Visual below

Question 18

What does entity integrity mean?

Answer 18

• That no primary key attribute may be null. All primary key fields MUST contain data values.

Question 19

What is referential integrity mean?

Answer 19

It refers to rules that maintain consistency between the rows of two related tables.

Question 20

What are rules that state that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)?

Answer 20

Referential Integrity

Question 21

What is an example of Referential Integrity?

Answer 21

Delete Rules

Restrict - don’t allow delete of “parent” side if related rows exist in “dependent” side

Cascade - automatically delete “dependent” side rows that correspon with the “parent” side row to be deleted

Set-to-Null - set the foreign key in the dependent side to null if deleting from the parent side -> not allowed for weak entities

Question 22

Referential Integrity Constraints visualized

Answer 22

Question 23

When transforming an ER diagram into a relation, how are simple attributes handled?

Answer 23

E-R attributes map directly onto the relation.

Question 24

When transforming an ER diagram into a relation, how are composite attributes handled?

Answer 24

Use only their simple, component attributes

Question 25

When transforming an ER diagram into a relation, how are multivalued attributes handled?

Answer 25

Becomes a seperate relation with a foreign key taken from the superior entity

Question 26

How are weak entities mapped from an ER diagram into relations?

Answer 26

- They become a seperate relation with a foreign key taken from the superior entity - Primary key composed of: - partial identifier of weak entity - primary key of identifying relation (strong entity)

Question 27

How is a One-to-Many Binary relationship mapped when transforming ER diagrams into relations?

Answer 27

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

Question 28

How is a Many-to-Many Binary relationship mapped when transforming ER diagrams into relations?

Answer 28

Create a new relation with the primary keys of the two entities as its primary key

Question 29

How is a one-to-one Binary relationship mapped when transforming ER diagrams into relations?

Answer 29

Primary key on mandatory side becomes a foreign key on optional side

Question 30

How are associative entities mapped when transforming ERDs into relations?

Answer 30

**If the Identifier is not assigned** Default primary key for the association relation is composed of the primary keys of the two entities (and in M:N relationship) **If the Identifer is assigned** It is natural and familiar to end-users Default identifier may not be unique

Question 31

Mapping an associative entity with identifer example

Answer 31

Question 32

How is a Unary relationship mapped when transforming ERD into relations?

Answer 32

One-to-Many = Recursive foreign key in the same relation Many-to-Many = Two relations: - One for the entity type - One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity

Question 33

How are ternary (and n-ary) relationships mapped when transforming ERDs into relations?

Answer 33

- One relation for each entity and one for the associative entity - The associative entity has foreign keys to each entity in the relationship

Question 34

How are supertype/subtype relationships mapped when transforming ERDs into relations?

Answer 34

- One relation for supertype and for each subtype - Supertype attributes (including identifier and subtype discriminator) go into supertype relation - Subtype attributes go into each subtype; primary key of supertype relation also become primary key of subtype relation - 1:1 relationship established between supertype and each subtype, with supertype as primary table

Question 35

What is primarily a tool to validate and improve a logical design so that it satisfies certain constraints that **avoid unnecessary duplication of data?**

Answer 35

Data normalization

Question 36

What is the process of decomposing relations with anomalies to produce smaller, **well-structured** relations?

Answer 36

Data normalization

Question 37

What defines a well-structured relation?

Answer 37

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies.

Question 38

What is the goal of a well-structured relation?

Answer 38

To avoid anomalies

Question 39

What are the different types of anomalies to be avoided?

Answer 39

Insertion Anomaly Deletion Anomaly Modification Anomaly

Question 40

What type of anomaly occurs when adding new rows forces users to create duplicate data?

Answer 40

Insertion Anomaly

Question 41

What type of anomaly occurs when deleting rows may cause a loss of data that would be needed for other future rows?

Answer 41

Deletion Anomaly

Question 42

What type of anomaly occurs when changing data in a row forces changes to other rows because of duplication?

Answer 42

Modification Anomaly

Question 43

Example table with anomalies

Answer 43

Question 44

Steps of normalization visualized

Answer 44

Question 45

What is it called when the value of one attribute (the determinant) determines the value of another attribute?

Answer 45

Functional dependency

Question 46

What is a candidate key?

Answer 46

A unique identifier. One of the candidate keys will become the primary key. For example: perhaps there is both credit card number and SS# in a table, in this case both are candidate keys

Question 47

What are non-key fields functionally dependent on?

Answer 47

Every candidate key

Question 48

What is First Normal Form?

Answer 48

- No multivalued attributes - Every attribute value is atomic - All relations are in 1st Normal Form Example of what is and isn't in 1st Normal Form

Question 49

Further Anomalies Example

Answer 49

Question 50

What is second normal form?

Answer 50

- 1NF PLUS every non-key attribute is fully funtionally dependent on the ENTIRE primar key. - Every non-key attribute must be defined by the entire key, not by only part of the key - No partial functional dependencies

Question 51

What is third normal form?

Answer 51

2NF PLUS **no transitive dependencies** (functional dependencies on non-primary-key attributes) \*It's called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third.

Question 52

What is the solution to transition transitive dependencies to make something third normal form?

Answer 52

Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table.

Question 53

What is it called when you combine entities from multiple ER models into common relations?

Answer 53

View integration

Question 54

What are the issues to watch out for when merging entities from different ER models?

Answer 54

Synonyms - two or more attributes with different names but same meaning Homonyms - attributes with same name but different meanings Transitive dependencies - even if relations are in 3NF prior to merging, they may not be after merging Supertype/Subtype relationships - may be hidden prior to merging