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.
2
Q
In an airline reservation system, Passenger and Booking are $.
A
Passenger and Booking are entities.
3
Q
What would 1 & 2 represent?
A
- Holds is a relationship between Passenger and Booking.
- PassengerNumber, PassengerName, BookingCode, BookingCost are attributes.
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.
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).
6
Q
What are attributes in an Entity-Relationship Model?
A
Descriptive properties of an entity.
7
Q
In SQL implementation, what do entities typically become?
A
Entities typically become tables.
8
Q
In SQL implementation, what do relationships become?
A
Relationships become foreign keys.
9
Q
In SQL implementation, what do attributes become?
A
Attributes become columns.
10
Q
What is an ER Diagram & it’s representations?
A
A visual representation showing:
- entities as rectangles
- relationships as lines
- attributes as additional text.
11
Q
Consider the ER Diagram
what would the entity-relationship-entity be read as?
A
12
Q
What is a glossary document?
Glossary or data dictionary
A
Documents names, synonyms, and descriptions of entities, relationships, and attributes.
13
Q
Define ‘Entity Type’ in the context of ER modeling.
A
Set of all instances
(e.g., all employees).
14
Q
Define ‘Relationship Type’ in the context of ER modeling.
A
Set of related pairs
(e.g., Employee-Manages-Department).
15
Q
Define Attribute Type in the context of ER modeling.
A
Set of values
(e.g., all employee salaries).
16
Q
What is a supertype entity?
A
A general category that encompasses subtypes.
17
Q
What is a subtype entity?
A
A specific subset of a supertype.
18
Q
What does ‘IsA’ relationship refer to?
A
The relationship between a supertype and its subtypes.
19
Q
A supertype may have $ subtypes.
A
Multiple
20
Q
In ER modeling, what notation is used to represent cardinality?
A
Crow’s Foot Notation.
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.
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’.
23
Q
What are the typical options for relationship minima?
A
Zero (optional) or one (required).
24
Q
What is a partition in the context of supertype and subtype entities?
A
A group of mutually exclusive subtypes within a supertype.
25
Why can't an entity instance be in two subtypes of the _same_ partition?
## Footnote
This is a fundamental rule in entity-relationship modeling and database design.
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](https://g.co/gemini/share/8e906e6065bb)
## Footnote
This ensures data integrity and avoids ambiguity.
26
How can an entity instance; be in two subtypes of _different_ partitions?
1. An entity instance cannot be in two subtypes of different partitions in a database model because, by definition, partitions are designed to be mutually exclusive.
2. An entity can only belong to one subtype within a specific partition; if it belongs to a different partition, it must be in a different subtype within that partition.
27
What are the main steps in the database design process?
Analysis, Logical Design, Physical Design.
28
What is the goal of the analysis phase in database design?
## Footnote
Often intertwined with conceptual logic
1. Gather data requirements
2. To develop the entity-relationship **model**.
29
What does the logical design phase involve?
_Converting_ ERM to tables, foreign keys, and columns.
30
What is the main purpose of the discovery phase in database design?
*Identifying* entities, relationships, and attributes through **interviews** with database users and managers.
31
What types of sources can be used to identify data _requirements_?
Interviews and written documents.
32
How should entities be named, when used in terms of conventions?
Singular nouns
## Footnote
(e.g., 'Employee').
33
What format should relationships be named in?
Entity-Verb-Entity
## Footnote
(e.g., Division-Contains-Department).
34
What is a unique attribute?
An attribute where each *entity instance has a unique value* across the entire database.
## Footnote
[Unique attributes V Keys](https://g.co/gemini/share/87de5fb3424c)
35
What is a singular attribute?
Each entity instance has *at most one attribute value*.
36
What is the relationship maximum for the `Employee-Has-Telephone` relationship if each employee can have at most three telephone numbers?
`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.
37
What is the relationship minimum for the `Customer-Has-Identification` relationship if two forms of identification may be required?
`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.
38
What is the main purpose of **documenting cardinality** in ER diagrams?
To _reflect_ business rules.
39
What are the steps in the discovery process?
- 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.
40
In database design, when are *standard attribute types* determined?
ER diagram development.
41
Identify nouns as _______ in database design.
entities.
42
Recognize verbs as _______ in database design.
relationships.
43
Specify nouns as _______ in database design.
attributes.
44
What are the characteristics of good primary keys?
* Unique
* Not NULL
* Stable
* Simple
* Meaningless
45
What is the ideal primary key structure?
Single-column primary keys are ideal; *composite primary keys* can be used if unavailable.
## Footnote
primary key that uses multiple columns in a table to create a unique value for each row
46
What is the primary key of a **subtype table**?
Matches the primary key of the supertype.
47
What do weak entities become in a database?
Weak tables.
48
How should foreign keys behave when changes are made to the primary key in the parent table?
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.
49
When implementing **many-one** relationships, where is the *foreign key* placed?
In the table on the many side of the relationship.
## Footnote
[📖](https://share.evernote.com/note/996e3715-8fd6-0967-abc4-6c5f28ac9b7a)
50
In a one-one relationship, where is the foreign key typically placed?
In the **table** with fewer rows.
51
How is a many-many relationship **implemented**?
Using a new *weak table* containing two foreign keys.
## Footnote
[📖](https://share.evernote.com/note/eeb74326-1792-3c6c-6349-93058d1d32ca)
52
What is the primary key of the new table in a many-many relationship?
Composite of the two foreign keys.
53
What do plural attributes become in a database?
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.
54
What is the SQL data type for a Code attribute?
CHAR(3).
55
What are the types of attribute cardinality?
* Unique
* Required
* Optional
56
What SQL keyword enforces unique attributes?
UNIQUE.
57
What are the criteria for a table to be in First Normal Form (1NF)?
* Every cell contains exactly one value
* The table has a primary key
* No duplicate rows are allowed
58
What is the significance of functional dependence?
Reflects business rules.
59
What does Boyce-Codd Normal Form (BCNF) eliminate?
Redundancy arising from functional dependence.
60
What is a candidate key?
A simple or composite **column** that is *unique and minimal*.
61
What does **normalization** aim to achieve in database design?
Elimination of redundancy from database tables.
62
What is **de**normalization used for?
1. Enhancing query performance *by merging tables*.
2. Accelerates all `JOIN` queries.
63
What are the characteristics of denormalization?
* Reduces complexity
* _Encourages_ redundancy
* Not common in *frequently updated* databases
64
What should be done when a column A depends on a *non-unique column* B?
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.
## Footnote
[What if column b was unique](https://g.co/gemini/share/040224bdce14)
65
What is the formal definition of BCNF?
A table is in BCNF if, whenever column A depends on column B, then B is unique.
66
What is the relationship between redundancy and functional dependence?
* Functional Dependency: One attribute *determines the value* of another.
* Redundancy: Storing the same information *multiple times*, often due to functional dependencies.
67
What is normalization?
The process of eliminating redundancy from database tables.
68
What is the goal of normalization?
To refine the design by transforming tables *into higher normal forms*.
69
What is Boyce-Codd normal form?
A higher normal form that aims to **eliminate redundancy**.
70
What happens to a table *in first normal form* during normalization?
A table in first normal form *might be split* into two (or more) organized tables during normalization.
71
What are unique columns in a table?
Columns that can uniquely identify records within the table.
## Footnote
[Foreign vs unique column](https://share.evernote.com/note/74b0ce2c-34df-aa6b-cace-f8f4894da738)
72
Define dependency in the context of database columns.
Column A depends on column B when; each B value **relates** to *at most one* A value.
73
What must be true for a table to be in Boyce-Codd normal form?
If column A depends on column B, then B must be unique.
74
What is the first step in normalizing a table to Boyce-Codd normal form?
List all **unique** columns in the table.
75
What should be done if column A depends on a non-unique column B?
Column A should be removed from the original table and placed in a *new table with B*.
76
What does denormalization refer to?
The practice of merging tables and allowing some redundancy to enhance query performance.
77
When is denormalization typically used?
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
## Footnote
Infrequent means something that doesn't happen often or regularly
78
Why might a database designer choose to denormalize?
To reduce complexity and enhance query performance.
## Footnote
[To normalize or not normalize](https://g.co/gemini/share/4d7a90071da7)
79
What is are 2 characteristics of denormalization?
1. It encourages redundancy in data storage.
2. Accelerates all `JOIN` queries.
80
What should be carefully *considered before* denormalizing a database?
The frequency of changes to the data.
81
What activities are involved in applying normal form?
Identifying dependencies, eliminating redundancy, and considering denormalization.
82
How do database designers assess tables for Boyce-Codd normal form?
By identifying dependencies and unique columns.
83
What is the relationship between normalization and performance?
- Normalization reduces redundancy
- Denormalization can enhance query performance.
84
What is the significance of E. F. Codd in relation to normalization?
He originally defined the relational model and the concept of normalization.
85
What kind of queries does denormalization help reduce?
Join queries.
86
What is the primary focus during the database design process?
Ensuring tables meet Boyce-Codd normal form characteristics.
87
What are conventions in data organization?
Conventions are **rules** for *naming and organizing* files, data sets, and other data.
## Footnote
Conventions help in standardizing how data is structured and accessed.
88
What does it mean for subtypes to be mutually _exclusive_?
A **single entity** can only belong to **one subtype** within a given supertype.
## Footnote
This ensures clarity in classification and prevents ambiguity.
89
What is the _purpose_ of mutually exclusive subtypes?
To maintain data integrity and consistency.
## Footnote
This prevents overlap in *how entities are classified* within a supertype.
90
What is an **entity type** in entity-relationship modeling?
A _set_ of unique individuals
## Footnote
Example: The entity type `Passenger` includes all passengers in the system.
91
What does the **relationship type** represent in entity-relationship modeling?
A set of related pairs
## Footnote
Example: The relationship type `Passenger-Holds-Booking` represents pairs of passengers and their associated bookings.
92
What is an attribute type?
A *set of values* related to an entity
## Footnote
Example: The attribute type `BookingCode` encompasses all possible booking codes.
93
Define entity instance
An individual _member_ of the entity type
## Footnote
Example: `Muhammed Ali` is a specific entity instance representing a passenger.
94
What is a relationship instance?
A specific **assertion** about entities
## Footnote
Example: `Muhammed Ali holds 39240` describes the relationship involving the passenger and their booking.
95
What is an attribute instance?
An *individual value* related to an attribute
## Footnote
Example: `39240` is a specific instance of the `BookingCode` attribute.
96
In entity-relationship modeling, what do entities, relationships, and attribute _types_ correlate to?
1. Entities types: Tables
2. Relationships types: foreign keys
3. Attribute types: columns
## Footnote
Their instances correspond to rows, foreign key values, and column values, respectively.
97
What is the first step in the analysis process?
Discover entities, relationships, and attributes
98
What is the second step in the analysis process?
Determine cardinality
99
What is the third step in the analysis process?
Distinguish strong and weak entities
100
What is the fourth step in the analysis process?
Create supertype and subtype entities
101
What is the fifth step in the logical design process?
Implement entities
## Footnote
[📖](https://share.evernote.com/note/996e3715-8fd6-0967-abc4-6c5f28ac9b7a)
102
What is the sixth step in the logical design process?
- Implement relationships
- 6: (A|M:1)(B|1:1)(C|M:M)
## Footnote
[📖](https://share.evernote.com/note/996e3715-8fd6-0967-abc4-6c5f28ac9b7a)
103
What is the seventh step in the logical design process?
Implement attributes
104
What is the eighth step in the logical design process?
Apply normal form
105
What type of attribute is represented by *at most one* in a database?
Singular attribute
## Footnote
Indicates that there can be *a maximum of one instance of this attribute* per entity.
106
What type of attribute is indicated by the term 'several'?
Plural attribute
## Footnote
Indicates that there can be multiple instances of this attribute per entity.
107
What does it mean when an attribute is described as *optional*?
1. The _attribute_ does not have to be provided *for every record*.
2. This means that information may be left blank in the database
108
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."
Required attribute
## Footnote
An attribute that must be filled out for every entity.
109
What is the significance of the phrase *used to identify in relation to an attribute*?
Unique attribute
## Footnote
Indicates that *each instance of this attribute* must be distinct across the database.
110
StudentNumber is a(n) $ attribute of Student
Unique
## Footnote
Each student is assigned a unique eight-digit number.
111
TelephoneNumber is a(n) $ attribute of Student.
Optional
## Footnote
The telephone number is optional and may be left blank.
112
In database design, what does the *cardinality of relationships* depend on?
Business rules
## Footnote
This determines *how entities relate to one another* in terms of quantity.
113
MajorSubjectName is a(n) _______ attribute of Student.
plural
## Footnote
Indicates that students *can major* in **multiple** subjects.
114
What are normal forms in relational databases?
A set of rules and guidelines for structuring a relational database
115
What is the **purpose** of normal forms?
To *address specific types* of data redundancy and potential issues
116
List the most common normal forms.
* 1NF (First Normal Form)
* 2NF (Second Normal Form)
* 3NF (Third Normal Form)
* BCNF (Boyce-Codd Normal Form)
117
What is the first step in eliminating redundancy?
Identifying Redundancy by analyzing data to spot redundant information
118
What is decomposition in the context of normalization?
Breaking down tables into smaller, more focused tables *to reduce redundancy*
119
What does 1NF (First Normal Form) achieve?
Eliminates repeating groups of data within a single column
120
What is the goal of 2NF (Second Normal Form)?
Remove redundant data that depends *on only part* of the primary key
## Footnote
2NF aims to remove transitive dependencies
121
What does 3NF (Third Normal Form) eliminate?
Data that depends on non-key attributes (transitive dependencies)
122
What distinguishes BCNF (Boyce-Codd Normal Form) from 3NF?
BCNF is a more stringent version of 3NF, further reducing redundancy
123
To normalize a table called Customers, you might __ ?
create a separate table that further divides
124
What are the benefits of normalization?
* Reduced Data Redundancy
* Improved Data Integrity
* Increased Flexibility
* Better Performance
125
While *higher normal forms* improve data integrity, they don't always mean better performance. Why?
* More tables & joins: This can slow down queries.
* Normalization overhead: *Maintaining consistency across* multiple tables takes extra work.
## Footnote
The best approach depends on balancing performance needs with data integrity.
126
Why is it important to consider trade-offs when aiming for higher normal forms?
Sometimes, **slight redundancy** might be acceptable to improve query performance
127
What does IMS stand for?
Information Management System
## Footnote
IMS is a _hierarchical_ database management system used primarily on IBM mainframe systems.
128
What type of database is IDMS?
Network database management system
## Footnote
IDMS stands for Integrated Database Management System.
129
What is a key characteristic of a hierarchical database?
Data is organized in a tree-like structure
## Footnote
Hierarchical databases utilize parent-child relationships.
130
What type of database does *Oracle Database Relational* refer to?
Relational database management system
## Footnote
Oracle Database uses SQL for data manipulation.
131
What is Neo4?
A graph database management system
## Footnote
Neo4j is designed to handle highly connected data.
132
What type of data structure does *ObjectStore Object* represent?
Object-oriented database management system
## Footnote
It allows for the storage of objects rather than just data.
133
What is MongoDB classified as?
Document database management system
## Footnote
MongoDB stores data in JSON-like format.
134
What are the primary components of a graph database?
## Footnote
Vertices represent entities, while edges represent relationships between them.
135
Define *primary data structure*.
The **main format** used to store data in a database
## Footnote
Examples include tables, trees, and linked lists.
136
What is a *Binary tree* an example of?
Tree data structure
## Footnote
A binary tree is a type of tree where each node *has at most two children*.
137
What is a linked list?
1. A linear data structure *where elements are stored* in nodes
2. Each node points to the next node in the sequence.
138
What is XML?
1. Extensible Markup Language
2. XML is used to define rules for encoding documents in a format that is *both human-readable and machine-readable*.
139
What does JSON stand for?
JavaScript Object Notation
## Footnote
JSON is a lightweight format for data interchange that is *easy for humans* to read and write.
140
What is a advantage of *document databases*?
Flexible schema
## Footnote
Document databases allow for unstructured and semi-structured data.
141
What are the advantages of *fast queries* in a database?
Improved performance and user experience
## Footnote
Fast queries reduce wait times for data retrieval.
142
What does *productivity and simplicity* refer to in database management?
- Ease of *use and efficiency* in database operations
- *Simplified database management* enhances user productivity.
143
A flexible schema can adapt to _?
1. Evolving business requirements
2. Flexible schemas are essential for *accommodating changes in data needs*.
144
What type of applications benefit from transactional databases?
Transactional applications
## Footnote
These applications require reliable and consistent data processing.
145
What visual representation difference is notable between Crow's Foot and Chen?
Lines in Crow's Foot, diamonds in Chen
## Footnote
This refers to how relationships are depicted in each model.
146
How does Crow's Foot symbolize cardinality?
Uses distinctive 'crow's foot' symbols at the ends of relationship lines
## Footnote
This contrasts with Chen's method of using numbers and letters.
147
How does Chen represent cardinality in its diagrams?
Uses *numbers and letters* near the relationship diamond
## Footnote
This provides a different approach than Crow's Foot.
148
Where are attributes placed in Crow's Foot diagrams?
Sometimes inside the entity rectangle
## Footnote
This can lead to a cleaner look compared to Chen.
149
Where does Chen place attributes in its diagrams?
Always keeps them separate from the entity
## Footnote
This is a consistent feature of Chen's model.
150
What does it mean that `ProjectCode` has at most one code?
The `ProjectCode` attribute is **singular**, meaning *each entity instance has* at most one attribute value.
## Footnote
That $ : Singular
151
What does it mean that **each** project code describes *at most* one project?
## Footnote
[At most? 📖](https://g.co/gemini/share/d74c41cc0fba)
The `ProjectCode` attribute is **unique**, meaning each attribute value describes at most one entity instance.
## Footnote
M: Plural: unique
152
A project *may have* no code. The `ProjectCode` attribute is ?
- Optional means an entity instance may have no *attribute value*.
- This is the case for `ProjectCode`, so `ProjectCode` is optional.
## Footnote
May : optional
153
1. A project may have no code.
2. Is `ProjectCode` an *identifying attribute* of the `Project` entity?
1. No, `ProjectCode` is optional, *not required, and therefore not an identifying* attribute of `Project`.
2. An identifying attribute is unique, singular, and required.
154
The foreign key implements the ________ identifying relationship.
**IsA**
155
Foreign keys that *implement identifying relationships* usually have the following referential integrity actions: __________ on primary key update and delete, and __________ on foreign key insert and update.
1. Cascade *on primary* key update and delete
2. Restrict *on foreign* key insert and update
156
What is Block Storage?
- Data is stored in fixed-size blocks, *typically with direct attached storage* or over a Storage Area Network (SAN)
- Block storage offers low latency and high performance.
- Suitable for applications requiring fast access to data, such as databases and transactional systems.
157
What is File Storage?
Data is stored as files organized within a **hierarchical** structure of folders
## Footnote
Accessed over a network using protocols like NFS or SMB. Well-suited for file sharing, general-purpose storage, and applications needing file locking capabilities.
158
What is Object Storage?
Data is stored as objects, each containing the *data, metadata, and a unique identifier*
- Accessed over a network using HTTP or REST APIs.
- Highly scalable and ideal for *storing large amounts of unstructured data* like images, videos, and backups.
159
List common use cases for Block Storage.
* Databases
* Virtual machines
* Transactional applications
160
List common use cases for File Storage.
* File sharing
* Web servers
* Content management systems
161
List common use cases for Object Storage.
* Cloud storage
* Backup and archiving
* Big data analytics
* Media storage
162
Which storage type is widely used for general-purpose storage and file sharing?
File Storage
## Footnote
Used in both on-premises and cloud environments.
163
Which storage type is increasingly popular for cloud-native applications?
Object Storage
## Footnote
Due to its scalability and cost-effectiveness.
164
Which storage type remains essential for applications requiring high performance and **low latency**?
Block Storage
## Footnote
Particularly in enterprise and database environments.
165
What is Hybrid Storage?
A combination of storage types to meet diverse needs
## Footnote
Many organizations utilize hybrid storage solutions.
166
What do **cloud providers** offer in terms of storage services?
Various storage services, including *block, file, and object storage*, with different performance and cost tiers.
167
What does the `UNIQUE` keyword enforce?
## Footnote
[📖](https://share.evernote.com/note/1d3cc363-f03b-9f8b-d3b3-bfa5ce0dbb8f)
Unique attributes and relationships have **maximum** cardinality *of one*.
## Footnote
This ensures that no two records can have the same value for the unique attribute.
168
What does the `NOT NULL` keyword enforce?
## Footnote
[📖](https://share.evernote.com/note/1d3cc363-f03b-9f8b-d3b3-bfa5ce0dbb8f)
_Required_ attributes and relationships have **minimum** cardinality *of one*.
## Footnote
This means that a value must be present in the column.
169
What does the `PRIMARY KEY` keyword specify?
## Footnote
[📖](https://share.evernote.com/note/1d3cc363-f03b-9f8b-d3b3-bfa5ce0dbb8f)
A primary key column must be unique and required
## Footnote
This corresponds to maximum and minimum cardinality of one.
170
What does minimum cardinality of zero indicate?
## Footnote
[📖](https://share.evernote.com/note/1d3cc363-f03b-9f8b-d3b3-bfa5ce0dbb8f)
`NULL` values are allowed
## Footnote
This means that columns can contain no value when no keyword is specified.
171
What is the maximum cardinality of one?
## Footnote
[📖](https://share.evernote.com/note/1d3cc363-f03b-9f8b-d3b3-bfa5ce0dbb8f)
It refers to the enforcement of _unique_ attributes and relationships
## Footnote
This means each instance can only relate to one instance of another entity.
172
What is redundancy?
1. Redundancy is the repetition of **related values** in a table.
2. When related values *are updated, all copies must be changed*, which makes queries slow and complex.
3. If copies are *not updated uniformly, the copies become inconsistent* and the correct version is uncertain.
173
What does Fourth Normal Form eliminate?
Multivalued dependencies and associated redundancy
## Footnote
A multivalued dependency occurs when two independent plural attributes are implemented in the same table.
174
What must be done to achieve Fourth Normal Form?
Independent plural attributes must be implemented *in separate tables*.
175
What does Boyce-Codd Normal Form eliminate?
All redundancy arising from functional dependencies
## Footnote
Redundancy occurs when a column depends on a non-unique column.
176
What is the least restrictive normal form?
First Normal Form
177
What does First Normal Form allow?
The most types of redundancy
178
What does Fifth Normal Form eliminate?
Join dependencies and associated redundancy
## Footnote
A join dependency occurs when a table is the join of other tables with fewer columns.
179
What must be done to achieve Fifth Normal Form?
The larger table must be replaced by the smaller tables
180
What are the most *general types* of dependencies?
**Join dependencies** which include *functional and multivalued* dependencies.
181
Which *Join dependencies* are less common?
Join dependencies that are *not functional dependencies* are less common.
