47 Questions
What is the role of a database administrator in a database system?
Enforcing procedures for user access and database system availability
What is the responsibility of the query processor in a database system?
Interpreting queries, creating a plan to modify the database, and returning query results
What is the purpose of the storage manager in a database system?
Using indexes to quickly locate data and translating query processor instructions into low-level file-system commands
What is the role of the transaction manager in a database system?
Ensuring transactions are properly executed
What is the purpose of a heap table?
Optimize insert operations
What type of table is particularly fast for bulk load of many rows?
Heap table
What is the purpose of a table scan?
Read table blocks directly, without accessing an index
What does the hit ratio measure in a query?
Percentage of table rows selected by a query
What type of index contains an entry for every table row?
Dense index
What is the purpose of a tablespace in a database?
Map one or more tables to a single file
What does a storage engine or storage manager do in a database?
Translates instructions into low-level commands to access data on storage media
What is the purpose of a CREATE INDEX statement in a database?
Create an index on specified columns of a table
What is an ER diagram?
A visual representation of entities, relationships, and attributes
What are entities in an ER diagram?
Things represented as rectangles
What is the first step in database design according to the text?
Analysis
What does cardinality refer to in database design?
The maximum and minimum number of instances of one entity related to another
What is the relationship between entities when one is a subtype of another?
IsA relationship
What is the next step after analysis in database design?
Logical design
What are primary keys in a database table used for?
To identify each record in a table
What is functional dependence in database terminology?
A relationship between columns where one column depends on another
What is normalization in the context of database design?
A process of eliminating redundancy in a database table
Which normal form is ideal for tables with frequent inserts, updates, and deletes?
Boyce-Codd normal form (BCNF)
What is the purpose of indexes in database tables?
To provide faster queries by allowing quick lookup of specific values
What is the difference between heap tables and tables with indexes?
Tables with indexes have no order imposed on rows, while heap tables are sorted based on a specific attribute
Which SQL operator is used to determine if a value falls between two other values?
BETWEEN
What type of keys are used to identify rows in SQL?
Primary keys
Which SQL statement deletes all rows from a table?
TRUNCATE
What is the purpose of auto-increment columns in SQL?
Assign unique values to new rows
What do foreign keys refer to in SQL?
Primary keys in another table
Which type of data representation includes entities, relationships, and attributes?
Entity-Relationship Model
What type of relationship relates an entity to itself?
Reflexive relationship
In SQL, what do materialized views store?
Table data
What type of query is nested within another SQL query?
Subquery
What is the purpose of the CHECK OPTION in a materialized view?
Rejects insert and updates that do not satisfy the view query WHERE clause.
In SQL, what are constraints used for?
To enforce rules on allowable values in a database
Which statement is true about the transaction manager?
It is responsible for preventing conflicts between concurrent transactions and restoring a database to a consistent state in case of failure.
What type of database is MongoDB?
Open-source NoSQL database
Which statement is true about SQL databases?
They use the CREATE TABLE statement to create new tables, specify column names, and assign data types.
What does physical design in database optimization involve?
Adding indexes and organizing tables on storage media without affecting query results.
What is the MySQL Command-Line Client used for?
Interacting with a MySQL database system through a text interface.
How many sublanguages is SQL divided into?
Five
What does a table consist of in a database?
Named columns, a variable set of rows, and a fixed sequence of columns.
What are business rules in the context of a database?
Policies specific to a particular database.
What do keywords have in SQL statements?
Special meaning
What does the ALTER TABLE statement do?
Modifies an existing table's columns
What are data types in SQL databases used for?
Defining the nature of data that can be stored in a column or variable
What does logical design involve in the context of databases?
Conversion of entities, relationships, and attributes into tables and columns
Study Notes
- The transaction manager is responsible for preventing conflicts between concurrent transactions and restoring a database to a consistent state in case of failure.
- MongoDB is an open-source NoSQL database, offering INSERT, SELECT, UPDATE, and DELETE operations.
- SQL databases use the CREATE TABLE statement to create new tables, specify column names, and assign data types.
- Data types include numeric, textual, and complex types, such as INT, DECIMAL, VARCHAR, and DATE.
- Logical design converts entities, relationships, and attributes into tables, keys, and columns in a specific database system.
- Physical design optimizes database performance by adding indexes and organizing tables on storage media, without affecting query results.
- The MySQL Command-Line Client is a text interface used to interact with a MySQL database system.
- SQL is divided into five sublanguages: Data Definition Language, Data Query Language, Data Manipulation Language, Data Control Language, and Data Transaction Language.
- A table consists of named columns, a variable set of rows, and a fixed sequence of columns. Each row is an unnamed tuple of values, and each value belongs to its respective column's data type.
- Business rules are policies specific to a particular database, and entities, relationships, and attributes are represented during analysis.
- Keywords have special meaning in SQL statements, such as SELECT, FROM, WHERE, and IDENTIFIER.
- The ALTER TABLE statement modifies an existing table's columns, and the DROP TABLE statement deletes a table and its rows.
- Integer data types represent different sizes of integers, such as INT (4 bytes) and SMALLINT (2 bytes).
Test your knowledge of SQL data types with this quiz! Identify the appropriate storage, range, and usage of TINYINT, SMALLINT, MEDIUMINT, INTEGER/INT, and BIGINT.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
