Database Concepts Quiz

• The relational model for databases was introduced by E. F. Codd of IBM in 1970 and gained popularity with the release of commercial products around 1980.

• The data structure, operations, and rules are standardized in SQL, the query language used in relational databases.

• Big data emerged during the 1990s, characterized by massive data volumes and rapidly changing structures, necessitating the development of advanced database systems.

• In the relational model:

  • A set is a collection of elements (enclosed in braces).
  • A tuple is an ordered collection of elements (enclosed in parentheses).
  • A table has a name, fixed columns, and a varying set of rows.
  • A column has a name and a data type.
  • A row is an unnamed collection of values.
  • A cell is a single value in a table.
  • An empty table has no rows.

• The SQL language includes the following categories and components:

  • Data Definition Language (DDL) defines the database structure.
  • Data Query Language (DQL) retrieves data.
  • Data Manipulation Language (DML) manipulates data.
  • Data Control Language (DCL) controls access to the data, and
  • Data Transaction Language (DTL) manages transactions.

• Managing databases involves creating, dropping, and altering tables, as well as performing various administrative tasks.

  • Database system instance: a single executing copy of a database system.
  • Creating a new database with the CREATE DATABASE statement.
  • Deleting a database with the DROP DATABASE statement.
  • Selecting a default database and listing databases with the USE and SHOW DATABASES statements.
  • Listing tables and columns in a particular database.
  • Showing the CREATE TABLE statement for a particular table.
  • Creating, dropping, and altering tables with the CREATE TABLE, DROP TABLE, and ALTER TABLE statements.

• Data types are used in defining columns and include the following:

  • Integer data types for representing positive and negative integers.
  • Decimal data types for representing numbers with fractional values.
  • Character data types for representing textual characters.
  • Date and time data types for representing dates, times, and intervals.
  • Binary data types for storing data exactly as it appears in memory.
  • Spatial data types for storing geometric information.
  • Document data types for storing textual data in a structured format.
  • Signed and unsigned numbers for representing values that may be negative or positive, respectively.

• The SELECT statement is used to retrieve rows from a table and includes the SELECT and FROM clauses with the required syntax for specifying the data to be retrieved.

• Operators in SQL are symbols used to perform operations, with different types such as arithmetic, comparison, and logical operators, and the precedence determining the order of evaluation.

• The FROM clause indicates the table from which data is selected.

• The SELECT clause lists the expressions specifying the desired data.

• The SELECT statement returns a result table, which can be limited using the LIMIT clause.

• A condition specified in the WHERE clause determines which rows to select.

• NULL is a special value representing unknown or inapplicable data.

• NOT NULL constraint prevents columns from having NULL values in a table.

• Primary keys are used to uniquely identify rows and consist of one or more columns.

• The INSERT statement adds rows to a table, while UPDATE modifies existing ones and DELETE deletes them.

• AUTO_INCREMENT columns automatically assign unique values to new rows.

• The PRIMARY KEY constraint ensures uniqueness and non-null values for primary keys.

• Foreign keys refer to primary keys and maintain referential integrity, ensuring consistency between related tables.

• The UNIQUE constraint ensures that values in a column or group of columns are unique.

• Column and table constraints govern values in specific columns or entire tables.

• IN operator is used to select data when a value matches one of several specified values.

• BETWEEN operator checks if a value falls within a specified range.

• Self-join allows joining a table to itself for complex querying.

• Boyce-Codd normal form: A table is in Boyce-Codd normal form if each column A that depends on column B has a unique B.

• Normalization: Normalization is a process to eliminate redundancy in tables. In this form, column A depends on column B if each B is related to at most one A.

• Denormalization: Denormalization is the opposite of normalization, which intentionally introduces redundancy.

• Data Storage:

  • Access time: time required to access the first byte of data in a read or write operation.
  • Transfer rate: speed at which data is read or written after initial access.
  • Volatile memory: lost when disconnected from power.
  • Non-volatile memory: data is retained without power.
  • Main memory/RAM: primary memory used when programs execute.
  • Flash memory/SSD: less expensive and higher capacity than main memory.
  • Magnetic disk/HDD: used to store large amounts of data.
  • sectors: groups data in sectors, varying from 512 bytes to 4 kilobytes.
  • pages: data in flash memory is grouped into pages, usually between 2 kilobytes and 16 kilobytes.
  • blocks: databases and file systems transfer data between main memory and storage media in uniformly sized blocks.
  • Row-oriented storage: minimizes block transfers by storing an entire row in one block.
  • Column-oriented/columnar storage: each block stores values for a single column.

