Brainscape's Knowledge GenomeTM

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


See full index

Data Foundations > Database Design > Flashcards

Database Design Flashcards

1
Q

Database design begins with blank for the database.

A

verbal or written requirements

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

Requirements are formalized as a blank and then implemented in SQL.

A

an entity-relationship model

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

An entity-relationship model is a high-level representation of blank, ignoring implementation details.

A

Data requirements

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

An entity-relationship model guides blank in a particular database system, such as MySQL.

A

Implementation

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

An entity-relationship model includes what three kinds of objects:

A

Entity
Relationship
Attribute

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

An blank is a person, place, product, concept, or activity.

A

Entity

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

A blank is a statement about two entities.

A

Relationship

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

An blank is a descript­ive property of an entity.

A

Attribute

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

A relationship is usually a statement about two different entities, but the two entities may be the same. A blank relates an entity to itself.

A

Reflexive relationship

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

When the model is implemented in SQL, entities typically become blank. Relationships and attributes typically become blank and blank, respectively.

A

Tables
Foreign keys
Columns

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

some relationships and attributes become tables. Since the conversion is indirect, requirements are documented as blank, blank, and blank rather than tables, keys, and columns.

A

entities, relationships, and attributes

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

Attribute is used in both entity-relationship and relational models. In the relational model, attribute is a formal term for blank

A

Column

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

An blank commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes.

A

entity-relationship diagram

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

Entities are drawn as blank

A

Rectabgles

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

Relationships are drawn as blank connecting rectangles

A

Lines

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

Attributes appear as blank within an entity rectangle, under the entity name.

A

Additional text

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

Blank and blank always appear in ER diagrams. Blank are optional and only appear when additional detail is needed.

A

Entities and relationships
Attributes

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

A blank, also known as a data dictionary or repository, documents additional detail in text format.

A

Glossary

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

A glossary includes blank, blank, and blank of entities, relationships, and attributes.

A

names, synonyms, and descriptions

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

The ER diagram and glossary are complementary and, together, completely describe an blank

A

entity-relationship model.

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

In entity-relationship modeling, a blank is a set:

A

Type

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

An blank is a set of things. Ex: All employees in a company.

A

Entity type

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

A blank is a set of related things. Ex: Employee-Manages-Department is a set of (employee, department) pairs, where the employee manages the department.

A

Relationship type

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

An blank is a set of values. Ex: All employee salaries.

A

Attribute set

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

Entity, relationship, and attribute types usually become blank, blank, and blank , respectively.

A

tables, foreign keys, and columns, respectively.

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

An blank is an element of a set:

A

Instance

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

An blank is an individual thing. Ex: The employee Sam Snead.

A

Entity instance

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

A blank is a statement about entity instances. Ex: “Maria Rodriguez manages Sales.”

A

Relationship instance

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

An blank is an individual value. Ex: The salary $35,000.

A

Attribute instance

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

Complex databases are developed in three phases. Name them

A

Analysis
Logical design
Physical design

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

Blank develops an entity-relationship model, capturing data requirements while ignoring implementation details.

A

Analysis

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

Blank converts the entity-relationship model into tables, columns, and keys for a particular database system.

A

Logical design

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

Blank adds indexes and specifies how tables are organized on storage media.

A

Physical design

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

Execution of database design is not always blank. Often an early step is revisited after a later step is completed.

A

Sequential

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

Blank is dependent on specific index and table structures, which vary greatly across relational databases.

A

Physical design

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

Name the four analysis steps

A
  1. Discover entities, relationships, and attributes
    2 Determine cardinality
    3 Distinguish strong and weak entities
    4 Create supertype and subtype entities
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Name the four logical design steps

A

5 Implement entities
6 Implement relationships
7 Implement attributes
8 Apply normal form

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

Entities, relationships, and attributes are blank in interviews with database users and managers.

A

Discovered

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

In addition to interviews, blank are a good source of data requirements.

A

Written documents

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

In interviews and documents, entities, relationships, and attributes surface as blank and blank

A

