43 Questions
What is the primary responsibility of a database administrator?
Enforcing procedures for user access and database system availability
What is the main function of the query processor in a database system?
Interpreting queries and creating a plan to modify or retrieve data
What is the purpose of the storage manager in a database system?
Creating efficient indexes for database storage
What is the role of the transaction manager in a database system?
Ensuring transactions are properly executed
Which type of table is particularly fast for bulk load of many rows?
Heap table
What kind of function is the modulo function in a hash table?
Simple hash function
What is the percentage of table rows selected by a query known as?
Hit ratio
What type of index contains an entry for every table row?
Dense index
Which database object maps one or more tables to a single file?
Tablespace
What does logical design specify in a database?
Tables, columns, and keys
What does a storage engine or storage manager translate into low-level commands?
Instructions generated by a query processor
What SQL statement is used to create an index?
CREATE INDEX
Which statement best describes the responsibility of a transaction manager?
Preventing conflicts between concurrent transactions and restoring a database to a consistent state in case of failure
Which operation is supported by MongoDB?
INSERT
What does the SQL's CREATE TABLE statement do?
Defines a new table with specified names and data types
What is the purpose of the logical design phase in database design?
Converts entities, relationships, and attributes into tables, keys, and columns
What is the main function of an application programming interface (API) like MySQL Command-Line Client?
Used to execute SQL statements
What is a tuple in the context of databases?
An ordered collection of elements
What are business rules based on in the context of databases?
Specific database requirements
Which SQL data type has the largest storage size and range among the given options?
BIGINT
Which SQL operator is used for exponentiation?
^
What SQL statement is used for deleting all rows in a table?
TRUNCATE
In SQL, what does a foreign key constraint ensure?
Referential integrity
Which SQL constraint is used to enforce data validation rules on a table?
Check constraint
Which SQL keyword is used to modify table data and structure?
ALTER TABLE
What is a high-level representation of data requirements in SQL that includes entities, relationships, and attributes?
Entity-relationship model
What does a reflexive relationship relate in SQL?
An entity to itself
Which SQL join combines each row from the first table with every row from the second table?
Cross join
What does a primary key constraint prevent in SQL?
Inserting duplicate values
In SQL, what does the % operator calculate?
Modulo
Which clause is used in SQL to specify the conditions that must be met for the records to be selected during a query?
WHERE
In an entity-relationship diagram (ER diagram), how are entities represented?
As rectangles
What does cardinality refer to in an ER diagram?
The maximum and minimum number of instances of one entity that can relate to a single instance of another entity
What is the purpose of primary keys in a table?
To uniquely identify a record
What is the first step in creating an ER diagram?
Analysis
What does data modeling progress through after analysis in the context of ER diagrams?
Logical design, analysis, physical design
What is the relationship between one column and another in the context of functional dependence?
Functional dependence
How are subtype entities documented within the supertype entity in an ER diagram?
As a subset of the supertype entity
What does normalization aim to achieve in a table?
Minimize redundancy
Which notation is used in ER diagrams to depict cardinality?
Crow's foot notation
What happens when tables are in various normal forms?
Minimize redundancy
What does a primary key represent for a table?
To uniquely identify a record
What is the role of an intangible entity in a database?
It is not documented
Study Notes
- An entity-relationship diagram (ER diagram) is a visual representation of entities, relationships, and attributes
- Entities are represented as rectangles, with types being sets of things (e.g., all employees in a company) and instances being individual things (e.g., the employee Sam Snead)
- Types of entities include entity types (sets of things) and relationship types (sets of related things)
- Attributes are represented as sets of values and are usually transformed into tables, columns, and keys, respectively
- Analysis is the first step in creating an ER diagram, during which entities, relationships, and attributes are discovered
- Cardinality refers to the maximum and minimum number of instances of one entity that can relate to a single instance of another entity
- Subtype entities are a subset of a supertype entity and are documented within the supertype entity in an ER diagram
- Primary keys are unique identifiers for a table that should be stable, simple, and meaningless
- Data modeling progresses through steps of analysis, logical design, and physical design
- Functional dependence is the relationship between one column and another, and normalization is the process of eliminating redundancy in a table
- ER diagrams use crow's foot notation to depict cardinality
- An intangible entity is documented but not tracked with data in the database
- Tables can be in various normal forms (e.g., third normal form, Boyce-Codd normal form) to minimize redundancy
- Trivial dependencies occur when one column is a subset of another, and normalization eliminates this redundancy
- Databases can have different table structures, such as heap tables with no imposed order on rows.
Test your knowledge on data types and their ranges with this quiz. From TINYINT to BIGINT, this quiz covers the different integer data types and their storage capacities.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