• Table structures:

  • Heap table: no order imposed on rows.
  • Sorted table: physical row order determined by a sort column.
  • Hash table: rows assigned to buckets based on hash key.
  • Buckets: a block or group of blocks containing rows.
  • Hash key: a column or group of columns, often the primary key.
  • Hash function: computes the bucket containing a row from the hash key.
  • Modulo function: a simple hash function that uses a four-step process.
  • Dynamic hash function: automatically adds more blocks and buckets to the table.

• Table clusters/multi-tables: interleaves rows of multiple tables in the same storage area.

  • Cluster key: a column available in all interleaved tables.

• Single-level indexes:

  • File containing column values and pointers to rows.
  • Multi-column index: each entry is a composite of all indexed columns.
  • Table scan: reads table blocks directly.
  • Index scan: reads index blocks sequentially.
  • Hit ratio: percentage of table rows selected by a query.
  • Binary search: efficiently searches indexes by repeatedly splitting the index in half.
  • Primary index: index on a sort column.
  • Secondary index: index not on a sort column.
  • Dense index: an entry for every table row.
  • Sparse index: an entry for every table block.

• Multi-level indexes: stores column values and row pointers in a hierarchical structure.

  • Fan-out: number of index entries per block.
  • Branch: a path from the top-level block to a bottom-level block.
  • Balanced/imbalanced: branches have the same length (balanced) or varying lengths (imbalanced).
  • B+tree: indexed values appear at all levels, pointers to table blocks appear only at the bottom level.
  • B-tree: indexed values appear at all levels, and a pointer to the corresponding table block is included at the higher level.

• Analysis involves developing an entity-relationship model to capture data requirements, ignoring implementation details.

• Logical design converts the entity-relationship model into tables, columns, and keys for a specific database system.

• Physical design adds indexes and specifies how tables are organized on storage media.

• In entity-relationship modeling, cardinality refers to the maximum and minimum number of instances of relationships and attributes.

• Relationship maximum refers to the greatest number of instances of one entity that can relate to a single instance of another entity, which can be singular or plural.

• Relationship minimum refers to the least number of instances of one entity that can relate to a single instance of another entity, which can be optional or required.

• An identifying attribute is unique, singular, and required, and its values correspond one-to-one with entity instances.

• A weak entity does not have an identifying attribute and instead has a relationship to an identifying entity, with a cardinality of 1(1).

• A subtype entity is a subset of another entity type (supertype entity) and is identified by an IsA relationship.

• Crow's foot notation is a popular convention for depicting cardinality that uses a circle for zero, a short line for one, and three short lines for many.

• A complex model can be decomposed into a group of related entities called a subject area.

• A strong entity becomes a strong table, while a weak entity becomes a weak table.

• A strong table has a unique and non-NULL primary key, while a weak table relies on a foreign key to establish a relationship to a strong table.

• A table is in the first normal form when every cell contains exactly one value and the table has a primary key.

• A table is in the second normal form when all non-key columns depend on the whole primary key.

• A table is in the third normal form when all non-key columns depend on the key and nothing but the key.

• Boyce-Codd normal form requires that a table is in third normal form and that any non-key column that depends on another column also depends on the entire primary key.

• The catalog or data dictionary is a directory of database objects including tables, columns, indexes, and other components that help process and execute queries.

• Metadata is data about the database, such as column names and number of rows in each table.

• Most leading database systems are relational, storing data in tables, columns, and rows with the same format in each column and row.

• SQL (Structured Query Language) is used in relational databases for data manipulation including queries to read and write data, create and delete tables, and administer the database system.

• Relational databases are suitable for accurate transaction records, such as banking, airline reservations, and student records.

• The growth of the internet in the 1990s led to massive volumes of unstructured or missing data, resulting in the emergence of non-relational databases.

• NoSQL databases, not limited to SQL, are optimized for handling big data with poor or missing information.

• Oracle Database, MySQL, SQL Server, PostgreSQL, and MongoDB are popular database systems with varying license types and support for SQL.

• A database design specifies database objects like tables, columns, data types, and indexes, with formal methods used for complex databases.

• Database design involves analyzing requirements, represented as entities, relationships, and attributes, to develop a specification for database implementation.

• Analysis phase converts entities, relationships, and attributes into tables, keys, and columns in logical design for relational databases.

• Physical design adds indexes and specifies table organization on storage media, affecting query processing speed without changing query results.

• Data independence is the principle that physical design does not affect query results.

= Expressions and Operators:

• An expression is a string of operators, operands, and parentheses that evaluates to a single value.
• Operands can be column names or fixed values, with any data type as the value.
• The value of an expression may depend on the order of operator evaluation and the use of parentheses.
• Operators have precedence and are evaluated in the order shown in the text.

= SELECT Statement:

• The SELECT statement selects rows from a table and has a SELECT and FROM clause.
• The SELECT clause determines what values are returned for each row, while the FROM clause specifies the table from which rows are selected.

