47 Questions
What is the role of a database administrator?
Enforcing procedures for user access and database system availability
What is the purpose of database authorization?
Authorizing individual users to access specific data
What does the query processor do?
Interpreting queries and creating a plan to modify the database
Why does the storage manager use indexes?
To quickly locate data in large databases
Which type of table is particularly fast for bulk load of many rows?
Heap table
What is the percentage of table rows selected by a query called?
Hit ratio
Which index type contains an entry for every table block?
Sparse index
What database operation reads index blocks sequentially?
Index scan
What database object maps one or more tables to a single file?
Tablespace
Which function translates instructions generated by a query processor into low-level commands that access data on storage media?
Storage engine
What specifies indexes, table structures, and partitions?
Physical design
In which type of index are index entries assigned to buckets?
Hash index
What is the purpose of a primary key in SQL?
To identify a row uniquely
Which SQL constraint ensures referential integrity?
Foreign key constraint
What does the TRUNCATE statement do in SQL?
Deletes all rows in a table
What is the purpose of the WHERE clause in SQL?
To delete specific rows based on a condition
Which SQL operator is used for exponentiation?
^
What are TINYINT, SMALLINT, INTEGER, MEDIUMINT, and BIGINT in SQL?
Data types for integer values
What does the UPDATE statement do in SQL?
Modifies existing rows
In SQL, what does the entity-relationship model represent?
A high-level representation of data requirements
Which SQL keyword is used to modify table data and structure?
ALTER TABLE
What do self-joins, cross-joins, materialized views, and subqueries represent in SQL?
Advanced SQL concepts
What type of relationship relates an entity to itself in SQL?
Reflexive relationship
Which SQL statement is used to delete specific rows based on a condition?
DELETE
What is the responsibility of the transaction manager?
Preventing conflicts between concurrent transactions and restoring a database to a consistent state in case of failure
What operations does MongoDB support?
INSERT, SELECT, UPDATE, DELETE
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?
Converts entities, relationships, and attributes into tables, keys, and columns
What is the principle of data independence?
Ensuring query results are not affected by physical design changes
Which API is used to execute SQL statements?
MySQL Command-Line Client
What is a tuple in the context of databases?
An ordered collection of elements
What are business rules based on?
Specific database requirements
What are the sublanguages of SQL?
DDL, DQL, DML, DCL, DTL
What does the CREATE TABLE statement do in SQL?
Creates a new table with specified column names and data types
What are the integer data types in SQL?
INT (4 bytes) and SMALLINT (2 bytes)
What does the physical design phase involve?
Adding indexes and optimizing table organization on storage media
What is the primary purpose of an entity-relationship diagram (ER diagram)?
To visually represent entities, relationships, and attributes
In an ER diagram, how are entities typically represented?
As rectangles with types and instances
What is the purpose of cardinality in an ER diagram?
To specify the maximum and minimum number of instances of one entity that can relate to another
What are primary keys in the context of database tables?
Unique identifiers for tables that should be stable, simple, and meaningless
What does normalization aim to achieve in database design?
To eliminate redundancy and minimize data anomalies
What notation is commonly used in ER diagrams to depict cardinality?
Crow's foot notation
What is the role of analysis in creating an ER diagram?
To discover entities, relationships, and attributes
What type of entities are subtype entities in an ER diagram?
A subset of a supertype entity
What best describes the purpose of attribute representation in an ER diagram?
Attributes represent sets of values transformed into tables.
What is the significance of data normalization in database design?
Normalization eliminates redundancy and minimizes data anomalies.
How are trivial dependencies related to normalization in database tables?
Trivial dependencies occur when one column is a subset of another, and normalization eliminates this redundancy.
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 of SQL data types with this quiz. Learn about TINYINT, SMALLINT, MEDIUMINT, INTEGER, INT, and BIGINT data types and their storage and range.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
