Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

C175 Data Management Foundations > Chapter 4 Database Design > Flashcards

Chapter 4 Database Design Flashcards

1
Q

What is the purpose of the Entity-Relationship Model (ERM) in database design?

A

ERM is a high-level representation of data requirements, ignoring implementation specifics.

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

In an airline reservation system, Passenger and Booking are $.

A

Passenger and Booking are entities.

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

What would 1 & 2 represent?

A
  1. Holds is a relationship between Passenger and Booking.
  2. PassengerNumber, PassengerName, BookingCode, BookingCost are attributes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What do entities represent in an Entity-Relationship Model?

A

Entities aren’t the rows themselves, they define what kind of information those rows will hold.

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

What are relationships in the context of an Entity-Relationship Model?

A

Statements about two entities; can be reflexive (entity relates to itself).

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

What are attributes in an Entity-Relationship Model?

A

Descriptive properties of an entity.

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

In SQL implementation, what do entities typically become?

A

Entities typically become tables.

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

In SQL implementation, what do relationships become?

A

Relationships become foreign keys.

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

In SQL implementation, what do attributes become?

A

Attributes become columns.

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

What is an ER Diagram?

A

A visual representation showing:

  1. entities as rectangles
  2. relationships as lines
  3. attributes as additional text.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Consider the ER Diagram

what would the entity-relationship-entity be read as?

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

What does a glossary document?

Glossary or data dictionary

A

Documents names, synonyms, and descriptions of entities, relationships, and attributes.

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

Define ‘Entity Type’ in the context of ER modeling.

A

Set of all instances

(e.g., all employees).

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

Define ‘Relationship Type’ in the context of ER modeling.

A

Set of related pairs

(e.g., Employee-Manages-Department).

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

Define Attribute Type in the context of ER modeling.

A

Set of values

(e.g., all employee salaries).

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

What is a supertype entity?

A

A general category that encompasses subtypes.

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

What is a subtype entity?

A

A specific subset of a supertype.

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

What does ‘IsA’ relationship refer to?

A

The relationship between a supertype and its subtypes.

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

A supertype may have $ subtypes.

A

Multiple

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

In ER modeling, what notation is used to represent cardinality?

A

Crow’s Foot Notation.

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

What does cardinality refer to in Entity-Relationship Modeling?

A

The maximum and minimum numbers of instances of one entity that can relate to a single instance of another entity.

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

What are the two maxima in a relationship?

A

One for each of the related entities, usually specified as either ‘one’ or ‘many’.

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

What are the typical options for relationship minima?

A

Zero (optional) or one (required).

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

What is a partition in the context of supertype and subtype entities?

A

A group of mutually exclusive subtypes within a supertype.

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

Why can’t an entity instance be in two subtypes of the same partition?

This is a fundamental rule in entity-relationship modeling and database design.

A
  1. Partitions divide an entity type into distinct, non-overlapping groups.
  2. Subtypes further categorize entities within a partition.
  3. An entity instance can only exist; in one partition and one subtype within that partition. Example

This ensures data integrity and avoids ambiguity.

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

How can an entity instance; be in two subtypes of different partitions?

A
  1. Subtyping allows an entity to inherit properties and relationships from multiple parent types.
  2. Still applies if those parent types are defined in different parts of the data model.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What are the main steps in the database design process?

A

Analysis, Logical Design, Physical Design.

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

What is the goal of the analysis phase in database design?

Often intertwined with conceptual logic

A
  1. Gather data requirements
  2. To develop the entity-relationship model.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What does the logical design phase involve?

A

Converting ERM to tables, columns, and keys.

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

What is the main purpose of the discovery phase in database design?

A

Identifying entities, relationships, and attributes through interviews with database users and managers.

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

What types of sources can be used to identify data requirements?

A

Interviews and written documents.

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

How should entities be named, when used in terms of conventions?

A

Singular nouns

(e.g., ‘Employee’).

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

What format should relationships be named in?

A

Entity-Verb-Entity

(e.g., Division-Contains-Department).

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

What is a unique attribute?

A

An attribute where each entity instance has a unique value across the entire database.

Unique attributes V Keys

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

What is a singular attribute?

A

Each entity instance has at most one attribute value.

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

What is the relationship maximum for the Employee-Has-Telephone relationship if each employee can have at most three telephone numbers?

A

1-3

  • “1-3” means an employee must have at least one (“1”) but no more than three (“3”) phone numbers.
  • It shows the allowed range, with any number within that range being acceptable.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What is the relationship minimum for the Customer-Has-Identification relationship if two forms of identification may be required?

