Database Concepts Quiz

CapableAmethyst avatar
CapableAmethyst
·
·
Download

Start Quiz

Study Flashcards

253 Questions

What is a database?

A collection of data in a structured format

What is a query language?

A specialized programming language for database systems

What is the main function of a database system?

To ensure data is secure, internally consistent, and available at all times

What is the role of a database administrator?

Responsible for securing the database system against unauthorized users

What was historically used to encode data?

Continuous variations on various physical media

What is the definition of data?

Numeric, textual, visual, or audio information that describes real-world systems

What does digital data refer to?

Encoded as zeros and ones on electronic and magnetic media

What is the purpose of a database application?

To help business users interact with database systems

What is a subquery in SQL?

A query within another SQL query

What is a correlated subquery in SQL?

A subquery's WHERE clause references a column from the outer query

What is an alias in SQL?

A temporary name assigned to a column or table

What is the purpose of the EXISTS operator in SQL?

Returns TRUE if a subquery selects at least one row

What is the standard language for querying relational databases?

SQL

What does the term 'flattening' refer to in SQL?

Replacing a subquery with an equivalent join

What is a materialized view in SQL?

A view for which data is stored at all times

What is the role of the MySQL Command-Line Client?

Text interface for connecting to the database and executing SQL statements

Which component is responsible for ensuring proper execution of database transactions?

Transaction manager

What are Codd's operations, also known as relational algebra, used for?

Manipulating tables based on formal operations

What does the query optimizer do in SQL?

Converts an SQL query into a sequence of low-level database actions

What is the architectural model of a database system based on?

Tabular data structures

What type of data model is the relational model based on?

Tabular model

What does a database programmer develop software for?

Software that utilizes a database

In which format do relational databases store data?

Tables, columns, and rows

What is a database design specification of?

Database objects, including tables, columns, and data types

What statement adds rows to a table?

INSERT

Which clause determines which rows to select?

WHERE

What does the NOT NULL constraint prevent?

Columns from having NULL values

What do FOREIGN KEYS refer to and maintain?

Primary keys, referential integrity

What does the UNIQUE constraint ensure?

Columns contain only unique values

What is the purpose of the IN operator?

To select data matching specified values

What does the BETWEEN operator check?

If a value is within a specified range

What does the Self-join allow for?

Joining different tables for complex querying

What is a tuple in the relational model?

An ordered collection of elements enclosed in parentheses

Which category of SQL language retrieves data?

Data Query Language (DQL)

What is used to create a new database in SQL?

CREATE DATABASE statement

Which data type is used for storing textual characters in SQL?

Character data types

What statement is used to retrieve rows from a table in SQL?

SELECT statement

What does the Data Control Language (DCL) control in SQL?

Access to the data

Which type of operator in SQL determines the order of evaluation?

Precedence operator

What does the DROP TABLE statement do in SQL?

Deletes a table

In the context of database systems, what is the primary responsibility of a database designer?

Determining the format of data elements and overall database structure

What is the main function of a database system, as per the context provided?

Ensuring secure, internally consistent, and available data at all times

What is the role of a database administrator in the context of database management?

Responsible for securing the database system against unauthorized users

What was historically used to encode data, based on the information provided?

Continuous variations on various physical media

What does the term 'flattening' refer to in the context of SQL?

Creating a single row from multiple related rows

What is the purpose of a materialized view in SQL?

Storing the result of a query for faster retrieval

Which component is responsible for ensuring proper execution of database transactions?

Database system

What does digital data refer to, as per the information provided?

Zeros and ones on electronic and magnetic media

What is the function of the MySQL Command-Line Client?

It is used for executing SQL statements through a text interface

What is the standard language for querying relational databases?

SQL

What is the role of a database programmer?

Developing software that utilizes a database

What is the primary function of a database system?

Process transactions in groups

Which component is responsible for ensuring proper execution of database transactions?

Transaction manager

What is the architecture of a database system based on?

Storage manager and query processor

Which model is the relational model based on?

Tabular data structures

What is the most common use of SQL?

Querying relational databases

In the relational model, what is the purpose of a cell?

It represents a single value in a tuple

What does the Data Transaction Language (DTL) manage in SQL?

Transaction management

What does the Decimal data type in SQL represent?

Numbers with fractional values

Which statement is used to create a new database in SQL?

CREATE DATABASE

What does the SELECT statement retrieve from a table in SQL?

Rows

What is the main function of a database system?

Storing and managing data

What do FOREIGN KEYS refer to and maintain in SQL?

Data integrity and relationships

What does the term 'flattening' refer to in SQL?

