106 Questions
In the relational model, what is a table?
A fixed tuple of columns and a varying set of rows
What are the data structures that prescribe how data is organized in a database model?
Tables and columns
What is the theoretical foundation of the SQL language?
Relational algebra
What characterizes big data?
Unprecedented data volumes and rapidly changing data structures
What is a data type in the context of databases?
A named set of values from which column values are drawn
What does a row represent in a database table?
An unnamed tuple of values
What do relational rules govern in a relational database?
Valid data
What is the relational model based on?
Set theory
Which language standardizes the data structure, operations, and rules in the relational model?
SQL
What is a column in the context of a database table?
An unnamed tuple of values corresponding to a column
In a CREATE TABLE statement, which keyword specifies the column values to be added?
VALUES
What is the keyword used to modify existing rows in a table with the UPDATE statement?
SET
Which keyword is used to ensure that a column or group of columns is always unique and non-null in a CREATE TABLE statement?
PRIMARY KEY
What keyword follows the column's data type to define an auto-increment column in a CREATE TABLE statement?
AUTO_INCREMENT
What is the term for a foreign key constraint added to a CREATE TABLE statement that refers to a primary key?
FOREIGN KEY
Which action, specified in the ON UPDATE and ON DELETE clauses of a FOREIGN KEY constraint, propagates primary key changes to foreign keys?
CASCADE
What type of constraint ensures that values in a column, or group of columns, are unique in a database?
UNIQUE
In a CREATE TABLE statement, where does the CHECK constraint appear?
In a separate clause of the CREATE TABLE statement
What is the term for a simple or composite foreign key in which all columns are NULL?
Fully NULL foreign key
Which keyword is used to delete all rows from a table in a TRUNCATE statement?
TRUNCATE
Which language is used for storing, manipulating, and retrieving data?
SQL
What does the CREATE TABLE statement do?
Creates a new table
What does the NULL value represent in a database?
A special value for unknown or inapplicable data
Which type of constraint prevents a column from having a NULL value?
NOT NULL constraint
What type of language is SQL?
High-level language
What does the SELECT statement do?
Selects rows from a table to retrieve data
What is the purpose of the LIMIT clause in MySQL?
Limits the number of rows returned in the result set
What is the purpose of Data Definition Language (DDL) in databases?
Defining database structure
What is meant by Data Independence?
The ability to modify the database schema without affecting the application programs
What does the DROP DATABASE statement do?
Deletes a database and its contents
Which keyword is used to prevent a column from having a NULL value in a CREATE TABLE statement?
NOT NULL
What keyword follows the column's data type to define an auto-increment column in a CREATE TABLE statement?
AUTO_INCREMENT
In a CREATE TABLE statement, where does the CHECK constraint appear?
In a separate clause of the CREATE TABLE statement
Which keyword is used to delete all rows from a table in a TRUNCATE statement?
TRUNCATE
What is the term for a simple or composite foreign key in which all columns are NULL?
Fully NULL foreign key
What does the UNIQUE constraint ensure in a database?
Unique values in a column or group of columns
Which keyword is used to modify existing rows in a table with the UPDATE statement?
SET
What is the purpose of the RESTRICT keyword in the context of referential integrity?
Rejects insert, update, or delete that violates referential integrity
'Referential integrity is a relational rule that requires foreign key values are either fully NULL or match some primary key value.' What keyword specifies this behavior?
RESTRICT
What is the purpose of the Data Control Language (DCL) in a database system?
Controls user access
Which type of language standardizes the data structure, operations, and rules in the relational model?
Data Definition Language (DDL)
In the context of a database table, what does a row represent?
Unnamed values
What keyword follows the column's data type to define an auto-increment column in a CREATE TABLE statement?
AUTO_INCREMENT
Which action is specified in the ON UPDATE and ON DELETE clauses of a FOREIGN KEY constraint to propagate primary key changes to foreign keys?
CASCADE
What is meant by Data Independence in the context of databases?
Allows changes in the physical storage of data without affecting the logical view
What characterizes big data in the context of databases?
Large volume, velocity, and variety of data
What does the IS NULL operator do in SQL?
Selects NULL values
What does the DROP DATABASE statement do in SQL?
Deletes an existing database
What is the purpose of the Limit clause in MySQL?
Limits the number of rows returned by a SQL query
Which of the following best describes the relational model in database systems?
It is a database model based on a tabular data structure.
What do relational rules govern in a relational database?
They govern the integrity and consistency of data within the database.
What is the theoretical foundation of the SQL language?
Relational Algebra
Which language standardizes the data structure, operations, and rules in the relational model?
SQL
What characterizes big data?
It is characterized by unprecedented data volumes and rapidly changing data structures.
What is meant by Data Independence in the context of databases?
It refers to the ability to modify data without affecting the programs that access the data.
What does the DROP DATABASE statement do?
It removes an entire database and all its objects from the server.
What is the primary data structure in the relational model?
Relational
Which database system is known for its integration with object-oriented programming languages?
Neo4j
What theoretical foundation is the relational data structure based on?
Set theory
Which database system is known for its flexibility in handling unstructured and semi-structured data?
MongoDB
Which type of constraint ensures that a column or group of columns in a table is always unique and non-null?
UNIQUE
What is the purpose of the CHECK constraint in a database table?
To specify conditions for column values
What is the purpose of the TRUNCATE statement in SQL?
To delete all rows from a table
In the context of a database, what does the term 'NULL' represent?
Missing data
What happens when a column is assigned the NOT NULL constraint in a database table?
The column cannot have a NULL value
What is the purpose of the SELECT statement in SQL?
To retrieve data from a table
What is the purpose of the INSERT statement in SQL?
To add new rows to a table
Which statement is used to modify existing rows in a table in SQL?
UPDATE
'Expressions' in SQL consist of which of the following components?
Operators, operands, and parentheses
What does the SELECT clause specify in SQL?
Expressions to return values for each row
Which statement deletes specific rows from a table based on specified criteria in SQL?
DELETE
What does a primary key constraint ensure in SQL?
Columns are unique and non-null
Which statement is used to delete an existing table and all its data in a SQL database?
DROP TABLE
Which data type stores raw binary data in a relational database?
BINARY
What keyword specifies the behavior that foreign key values are either fully NULL or match some primary key value in referential integrity?
RESTRICT
Which clause of the ALTER TABLE statement is used to add new columns to an existing table in a SQL database?
ADD COLUMN
Which data type is used for storing geometric information in a relational database?
POINT
What does the modulo operator (%) do in SQL?
Returns the remainder of a division
Which statement is used to select specific rows from tables based on various conditions in SQL?
SELECT TABLE
.What does the IS NULL operator do in SQL?
Returns true if a value is NULL
Which keyword follows the column's data type to define an auto-increment column in a CREATE TABLE statement?
AUTO_INCREMENT
Which action, specified in the ON UPDATE and ON DELETE clauses of a FOREIGN KEY constraint, propagates primary key changes to foreign keys?
CASCADE
Which type of constraint prevents a column from having a NULL value?
Check constraint
What does the CREATE TABLE statement do?
Defines a new table and its columns
What characterizes big data in the context of databases?
Data with volume, velocity, and variety
What is the purpose of the RESTRICT keyword in the context of referential integrity?
To prevent delete or update operations if they would violate referential integrity
What does the IS NULL operator do in SQL?
Checks if a value is NULL
What is the purpose of Data Definition Language (DDL) in databases?
To define and modify the structure of the database
What characterizes big data?
It has high volume, velocity, and variety
'Referential integrity is a relational rule that requires foreign key values are either fully NULL or match some primary key value.' What keyword specifies this behavior?
ON DELETE SET NULL
'In the context of a database table, what does a row represent?'
A single record or tuple
Which language standardizes the data structure, operations, and rules in the relational model?
Structured Query Language (SQL)
What is the term for a simple or composite foreign key in which all columns are NULL?
Weak foreign key
What keyword follows the column's data type to define an auto-increment column in a CREATE TABLE statement?
AUTO_INCREMENT
Which keyword is used to specify the column values to be added in a CREATE TABLE statement?
VALUES
What characterizes big data in the context of databases?
Data with velocity, variety, and volume
What is the purpose of the Data Control Language (DCL) in a database system?
Enforce data integrity and security
In a CREATE TABLE statement, where does the CHECK constraint appear?
After the column name and data type
What do relational rules govern in a relational database?
Referential integrity constraints
What is the term for a foreign key constraint added to a CREATE TABLE statement that refers to a primary key?
Referential key
What does the IS NULL operator do in SQL?
Checks if a value is NULL
What is meant by Data Independence in the context of databases?
The ability to modify data without impacting applications using that data
What does the SELECT statement do?
Retrieves data from one or more tables
What characterizes big data in the context of databases?
Data with velocity, variety, and volume
Which keyword is used to prevent a column from having a NULL value in a CREATE TABLE statement?
NOT NULL
What is the purpose of Data Definition Language (DDL) in databases?
Define database structure and schema
Study Notes
-
Business rules are specific to a database and based on business policy.
-
Constraints are rules implemented as SQL constraints enforced by the database system.
-
SQL is a high-level language used for storing, manipulating, and retrieving data.
-
SQL statement is a complete command composed of one or more clauses, e.g. SELECT, FROM, and WHERE.
-
Data Definition Language (DDL) defines database structure, Data Query Language (DQL) retrieves data, Data Manipulation Language (DML) manipulates data, Data Control Language (DCL) controls user access, and Data Transaction Language (DTL) manages transactions.
-
Database system instance: a single executing copy of a database system, e.g., personal computers run one instance, shared computers run multiple instances.
-
Creating a new database: CREATE DATABASE DatabaseName, deleting a database: DROP DATABASE DatabaseName, selecting a default database: USE DatabaseName.
-
Tables: have a name, sequence of columns, and varying set of rows. Columns have a name and data type, rows have unnamed values, cells are single column-single row, empty table is a table without rows.
-
Data independence: Rule 4, CREATE TABLE creates a new table, DROP TABLE deletes a table and its rows, ALTER TABLE modifies columns.
-
Data types include: Integer, Decimal, Character, Date and time, Binary, Spatial, Document, Signed, and Unsigned.
-
Operators: Arithmetic, Comparison, Logical, Unary, and Binary. Expressions evaluate to a single value.
-
SELECT statement: selects rows from a table, has a SELECT and FROM clause, returns a result table.
-
Limiting the number of rows: MySQL's LIMIT clause.
-
NULL: a special value for unknown or inapplicable data.
-
NOT NULL constraint: prevents a column from having a NULL value.
-
IS NULL and IS NOT NULL operators: used to select NULL values.
-
Business rules are specific to a database and based on business policy.
-
Constraints are rules implemented as SQL constraints enforced by the database system.
-
SQL is a high-level language used for storing, manipulating, and retrieving data.
-
SQL statement is a complete command composed of one or more clauses, e.g. SELECT, FROM, and WHERE.
-
Data Definition Language (DDL) defines database structure, Data Query Language (DQL) retrieves data, Data Manipulation Language (DML) manipulates data, Data Control Language (DCL) controls user access, and Data Transaction Language (DTL) manages transactions.
-
Database system instance: a single executing copy of a database system, e.g., personal computers run one instance, shared computers run multiple instances.
-
Creating a new database: CREATE DATABASE DatabaseName, deleting a database: DROP DATABASE DatabaseName, selecting a default database: USE DatabaseName.
-
Tables: have a name, sequence of columns, and varying set of rows. Columns have a name and data type, rows have unnamed values, cells are single column-single row, empty table is a table without rows.
-
Data independence: Rule 4, CREATE TABLE creates a new table, DROP TABLE deletes a table and its rows, ALTER TABLE modifies columns.
-
Data types include: Integer, Decimal, Character, Date and time, Binary, Spatial, Document, Signed, and Unsigned.
-
Operators: Arithmetic, Comparison, Logical, Unary, and Binary. Expressions evaluate to a single value.
-
SELECT statement: selects rows from a table, has a SELECT and FROM clause, returns a result table.
-
Limiting the number of rows: MySQL's LIMIT clause.
-
NULL: a special value for unknown or inapplicable data.
-
NOT NULL constraint: prevents a column from having a NULL value.
-
IS NULL and IS NOT NULL operators: used to select NULL values.
-
An auto-increment column is a column that automatically receives a new value when a new row is added. It is defined using the AUTO_INCREMENT keyword in a CREATE TABLE statement.
-
Auto-increment errors: MySQL allows inserting a specific value to an auto-increment column, but overriding the auto-increment for a primary key is generally a mistake.
-
A foreign key is a column or group of columns that refers to a primary key. Data types must match, but names do not have to. Foreign keys can be NULL or repeat values. They follow the referential integrity rule.
-
Multiple foreign keys can refer to the same primary key, and a foreign key can refer to a primary key in the same table.
-
A foreign key constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords. It enforces referential integrity.
-
Referential integrity rules state that referencing key values must be NULL or match the primary key value.
-
Referential integrity violations can occur in four ways: a primary key is updated, a foreign key is updated, a primary key row is deleted, or a foreign key row is inserted.
-
When an insert, update, or delete statement violates referential integrity, databases can automatically correct it using one of four actions: RESTRICT, SET NULL, SET DEFAULT, or CASCADE.
-
ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint specify the action taken when a primary key is updated or deleted.
-
Column constraints govern values in a single column and are added after the column name and data type. Table constraints govern values in one or more columns and are added in a separate clause. The UNIQUE constraint ensures unique values in a column or group of columns. The CHECK constraint specifies an expression on one or more columns.
-
Constraints can be added and dropped using the ALTER TABLE statement.
Learn about the relational model, a database model based on tabular data structure, published in 1970 by E. F. Codd of IBM and released in commercial products around 1980. Explore the data structures, operations, and rules governing valid data in the relational model.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