Nouns and verbs

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

Entities usually appear as blank, but not all blank are entities. Designers should ignore nouns that denote specific data or are not relevant to the database.

A

Nouns

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

Relationships are often expressed as blank. Designers should ignore statements that are not about entities, not relevant to the database, or redundant to other relationships. Designers should look for relationships that are not explicitly stated, since users may overlook important information.

A

Verbs

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

Attributes are usually blank that denote specific data, such as names, dates, quantities, and monetary values

A

Nouns

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

Entity names are a blank noun. Ex: Employee rather than Employees. The best names are commonly used and easily understood by database users.

A

Singular

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

Relationships names have the form blank, such as Division-Contains-Department.

A

Entity-Verb-Entity

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

When the related entities are blank, in ER diagrams or informal conversation, Verb is sufficient and entity names can be omitted.

A

Obvious

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

The verb should be blank rather than passive

A

Active

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

Occasionally, the same verb relates blank. Ex: Order-Contains-LineItem and Division-Contains-Department.

A

Different entity pairs

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

Attribute names have the form blank, such as EmployeeFirstName:

A

EntityQualifierType

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

Blank is the name of the entity that the attribute describes. When the entity is obvious, in ER diagrams or informal conversation, blank is sufficient and the entity name can be omitted.

A

Entity
QualifierType

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

Qualifier describes the blank of the attribute. Ex: First, Last, and Alternate. Sometimes a qualifier is unnecessary and can be omitted. Ex: StudentNumber.

A

Meaning

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

Blank is chosen from a list of standard attribute types such as Name, Number, and Count. Attribute types are not identical to SQL data types. Ex: “Amount” might be an attribute type representing monetary values, implemented as the MONEY data type in SQL. “Count” might be an attribute type representing quantity, implemented as NUMBER in SQL.

A

Type

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

Often, entity, relationship, and attribute names have blank

A

Synonyms

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

Synonyms are common in informal communications. To avoid confusion, one official name is selected for each entity, relationship, and attribute. Other names are blank as synonyms.

A

Documented as synonyms

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

In the first step of the logical design phase, each entity becomes a blank and each attribute becomes a blank.

A

Table
Column

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

As tables and columns are specified, blank are selected.

A

primary keys

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

Primary keys must be blank and not blank, and thus correspond to unique and required attributes

A

unique
null

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

Name three things primary keys should be (outside of unique and null)

A

Stable.
Simple
Meaningless.

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

Primary key values should not blank

A

change

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

When a primary key value changes, statements that blank the old value must also change. Furthermore, the new primary key value must blank to matching foreign keys.

A

specify
cascade

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

Primary key values should be easy to blank and blank. Small values are easy to specify in an blank clause and speed up query processing.

A

Type and store
SQL WHERE

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

Primary keys should not contain blank. Blank occasionally changes, so primary keys containing blank are unstable.

A

Descriptive information

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

Stable, simple, and meaningless primary keys are blank but not blank. Depending on database standards, these guidelines may be violated in some cases.

A

desired
required

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

On table diagrams, blank denote primary key columns.

A

solid bullets (●)

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

A strong entity becomes a blank.

A

strong table

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

Blank primary keys are best, but if no such column exists, a blank may have the required properties

A

Single-column
composite primary key

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

An blank is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.

A

artificial key

68
Q

Usually artificial key values are blank, generated automatically by the database as new rows are inserted to the table.

A

integers

69
Q

Artificial keys are blank, blank, and blank

A

stable, simple, and meaningless.

70
Q

A subtype entity becomes a what

A

subtype table

71
Q

How do you implement a subtype table

A

The primary key is identical to the supertype primary key.
The primary key is also a foreign key that references the supertype primary key

72
Q

The foreign key implements the blank.

A

IsA identifying relationship

73
Q

Foreign keys that implement identifying relationships usually have the what referential integrity two actions:

A

Cascade on primary key update and delete
Restrict on foreign key insert and update

74
Q

On table diagrams, blank denote foreign key columns.

A

open bullets (⚬)