= Conditions and NULL Values:

• A condition is an expression that evaluates to a logical value and can be used in a WHERE clause to select specific rows.
• NULL is a special value representing unknown or inapplicable data, which is not the same as zero or blanks for numeric or character data types.
• A NOT NULL constraint can be added to a column to prevent it from having a NULL value.

= Inserting, Updating, and Deleting Rows:

• The INSERT statement adds rows to a table using the INSERT INTO and VALUES clauses.
• The UPDATE statement modifies existing rows with the SET clause and an optional WHERE clause.
• The DELETE statement deletes rows with the FROM clause and an optional WHERE clause.
• The TRUNCATE statement deletes all rows from a table, similar to a DELETE statement without a WHERE clause.

= Primary Keys:

• A primary key is a column or group of columns used to uniquely identify a row.

• Primary keys must be unique and not null to ensure that each value identifies exactly one row.

• A composite primary key consists of multiple columns to uniquely identify a row, and it must also be unique, not null, and minimal.

• The PRIMARY KEY constraint in a CREATE TABLE statement ensures that the primary key is always unique and non-null.

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

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

• Not Exists returns TRUE if no rows are selected and FALSE if at least one row is selected.

• Subqueries can be rewritten as joins, a process called flattening, which is usually faster and preferred for better performance.

• View tables are created to restructure and optimize table design for users and programmers by creating a virtual table based on a SELECT statement.

• A view is associated with a SELECT statement called the view query, which can exclude sensitive columns while allowing access to other columns.

• Views do not store data but are merged with SQL queries to execute against base tables.

• Materialized views store the result of a query and are refreshed when base tables change.

• Views offer advantages such as protecting sensitive data, saving complex queries, and optimizing query performance.

• When using views with INSERT, UPDATE, and DELETE statements, difficulties may arise due to foreign keys, primary keys, and aggregate values.

• With the optional CHECK OPTION clause, databases can prevent inserting or updating rows that do not satisfy the view query WHERE clause.

• Relational algebra is a method used to manipulate data using nine operations, each represented by a Greek letter, with each symbol denoting a specific operation such as selection, projection, and join.

• Relationships have maximum and minimum: maximum is the greatest number of instances of one entity that can relate to another, minimum is the least.

• Maxima and minima are specified as one or many, zero or one.

• A related entity is singular when maximum is one, plural when it's many, optional when minimum is zero, and required when minimum is one.

• Attribute maximum and minimum also follow an entity in ER diagrams: maximum is the greatest number of attribute values, minimum is the least.

• Unique attributes describe at most one entity instance per attribute value, and are indicated by a "1" in ER diagrams, while non-unique attributes are indicated by an "M".

• Strong entities have one or more identifying attributes, weak entities have no identifying attribute and are connected to an identifying entity through an identifying relationship.

• Supertype and subtype entities: a subtype is a subset of a supertype and shares its attributes, but subtype entities within a partition do not share instances, while those in different partitions can.

• After determining entities, relationships, attributes, cardinality, and strong and weak entities, the database designer looks for supertype and subtype entities and partitions.

• ER diagram conventions vary, including depiction of relationship names, weak entities, subtype entities, and cardinality.

• Crow's foot notation is a popular cardinality convention, representing zero, one, and many relationships with circles, short lines, and three short lines respectively.

• ER modeling concepts also vary, including allowing relationships between three or more entities.

• Binary search is supported by databases for efficient data retrieval.

• A binary search repeatedly splits the index in half until it finds the entry with the search value.

• A database first compares the search value to the middle entry in the index, then narrows the search to one half.

• The process continues until the index block containing the search value is found.

• Primary and secondary indexes are used on sorted tables.

• Primary index, or clustering index, is an index on a sort column, creating a sorted table.

• Secondary index, or non-clustering index, is an index not on the sort column, and tables can have many secondary indexes.

• Indexes may be dense or sparse, with dense indexes containing an entry for every table row and sparse indexes containing an entry for every table block.

• Multi-level indexes are hierarchical, with each level a sparse-sorted index to the level below, and can be balanced or imbalanced.

• Balanced multi-level index is called a B+tree, while an earlier approach is called a B-tree, with the main difference being the repetition of indexed values.

• Hash index, bitmap index, logical index, and function index are additional index types.

• Hash indexes use buckets to store index entries, bitmap indexes store data as a grid of bits, and logical indexes store primary key values instead of pointers to table blocks.

• Function indexes contain the result of a function applied to the indexed column values.

• Tablespaces and partitions are database objects used to map tables to files, and a database can assign tables to specific tablespaces using the CREATE TABLESPACE and CREATE TABLE statements.