01 Data Management Applications

What are the three parts of a database model?

1. Data structures (how data is organized)

2. Operations (manipulate data structures)

3. Rules (govern valid data)

relational model

a database model based on a tabular data structure first published in 1970 by E. F. Codd of IBM and released in commercial products around 1980

What two types of non-relational database models were dominant in the 1960s and 70s but eventually got displaced by relational databases?

hierarchical and network

What kind of data was the relational database model initially designed for?

Transactional

Hierarchical database

a database model initially released in the 1960s with a tree-based primary data structure. Its strengths are fast queries and efficient storage. An example system is IMS.

Network database

A type of database model introduced in the 1970s with a linked list as its primary data structure. Its strengths are fast queries and efficient storage. An example system is IDMS.

Object database

A database model introduced in the 1990s with a class-based primary data structure. Its strength is integration with object-oriented programming languages. An example system is ObjectStore.

Graph database

A database model introduced in the 2000s with a vertex- and edge-based primary data structure. Its strengths are flexible schema and support for evolving business requirements. An example system is Neo4j.

Document database

A database model introduced in the 2010s with its primary data structure based on XML and JSON. Its strengths are flexible schema and support for unstructured and semi-structured data. An example system is MongoDB.

What development in computing during the 1980s allowed relational databases to become dominant?

Increases in storage size and processing speeds.

set

an unordered collection of elements enclosed in braces

tuple

an ordered collection of elements enclosed in parentheses

Characteristics of a table

• a name

• a fixed tuple of columns

• a varying set of rows

Characteristics of a Column

• name

• data type

Characteristics of a Row

• unnamed tuple of values

• values correspond to a column

• values belong to their column’s data type

Characteristics of a Data Type

• named set of values

• source of column values

What theory are relational data structures and relational operations based on?

Set Theory

Select

selects a subset of (or all) the rows of a table

Project

selects one or more columns of a table

Product

lists all combination of the rows of two tables

Join

combines two tables by comparing related columns

Union

selects ALL rows of two tables

Intersect

selects rows common to two tables

Difference

selects rows that appear in one table but not another

Rename

changes a table name

Aggregate

computes functions over multiple table rows, such as sum and count

Name the 9 operations of relational algebra

1. Select

2. Project

3. Product

4. Join

5. Union

6. Intersect

7. Difference

8. Rename

9. Aggregate

The result of an SQL query is alway a __.

table

relational rules

rules that govern data in every relational database and are part of the relational model

business rules

rules based on business policy that are specific to a particular database

Can all business rules be implemented as SQL constraints?

No, some complex business rules must be enforced by applications running on the database.

Under what circumstances can data violate relational rules?

If the relational rules are not implemented properly as constraints.

What are the 5 sublanguages of SQL?

1. Data Definition Language (DDL)

2. Data Query Language (DQL)

3. Data Manipulation Language (DML)

4. Data Transaction Language (DTL)

5. Data Control Language (DCL)

Name the 3 primary statements of DDL.

1. CREATE

2. ALTER

3. DROP

What statements comprise DTL?

1. START TRANSACTION, COMMIT, and ROLLBACK statements

2. SAVEPOINT statements

3. LOCK and UNLOCK INSTANCE statements

4. LOCK and UNLOCK TABLES statements

5. SET TRANSACTION

Data Definition Language

defines database structure

Data Query Language

retrieves data

Data Manipulation Language

inserts, updates, and deletes data

Data Transaction Language

manages transactions

Data Control Language

specifies user access to data

literal

explicit values such as a character string or number

keyword

word with special meaning for the language processor, which are defined by the database system

identifier

the name of a database object, such as a column, table, or database. The programmer defines these.

expression

sequence of literals, identifies, and operations that evaluate to a single value

Comment

text that the language processor ignores

statement

a complete, executable instruction, ending with a semicolon

clause

a portion of a statement that begins with a keyword, followed by additional language elements

Are keywords case sensitive?

It depends on the database system and can usually be set with a system identifier.

What do square brackets [] indicate in the formal syntax definition of a SQL statement?

an optional language element

What do curly braces {} indicate in the formal syntax definition of a SQL statement?

a series of alternative language elements, each separated by vertical bars. One and only one of these alternatives must be included in the statement.

What does an ellipsis indicate in the formal syntax definition of a SQL statement?

that the preceding language element may be repeated

How many parts are there in the SQL standard?

16: 11 active and 5 unpublished/withdrawn

What is a “part” in the SQL standard?

a formal specification of syntax and behavior

Which 4 parts of the SQL Standard are implemented in MySQL?

Part 2: Foundation

Part 4: Persisted Stored Modules

Part 11: Information and Definition Schemas

Part 14: XML-Related Specifications (functions but not the XML data type itself)

Foundation

The part of SQL standard that specifies statements of the five SQL sublanguages and is considered the core of the standard.

SQL/Persisted Stored Modules

The part of the SQL standard that extends SQL with programming elements such as loops and procedures like REPEAT/UNTIL and IF/ELSE.

SQL/Schemata

The part of the SQL standard that defines the information_schema database, a database that tracks details of user database objects such as tables, columns, and indexes and is commonly referred to as a catalog or directory.

SQL/XML

The part of SQL standard that specifies the XML data type and related functions

Framework

The part of the SQL standard that introduces relational principles, terms, and concepts, but does not specify SQL syntax

database system instance

a single executing copy of a database system

What is the statement that creates a new database on an instance?

CREATE DATABASE DatabaseName

What is the statement to delete a database, including all tables in the database, on an instance?

DROP DATABASE DatabaseName

Can the CREATE DATABASE statement create two databases with the same name?

No, each database must have a unique name.

What does the statement USE DatabaseName do?

It selects a default database for use in subsequent statement.

What does the statement SHOW DATABASES do?

List all databases in the database system instance.

What does the statement SHOW TABLES do?

List all tables in the default database with an optional clause FROM DatabaseName that lists tables in a named database.

What statement would one use to see the statement used to create a specific table in the default database?

SHOW CREATE TABLE TableName

How would one produce a list of all columns from a specified table in the default database?

SHOW COLUMNS FROM TableName

What does the principle of data independence state?

The result of a database query is not affected by the physical organization of data on storage devices.

How many values can a cell contain?

One

Can rows duplicate in the same table?

No.

What statement deletes a table, along with all of its rows, from a database?

DROP TABLE TableName;

What must a CREATE TABLE statement include?

• Table name

• at least one column name

• data types for each table

What happens if the named table in a CREATE TABLE statement already exists?

The statement fails. This can be avoided by using the option IF NOT EXISTS clause.

How are columns listed in a CREATE TABLE statement?

In parentheses, with commas after the data types

What keywords are used with the ALTER TABLE statement?

• CHANGE

• ADD

• DROP

What statement would change the name or data type of an existing column?

ALTER TABLE TableName
CHANGE CurrentColumnName NewColumnName New Data Type;

What statement deletes an existing column?

ALTER TABLE TableName
DROP ColumnName;