Database Design Flashcards
1
Q
Database design begins with blank for the database.
A
verbal or written requirements
2
Q
Requirements are formalized as a blank and then implemented in SQL.
A
an entity-relationship model
3
Q
An entity-relationship model is a high-level representation of blank, ignoring implementation details.
A
Data requirements
4
Q
An entity-relationship model guides blank in a particular database system, such as MySQL.
A
Implementation
5
Q
An entity-relationship model includes what three kinds of objects:
A
Entity
Relationship
Attribute
6
Q
An blank is a person, place, product, concept, or activity.
A
Entity
7
Q
A blank is a statement about two entities.
A
Relationship
8
Q
An blank is a descriptive property of an entity.
A
Attribute
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
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
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
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
13
Q
An blank commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes.
A
entity-relationship diagram
14
Q
Entities are drawn as blank
A
Rectabgles
15
Q
Relationships are drawn as blank connecting rectangles
A
Lines
16
Q
Attributes appear as blank within an entity rectangle, under the entity name.
A
Additional text
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
18
Q
A blank, also known as a data dictionary or repository, documents additional detail in text format.
A
Glossary
19
Q
A glossary includes blank, blank, and blank of entities, relationships, and attributes.
A
names, synonyms, and descriptions
20
Q
The ER diagram and glossary are complementary and, together, completely describe an blank
A
entity-relationship model.
21
Q
In entity-relationship modeling, a blank is a set:
A
Type
22
Q
An blank is a set of things. Ex: All employees in a company.
A
Entity type
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
24
Q
An blank is a set of values. Ex: All employee salaries.
A
Attribute set
25
Entity, relationship, and attribute types usually become blank, blank, and blank , respectively.
tables, foreign keys, and columns, respectively.
26
An blank is an element of a set:
Instance
27
An blank is an individual thing. Ex: The employee Sam Snead.
Entity instance
28
A blank is a statement about entity instances. Ex: "Maria Rodriguez manages Sales."
Relationship instance
29
An blank is an individual value. Ex: The salary $35,000.
Attribute instance
30
Complex databases are developed in three phases. Name them
Analysis
Logical design
Physical design
31
Blank develops an entity-relationship model, capturing data requirements while ignoring implementation details.
Analysis
32
Blank converts the entity-relationship model into tables, columns, and keys for a particular database system.
Logical design
33
Blank adds indexes and specifies how tables are organized on storage media.
Physical design
34
Execution of database design is not always blank. Often an early step is revisited after a later step is completed.
Sequential
35
Blank is dependent on specific index and table structures, which vary greatly across relational databases.
Physical design
36
Name the four analysis steps
1. Discover entities, relationships, and attributes
2 Determine cardinality
3 Distinguish strong and weak entities
4 Create supertype and subtype entities
37
Name the four logical design steps
5 Implement entities
6 Implement relationships
7 Implement attributes
8 Apply normal form
38
Entities, relationships, and attributes are blank in interviews with database users and managers.
Discovered
39
In addition to interviews, blank are a good source of data requirements.
Written documents
40
In interviews and documents, entities, relationships, and attributes surface as blank and blank
Nouns and verbs
41
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.
Nouns
42
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.
Verbs
43
Attributes are usually blank that denote specific data, such as names, dates, quantities, and monetary values
Nouns
44
Entity names are a blank noun. Ex: Employee rather than Employees. The best names are commonly used and easily understood by database users.
Singular
45
Relationships names have the form blank, such as Division-Contains-Department.
Entity-Verb-Entity
46
When the related entities are blank, in ER diagrams or informal conversation, Verb is sufficient and entity names can be omitted.
Obvious
47
The verb should be blank rather than passive
Active
48
Occasionally, the same verb relates blank. Ex: Order-Contains-LineItem and Division-Contains-Department.
Different entity pairs
49
Attribute names have the form blank, such as EmployeeFirstName:
EntityQualifierType
50
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.
Entity
QualifierType
51
Qualifier describes the blank of the attribute. Ex: First, Last, and Alternate. Sometimes a qualifier is unnecessary and can be omitted. Ex: StudentNumber.
Meaning
52
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.
Type
53
Often, entity, relationship, and attribute names have blank
Synonyms
54
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.
Documented as synonyms
55
In the first step of the logical design phase, each entity becomes a blank and each attribute becomes a blank.
Table
Column
56
As tables and columns are specified, blank are selected.
primary keys
57
Primary keys must be blank and not blank, and thus correspond to unique and required attributes
unique
null
58
Name three things primary keys should be (outside of unique and null)
Stable.
Simple
Meaningless.
59
Primary key values should not blank
change
60
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.
specify
cascade
61
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.
Type and store
SQL WHERE
62
Primary keys should not contain blank. Blank occasionally changes, so primary keys containing blank are unstable.
Descriptive information
63
Stable, simple, and meaningless primary keys are blank but not blank. Depending on database standards, these guidelines may be violated in some cases.
desired
required
64
On table diagrams, blank denote primary key columns.
solid bullets (●)
65
A strong entity becomes a blank.
strong table
66
Blank primary keys are best, but if no such column exists, a blank may have the required properties
Single-column
composite primary key
67
An blank is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.
artificial key
68
Usually artificial key values are blank, generated automatically by the database as new rows are inserted to the table.
integers
69
Artificial keys are blank, blank, and blank
stable, simple, and meaningless.
70
A subtype entity becomes a what
subtype table
71
How do you implement a subtype table
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
The foreign key implements the blank.
IsA identifying relationship
73
Foreign keys that implement identifying relationships usually have the what referential integrity two actions:
Cascade on primary key update and delete
Restrict on foreign key insert and update
74
On table diagrams, blank denote foreign key columns.
open bullets (⚬)
75
A weak entity becomes a blank.
weak table
76
In a weak table, the primary key is usually blank
composite
77
In a weak table, the primary key is usually composite and includes what two things
A foreign key that references the primary key of the identifying table.
Another column that makes the composite primary key unique.
78
If no suitable column that makes the primary key unique is available in the weak table, an blank can be created.
artificial column
79
In a weak table, if the identifying relationship is blank the second column is unnecessary and the primary key includes the foreign key only.
one-one, maximum,
80
In a weak table, the foreign key implements the blank
identifying relationship
80
In a weak table, the foreign key implements the identifying relationship and usually has what two referential integrity actions:
Cascade on primary key update and delete
Restrict on foreign key insert and update
81
On table diagrams, blank enclose composite primary key columns.
parentheses
82
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.
arrow
83
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.
multiple entities
84
Name the five steps to implementing entities
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
The 'implement relationships' step converts relationships into blank or blank depending on relationship cardinality.
keys or tables
86
When implementing relationships, a many-one or one-many relationship becomes a blank
foreign key
87
A many-one or one-many relationship becomes a foreign key. What three things happen on the table diagram
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
How is a prefix for a foreign key derived in a table diagram
The prefix is derived from the relationship name and clarifies the meaning of the foreign key.
89
When implementing relationships, a one-one relationship becomes a what?
foreign key
90
When implementing relationships, a one-one relationship becomes a foreign key. what three things happen in the table diagram.
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
A many-many relationship becomes a new blank:
weak table
92
a new weak table has what four characteristics
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
What four steps are performed when implementing relationships
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
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:
Singular
Plural
95
In a new weak table, the new table contains the plural attribute and a blank referencing the initial table.
foreign key
96
In a new weak table, the blank of the new table is the composite of the plural attribute and the foreign key.
primary key
97
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.
cascade
restrict
98
In a new weak table, the new table name consists of the blank followed by the blank.
Initial table name
Attribute name
99
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.
maximum
multiple columns
simplifies queries
100
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.
Attribute type
SQL data type
Attribute type
101
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).
attribute type
102
Attributes can be blank, blank or blank:
unique, required, or optional:
103
Each unique attribute instance describes at most blank.
one entity instance
104
Each entity instance has at least one required blank.
attribute instance
105
Each entity instance can have zero blank.
optional attribute instances
106
Unique and required attributes are implemented with keywords following the column name in the blank
CREATE TABLE statement
107
Blank attributes and relationships become columns with NULLs allowed and do not require special keywords.
Optional
108
Unique has a blank cardinality of one
maximum
109
Not null has a blank cardinality of one
minimum
110
What is the cardinality of a primary key
Maximum and minimum cardinality of one.
111
No keyword in the column descriptions has a blank cardinality of zero
minimum
112
What are the four steps of implementing attributes
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
Column A blank on column B means each B value is related to at most one A value.
Depends
114
Dependence of one column on another is called blank
functional dependence.
115
Functional dependence reflects blank
Business rules
116
Functional dependence cannot be inferred from values in a table at blank
One point in time
117
Multivalued dependence and join dependence entail dependencies between blank
Three and more columns
118
Blank is the repetition of related values in a table
Redundancy
119
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.
Database management
120
Blank are rules for designing tables with less redundancy.
Normal forms
121
Blank are numbered, first through fifth.
Normal forms
122
An additional normal form, blank , is an improved version of third normal form.
Boyce-Codd
123
The six normal forms comprise a blank, with each successive normal form allowing less redundancy.
Sequence
124
Redundancy occurs when a dependence is on a column that is not blank
Unique
125
Blank normal form eliminates all dependencies on non-unique columns and, in practice, is the most important normal form.
Boyce-Codd
126
Blank and blank normal forms eliminate multivalued and join dependencies, respectively.
Fourth and fifth
127
Eliminates multivalued dependencies and associated redundancy.
Fourth normal form
128
Eliminates all redundancy arising from functional dependencies.
Boyce-Codd normal form
129
Allows the most redundancy of any normal form.
First normal form
130
Eliminates join dependencies and associated redundancy.
Fifth normal form
131
Every cell of a table contains exactly one value. A table is in blank when, in addition, the table has a primary key.
First normal form
132
In a first normal form table, every non-key column depends on the blank
Primary key
133
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.
First normal form
134
A first normal form table has no blank. Every row contains a different primary key value and therefore every row is different.
Duplicate rows
135
In practice, databases allow tables with duplicate rows and no primary key. However, such tables are usually blank
Temporary
136
Normally, when data is moved to a blank duplicate rows are removed and a primary key is created.
permanent table
137
First normal form is commonly defined in what four characteristics:
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
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.
Second normal form
139
A table with a blank is automatically in second normal form.
Simple primary key
140
Redundancy can occur in a second normal form table when a non-key column depends on blank
another non-key column.
141
Informally, a table is in blank when all non-key columns depend on the key, the whole key, and nothing but the key.
Third normal form
142
In a Boyce-Codd normal form table, all dependencies are on blank
Unique columns
143
Dependence on a unique column never creates redundancy, so blank eliminates all redundancy arising from functional dependence.
Boyce-Codd normal form
144
A blank is a simple or composite column that is unique and minimal.
Candidate key
145
Blank means all columns are necessary for uniqueness.
Minimal
146
A blank column is a column that is not contained in a candidate key.
Non-key
147
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.
Third normal form
148
The definition of third normal form applies to blank columns only, which allows for occasional redundancy.
Non-key
149
Boyce-Codd normal form applies to blank columns and eliminates this redundancy.
all
150
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.
Boyce-Codd
151
Blank normal form is considered the gold standard of table design.
Boyce-Codd
152
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
trivial
153
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.
non-trivial dependencies
154
Implementing entities, relationships, and attributes usually generates tables with no redundancy. Occasionally, however, implementation results in redundant tables. This redundancy is eliminated with blank
normalization
155
normalization is the last step of blank
logical design
156
Normalization eliminates redundancy by blank a table into two or more tables in higher normal form.
decomposing
157
In a Boyce-Codd normal form table, if column A depends on column B, then B must be blank.
unique
158
Normalizing a table to Boyce-Codd normal form involves what three steps:
List all unique columns.
Identify dependencies on non-unique columns.
Eliminate dependencies on non-unique columns.
159
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.
first normal form
160
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.
Boyce-Codd normal form
161
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.
redundancy
162
Blank means intentionally introducing redundancy by merging tables.
Denormalization
163
Denormalization eliminates blank and therefore improves query performance.
join queries
164
Denormalization results in blank and blank normal form tables and should be applied selectively and cautiously.
First and Second
165
Name the three steps in applying normal form
8A Identify dependencies on non-unique columns.
8B Eliminate redundancy by decomposing tables.
8C Consider denormalizing tables in reporting databases.