75
Q

A weak entity becomes a blank.

A

weak table

76
Q

In a weak table, the primary key is usually blank

A

composite

77
Q

In a weak table, the primary key is usually composite and includes what two things

A

A foreign key that references the primary key of the identifying table.

Another column that makes the composite primary key unique.

78
Q

If no suitable column that makes the primary key unique is available in the weak table, an blank can be created.

A

artificial column

79
Q

In a weak table, if the identifying relationship is blank the second column is unnecessary and the primary key includes the foreign key only.

A

one-one, maximum,

80
Q

In a weak table, the foreign key implements the blank

A

identifying relationship

80
Q

In a weak table, the foreign key implements the identifying relationship and usually has what two referential integrity actions:

A

Cascade on primary key update and delete
Restrict on foreign key insert and update

81
Q

On table diagrams, blank enclose composite primary key columns.

A

parentheses

82
Q

An blank between two tables indicates a foreign key. The foreign key is at the tail of the blank and the referenced primary key is at the head.

A

arrow

83
Q

If an entity is identified by blank, the primary key includes one foreign key for each identifying table. The primary key may include additional columns for uniqueness, depending on business rules.

A

multiple entities

84
Q

Name the five steps to implementing entities

A

5A Implement strong entities as tables.
5B Create an artificial key when no suitable primary key exists.
5C Implement subtype entities as tables.
5D Implement weak entities as tables.
5E Specify cascade and restrict actions for identifying relationships.

85
Q

The ‘implement relationships’ step converts relationships into blank or blank depending on relationship cardinality.

A

keys or tables

86
Q

When implementing relationships, a many-one or one-many relationship becomes a blank

A

foreign key

87
Q

A many-one or one-many relationship becomes a foreign key. What three things happen on the table diagram

A

The foreign key goes in the table on the ‘many’ side of the relationship.

The foreign key refers to the primary key on the ‘one’ side.

The foreign key name is the primary key name with an optional prefix.

88
Q

How is a prefix for a foreign key derived in a table diagram

A

The prefix is derived from the relationship name and clarifies the meaning of the foreign key.

89
Q

When implementing relationships, a one-one relationship becomes a what?

A

foreign key

90
Q

When implementing relationships, a one-one relationship becomes a foreign key. what three things happen in the table diagram.

A

The foreign key can go in the table on either side of the relationship. Usually, the foreign key is placed in the table with fewer rows, to minimize the number of NULL values.

The foreign key refers to the primary key on the opposite side of the relationship.

The foreign key name is the primary key name with an optional prefix. The prefix is derived from the relationship name and clarifies the meaning of the foreign key.

91
Q

A many-many relationship becomes a new blank:

A

weak table

92
Q

a new weak table has what four characteristics

A

The new table contains two foreign keys, referring to the primary keys of the related tables.

The primary key of the new table is the composite of the two foreign keys.

The new table is identified by the related tables, so primary key cascade and foreign key restrict rules are specified.

The new table name consists of the related table names with an optional qualifier in between. The qualifier is derived from the relationship name and clarifies the meaning of the table.

93
Q

What four steps are performed when implementing relationships

A

6A Implement many-one relationships as foreign key on ‘many’ side.
6B Implement one-one relationships as foreign key in table with fewer rows.
6C Implement many-many relationships as new weak tables.
6D Specify cascade and restrict rules on foreign keys that implement dependency relationships.

94
Q

In the ‘implement entities’ step, entities become tables and attributes become columns. Blank attributes remain in the initial table, but blank attributes move to a new weak table:

A

Singular
Plural

95
Q

In a new weak table, the new table contains the plural attribute and a blank referencing the initial table.

A

foreign key

96
Q

In a new weak table, the blank of the new table is the composite of the plural attribute and the foreign key.

A

primary key

97
Q

In a new weak table, the new table is identified by the initial table, so primary key blank and foreign key blank rules are specified.

A

cascade
restrict

98
Q

In a new weak table, the new table name consists of the blank followed by the blank.

A

Initial table name
Attribute name

