Chapter 8 (Section 1) #2

Question 1

Section 1: Database Fundamentals

Question 2

What is the definition of a database?

Answer 2

A structured collection of data items that are accessible by various applications.

Question 3

What is a relational database?

Answer 3

A database that links data items together via the use of internal pointers.

Question 4

In a database context, what is a table?

Answer 4

A group of similar data organized into rows (for entities) and columns (for attributes).

Question 5

Define a “Record” or “Tuple.”

Answer 5

A row in a table that represents a single instance of an entity.

Question 6

Define a “Field” or “Attribute.”

Answer 6

A column in a table representing a specific data item or characteristic stored about an entity.

Question 7

What is an “Entity”?

Answer 7

Anything about which data can be stored, such as a person, event, or object.

Question 8

What is the purpose of “Indexing” in a database?

Answer 8

To optimize search operations by creating a structure for fast data retrieval based on specific columns.

Question 9

Section 2: File-Based vs. Relational Approach

Question 10

How is a file-based system defined?

Answer 10

A collection of data items structured as records, where each record contains fields about the same object.

Question 11

What are the three primary data problems associated with file-based systems?

Answer 11

1. Duplicate data (redundancy).
2. Format inconsistency.
3. Inconsistent data across different files.

Question 12

Why is “Storage Wastage” a limitation of file-based systems?

Answer 12

Duplicated data across multiple files wastes storage space and increases data redundancy.

Question 13

What is “Data Independence” in a relational database?

Answer 13

The ability to perform diverse queries without being limited by the specific structure of the data or the software.

Question 14

How does a relational database handle data updates differently than a file-based system?

Answer 14

Data changes are instantly available across all applications because information is stored only once, ensuring consistency.

Question 15

List the three main functions of a database management system.

Answer 15

1. Adding new files.
2. Inserting/retrieving/updating data.
3. Cross-referencing data for analysis.

Question 16

Section 3: Database Keys

Question 17

What is a Candidate Key?

Answer 17

The smallest set of attributes that can uniquely identify a record without duplication.

Question 18

How does a Candidate Key differ from a Primary Key in theoretical terms?

Answer 18

A candidate key is any attribute or set of attributes that could uniquely identify a record; the primary key is the specific candidate key selected by the designer to perform that role.

Question 19

What is a Primary Key?

Answer 19

A unique identifier for a table selected from the available candidate keys.

Question 20

Define a Secondary Key.

Answer 20

An alternative unique identifier used for accessing records.

Question 21

What is the theoretical role of a Secondary Key?

Answer 21

It serves as an alternative identifier for a record, used primarily to provide a different path for searching or indexing data without replacing the primary key.

Question 22

What is a Foreign Key?

Answer 22

An attribute or set of attributes that links records in one table to the primary key of another table.

Question 23

What is a Composite Key?

Answer 23

A primary key consisting of a set of two or more attributes that together provide a unique identifier for a record.

Question 24

What defines a “Composite Key” in a relational structure?

Answer 24

A set of attributes that, when combined, provide a unique identifier for a table that cannot be uniquely identified by a single attribute alone.

Question 25

Section 4: Referential Integrity and Data Logic

Question 26

What is "Referential Integrity"?

Answer 26

A concept ensuring that table relationships remain consistent; every foreign key value must match a primary key value in the referenced table.

Question 27

What is the theoretical purpose of Referential Integrity?

Answer 27

To ensure that the "links" between tables remain valid, meaning every value in a foreign key column must have a corresponding, matching value in the primary key column of the related table.

Question 28

What is the rule regarding the deletion of a primary key in a relational database?

Answer 28

A primary key cannot be deleted if there are dependent records in other tables, unless a "cascading delete" is performed.

Question 29

Define the concept of "Cascading Deletes" in data integrity.

Answer 29

A rule stating that a primary key record cannot be deleted until all dependent records (those referencing it via a foreign key) are deleted first to prevent orphaned data.

Question 30

Define the concept of "Cascading Updates" in data integrity.

Answer 30

A rule ensuring that if a primary key value is changed, all corresponding foreign key values in related tables are automatically updated to maintain the relationship.

Question 31

Section 5: Entity-Relationship (E-R) Modeling

Question 32

What is the primary purpose of an E-R Diagram?

Answer 32

To provide a visual representation of database entities and the relationships between them.

Question 33

When designing an E-R diagram, what is the first step?

Answer 33

Identifying the entities, which are typically the "nouns" within the requirements.

Question 34

Why is identifying "Nouns" the standard method for choosing entities?

Answer 34

Because entities represent objects, people, or events that exist independently and about which specific attributes (data) can be stored.

Question 35

Define a One-to-One (1:1) relationship.

Answer 35

Each record in the first table corresponds to exactly one record in the second table.

Question 36

Define a One-to-Many (1:M) relationship.

Answer 36

One record in the first table connects to multiple records in the second table.

Question 37

Define a Many-to-Many (M:M) relationship.

Answer 37

Multiple records in the first table relate to multiple records in the second table.

