Database Catalog and Metadata Quiz

The catalog, also known as a data dictionary, is a directory of database objects including tables, columns, indexes, and other components.

Leading database systems are predominantly relational, storing data in tables, columns, and rows.

SQL is the query language for relational databases, used for data manipulation and querying.

Relational databases are suitable for databases requiring accurate transaction records.

The rise of big data and the internet in the late 1990s led to the emergence of non-relational databases (NoSQL) optimized for handling large, unstructured data.

Open-source software licensing has become a popular alternative to commercial database systems since 2000.

Common queries include CRUD operations (Create, Read, Update, and Delete) and are used to manipulate and access data.

SQL query language includes statements for data manipulation and database administration.

Database design process includes three phases: analysis, logical design, and physical design.

Analysis phase specifies database requirements, represented as entities, relationships, and attributes.

Logical design implements database requirements in a specific database system, converting entities, relationships, and attributes into tables, keys, and columns.

Physical design adds indexes and optimizes table storage, affecting query processing speed.

Relational database design includes logical and physical design, with logical design affecting query results and physical design affecting query processing speed.

Auto-increment columns: assigned automatically incrementing values when new rows are inserted, defined using the AUTO_INCREMENT keyword in a CREATE TABLE statement.

Errors when inserting primary keys: inserting values for auto-increment primary keys or omitting values for non-auto-increment primary keys.

MySQL allows insertion of a specific value to an auto-increment column, but this may lead to errors in managing primary keys.

Foreign keys: refer to a primary key and have the same data type but may have different names. They obey the rule of referential integrity which requires foreign key values to match primary key values or be NULL.

Multiple foreign keys may refer to the same primary key and a foreign key can refer to a primary key in the same table.

Foreign key constraint: added to a CREATE TABLE statement using the FOREIGN KEY and REFERENCES keywords, it ensures referential integrity by rejecting statements that violate it.

Referential integrity: a relational rule requiring foreign key values to either be fully NULL or match some primary key value.

Referential integrity can be violated in four ways: primary key update, foreign key update, primary key deletion, or foreign key insertion.

Automatic correction of referential integrity violations: databases can automatically correct violations using actions such as RESTRICT, SET NULL, SET DEFAULT, or CASCADE.

ON UPDATE and ON DELETE clauses: specify actions to be taken when the referenced primary key is updated or deleted.

Primary key updates and deletes: MySQL supports constraints for these actions, but there are limitations depending on the configuration.

Column and table constraints: rules that govern allowable values in a database, implemented using special keywords in a CREATE TABLE statement.

UNIQUE constraint: ensures values in a column or group of columns are unique and can be applied as a column or table constraint.

CHECK constraint: specifies an expression on one or more columns and is violated when the expression is FALSE and satisfied when it is TRUE or NULL.

Constraints are added and dropped using the ALTER TABLE statement followed by an ADD, DROP, or CHANGE clause.

An alias is a temporary name assigned to a column or table using the AS keyword.

The EXISTS operator is used in correlated subqueries and returns TRUE if a subquery selects at least one row and FALSE if no rows are selected.

Subqueries can be rewritten as joins for better performance, a process called flattening a query.

View tables are created to improve table design for users by restructuring columns and data types without altering the underlying database design.

View tables do not store data; instead, they merge the view query with an SQL query, and the database executes the merged query against base tables.

Materialized views store the view data, requiring regular refreshes when base tables change.

Views offer several advantages, such as data security by hiding sensitive information, saving complex queries, and optimizing performance.

Inserting, updating, and deleting data in views is problematic due to primary keys, aggregate values, and join views.

The WITH CHECK OPTION clause in databases that support view updates prevents inserts and updates that do not satisfy the view query conditions.

Relational algebra is a mathematical approach to manipulating data in databases, with nine operations represented by Greek letter symbols.

Each relational algebra operation corresponds to an SQL query and defines a single result table.

The operations include Select (Sigma), Project (Pi), Product (multiplication), Join (⋈), Union (∪), Intersect (∩), Difference (-), Rename (ρ), and Aggregate (γ).

Model complex systems into subject areas with entities, where strong entities are independent and weak entities are dependent.

UML and IDEF1X are popular modeling conventions for software development and database design respectively.

Intangible entities are documented in data models but not tracked with data in databases, distinguished with special notation.

In the logical design phase, entities become tables and attributes become columns, primary keys are selected.

Primary keys should be unique, non-NULL, stable, simple, and meaningless for efficient database management.

Strong entities become strong tables, with stable, simple, and meaningless primary keys, either single-column or composite.

Subtype entities become subtype tables, implementing IsA relationship with identical primary keys and referential integrity actions.

One-one relationships become foreign keys, placed in the table with fewer rows to minimize NULL values.

Many-many relationships become new weak tables, containing composite primary keys and identified by related tables.

Implementing relationships results in foreign keys or new tables, with referential integrity actions for cascade and restrict.

Database design involves adding foreign keys to initial table design, converting many-one, one-one, and many-many relationships.

Plural attributes move to new weak tables, containing primary keys and foreign keys, and their primary keys are composite.

Column-oriented storage stores values for a single column in each block

Column-oriented storage benefits analytic applications due to faster data access and better data compression

In column-oriented storage, reading or writing an entire row requires accessing multiple blocks, making it less suitable for transactional applications

Heap table is a table structure in which rows are not ordered and are stored in the order they are inserted

Heap tables optimize insert operations, especially for bulk loads, but are not optimal for queries that read rows in a specific order

Sorted table is a table structure where rows are ordered according to a sort column and are stored in blocks based on the value of the sort column

Sorted tables are optimized for queries that read data in order of the sort column but are not optimal for insert or update operations

Hash table is a table structure where rows are assigned to buckets based on a hash function and a hash key

Hash tables distribute rows evenly across blocks, making them suitable for queries that require random access to rows, but their performance can degrade when the number of rows grows

Table clusters, also called multi-tables, interleave rows of two or more tables in the same storage area, making them less suitable for most queries and not commonly used.

A single-level index is a file containing column values and pointers to the rows containing the column value

Indexes can be created on a single column or multiple columns, and the database can use either a table scan or an index scan to execute a query

Hit ratio, or the percentage of table rows selected by a query, influences the decision between a table scan and an index scan. A high hit ratio indicates a table scan is more efficient, while a low hit ratio indicates an index scan is more efficient.