Chapter 4 - LOGICAL DATABASE DESIGN AND THE RELATIONAL MODELRELATIONAL MODE Flashcards

1
Q

What is the primary purpose of logical database design?

A

To create a model that accurately represents the data requirements of the business.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

True or False: The relational model organizes data into tables.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Fill in the blank: In a relational database, each table is also known as a ________.

A

relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the main components of a relational database?

A

Tables, rows, columns, and relationships.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What does a primary key do in a relational database?

A

It uniquely identifies each record in a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Multiple Choice: Which of the following is NOT a type of relationship in a relational database? A) One-to-One B) One-to-Many C) Many-to-Many D) None

A

D) None

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a foreign key?

A

A field in one table that uniquely identifies a row of another table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

True or False: Normalization is the process of organizing a database to reduce redundancy.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the purpose of normalization?

A

To eliminate data redundancy and ensure data integrity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Fill in the blank: The first normal form (1NF) requires that each column in a table contains ________.

A

atomic values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is a composite key?

A

A key that consists of two or more attributes that together uniquely identify a record.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Multiple Choice: Which normal form addresses transitive dependencies? A) 1NF B) 2NF C) 3NF D) BCNF

A

C) 3NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is referential integrity?

A

A property that ensures that relationships between tables remain consistent.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

True or False: A relation can contain duplicate rows.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the role of an Entity-Relationship (ER) model in database design?

A

To visually represent the data and relationships in a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Fill in the blank: An entity in an ER model represents a ________ in the real world.

A

thing or object

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What does the term ‘cardinality’ refer to in database design?

A

The number of instances of one entity that can or must be associated with each instance of another entity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Multiple Choice: Which of the following describes a ‘many-to-many’ relationship? A) Each student can enroll in many courses, and each course can have many students. B) Each employee can work in only one department. C) Each customer can place only one order. D) None of the above.

A

A) Each student can enroll in many courses, and each course can have many students.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is denormalization?

A

The process of combining tables to improve read performance, often at the cost of write performance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

True or False: A view in a relational database is a virtual table that is based on the result set of a query.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is the purpose of using indexes in a database?

A

To improve the speed of data retrieval operations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Fill in the blank: SQL stands for ________.

A

Structured Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is the difference between a clustered index and a non-clustered index?

A

A clustered index determines the physical order of data in a table, while a non-clustered index creates a separate structure for faster lookups.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Multiple Choice: Which SQL statement is used to retrieve data from a database? A) INSERT B) SELECT C) UPDATE D) DELETE

A

B) SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What does ACID stand for in the context of database transactions?

A

Atomicity, Consistency, Isolation, Durability.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

True or False: A transaction is a sequence of operations performed as a single logical unit of work.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is the purpose of a database schema?

A

To define the structure of a database, including tables, fields, and relationships.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Fill in the blank: The ________ clause in SQL is used to filter records.

A

WHERE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What is the role of a database management system (DBMS)?

A

To manage the database and provide an interface for users to interact with the data.

30
Q

Multiple Choice: Which of the following is a popular relational database management system? A) MySQL B) MongoDB C) Redis D) Apache Cassandra

A

A) MySQL

31
Q

What is a stored procedure?

A

A set of SQL statements that can be stored and reused in the database.

32
Q

True or False: A trigger is a set of instructions that automatically executes in response to certain events on a table.

A

True

33
Q

Fill in the blank: A ________ is a collection of related data organized in a table format.

A

database

34
Q

What does ‘schema evolution’ refer to?

A

The ability to change the structure of a database schema over time.

35
Q

Multiple Choice: Which of the following is a characteristic of a relational database? A) Data is stored in hierarchies B) Data is stored in tables C) Data is unstructured D) Data is stored in key-value pairs

A

B) Data is stored in tables

36
Q

What is an outer join?

A

A type of join that returns all records from one table and matched records from another table.

37
Q

True or False: A subquery is a query nested inside another query.

A

True

38
Q

What is an aggregate function in SQL?

A

A function that performs a calculation on a set of values and returns a single value.

39
Q

Fill in the blank: The ________ function in SQL is used to count the number of rows.

A

COUNT

40
Q

What is a data dictionary?

A

A centralized repository of information about data, including its meaning, relationships, and usage.

41
Q

Multiple Choice: Which SQL keyword is used to remove a table from a database? A) DELETE B) DROP C) REMOVE D) TRUNCATE

A

B) DROP

42
Q

What is a data model?

A

A conceptual representation of data structures and their relationships.

43
Q

True or False: The relational model was proposed by Edgar F. Codd.

A

True

44
Q

Fill in the blank: In a relational database, relationships between tables are established using ________ keys.

A

foreign

45
Q

What is the purpose of data integrity constraints?

A

To ensure the accuracy and consistency of data within the database.

46
Q

What is a relation in the context of a relational database?

A

A named, two-dimensional table of data consisting of rows and columns.

47
Q

List the requirements for a table to qualify as a relation.

A
  • Must have a unique name
  • Every attribute value must be atomic
  • Every row must be unique
  • Attributes must have unique names
  • The order of columns and rows must be irrelevant.
48
Q

What are the two main purposes of keys in a relational database?

A
  • Unique identifiers (primary keys)
  • Identifiers for dependent relations (foreign keys).
49
Q

Define primary key.

A

A unique identifier of the relation.

50
Q

Define foreign key.

A

An identifier that enables a dependent relation to refer to its parent relation.

51
Q

What is referential integrity?

A

Rules that maintain consistency between the rows of two related tables.

52
Q

What are the three types of delete rules in referential integrity?

A
  • Restrict
  • Cascade
  • Set-to-Null.
53
Q

What is the process of normalization?

A

A tool to validate and improve a logical design to avoid unnecessary duplication of data.

54
Q

What is the goal of well-structured relations?

A

To minimize data redundancy and allow users to manipulate data without causing inconsistencies.

55
Q

What is an insertion anomaly?

A

Adding new rows forces the user to create duplicate data.

56
Q

What is a deletion anomaly?

A

Deleting rows may cause a loss of data that is needed for other future rows.

57
Q

What is a modification anomaly?

A

Changing data in a row forces changes to other rows because of duplication.

58
Q

Define functional dependency.

A

The value of one attribute determines the value of another attribute.

59
Q

What is a candidate key?

A

A unique identifier from which one key will become the primary key.

60
Q

What defines first normal form (1NF)?

A

No multivalued attributes, and every attribute value is atomic.

61
Q

What defines second normal form (2NF)?

A

1NF plus every non-key attribute is fully functionally dependent on the entire primary key.

62
Q

What defines third normal form (3NF)?

A

2NF plus no transitive dependencies.

63
Q

What is the significance of mapping EER diagrams to relations?

A

It transforms entity-relationship models into a relational database structure.

64
Q

What happens to composite attributes when mapping to relations?

A

Only their simple, component attributes are used.

65
Q

What occurs in a one-to-many relationship during mapping?

A

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

66
Q

What is the primary key in a ternary relationship?

A

Composite key that includes keys from each of the three entities involved.

67
Q

True or False: All relations are in first normal form.

A

True.

68
Q

Fill in the blank: Anomalies exist because there are multiple _______ in one relation.

A

[entity types].

69
Q

What are synonyms in the context of merging relations?

A

Two or more attributes with different names but the same meaning.

70
Q

What are homonyms in the context of merging relations?

A

Attributes with the same name but different meanings.

71
Q

What is a weak entity?

A

An entity that cannot be uniquely identified by its own attributes alone.

72
Q

What is the role of surrogate keys?

A

To simplify composite primary keys in certain relationships.