Converting multi-dimensional data into tabular format

What is the purpose of the LIMIT clause in SQL?

To restrict the number of rows returned in the result table

What does the AUTO_INCREMENT column do in SQL?

Automatically assign unique values to new rows

What is the purpose of the FOREIGN KEY constraint in SQL?

To ensure referential integrity between related tables

What does the NOT NULL constraint prevent in SQL?

Having NULL values in a table

What is the purpose of the UNIQUE constraint in SQL?

To enforce uniqueness for values in a column or group of columns

What statement is used to modify existing rows in a table in SQL?

UPDATE

What does the Self-join allow for in SQL?

Joining a table to itself for complex querying

What is the purpose of the IN operator in SQL?

To select data when a value matches one of several specified values

In SQL, what is the purpose of the EXISTS operator?

It returns TRUE if the subquery selects at least one row and FALSE if no rows are selected.

What does the theta join operation in SQL refer to?

It combines all columns and all combinations of rows from two tables.

What is the purpose of the WITH CHECK OPTION in SQL?

It rejects inserts and updates that do not satisfy the view query WHERE clause.

What does the product operation in relational algebra do?

It combines all columns and all combinations of rows from two tables.

What is the role of a query optimizer in SQL?

It converts an SQL query into a sequence of low-level database actions and specifies precisely how to process an SQL statement.

What does the aggregate operation in relational algebra do?

It applies aggregate functions like SUM(), AVG(), MIN(), and MAX().

What is the purpose of the rename operation in relational algebra?

It specifies new table and column names.

'Flattening' a query in SQL refers to:

Replacing a subquery with an equivalent join.

What is the main effect of horizontal partitioning in a database system?

Organizing table rows into subsets

What is the difference between a range partition and a list partition in database design?

Range partitions use the VALUES LESS THAN keywords, while list partitions use the VALUES IN keywords

What is the purpose of a shard in a distributed database?

To store a subset of table data on different computers

Which statement creates an index by specifying the index name and table columns that compose the index?

CREATE INDEX

What is the primary function of a storage engine or storage manager in a database system?

Translating high-level commands into low-level commands

In a range partition, what do the VALUES LESS THAN keywords specify?

The upper bound of each range

What does the EXPLAIN statement generate in a database system?

Result table for query execution

What is the primary purpose of an entity-relationship model?

To provide a high-level representation of data requirements

Which term refers to a set of things, such as all employees in a company?

Entity type

What does an attribute instance represent?

An individual value

In the context of database design, what does a glossary, data dictionary, or repository document?

Additional detail in text format

What does a relationship instance represent?

A statement about entity instances

What is the main function of an entity type in database design?

To represent a set of things

What does an attribute represent in the context of database design?

A descriptive property of an entity

What is the purpose of an entity instance in database design?

To represent an individual thing

What does a glossary, data dictionary, or repository document in database design?

Additional detail in text format

What does a relationship type represent in database design?

A set of related things

What is the purpose of logical design in database management?

To convert the entity-relationship model into tables, columns, and keys for a specific database system

In entity-relationship modeling, what does the relationship minimum refer to?

The least number of instances of one entity that can relate to a single instance of another entity

What is the characteristic of an identifying attribute in database design?

It is unique, singular, and required

What is the cardinality of a weak entity in an entity-relationship model?

(0,1)

In database normalization, when is a table in the second normal form?

When all non-key columns depend on the whole primary key

What does Crow's foot notation depict for cardinality in entity-relationship modeling?

One circle for zero, one short line for one, and three short lines for many

What is the purpose of Boyce-Codd normal form in database normalization?

It 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

What does it mean for a table to be in the first normal form?

Every cell contains exactly one value and the table has a primary key

What aspect distinguishes a strong table from a weak table in database design?

A strong table has a unique and non-NULL primary key

What is the purpose of physical design in database management?

To add indexes and specify how tables are organized on storage media

Which storage medium is characterized by being less expensive and having higher capacity than main memory?

Flash memory/SSD

In the context of denormalization, what is the intentional introduction of redundancy aimed to achieve?

Enhance data access time

What type of storage minimizes block transfers by storing an entire row in one block?

Row-oriented storage

What is the role of a dynamic hash function in table storage?

Automatically add more blocks and buckets to the table

In the context of table clusters/multi-tables, what is a cluster key?

A column available in all interleaved tables

What type of index has an entry for every table block?

Sparse index

In multi-level indexes, what does 'fan-out' refer to?

Number of index entries per block

What is the primary distinction between B+tree and B-tree structures?