A

1-2

  • 1 means at least one form of identification is always required.
  • 2 means that in some cases, a second form of identification may also be necessary.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is the main purpose of documenting cardinality in ER diagrams?

A

To reflect business rules.

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

What are the steps in the discovery process?

A
  • The discovery process in design is not a rigid, step-by-step procedure.
  • It’s more of an iterative process where designers often move back and forth between different stages as they gather new information and insights.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

In database design, when are standard attribute types determined?

A

ER diagram development.

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

Identify nouns as _______ in database design.

A

entities.

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

Recognize verbs as _______ in database design.

A

relationships.

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

Specify nouns as _______ in database design.

A

attributes.

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

What are the characteristics of good primary keys?

A
  • Unique
  • Not NULL
  • Stable
  • Simple
  • Meaningless
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What is the ideal primary key structure?

A

Single-column primary keys are ideal; composite primary keys can be used if unavailable.

primary key that uses multiple columns in a table to create a unique value for each row

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

What is the primary key of a subtype table?

A

Matches the primary key of the supertype.

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

What do weak entities become in a database?

A

Weak tables.

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

How should foreign keys behave when changes are made to the primary key in the parent table?

A

Think of Customers and Orders tables. An order needs a customer, so Orders uses a foreign key to link to Customers.

  • Cascade on delete: Deleting a customer also deletes their orders.
  • Restrict on insert: You can’t create an order for a non-existent customer.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

When implementing many-one relationships, where is the foreign key placed?

A

In the table on the many side of the relationship.

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

In a one-one relationship, where is the foreign key typically placed?

A

In the table with fewer rows.

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

How is a many-many relationship implemented?

A

Using a new weak table containing two foreign keys.

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

What is the primary key of the new table in a many-many relationship?

A

Composite of the two foreign keys.

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

What do plural attributes become in a database?

A

New weak tables

  • plural attributes are transformed into new weak tables to effectively model many-to-many relationships and maintain data integrity within a database.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

What is the SQL data type for a Code attribute?

A

CHAR(3).

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

What are the types of attribute cardinality?

A
  • Unique
  • Required
  • Optional
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

What SQL keyword enforces unique attributes?

A

UNIQUE.

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

What are the criteria for a table to be in First Normal Form (1NF)?

A
  • Every cell contains exactly one value
  • The table has a primary key
  • No duplicate rows are allowed
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

What is the significance of functional dependence?

A

Reflects business rules.

59
Q

What does Boyce-Codd Normal Form (BCNF) eliminate?

A

Redundancy arising from functional dependence.

60
Q

What is a candidate key?

A

A simple or composite column that is unique and minimal.

61
Q

What does normalization aim to achieve in database design?

A

Elimination of redundancy from database tables.

62
Q

What is denormalization used for?

A

Enhancing query performance by merging tables.

63
Q

What are the characteristics of denormalization?

A
  • Reduces complexity
  • Encourages redundancy
  • Not common in frequently updated databases
64
Q

What should be done when a column A depends on a non-unique column B?

A
  1. Remove column A from the original table and create a new table for A and B.
  2. This reduces redundancy and improves data integrity by ensuring updates are made in one place.

What if column b was unique

65
Q

What is the formal definition of BCNF?

A

A table is in BCNF if, whenever column A depends on column B, then B is unique.

66
Q

What is the relationship between redundancy and functional dependence?

A
  • Functional Dependency: One attribute determines the value of another.
  • Redundancy: Storing the same information multiple times, often due to functional dependencies.
67
Q

What is normalization?

A

The process of eliminating redundancy from database tables.

68
Q

What is the goal of normalization?

A

To refine the design by transforming tables into higher normal forms.

69
Q

What is Boyce-Codd normal form?

A

A higher normal form that aims to eliminate redundancy.

70
Q

What happens to a table in first normal form during normalization?

A

A table in first normal form might be split into two (or more) organized tables during normalization.

71
Q

What are unique columns in a table?

A

Columns that can uniquely identify records within the table.

Foreign vs unique column

72
Q

Define dependency in the context of database columns.

A

Column A depends on column B when; each B value relates to at most one A value.

73
Q

What must be true for a table to be in Boyce-Codd normal form?

A

If column A depends on column B, then B must be unique.

74
Q

What is the first step in normalizing a table to Boyce-Codd normal form?

A

List all unique columns in the table.

75
Q

What should be done if column A depends on a non-unique column B?

A