Question 38

How is a "Many-to-Many" (M:M) relationship theoretically resolved in database design?

Answer 38

By introducing a third "link" or "junction" table that converts the M:M relationship into two separate "One-to-Many" (1:M) relationships.

Question 39

Define "Cardinality" in the context of E-R Diagrams.

Answer 39

It defines the nature of the relationship between entities, indicating how many instances of one entity relate to another.

Question 40

What does "Cardinality" describe in an Entity-Relationship model?

Answer 40

It defines the nature and numerical obligation of a relationship, specifying the minimum and maximum number of times an instance in one entity can relate to an instance in another.

Question 41

What characterizes a "Mandatory Relationship"?

Answer 41

A relationship where each instance of an entity must relate to at least one instance of another entity.

Question 42

What characterizes an "Optional Relationship"?

Answer 42

A relationship where an instance of an entity does not necessarily need a corresponding instance in the related entity.

Question 43

What is the difference between a "Forward" and "Backward" reading of an E-R relationship?

Answer 43

A relationship always considers just one entity to begin a descriptive sentence (e.g., "One Band is booked for many Bookings"), and the "backward" reading describes it from the perspective of the other entity (e.g., "One Booking is for many Bands").

Question 44

Section 6: Normalization

Question 45

What is "Normalization"?

Answer 45

A design technique for constructing or improving table designs to efficiently organize data.

Question 46

List the three main objectives of the normalization process.

Answer 46

1. Eliminating redundancy. 2. Ensuring data is stored in the correct table. 3. Eliminating the need for frequent database restructuring.

Question 47

What is the theoretical definition of "Repeating Groups" in a non-normalized table?

Answer 47

A repeating group occurs when a single attribute or a set of attributes contains multiple data values for a single instance of an entity.

Question 48

What defines a table in First Normal Form (1NF)?

Answer 48

A table that contains no repeating groups of attributes and where all data items are atomic.

Question 49

What are the two mandatory conditions for a table to satisfy First Normal Form (1NF)?

Answer 49

1. It must contain no repeating groups of attributes. 2. Every attribute value must be atomic, meaning it cannot be divided further into multiple data items.

Question 50

What does it mean for data to be "Atomic"?

Answer 50

Each field contains only a single value, and there are no multiple data items stored under one attribute.

Question 51

What is "Partial Dependency"?

Answer 51

When a non-key attribute is dependent on only one part of a composite primary key rather than the whole key.

Question 52

Theoretically, how is a 1NF table converted into Second Normal Form (2NF)?

Answer 52

By removing all "partial dependencies," which occur when a non-key attribute is dependent on only part of a composite primary key rather than the entire key.

Question 53

What defines a table in Second Normal Form (2NF)?

Answer 53

A table that is in 1NF and contains no partial dependencies; all non-key attributes depend entirely on the primary key.

Question 54

What are the specific characteristics of a table that has reached 2NF?

Answer 54

The table must already be in 1NF and must either have a single-attribute primary key or a composite primary key where every non-key attribute is dependent on every part of that key.

Question 55

What is "Non-key Dependency" (or Transitive Dependency)?

Answer 55

When a non-key attribute depends on another non-key attribute rather than directly on the primary key.

Question 56

What is the definition of "Transitive Dependency" (Non-key Dependency) in the context of 3NF?

Answer 56

It is a situation where a non-key attribute depends on another non-key attribute instead of being directly dependent on the primary key.

Question 57

What defines a table in Third Normal Form (3NF)?

Answer 57

A table that is in 2NF and has no non-key dependencies; all attributes are dependent "on the key, the whole key, and nothing but the key".

Question 58

What is the "Normalization Litany" used to summarize Third Normal Form (3NF)?

Answer 58

Every non-key attribute must be dependent on "the key, the whole key, and nothing but the key".

Question 59

How are many-to-many (M:M) relationships usually handled during normalization?

Answer 59

They are typically resolved by creating a link table (often using a composite key) to break the M:M relationship into two 1:M relationships.

Question 60

Explain why a file-based approach is usually not better than a relational database (Bobby is incorrect).

Answer 60

1. Flat-file has more data redundancy: the same data is stored many times. 2. There is program-data dependence with flat-files: any changes to the structure of the data means the programs that access that data have to be re-written. 3. Flat-file has more data inconsistency // worse data integrity: duplicated data might be stored differently; when data is updated in one place, it is not updated everywhere. 4. It is not easy to perform complex searches/queries: a new program has to be written each time. 5. Flat files could have a lack of privacy: user views cannot easily be implemented.

Question 61

Describe the advantages of using a relational database compared to a file-based approach.

Answer 61

1. Reduced data redundancy // less repeated data: each item of data is only stored once. 2. Maintains data consistency // improves data integrity: changes in one table will automatically update in another; linked data cannot be entered differently in two tables. 3. Program-data independence: changes to the data do not require programs to be re-written. 4. Complex queries are easier to run. 5. Can provide different views: so users can only see specific aspects of the database.