whole book Flashcards
study (191 cards)
1
Q
A
2
Q
What is data?
A
Data is numeric, textual, visual, or audio information that describes real-world systems.
3
Q
What are the main variations in data?
A
- Scope
- Format
- Access
4
Q
Define a database.
A
A database is a collection of data in a structured format.
5
Q
What is a database management system (DBMS)?
A
Software that reads and writes data in a database, ensuring data is secure, consistent, and available.
6
Q
What is a query?
A
A request to retrieve or change data in a database.
7
Q
What is a database application?
A
Software that helps business users interact with database systems.
8
Q
Who is responsible for securing the database system?
A
A database administrator.
9
Q
What does a database designer do?
A
Determines the format of each data element and the overall database structure.
10
Q
What role does a database programmer play?
A
Develops computer programs that utilize a database.
11
Q
What is the main purpose of a database user?
A
To request, update, or use stored data to generate reports or information.
12
Q
What are the main differences between file systems and database systems?
A
- Performance
- Authorization
- Security
- Rules
- Recovery
13
Q
What is a transaction in database systems?
A
A group of queries that must be either completed or rejected as a whole.
14
Q
What does the transaction manager do?
A
Ensures transactions are properly executed and restores the database to a consistent state.
15
Q
What is the purpose of the log file in a database system?
A
Contains a complete record of all inserts, updates, and deletes processed by the database.
16
Q
Define metadata.
A
Data about the database, such as column names and the number of rows in each table.
17
Q
What is SQL?
A
Structured Query Language used for reading and writing data in relational databases.
18
Q
List the four common database queries.
A
- Create
- Read
- Update
- Delete
19
Q
What does the SQL CREATE TABLE statement do?
A
Creates a new table by specifying the table and column names.
20
Q
What is the analysis phase in database design?
A
Specifies database requirements without regard to a specific database system.
21
Q
What are ER diagrams used for?
A
Depicting entities, relationships, and attributes in a database.
22
Q
What is the difference between logical and physical design in databases?
A
Logical design affects query results; physical design affects query processing speed.
23
Q
What is data independence?
A
Allows database designers to tune query performance without changes to application programs.
24
Q
What is an application programming interface (API)?
A
A library of procedures or classes that links a host programming language to a database.
25
What is MySQL Workbench?
A graphical user interface for interacting with MySQL Server.
26
What is the primary data structure in relational databases?
Table.
27
What does the term NoSQL refer to?
A new generation of non-relational databases optimized for big data.
28
What does the term 'CRUD' stand for?
Create, Read, Update, Delete.
29
What is a key in a database?
A column used to identify individual rows of a table.
30
What is the purpose of indexes in database design?
To quickly locate data.
31
What are the strengths of relational database systems?
* Productivity and simplicity
* Ideal for transactional applications
32
What is the significance of the number in a data type like VARCHAR(10)?
Indicates the maximum size of the data type.
33
What is the role of a transaction manager?
Ensures transactions are executed properly and resolves conflicts.
34
True or False: A tuple is an unordered collection of elements.
False.
35
Fill in the blank: The _______ phase in database design adds indexes and specifies how tables are organized on storage media.
physical design
36
What does the SQL INSERT statement do?
Inserts rows into a table.
37
What is the main focus of the physical design phase?
Specifying how tables are organized on storage media.
38
What is a tuple?
An ordered collection of elements enclosed in parentheses.
39
What are the components of a table in a relational database?
A table has a name, a fixed tuple of columns, and a varying set of rows.
40
Define a column in the context of a relational database.
A column has a name and a data type.
41
What is a row in a relational database?
An unnamed tuple of values where each value corresponds to a column.
42
What is a data type?
A named set of values from which column values are drawn.
43
True or False: A table has an inherent order of rows.
False
44
List synonyms for 'table' in a relational database context.
* File
* Relation
45
What does the SELECT operation do in relational operations?
Selects a subset of rows of a table.
46
What is the purpose of the Project operation in relational operations?
Eliminates one or more columns of a table.
47
What is the function of the Join operation?
Combines two tables by comparing related columns.
48
Define a unique primary key in relational rules.
All tables have a primary key column, or group of columns, in which values may not repeat.
49
What is the difference between relational rules and business rules?
Relational rules are logical constraints for data validity; business rules are specific to a particular database.
50
What is Structured Query Language (SQL)?
A high-level computer language for storing, manipulating, and retrieving data.
51
What is the preferred pronunciation of SQL?
'S-Q-L'
52
What constitutes an SQL statement?
A complete command composed of one or more clauses.
53
What are literals in SQL?
Explicit values that are string, numeric, or binary.
54
Name the five sublanguages of SQL.
* Data Definition Language (DDL)
* Data Query Language (DQL)
* Data Manipulation Language (DML)
* Data Control Language (DCL)
* Data Transaction Language (DTL)
55
What does the CREATE DATABASE statement do?
Creates a new database.
56
What is the purpose of the USE statement in SQL?
Selects a default database for use in subsequent SQL statements.
57
What does the SHOW TABLES statement do?
Lists all tables in the default database.
58
What is a cell in the context of a table?
A single column of a single row.
59
What does the DROP TABLE statement do?
Deletes a table, along with all the table's rows, from a database.
60
Fill in the blank: A data type is a named set of values from which _______ are drawn.
column values
61
What are the categories of data types in relational databases?
* Integer
* Decimal
* Character
* Date and time
* Binary
* Spatial
* Document
62
What does the ALTER TABLE statement do?
Adds, deletes, or modifies columns on an existing table.
63
What does the ADD clause in ALTER TABLE do?
Adds a column.
64
What is the function of the CHANGE clause in ALTER TABLE?
Modifies a column.
65
What does the DROP clause in ALTER TABLE do?
Deletes a column.
66
What is an operator in SQL?
A symbol that computes a value from one or more other values.
67
What are arithmetic operators used for?
Compute numeric values from numeric operands.
68
What is a comparison operator?
Compares two values for equality or inequality.
69
What is a logical operator?
Computes logical values from logical operands.
70
What is an expression in SQL?
A string of operators, operands, and parentheses that evaluates to a single value.
71
What does the WHERE clause do in a SELECT statement?
Specifies a condition for selecting rows.
72
What is NULL in SQL?
A special value that represents either unknown or inapplicable data.
73
What does the WHERE clause do in a SELECT statement?
Filters rows based on specified conditions
74
What is NULL?
A special value representing unknown or inapplicable data
75
What does the NOT NULL constraint do?
Prevents a column from having a NULL value
76
What is the purpose of the INSERT statement?
Adds rows to a table
77
What clauses does the INSERT statement include?
* INSERT INTO clause
* VALUES clause
78
What is the DEFAULT keyword used for in a CREATE TABLE statement?
Assigns a default value to a column when omitted from an INSERT statement
79
What does the UPDATE statement do?
Modifies existing rows in a table
80
What is the DELETE statement used for?
Deletes existing rows in a table
81
What is the difference between DELETE and TRUNCATE?
TRUNCATE deletes all rows from a table without a WHERE clause
82
What is a primary key?
A column or group of columns used to identify a row uniquely
83
What are the requirements for primary keys?
* Unique
* Not NULL
84
What is a composite primary key?
A primary key that consists of multiple columns
85
What is the PRIMARY KEY constraint?
Ensures that a column or group of columns is always unique and non-null
86
What is an auto-increment column?
A numeric column assigned an automatically incrementing value upon row insertion
87
What is a foreign key?
A column or group of columns that refer to a primary key
88
What does referential integrity ensure?
Foreign key values must either be NULL or match some value of the referenced primary key
89
What are the four ways referential integrity can be violated?
* A primary key is updated
* A foreign key is updated
* A row containing a primary key is deleted
* A row containing a foreign key is inserted
90
What does the CASCADE action do in referential integrity?
Propagates primary key changes to foreign keys
91
What is a constraint in a database?
A rule that governs allowable values in a database
92
What is the UNIQUE constraint?
Ensures that values in a column or group of columns are unique
93
What is the CHECK constraint?
Specifies an expression that must be TRUE or NULL for the constraint to be satisfied
94
What does the IN operator do?
Determines if a value matches one of several values
95
What is the BETWEEN operator used for?
Determines if a value is between two other values
96
What characters does the LIKE operator use for pattern matching?
* % matches any number of characters
* _ matches exactly one character
97
What does the DISTINCT clause do?
Returns only unique or distinct values in a SELECT statement
98
What does the ORDER BY clause do?
Orders selected rows by one or more columns
99
What is an aggregate function?
Processes values from a set of rows and returns a summary value
100
What is the purpose of the COUNT() function?
Counts the number of rows in the set
101
What does the SUM() function do?
Sums all the values in the set
102
What does the AVG() function compute?
Calculates the arithmetic mean of all the values in the set
103
What does an aggregate function do?
Processes values from a set of rows and returns a summary value.
104
Name five common aggregate functions.
* COUNT()
* MIN()
* MAX()
* SUM()
* AVG()
105
What clause is commonly used with aggregate functions?
GROUP BY clause
106
What is the purpose of the HAVING clause?
To filter group results after grouping.
107
Define a join in the context of relational databases.
A SELECT statement that combines data from two tables into a single result.
108
What is the difference between INNER JOIN and FULL JOIN?
* INNER JOIN: selects only matching rows
* FULL JOIN: selects all rows from both tables, with NULLs for unmatched rows
109
What does a LEFT JOIN do?
Selects all left table rows and matching right table rows.
110
What is an equijoin?
A join that compares columns of two tables using the = operator.
111
What is a self-join?
A join that compares a table to itself.
112
What is a cross-join?
Combines two tables without comparing columns, resulting in all possible combinations.
113
What is a subquery?
A query within another SQL query.
114
What makes a subquery correlated?
When its WHERE clause references a column from the outer query.
115
What does the EXISTS operator do?
Returns TRUE if a subquery selects at least one row.
116
What is flattening a query?
Replacing a subquery with an equivalent join.
117
What is a view table?
A table name associated with a SELECT statement, restructuring table columns.
118
What is the CREATE VIEW statement used for?
To create a view table and specify the view name and query.
119
What is a materialized view?
A view for which data is stored at all times.
120
List three advantages of using view tables.
* Protect sensitive data
* Save complex queries
* Save optimized queries
121
What is the WITH CHECK OPTION clause?
Prevents inserts or updates that do not satisfy the view query WHERE clause.
122
What are the nine operations of relational algebra?
* Select (σ)
* Project (π)
* Product (×)
* Join (⋈)
* Union (∪)
* Intersect (∩)
* Difference (−)
* Rename (ρ)
* Aggregate (γ)
123
What is an entity in an entity-relationship model?
A person, place, product, concept, or activity.
124
What is a relationship in an entity-relationship model?
A statement about two entities.
125
What is an attribute in an entity-relationship model?
A descriptive property of an entity.
126
What does an entity-relationship diagram represent?
A schematic picture of entities, relationships, and attributes.
127
What are the three phases of database design?
* Analysis
* Logical design
* Physical design
128
What is the first step in the analysis phase of database design?
Discover entities, relationships, and attributes.
129
What is the purpose of a glossary in database design?
Documents additional detail about entities, relationships, and attributes.
130
What is the naming convention for entity names?
A singular noun.
131
What should relationship names include?
The form Entity-Verb-Entity.
132
What is the significance of synonyms in database design?
To avoid confusion by documenting one official name for each entity, relationship, and attribute.
133
What is the purpose of a glossary in database design?
To document names, synonyms, and complete descriptions of entities, relationships, and attributes.
## Footnote
The glossary includes the meaning of each term and examples to illustrate usage.
134
What is the first step of the analysis phase in database design?
Discovery of entities, relationships, and attributes through interviews and document review.
135
What does cardinality refer to in entity-relationship modeling?
Maxima and minima of relationships and attributes.
136
What is the relationship maximum?
The greatest number of instances of one entity that can relate to a single instance of another entity.
137
What is the relationship minimum?
The least number of instances of one entity that can relate to a single instance of another entity.
138
Define unique attributes.
Each value of a unique attribute describes at most one entity instance.
139
What is a strong entity?
An entity that has one or more identifying attributes and can be implemented as a table with a primary key.
140
What is a weak entity?
An entity that does not have an identifying attribute and is usually identified by a strong entity through an identifying relationship.
141
What is the IsA relationship in the context of supertype and subtype entities?
The identifying relationship where a supertype entity identifies its subtype entities.
142
What are partitions in the context of supertype entities?
Groups of mutually exclusive subtype entities within a supertype entity.
143
What does the term 'crow's foot notation' refer to?
A popular convention for depicting cardinality in ER diagrams.
144
What is the purpose of primary keys in database design?
To ensure uniqueness and non-null values for each entity.
145
What characteristics should primary keys ideally have?
Stable, simple, and meaningless.
146
What happens to a many-one relationship in database design?
It becomes a foreign key in the table on the 'many' side of the relationship.
147
What is normalization in the context of database design?
The process of decomposing tables to eliminate redundancy.
148
Define first normal form.
A table is in first normal form when every cell contains exactly one value and the table has a primary key.
149
What is the definition of Boyce-Codd normal form?
A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is unique.
150
What is functional dependence?
When each value of column B is related to at most one value of column A.
151
True or False: A candidate key can be composite.
True.
152
Fill in the blank: A weak entity usually has a relationship called an _______ relationship to another entity.
identifying.
153
What is the primary key of a subtype entity?
Identical to the supertype primary key and also a foreign key referencing it.
154
What does the term 'trivial dependencies' refer to?
Dependencies where the columns of A are a subset of the columns of B.
155
What is the role of foreign keys in implementing relationships?
To establish the relationship between tables based on cardinality.
156
What does a many-many relationship become in database design?
A new weak table containing two foreign keys.
157
What is normalization in database design?
Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form.
## Footnote
Normalization is the last step of logical design in database implementation.
158
What is Boyce-Codd normal form?
A table is in Boyce-Codd normal form when every non-trivial functional dependency A depends on a unique column B.
## Footnote
In this form, if A depends on B, B must be unique.
159
List the steps to normalize a table to Boyce-Codd normal form.
* List all unique columns
* Identify dependencies on non-unique columns
* Eliminate dependencies on non-unique columns
## Footnote
The primary key is always included in the unique columns list.
160
What is denormalization?
Denormalization means intentionally introducing redundancy by merging tables to improve query performance.
## Footnote
It is applied selectively and cautiously.
161
What is row-oriented storage?
Row-oriented storage stores entire rows within one block, optimizing for transactional applications.
## Footnote
This approach minimizes block transfers.
162
What are the benefits of column-oriented storage?
* Faster data access
* Better data compression
## Footnote
Column-oriented storage is optimal for analytic applications.
163
Define a heap table.
A heap table is a structure where no order is imposed on rows, optimizing insert operations.
## Footnote
It is fast for bulk loading of many rows.
164
What is a sorted table?
A sorted table has rows assigned to blocks according to a sort column, optimizing read queries.
## Footnote
The sort column is usually the primary key.
165
Explain a hash table.
In a hash table, rows are assigned to buckets based on a hash function and hash key.
## Footnote
Buckets can grow dynamically as the table expands.
166
What is a single-level index?
A single-level index contains column values and pointers to rows containing those values.
## Footnote
It is created using the CREATE INDEX command.
167
Differentiate between primary and secondary indexes.
* Primary index: on a sort column
* Secondary index: not on a sort column
## Footnote
A sorted table can have only one primary index.
168
What is a multi-level index?
A multi-level index stores column values and row pointers in a hierarchy with a sparse structure.
## Footnote
It improves search efficiency by reducing the number of accesses needed.
169
What is a B+tree?
A B+tree is a balanced multi-level index where all indexed values appear in the bottom level.
## Footnote
It is commonly used for multi-level indexing.
170
What are bitmap indexes?
A bitmap index is a grid of bits where 'one' indicates the presence of a value and 'zero' indicates absence.
## Footnote
They are useful for columns with low cardinality.
171
Define a tablespace.
A tablespace is a database object that maps one or more tables to a single file.
## Footnote
It is created with the CREATE TABLESPACE statement.
172
What is a partition in database terms?
A partition is a subset of table data that does not overlap with others, improving query performance.
## Footnote
Partitions can be horizontal (rows) or vertical (columns).
173
What is a partition in database management?
A subset of table data, consisting of many partitions that do not overlap and contain all table data.
174
What are the two types of partitions?
* Horizontal partition
* Vertical partition
175
How do partitions improve query performance?
By reducing the amount of data accessed by INSERT, UPDATE, DELETE, and SELECT statements.
176
What is a shard in the context of databases?
A subset of table data, usually a subset of rows, stored on different computers of a distributed database.
177
What is the role of a partition expression?
Specifies how rows are assigned to partitions based on one or more partition columns.
178
What is a range partition?
Associates each partition with a range of partition expression values, defined by the VALUES LESS THAN keywords.
179
What does the MAXVALUE keyword represent in partitioning?
The highest column value in a range partition.
180
What is a list partition?
Associates each partition with an explicit list of partition expression values using the VALUES IN keywords.
181
What is a hash partition?
Requires a partition expression with positive integer values, where the partition number for each row is computed as (partition expression value) modulo N.
182
What does physical design specify in database management?
Indexes, table structures, and partitions.
183
What is the function of a storage engine?
Translates instructions generated by a query processor into low-level commands that access data on storage media.
184
What command is used to create an index?
CREATE INDEX IndexName ON TableName (Column1, Column2, ..., ColumnN);
185
What command is used to delete an index?
DROP INDEX IndexName ON TableName;
186
What does the EXPLAIN statement do?
Generates a result table that describes how a statement is executed by the storage engine.
187
What is the first step in the physical design process for MySQL with InnoDB?
Create initial physical design with primary and secondary indexes.
188
What is the purpose of the MySQL slow query log?
Records all long-running queries submitted to the database.
189
When should indexes be created or dropped based on EXPLAIN results?
Create indexes when rows value is high and filtered value is low; drop indexes that are never used.
190
When should tables be partitioned?
When slow queries access a small subset of rows of a large table.
191
Fill in the blank: A _______ partition is often best when partitioning large tables.
range