Column A should be removed from the original table and placed in a new table with B.

76
Q

What does denormalization refer to?

A

The practice of merging tables and allowing some redundancy to enhance query performance.

77
Q

When is denormalization typically used?

A
  1. In reporting databases where changes are infrequent.
  2. This way, you can retrieve all the information you need with a single query, without the need for a join

Infrequent means something that doesn’t happen often or regularly

78
Q

Why might a database designer choose to denormalize?

A

To reduce complexity and enhance query performance.

To normalize or not normalize

79
Q

What is a characteristic of denormalization?

A

It encourages redundancy in data storage.

80
Q

What should be carefully considered before denormalizing a database?

A

The frequency of changes to the data.

81
Q

What activities are involved in applying normal form?

A

Identifying dependencies, eliminating redundancy, and considering denormalization.

82
Q

How do database designers assess tables for Boyce-Codd normal form?

A

By identifying dependencies and unique columns.

83
Q

What is the relationship between normalization and performance?

A

Normalization reduces redundancy, while denormalization can enhance query performance.

84
Q

What is the significance of E. F. Codd in relation to normalization?

A

He originally defined the relational model and the concept of normalization.

85
Q

What kind of queries does denormalization help reduce?

A

Join queries.

86
Q

What is the primary focus during the database design process?

A

Ensuring tables meet Boyce-Codd normal form characteristics.

87
Q

What are conventions in data organization?

A

Conventions are rules for naming and organizing files, data sets, and other data.

Conventions help in standardizing how data is structured and accessed.

88
Q

What does it mean for subtypes to be mutually exclusive?

A

A single entity can only belong to one subtype within a given supertype.

This ensures clarity in classification and prevents ambiguity.

89
Q

What is the purpose of mutually exclusive subtypes?

A

To maintain data integrity and consistency.

This prevents overlap in how entities are classified within a supertype.

90
Q

What is an entity type in entity-relationship modeling?

A

A set of unique individuals

Example: The entity type Passenger includes all passengers in the system.

91
Q

What does the relationship type represent in entity-relationship modeling?

A

A set of related pairs

Example: The relationship type Passenger-Holds-Booking represents pairs of passengers and their associated bookings.

92
Q

What is an attribute type?

A

A set of values related to an entity

Example: The attribute type BookingCode encompasses all possible booking codes.

93
Q

Define entity instance

A

An individual member of the entity type

Example: Muhammed Ali is a specific entity instance representing a passenger.

94
Q

What is a relationship instance?

A

A specific assertion about entities

Example: Muhammed Ali holds 39240 describes the relationship involving the passenger and their booking.

95
Q

What is an attribute instance?

A

An individual value related to an attribute

Example: 39240 is a specific instance of the BookingCode attribute.

96
Q

In entity-relationship modeling, what do entities, relationships, and attribute types correlate to?

A
  1. Entities types: Tables
  2. Relationships types: foreign keys
  3. Attribute types: columns

Their instances correspond to rows, foreign key values, and column values, respectively.

97
Q

What is the first step in the analysis process?

A

Discover entities, relationships, and attributes

98
Q

What is the second step in the analysis process?

A

Determine cardinality

99
Q

What is the third step in the analysis process?

A

Distinguish strong and weak entities

100
Q

What is the fourth step in the analysis process?

A

Create supertype and subtype entities

101
Q

What is the fifth step in the logical design process?

A

Implement entities

102
Q

What is the sixth step in the logical design process?

A

Implement relationships

103
Q

What is the seventh step in the logical design process?

A

Implement attributes

104
Q

What is the eighth step in the logical design process?

A

Apply normal form

105
Q

What type of attribute is represented by at most one in a database?

A

Singular attribute

Indicates that there can be a maximum of one instance of this attribute per entity.

106
Q

What type of attribute is indicated by the term ‘several’?

A

Plural attribute

Indicates that there can be multiple instances of this attribute per entity.

107
Q

What does it mean when an attribute is described as ‘optional’?

A

Information may be left blank in the database

This means that the attribute does not have to be provided for every record.

108
Q

What type of attribute must every student have according to the text?

“All students enrolled in the school must have a unique student ID number.”

A

Required attribute

An attribute that must be filled out for every entity.

109
Q

What is the significance of the phrase used to identify in relation to an attribute?

A

Unique attribute

Indicates that each instance of this attribute must be distinct across the database.

110
Q

StudentNumber is a(n) $ attribute of Student

A

Unique

Each student is assigned a unique eight-digit number.

111
Q