Pointer structure at the bottom level

What is the purpose of a primary index in the context of single-level indexes?

Index on a sort column

What is the characteristic feature of a heap table?

No order imposed on rows

In the context of data storage, which type of memory is lost when disconnected from power?

Volatile memory

What is the purpose of denormalization in database design?

Intentionally introducing redundancy

Which table structure imposes no order on rows?

Heap table

What type of index stores an entry for every table block?

Dense index

What is the function of the dynamic hash function in table storage?

Automatically adds more blocks and buckets to the table

Which type of storage minimizes block transfers by storing an entire row in one block?

Row-oriented storage

In multi-level indexes, what does 'fan-out' refer to?

Number of index entries per block

In entity-relationship modeling, what does the term 'cardinality' refer to?

The maximum and minimum number of instances of relationships and attributes

What does Crow's foot notation depict in entity-relationship modeling?

Cardinality using circles, short lines, and three short lines

What characterizes an identifying attribute in database design?

It is unique, singular, and required

What does the term 'flattening' refer to in SQL?

Simplifying complex nested queries into a single result set

What is the primary function of a strong table in database design?

It has a unique and non-NULL primary key

When is a table considered to be in the third normal form?

When all non-key columns depend on the whole primary key

What does Boyce-Codd normal form require for a table?

It must be in third normal form

What is the role of a weak entity in database design?

It does not have an identifying attribute

What is the main purpose of logical design in database management?

To convert the entity-relationship model into tables for a specific database system

When does physical design occur in the database design process?

After logical design and entity-relationship modeling

In the entity-relationship model, what do we mean by an attribute instance?

A specific value of an attribute for a particular entity instance

What is the purpose of a reflexive relationship in the entity-relationship model?

To relate an entity to itself

What does the term 'glossary' refer to in the context of database design?

A document detailing additional information in text format

What is the characteristic of an identifying attribute in database design?

Uniquely identifies an entity within its entity type

In entity-relationship modeling, what does the term 'entity type' refer to?

A set of things

What does the term 'reflexive relationship' denote in the context of database design?

Relates an entity to itself

What is the purpose of an ER diagram in database design?

To relate entities to each other

In database design, what does the term 'attribute type' refer to?

A set of values

What does the term 'relationship instance' denote in the context of database design?

A statement about entity instances.

How do we define an 'entity instance' in the context of database design?

An individual thing.

What is the main difference between a logical index and a physical index in a database?

A logical index uses primary key values, while a physical index uses pointers to table blocks.

What is the purpose of a function index in a database?

To specify a function on the column value and store the result of the function in the index entries.

What is the difference between a horizontal partition and a shard in database design?

A horizontal partition stores data on different storage devices of a single computer, while a shard stores data on different computers of a distributed database.

What does the CREATE INDEX statement do in the context of databases?

Creates an index by specifying the index name and table columns that compose the index.

In database design, what is the purpose of a storage engine or storage manager?

To translate instructions generated by the query processor into low-level commands that access data on storage media.

What distinguishes a range partition from a list partition in database design?

A range partition associates each partition with a range of partition expression values, while a list partition associates each partition with an explicit list of partition expression values.

What is the main role of the EXPLAIN statement in the context of databases?

To generate a result table that describes how a statement is executed by the storage engine.

What does the MySQL slow query log record in the context of database management?

All long-running queries submitted to the database.

What is the purpose of DROP INDEX statement in SQL?

Deletes a table's index.

What is the primary purpose of a database management system (DBMS)?

Ensure data is secure, internally consistent, and available at all times

What does the scope of data refer to?

The amount of data produced and collected

In the context of databases, what does the term 'data' encompass?

Numeric, textual, visual, or audio information

What is the main characteristic feature of a database system?

Strict enforcement of standardized data storage

What is the primary function of a structured database?

Ensure that similar data is stored in a standardized manner

What is the primary function of an application programming interface (API) in the context of database programming?

To link a host programming language to a database by offering a library of procedures or classes

What is the standard relational query language used in database programming?

SQL

In the context of MySQL Server, what is the purpose of MySQL Workbench?

To administer and execute SQL commands using a graphical user interface

What does data independence allow database designers to do in terms of query performance?

Tune query performance without changes to application programs

What is the primary role of the MySQL Command-Line Client?

To provide an interactive text interface for administering MySQL Server

What is the primary purpose of the query processor in a database system?

Interpreting queries, optimizing them, and returning results

What is the main effect of denormalization in a database system?

It introduces redundancy to achieve improved query performance and reduce complexity

What is the purpose of the transaction manager in a database system?

