47 Questions
Database administrators are responsible for optimizing query performance and ensuring efficient data retrieval
False
Database systems do not enforce any restrictions on user access to specific tables, columns, or rows of a database
False
The query processor interprets queries, creates a plan to modify the database, and returns query results to the application
True
The storage manager uses indexes to quickly locate data because database sizes can range from megabytes to many terabytes
True
Hash tables are particularly fast for bulk load of many rows, since rows are stored in load order.
True
A table scan is a database operation that reads index blocks sequentially, in order to locate the needed table blocks.
False
Hit ratio, also called filter factor or selectivity, is the percentage of table rows selected by a query.
True
In a binary search, the database repeatedly splits the index in two until it finds the entry containing the search value.
True
Hash indexes contain an entry for every table block.
False
Physical design affects query performance but never affects query results.
False
A bitmap index is a grid of bits: Bitmap indexes contain ones and zeros.
True
A tablespace is a database object that maps one or more tables to a single file.
True
An ER diagram can only represent entities, not relationships or attributes.
False
Entity instances are drawn as rectangles in an ER diagram.
False
Entity types and relationship types are both sets of related things.
True
Attribute types become tables, columns, and keys in an ER diagram.
False
Cardinality in an ER diagram refers to the maximum number of instances that can relate to each other.
True
Subtype entities are tracked with data in the database.
False
Creating supertype and subtype entities is not part of the analysis steps in ER modeling.
False
Logical design involves converting the ER model into tables, columns, and keys for a specific database system.
True
Primary keys should uniquely identify each column in a table.
False
Functional dependence is the relationship between one column and another in database design.
True
Normalization eliminates redundancy by decomposing a table into one or more tables.
True
Heap tables impose an order on rows in a database.
False
True or false: The transaction manager is responsible for preventing conflicts between concurrent transactions and restoring the database to a consistent state in the event of a failure.
True
True or false: In MongoDB, data can be INSERTed, SELECTed, UPDATEd, or DELETEd from tables.
False
True or false: In logical database design, entities, relationships, and attributes are converted into tables, keys, and columns.
True
True or false: Physical design adds indexes and determines how tables are organized on storage media, affecting query processing speed but never the query results.
False
True or false: The MySQL Command-Line Client is a text interface for the MySQL Server, used for executing SQL statements.
True
True or false: A table in a relational database consists of a name, a fixed sequence of columns, and a varying set of rows.
True
True or false: Data is structured in tables, allowing each value to belong to a specific column and its data type.
True
True or false: Business rules are based on business policy and specific to a particular database. They can include literals, keywords, identifiers, and comments.
True
True or false: SQL uses an application programming interface to simplify use with a general-purpose language.
False
True or false: Existing tables can be modified using the ALTER TABLE statement.
True
True or false: Integer data types represent positive and negative integers and are allocated varying amounts of storage (e.g., INT, INTEGER, SMALLINT).
True
SQL data types include TINYINT, SMALLINT, INTEGER (MEDIUMINT or BIGINT), and their respective signed and unsigned ranges, as well as their descriptions and uses.
True
The UPDATE statement modifies existing rows in a table, using the SET clause to specify new values and an optional WHERE clause to specify which rows are updated.
True
The TRUNCATE statement deletes all rows from a table, differing from DELETE in its impact on the table.
True
A primary key is a column or group of columns used to identify a row, with simple primary keys consisting of a single column and composite primary keys consisting of multiple columns.
True
Auto-increment columns have an automatically incrementing value when a new row is inserted, but should not be manually overridden.
True
A foreign key references a primary key and keeps related data consistent between tables.
True
The FOREIGN KEY constraint ensures referential integrity, rejecting insert, update, or delete statements that violate it.
True
The BETWEEN operator allows determining if a value falls between two other values.
True
Self-joins join a table to itself, while cross-joins combine two tables without comparison.
True
A materialized view is a view for which data is stored and refreshed when the base table changes.
True
An entity-relationship model represents data requirements at a high level, ignoring implementation details and consisting of entities, relationships, and attributes.
True
An entity is a person, place, product, concept, or activity, and a relationship is a statement about two entities.
True
Study Notes
- The text discusses various SQL concepts, including data types, operators, statements, primary keys, foreign keys, constraints, and queries.
- SQL data types include TINYINT, SMALLINT, INTEGER (MEDIUMINT or BIGINT), and their respective signed and unsigned ranges, as well as their descriptions and uses.
- Arithmetic operators in SQL include addition, subtraction, multiplication, division, and modulo, all of which take numeric values as operands.
- The UPDATE statement modifies existing rows in a table, using the SET clause to specify new values and an optional WHERE clause to specify which rows are updated.
- The DELETE statement deletes existing rows in a table, using the FROM keyword followed by the table name and an optional WHERE clause to specify which rows are deleted.
- The TRUNCATE statement deletes all rows from a table, differing from DELETE in its impact on the table.
- The MERGE statement merges data from one table (source) into another (target).
- A primary key is a column or group of columns used to identify a row, with simple primary keys consisting of a single column and composite primary keys consisting of multiple columns.
- Auto-increment columns have an automatically incrementing value when a new row is inserted, but should not be manually overridden.
- A foreign key references a primary key and keeps related data consistent between tables.
- The FOREIGN KEY constraint ensures referential integrity, rejecting insert, update, or delete statements that violate it.
- Database constraints include ADD, DROP, or CHANGE constraints, which can be added or dropped from a table with the ALTER TABLE statement.
- The BETWEEN operator allows determining if a value falls between two other values.
- Self-joins join a table to itself, while cross-joins combine two tables without comparison.
- A subquery is a query within another SQL query, and an alias is a temporary name assigned to a column or table.
- A materialized view is a view for which data is stored and refreshed when the base table changes.
- When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query's WHERE clause.
- An entity-relationship model represents data requirements at a high level, ignoring implementation details and consisting of entities, relationships, and attributes.
- An entity is a person, place, product, concept, or activity, and a relationship is a statement about two entities.
- An attribute is a descriptive property of an entity, and a reflexive relationship relates an entity to itself.
Test your knowledge of MySQL data types with this quiz focusing on the various sizes and ranges of integer data types such as TINYINT, SMALLINT, MEDIUMINT, INTEGER (or INT), and BIGINT.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