99
Q

If a plural attribute has a small, fixed blank, the plural attribute can be implemented as blank in the initial table. However, implementing plural attributes in a new table blank and is usually a better solution.

A

maximum
multiple columns
simplifies queries

100
Q

When Implementing attribute types, each attribute has an blank included in the attribute name.

During logical design, an blank is defined for each blank and documented in the glossary.

A

Attribute type
SQL data type
Attribute type

101
Q

When an attribute becomes a column, the blank determines the column data type. Ex: The attribute type Code has standard data type CHAR(3), so the FlightCode column is implemented with data type CHAR(3).

A

attribute type

102
Q

Attributes can be blank, blank or blank:

A

unique, required, or optional:

103
Q

Each unique attribute instance describes at most blank.

A

one entity instance

104
Q

Each entity instance has at least one required blank.

A

attribute instance

105
Q

Each entity instance can have zero blank.

A

optional attribute instances

106
Q

Unique and required attributes are implemented with keywords following the column name in the blank

A

CREATE TABLE statement

107
Q

Blank attributes and relationships become columns with NULLs allowed and do not require special keywords.

A

Optional

108
Q

Unique has a blank cardinality of one

A

maximum

109
Q

Not null has a blank cardinality of one

A

minimum

110
Q

What is the cardinality of a primary key

A

Maximum and minimum cardinality of one.

111
Q

No keyword in the column descriptions has a blank cardinality of zero

A

minimum

112
Q

What are the four steps of implementing attributes

A

7A Implement plural attributes as new weak tables.
7B Specify cascade and restrict rules on new foreign keys in weak tables.
7C Specify column data types corresponding to attribute types.
7D Enforce relationship and attribute cardinality with UNIQUE and NOT NULL keywords.

113
Q

Column A blank on column B means each B value is related to at most one A value.

A

Depends

114
Q

Dependence of one column on another is called blank

A

functional dependence.

115
Q

Functional dependence reflects blank

A

Business rules

116
Q

Functional dependence cannot be inferred from values in a table at blank

A

One point in time

117
Q

Multivalued dependence and join dependence entail dependencies between blank

A

Three and more columns

118
Q

Blank is the repetition of related values in a table

A

Redundancy

119
Q

Redundancy causes blank problems. When related values are updated, all copies must be changed, which makes queries slow and complex. If copies are not updated uniformly, the copies become inconsistent and the correct version is uncertain.

A

Database management

120
Q

Blank are rules for designing tables with less redundancy.

A

Normal forms

121
Q

Blank are numbered, first through fifth.

A

Normal forms

122
Q

An additional normal form, blank , is an improved version of third normal form.

A

Boyce-Codd

123
Q

The six normal forms comprise a blank, with each successive normal form allowing less redundancy.

A

Sequence

124
Q

Redundancy occurs when a dependence is on a column that is not blank

A

Unique

125
Q

Blank normal form eliminates all dependencies on non-unique columns and, in practice, is the most important normal form.

A

Boyce-Codd

126
Q

Blank and blank normal forms eliminate multivalued and join dependencies, respectively.

A

Fourth and fifth

127
Q

Eliminates multivalued dependencies and associated redundancy.

A

Fourth normal form

128
Q

Eliminates all redundancy arising from functional dependencies.

A

Boyce-Codd normal form

129
Q

Allows the most redundancy of any normal form.

A

First normal form

130
Q

Eliminates join dependencies and associated redundancy.

A

Fifth normal form

131
Q

Every cell of a table contains exactly one value. A table is in blank when, in addition, the table has a primary key.

A

First normal form

132
Q

In a first normal form table, every non-key column depends on the blank

A

Primary key

133
Q

In blank, each primary key value appears in exactly one row, and each non-key cell contains exactly one value. So each primary key value is related to exactly one non-key value.

A

First normal form

134
Q

A first normal form table has no blank. Every row contains a different primary key value and therefore every row is different.

A

Duplicate rows

135
Q

In practice, databases allow tables with duplicate rows and no primary key. However, such tables are usually blank