Ensuring transactions are executed properly and managing recovery from failures

What does the storage manager do in a database system?

Translates query processor instructions into file-system commands and uses indexes for efficient data location

What is the purpose of the log in a database system?

Records all inserts, updates, and deletes and restores the database in the event of failure

What is the main purpose of a catalog or data dictionary in a database system?

Providing a directory of database objects including tables, columns, indexes, and other components

In database design, what is the main role of the physical design phase?

Adding indexes and specifying table organization on storage media

What is the primary function of a NoSQL database?

Optimizing for handling big data with poor or missing information

What is the main feature of relational databases that makes them suitable for accurate transaction records?

Storing data in tables, columns, and rows with the same format in each column and row

What type of records are relational databases suitable for?

Accurate transaction records

In the relational model, what is the primary purpose of a column?

To specify the data type for values

What is the distinguishing characteristic of a tuple in the relational data structure?

It is an ordered collection of elements

What key aspect differentiates a set from a tuple in the relational data structure?

Ordering of elements

Which clause is used to add unnamed constraints such as NOT NULL and DEFAULT in SQL?

CHANGE

What fails when the table contains data that violates a constraint in SQL?

Adding a constraint

In SQL, what must be dropped before dropping a table with a foreign key constraint referring to the table's primary key?

The foreign key constraint

What does the DROP INDEX statement in SQL do?

Drops UNIQUE constraints

What fails when adding a constraint in SQL if the table contains data that violates the constraint?

Adding a constraint

Which statement is used to modify existing tables in a database?

ALTER TABLE

What are the primary categories of data types in relational databases?

Integer and Decimal

What do spatial data types store and represent in relational databases?

Geometric information

What are the components of the five sublanguages of SQL?

Data Definition Language (DDL), Data Query Language (DQL), Data Manipulation Language (DML), Data Control Language (DCL), and Data Transaction Language (DTL)

What are the unique relational rules that ensure data consistency in a relational database?

Having a unique primary key and column names

What is the standard language for managing relational databases?

SQL

Which clause specifies the action to be taken when a primary key is updated or deleted and the corresponding foreign key is affected?

ON UPDATE and ON DELETE

What type of constraint ensures the uniqueness of values in a column or a group of columns?

UNIQUE constraint

What relational rule requires foreign key values to either be NULL or match a primary key value?

Referential integrity

In the context of relational databases, what is the primary function of the query processor?

It optimizes the execution plan for SQL statements to ensure efficient query processing.

What is the main effect of horizontal partitioning in a database system?

It enables the scaling of database systems by dividing tables into smaller, more manageable pieces.

What does the NOT NULL constraint prevent in SQL?

It prevents the insertion of NULL values into a specific column or columns.

In SQL, the BETWEEN operator is used to determine if a value is:

Greater than or equal to a minimum value and less than or equal to a maximum value

What does a self-join in SQL allow for?

Joining a table to itself and comparing any columns with comparable data types

What is the primary purpose of a subquery in SQL?

To restrict the selected results

When is a subquery considered correlated in SQL?

When it references a column from the outer query in its WHERE clause

What does a cross-join in SQL do?

Returns all possible combinations of rows from both tables in the result

In SQL, what is the purpose of the IN operator in a WHERE clause?

To check if a value matches one of several values

What is the equivalent condition of using the BETWEEN operator in SQL?

'value >= minValue AND value <= maxValue'

In SQL, what does a self-join commonly compare?

The foreign key and the referenced primary key from the same table

What is the main characteristic feature of a correlated subquery in SQL?

It references a column from the outer query in its WHERE clause

In SQL, what does a subquery do within another query's WHERE clause?

Returns data to the outer query and restricts the selected results

What is the primary purpose of a materialized view in SQL?

Optimizing query performance

In SQL, what does the CHECK OPTION clause do when used with views?

Prevents inserting or updating rows that do not satisfy the view query WHERE clause

What characteristic of views in SQL can pose difficulties when using INSERT, UPDATE, and DELETE statements?

Handling of foreign keys and aggregate values

What advantage do materialized views offer in SQL compared to regular views?

Saving complex queries

What is the primary purpose of creating an alias in SQL?

Assigning a temporary name to a column or table

What does the EXISTS operator return in correlated subqueries?

At least one row if the subquery selects at least one row

What is the main function of a view in SQL?

Creating virtual tables

How are subqueries rewritten for better performance in SQL?

'Flattening' process

'Flattening' a query in SQL refers to what process?

'Flattening' process

What does a view in SQL do when used with INSERT, UPDATE, and DELETE statements?