TelephoneNumber is a(n) $ attribute of Student.

A

Optional

The telephone number is optional and may be left blank.

112
Q

In database design, what does the cardinality of relationships depend on?

A

Business rules

This determines how entities relate to one another in terms of quantity.

113
Q

MajorSubjectName is a(n) _______ attribute of Student.

A

plural

Indicates that students can major in multiple subjects.

114
Q

What are normal forms in relational databases?

A

A set of rules and guidelines for structuring a relational database

115
Q

What is the purpose of normal forms?

A

To address specific types of data redundancy and potential issues

116
Q

List the most common normal forms.

A
  • 1NF (First Normal Form)
  • 2NF (Second Normal Form)
  • 3NF (Third Normal Form)
  • BCNF (Boyce-Codd Normal Form)
117
Q

What is the first step in eliminating redundancy?

A

Identifying Redundancy by analyzing data to spot redundant information

118
Q

What is decomposition in the context of normalization?

A

Breaking down tables into smaller, more focused tables to reduce redundancy

119
Q

What does 1NF (First Normal Form) achieve?

A

Eliminates repeating groups of data within a single column

120
Q

What is the goal of 2NF (Second Normal Form)?

A

Remove redundant data that depends on only part of the primary key

2NF aims to remove transitive dependencies

121
Q

What does 3NF (Third Normal Form) eliminate?

A

Data that depends on non-key attributes (transitive dependencies)

122
Q

What distinguishes BCNF (Boyce-Codd Normal Form) from 3NF?

A

BCNF is a more stringent version of 3NF, further reducing redundancy

123
Q

To normalize a table called Customers, you might __ ?

A

create a separate table that further divides

124
Q

What are the benefits of normalization?

A
  • Reduced Data Redundancy
  • Improved Data Integrity
  • Increased Flexibility
  • Better Performance
125
Q

While higher normal forms improve data integrity, they don’t always mean better performance. Why?

A
  • More tables & joins: This can slow down queries.
  • Normalization overhead: Maintaining consistency across multiple tables takes extra work.

The best approach depends on balancing performance needs with data integrity.

126
Q

Why is it important to consider trade-offs when aiming for higher normal forms?

A

Sometimes, slight redundancy might be acceptable to improve query performance

127
Q

What does IMS stand for?

A

Information Management System

IMS is a hierarchical database management system used primarily on IBM mainframe systems.

128
Q

What type of database is IDMS?

A

Network database management system

IDMS stands for Integrated Database Management System.

129
Q

What is a key characteristic of a hierarchical database?

A

Data is organized in a tree-like structure

Hierarchical databases utilize parent-child relationships.

130
Q

What type of database does Oracle Database Relational refer to?

A

Relational database management system

Oracle Database uses SQL for data manipulation.

131
Q

What is Neo4?

A

A graph database management system

Neo4j is designed to handle highly connected data.

132
Q

What type of data structure does ObjectStore Object represent?

A

Object-oriented database management system

It allows for the storage of objects rather than just data.

133
Q

What is MongoDB classified as?

A

Document database management system

MongoDB stores data in JSON-like format.

134
Q

What are the primary components of a graph database?

A

Vertex and edge

Vertices represent entities, while edges represent relationships between them.

135
Q

Define primary data structure.

A

The main format used to store data in a database

Examples include tables, trees, and linked lists.

136
Q

What is an example of a tree data structure?

A

Binary tree

A binary tree is a type of tree where each node has at most two children.

137
Q

What is a linked list?

A

A linear data structure where elements are stored in nodes

Each node points to the next node in the sequence.

138
Q

What is XML?

A

Extensible Markup Language

XML is used to define rules for encoding documents in a format that is both human-readable and machine-readable.

139
Q

What does JSON stand for?

A

JavaScript Object Notation

JSON is a lightweight format for data interchange that is easy for humans to read and write.

140
Q

What is a strength of document databases?

A

Flexible schema

Document databases allow for unstructured and semi-structured data.

141
Q

What are the advantages of fast queries in a database?

A

Improved performance and user experience

Fast queries reduce wait times for data retrieval.

142
Q

What does productivity and simplicity refer to in database management?

A

Ease of use and efficiency in database operations

Simplified database management enhances user productivity.

143
Q

True or False: A flexible schema can adapt to evolving business requirements.

A

True

Flexible schemas are essential for accommodating changes in data needs.

144
Q

What type of applications benefit from transactional databases?

A

Transactional applications

These applications require reliable and consistent data processing.

C175 Data Management Foundations (29 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions