Data Management - Foundations - D426 (2/6)

Data structures(Database models)

____ __________ that prescribe how data is organized.

Operations (Database models)

_________ that manipulate data structures.

Rules (Database models)

_____ that govern valid data.

relational model(Database models)

The__________ _____ is a database model based on a tabular data structure. The model was published in 1970 by E. F. Codd of IBM and released in commercial products around 1980. The data structure, operations, and rules are standardized in SQL, the universal query language of relational databases.

big data(Database models)

The rise of the internet in the 1990s generated ___ ____, characterized by unprecedented data volumes and rapidly changing data structures.

Relational data structure (Relational data structure)

The ___________ ____ ________ is based on set theory

set (Relational data structure)

A ___ is an unordered collection of elements enclosed in braces.\n\n\nEx: {a, b, c} and {c, b, a} are the same, since ___s are not ordered.

tuple (Relational data structure)

A _____ is an ordered collection of elements enclosed in parentheses. Ex: (a, b, c) and (c, b, a) are different, since _____s are ordered. \n\n\nEx: (a, b, c) and (c, b, a) are different, since _____s are ordered.

table (Relational data structure)

A _____ has a name, a fixed tuple of columns, and a varying set of rows.

column (Relational data structure)

A _______ has a name and a data type.

row (Relational data structure)

A ___ is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type.

data type(Relational data structure)

A ____ type is a named set of values, from which column values are drawn.

Relational operations(Relational operation)

Like the relational data structure, __________ __________ are based on set theory. Each operation generates a result table from one or two input tables

Select (Relational operation)

______ a subset of rows of a table.

Project (Relational operation)

_______ eliminates one or more columns of a table.

Product (Relational operation)

_______ lists all combinations of rows of two tables.

Join (Relational operation)

____ combines two tables by comparing related columns.

Union (Relational operation)

_____ selects all rows of two tables.

Intersect (Relational operation)

_________ selects rows common to two tables.

Difference (Relational operation)

__________ selects rows that appear in one table but not another.

Rename (Relational operation)

______ changes a table name.

Aggregate (Relational operation)

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

relational algebra (Relational operation)

These operations are collectively called__________ _______ and are the theoretical foundation of the SQL language.

Relational rules (Relational rules)

__________ _____ are part of the relational model and govern data in every relational database.

Unique primary key (Relational rules)

All tables have a primary key column, or group of columns, in which values may not repeat.

Unique column names (Relational rules)

Different columns of the same table have different names.

Unique column names (Relational rules)

No two rows of the same table have identical values in all columns.

Business rules (Relational rules)

________ _____ are based on business policy and specific to a particular database.

constraints (Relational rules)

Relational rules are implemented as SQL __________ and enforced by the database system.

Structured Query Language/SQL

__________ _____ _________ is a high-level computer language for storing, manipulating, and retrieving data.

statement (SQL syntax)

An SQL _________ is a complete command composed of one or more clauses.

clause (SQL syntax)

A______ groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000.

Literals (SQL syntax features)

Explicit values that are string, numeric, or binary. Strings must be surrounded by single quotes or double quotes. Binary values are represented with x'0' where the 0 is any hex value. \n\n\n\nExamples: 'String'"String"123x'0fa2'

Keywords(SQL syntax features)

Words with special meaning. \n\n\n\nExamples: SELECT, FROM, WHERE

Identifiers(SQL syntax features)

Objects from the database like tables, columns, etc. \n\n\n\nExamples: City, Name, Population

Comment */ Comments(SQL syntax features)