May cause difficulties due to foreign keys and aggregate values.

Which kind of object in the entity-relationship model is a statement about two entities?

Relationship

In the context of entity-relationship diagrams, how are entities typically represented?

As rectangles

What type of object is an attribute in the entity-relationship model?

A descript­ive property of an entity

What is the purpose of a glossary in the context of database design?

To document additional detail in text format

In entity-relationship modeling, what is the purpose of the logical design phase?

To implement entities, relationships, and attributes as tables, columns, and keys

What is the primary purpose of a glossary or data dictionary in database design?

To document names, synonyms, and descriptions for entities, relationships, and attributes

What does cardinality in entity-relationship modeling refer to?

The maximum and minimum relationships and attributes between entities

What is the first step in the analysis phase of developing complex databases?

Uncover entities, relationships, and attributes through interviews and documents

Which of the following is true about functional dependence in database normalization?

Functional dependence reflects business rules and occurs when one column depends on another.

What is the primary purpose of Boyce-Codd normal form in database design?

Eliminating all redundancy arising from functional dependence.

In database design, what is the main effect of denormalization in a database system?

It intentionally adds redundancy for certain performance benefits.

When is a table considered to be in the second normal form in database normalization?

When all non-key columns depend on the entire primary key.

In database design, which convention gained popularity due to US DoD adoption?

IDEF1X

What special notation is used to distinguish intangible entities in data models but not tracked in databases?

Dashed rectangles

What is the primary characteristic of stable and simple primary keys in database design?

Stable and simple

In the 'implementing subtype entities' step in database design, what do primary keys of subtype tables have in common with supertype tables?

They are identical to supertype tables

In entity-relationship modeling, what does the term 'cardinality' refer to?

The total number of instances of one entity that can relate to another

What characterizes an identifying attribute in database design?

An attribute that is unique and has a maximum value of one

What is the primary function of a strong table in database design?

Stores one or more identifying attributes

What statement creates an index by specifying the index name and table columns that compose the index?

CREATE INDEX statement in SQL

In the context of storage media management, what is the primary role of controllers?

Converting between blocks and sectors or pages

For which type of applications is row-oriented storage in relational databases optimized?

Transactional applications, often reading and writing individual rows

Why does row-oriented storage perform best when row size is small relative to block size?

Improved query performance and less wasted storage

What type of applications are newer relational databases optimized for?

Analytic applications rather than transactional applications

In the context of analyzing applications, when is column-oriented storage optimal?

When applications often read just a few columns from many rows

In the context of database physical design, which statement best describes the purpose of partitioning?

Partitioning improves query performance by reducing the amount of data accessed by INSERT, UPDATE, DELETE, and SELECT statements.

Which statement accurately describes the purpose of the EXPLAIN statement in the context of databases?

The EXPLAIN statement is used to generate a result table that describes how a statement is executed by the storage engine.

What is the primary role of the storage engine or storage manager in the context of database physical design?

The storage engine translates instructions generated by a query processor into low-level commands that access data on storage media.

Which type of partition requires a partition expression with positive integer values and automatically names partitions as p0 through p(N-1)?

Hash partition

What does the CREATE INDEX statement do in the context of databases?

Create an index on specified columns to improve query performance

In a relational database, what is the primary characteristic of a heap table?

No order imposed on rows and optimized for insert operations

What is the main advantage of using column-oriented storage for analytic applications?

Faster data access and better data compression

What is the purpose of a single-level index in a relational database?

Contain column values and pointers to rows

What is the characteristic of table clusters in a relational database system?

Interleave rows of two or more tables in the same storage area

What is the primary decision factor for query processing to involve either a table scan or an index scan in a relational database?

Percentage of table rows selected by a query

What is the main difference between a primary index and a secondary index in a database?

Primary index is on the sort column, while secondary index is on a non-sort column.

What distinguishes a balanced multi-level index from an imbalanced multi-level index?

Balanced multi-level indexes have equally distributed data, while imbalanced multi-level indexes have unevenly distributed data.

What is the primary characteristic of a hash index in a database system?

Hash indexes use buckets to store index entries.

What is the distinguishing feature of a bitmap index in a database?

Bitmap indexes store data as a grid of bits.

What is the main purpose of tablespaces and partitions in a database?

To map tables to files and assign tables to specific tablespaces.

Study Notes

  • 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.

Test your knowledge of fundamental database concepts with this quiz. Topics covered include the relational model, SQL, big data, sets, and tuples.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.

Get started for free

More Quizzes Like This

Use Quizgecko on...
Browser
Browser