A

Temporary

136
Q

Normally, when data is moved to a blank duplicate rows are removed and a primary key is created.

A

permanent table

137
Q

First normal form is commonly defined in what four characteristics:

A

The table has a primary key.

Every non-key column depends on the primary key.

The table cannot have duplicate rows.

Every cell contains exactly one value.

138
Q

A table is in blank when all non-key columns depend on the whole primary key. In other words, a non-key column cannot depend on part of a composite primary key.

A

Second normal form

139
Q

A table with a blank is automatically in second normal form.

A

Simple primary key

140
Q

Redundancy can occur in a second normal form table when a non-key column depends on blank

A

another non-key column.

141
Q

Informally, a table is in blank when all non-key columns depend on the key, the whole key, and nothing but the key.

A

Third normal form

142
Q

In a Boyce-Codd normal form table, all dependencies are on blank

A

Unique columns

143
Q

Dependence on a unique column never creates redundancy, so blank eliminates all redundancy arising from functional dependence.

A

Boyce-Codd normal form

144
Q

A blank is a simple or composite column that is unique and minimal.

A

Candidate key

145
Q

Blank means all columns are necessary for uniqueness.

A

Minimal

146
Q

A blank column is a column that is not contained in a candidate key.

A

Non-key

147
Q

A table is in blank if, whenever a non-key column A depends on column B, then B is unique. Columns A and B may be simple or composite. Although B is unique, B is not necessarily minimal and therefore is not necessarily a candidate key.

A

Third normal form

148
Q

The definition of third normal form applies to blank columns only, which allows for occasional redundancy.

A

Non-key

149
Q

Boyce-Codd normal form applies to blank columns and eliminates this redundancy.

A

all

150
Q

A table is in blank normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite. This definition is identical to the definition of third normal form with the term ‘non-key’ removed.

A

Boyce-Codd

151
Q

Blank normal form is considered the gold standard of table design.

A

Boyce-Codd

152
Q

When the columns of A are a subset of the columns of B, A always depends on B. Ex: FareClass depends on (FlightCode, FareClass). These dependencies are called blank

A

trivial

153
Q

Technically, trivial dependencies must be excluded in definitions of normal form: A table is in Boyce-Codd normal form if, for all blank B → A, B is unique.

A

non-trivial dependencies

154
Q

Implementing entities, relationships, and attributes usually generates tables with no redundancy. Occasionally, however, implementation results in redundant tables. This redundancy is eliminated with blank

A

normalization

155
Q

normalization is the last step of blank

A

logical design

156
Q

Normalization eliminates redundancy by blank a table into two or more tables in higher normal form.

A

decomposing

157
Q

In a Boyce-Codd normal form table, if column A depends on column B, then B must be blank.

A

unique

158
Q

Normalizing a table to Boyce-Codd normal form involves what three steps:

A

List all unique columns.
Identify dependencies on non-unique columns.
Eliminate dependencies on non-unique columns.

159
Q

In E. F. Codd’s original paper on the relational model, normalization meant achieving blank. Over time, however, normalization has come to mean achieving higher normal forms.

A

first normal form

160
Q

Blank is ideal for tables with frequent inserts, updates, and deletes. In a database used primarily for reporting, changes are infrequent and redundancy is acceptable.

A

Boyce-Codd normal form

161
Q

In fact, blank can be desirable in reporting databases, as processing is faster and queries are simpler. Therefore, reporting databases may contain tables that, by design, are not in third normal form.

A

redundancy

162
Q

Blank means intentionally introducing redundancy by merging tables.

A

Denormalization

163
Q

Denormalization eliminates blank and therefore improves query performance.

A

join queries

164
Q

Denormalization results in blank and blank normal form tables and should be applied selectively and cautiously.

A

First and Second

165
Q

Name the three steps in applying normal form

A

8A Identify dependencies on non-unique columns.
8B Eliminate redundancy by decomposing tables.
8C Consider denormalizing tables in reporting databases.

Data Foundations (7 decks)

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