Statement intended only for humans and ignored by the database when parsing an SQL statement. \n\n\n\nExamples:\n\n-- single line comment \n\n/* multi-line

SQL sublanguages (SQL sublanguages)

The SQL language is divided into five ____________

Data Definition Language/DDL (SQL sublanguages)

____ __________ ________ defines the structure of the database

Data Query Language/DQL (SQL sublanguages)

____ _____ _________ retrieves data from the database.

Data Manipulation Language/DML (SQL sublanguages)

____ ____________ ________ manipulates data stored in a database.

Data Control Language/DCL (SQL sublanguages)

____ _______ ________ controls database user access.

Data Transaction Language/DTL (SQL sublanguages)

____ ___________ ________ manages database transactions.

automated script

An _________ ______ is a series of SQL statements that is executed repeatedly. The statements are prepared in advance and saved in a file or as a database stored procedure.The statements are executed by a computer program or from the SQL command line, by invoking the name of the file or stored procedure.

database system instance(Managing databases)

A _________ ______ ________ is a single executing copy of a database system. Personal computers usually run just one instance of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances of a database system.

CREATE DATABASE DatabaseName (Managing databases)

______ ________ creates a new database.

DROP DATABASE DatabaseName(Managing databases)

____ ________ deletes a database, including all tables in the database.

USE DatabaseName (Managing databases)

___ selects a default database for use in subsequent SQL statements.

SHOW DATABASES(Managing databases)

____ _________ lists all databases in the database system instance.

SHOW TABLES (Managing databases)

____ ______ lists all tables in the default database.

SHOW COLUMNS FROM TableName (Managing databases)

____ _______ lists all columns in the TableName table of the default database.

SHOW CREATE TABLE TableName (Managing databases)

____ ______ _____ shows the CREATE TABLE statement for the TableName table of the default database.

Tables (Tables)

All data in a relational database is structured in ______

table (Tables)

A _____ has a name, a fixed sequence of columns, and a varying set of rows.

column (Tables)

A ______ has a name and a data type.

row (Tables)

A ___ is an unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type.

cell (Tables)

A ____ is a single column of a single row.

empty table(Tables)

A table without rows is called an _____ _____.

Exactly one value per cell (Rules governing tables)

A cell may not contain multiple values. Unknown data is represented with a special NULL value.

No duplicate column names (Rules governing tables)

Duplicate column names are allowed in different tables, but not in the same table.

No duplicate rows (Rules governing tables)

No two rows may have identical values in all columns.

No row order (Rules governing tables)

Rows are not ordered. The organization of rows on a storage device, such as a disk drive, never affects query results.

data independence (Rules governing tables)

____ ____________ allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results.

CREATE TABLE (CREATE TABLE)

The _____ _____ statement creates a new table by specifying the table name, column names, and column data types.

INT or INTEGER (CREATE TABLE)

integer values

VARCHAR(N) (CREATE TABLE)

values with 0 to N characters

DATE (CREATE TABLE)

date values

DROP TABLE (DROP TABLE)

The ____ _____ statement deletes a table, along with all the table's rows, from a database.

ALTER TABLE (ALTER TABLE)

The _____ _____ statement adds, deletes, or modifies columns on an existing table.

ADD (ALTER TABLE)

Adds a column \n\n\n\nEXAMPLE:\n\nALTER TABLE TableName \n\n___ ColumnName DataType;

CHANGE (ALTER TABLE)

Modifies a column \n\n\n\nEXAMPLE: \n\nALTER TABLE TableName \n\n______ CurrentColumnName NewColumnName NewDataType;

DROP (ALTER TABLE)

Deletes a column \n\n\n\nEXAMPLE: \n\nALTER TABLE TableName \n\n____ ColumnName;

Transpose

_________ is an operation on a table in which rows become columns, and columns become rows. _________is an important operation for mathematical matrices, but is not commonly applied to tables.

data type(Data type categories)

A ____ ____ is a named set of values from which column values are drawn.

Integer (Data type categories)

_______ data types represent positive and negative integers. \n\n\nEXAMPLE:INT-9281344

Decimal (Data type categories)

_______ data types represent numbers with fractional values. \n\n\n\nEXAMPLE:FLOAT3.1415

Character (Data type categories)

_________ data types represent textual characters.\n\n\nEXAMPLE:VARCHARChicago

Date and time(Data type categories)

____ ___ ____ data types represent date, time, or both. Some date and time data types include a time zone or specify a time interval. \n\n\n\nEXAMPLE:DATETIME12/25/2020 10:35:00

Binary (Data type categories)

______ data types store data exactly as the data appears in memory or computer files, bit for bit.\n\n\nEXAMPLE:BLOB1001011101 . . .

Spatial (Data type categories)

_______ data types store geometric information, such as lines, polygons, and map coordinates. \n\n\n\nEXAMPLE:POINT(2.5, 33.44)

Document (Data type categories)

_________ data types contain textual data in a structured format such as XML or JSON. \n\n\n\nEXAMPLE:XML

signed (MySQL data types)

A ______ number may be negative.

unsigned (MySQL data types)

An ________ number cannot be negative.

TINYINT (MySQL data types)

1 byte \n\nSigned range: -128 to 127 \n\nUnsigned range: 0 to 255

SMALLINT (MySQL data types)

2 bytes \n\nSigned range: -32,768 to 32,767 \n\nUnsigned range: 0 to 65,535

MEDIUMINT (MySQL data types)

3 bytes \n\nSigned range: -8,388,608 to 8,388,607 \n\nUnsigned range: 0 to 16,777,215

INTEGER or INT (MySQL data types)

4 bytes \n\nSigned range: -2,147,483,648 to 2,147,483,647\nUnsigned range: 0 to 4,294,967,295

BIGINT (MySQL data types)

8 byte\nSigned range: -263 to 263 -1 \n\nUnsigned range: 0 to 264 -1

DECIMAL(M,D) (MySQL data types)

Varies depending on M and \n\nExact decimal number where M = number of significant digits, D = number of digits after decimal point

FLOAT (MySQL data types)

4 bytes\nApproximate decimal numbers with range: -3.4E+38 to 3.4E+38

DOUBLE (MySQL data types)

8 bytes \n\nApproximate decimal numbers with range: -1.8E+308 to 1.8E+308

DATE (MySQL data types)

3 bytes \n\nFormat: YYYY-MM-DD. Range: '1000-01-01' to '9999-12-31'

TIME(MySQL data types)

3 bytes \n\nFormat: hh:mm:ss

DATETIME (MySQL data types)

5 bytes \n\nFormat: YYYY-MM-DD hh:mm:ss. Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

CHAR(N) (MySQL data types)

N bytes \n\nFixed-length string of length N; 0 ≤ N ≤ 255

VARCHAR(N) (MySQL data types)

Length of characters + 1 bytes \n\nVariable-length string with maximum N characters; 0 ≤ N ≤ 65,535

TEXT (MySQL data types)

Length of characters + 2 bytes \n\nVariable-length string with maximum 65,535 characters

operator/operands (Operators)

An ________ is a symbol that computes a value from one or more other values, called operands

Arithmetic operators (Operators)

__________ operators compute numeric values from numeric operands.

Comparison operators (Operators)

__________ operators compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types.

Logical operators (Operators)

_______ operators compute logical values from logical operands.