D426

Database management - foundations

database administrator

responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.

database designer

determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data. Since these priorities often conflict, database design is technically challenging.

Relational operation - Select

selects a subset of rows of a table.

Relational operation - Project

eliminates one or more columns of a table.

Relational operation - Product

lists all combinations of rows of two tables.

Relational operation - Join

combines two tables by comparing related columns.

Relational operation - Union

selects all rows of two tables.

Relational operation - Intersect

selects rows common to two tables.

Relational operation - Difference

selects rows that appear in one table but not another.

Relational operation - Rename

changes a table name.

Relational operation - Aggregate

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

Relational rules - Unique primary key.

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

Relational rules - Unique column names.

Different columns of the same table have different names.

Relational rules - No duplicate rows

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

Business rules

are based on business policy and specific to a particular database. Ex: All rows of the Employee table must have a valid entry in the DepartCode column.

Relational rules

Rules are logical constraints that ensure data is valid.

SQL syntax - clause

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

SQL syntax - Statement

is a complete command composed of one or more clauses.

Data Definition Language (DDL)

defines the structure of the database.

Data Query Language (DQL)

retrieves data from the database.

Data Manipulation Language (DML)

Manipulates data stored in a database

Data Control Language (DCL)

controls database user access.

Data Transaction Language (DTL)

manages database transactions.

A tuple

is an ordered collection of elements enclosed in parentheses.

table

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

Column

Has a name and a data type

Row

Is an unamed tuple of values. Each value corresponds to a column and belongs to the column’s data type.

Data type

Is a named set of values, from which column values are drawn.

Rules Governing Tables

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

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

3. No duplicate rows. No two rows may have identical values in all columns.

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

Synonym - table

File, Relation

Synonym - Row

Record, Tuple

Synonym - Column

Field, Attribute

Synonym - Data type

Domain, Data Type

Literals

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.

Example: 'String'

"String"

123

x'0fa2'

Keywords

Words with special meaning Example: SELECT, FROM, WHERE

Identifiers

Objects from the database like tables, columns, etc Example: City, Name, Population

database programmer

develops computer programs that utilize a database. Database programmers write applications that combine database query languages and general-purpose programming languages. Query languages and general-purpose languages have significant differences, so database programming is a specialized challenge.

database user

a consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system.

Query processor

interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. The query processor performs query optimization to ensure the most efficient instructions are executed on the data.

Storage manager

translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data.

Transaction manager

ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure.

Metadata

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

Database design phases

1. Analysis

2. Logical design

3. Physical design

Database design - Analysis phase

Phase 1 the phase specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity.

Entity relationship diagram - Rectangle

• Rectangles represent entities. Entity names appear at the top of rectangles.

Database design - Logical design phase

Phase 2 - The logical design phase implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns.

Key

is a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements.

Database design - Physical design phase

Phase 3 The physical design phase adds indexes and specifies how tables are organized on storage media.

In relational databases, how does logical and physical design affect queries?

Logical design affects the query result. Physical design affects query processing speed but never affects the query result. The principle that physical design never affects query results is called data independence.

Data independance

Data independence allows database designers to tune query performance without changes to application programs.

The term information independence is occasionally used instead of data independence. Synonymous terms.

MySQL Command-Line Client

Text interface included in MySQL Server download

"World database”

Included with MySQL

Error code

Returned when an SQL Statement is syntactically incorrect or the database cannot execute the statement.

Database Model

Conceptual framework for database systems, 3 parts:

• Data structures that prescribe how data is organized.

• Operations that manipulate data structures.

• Rules that govern valid data.

Cell

Single column of a single row

INT or Integer

Integer values

TINY INT 1 bytes

SMALLINT 2 bytes

MEDIUMINT 3 bytes

INTEGER or INT 4 bytes

BIGINT 8 bytes

VARCHAR(N)

Values with 0 to N characters

DATE

Format: YYYY-MM-DD. Range: '1000-01-01' to '9999-12-31'

DATETIME

Format: YYYY-MM-DD hh:mm:ss. Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

DECIMAL

numeric values with M digits, of which D digits follow the decimal point

DECIMAL(M,D)

FLOAT 4 bytes

DOUBLE 8 bytes

ALTER TABLE

Adds, deletes, or modifies columns on an existing table.

Data type

a named set of values from which column values are drawn.

Integer

data types represent positive and negative integers. Several integer data types exist, varying by the number of bytes allocated for each value. Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes.

Operator arithmetic - % (modulo)

Divides one numeric value by another and returns

the integer remainder

Operator artithmetic - ^ (exponent)

Raises one numeric value to the power of another

Operator comparison - “=”

Compares two values for equality

Operator comparison - “!=”

Compares two values for inequality.

NULL

a special value that represents either unknown or inapplicable data.

INSERT statement

Statement adds rows to a table

EX: INSERT [INTO] TableName (Column1, Column2, ...)

VALUES (Value1, Value2, ...);

UPDATE statement

The UPDATE statement uses the SET clause to specify the new column values.

DELETE statement

The DELETE statement deletes existing rows in a table. The FROM keyword is followed by the table name whose rows are to be deleted

TRUNCATE statement

The TRUNCATE statement deletes all rows from a table. TRUNCATE is nearly identical to a DELETE statement with no WHERE clause except for minor differences that depend on the database system.

MERGE statement

The MERGE statement selects data from one table, called the source, and inserts the data to another table, called the target.

Primary Key

is a column, or group of columns, used to identify a row. The primary key is usually the table's first column

Simple Primary Key

A simple primary key consists of a single column.

Composite Primary Key

Consists of multiple columns.

Auto-increment column

A numeric column that is assigned an automatically incrementing value when a new row is inserted.

What errors do users occasionally make when inserting primary keys?

• Inserting values for auto-increment primary keys.

• Omitting values for primary keys that are not auto-increment columns.

  MySQL allows insertion of a specific value to an auto-increment column. However, overriding auto-increment for a primary key is usually a mistake.

Foreign Key

Is a column , or group of columns that refer to a primary key.

FOREIGN KEY constraint

A foreign key constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords. When a foreign key constraint is specified, the database rejects insert, update, and delete statements that violate referential integrity.

Referential integrity action - RESTRICT

Rejects an insert, update, or delete that violates referential integrity.

Referential integrity action - SET NULL

Sets invalid foreign keys to NULL

Referential integrity action - SET DEFAULT

Sets invalid foreign keys to NULL

Referential integrity action - CASCADE

Propagates primary key changes to foreign keys.

Constraint

A constraint is a rule that governs allowable values in a database. Constraints are based on relational and business rules, and implemented with special keywords in a CREATE TABLE statement.

UNIQUE constraint

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

CHECK constraint

The CHECK constraint specifies an expression on one or more columns of a table.

How are Constraints added or dropped?

Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause.

Operators and Clauses - IN operator

The IN operator is used in a WHERE Clause to determine if a value matches one of several values.

Operators and Clauses - BETWEEN operator

The BETWEEN operator provides an alternative way to determine if a value is between two other values.

Operators and Clauses - LIKE operators

The LIKE operator, when used in a WHERE clause matches text against a pattern using the two wildcard characters % and _.

• % matches any number of characters. Ex: LIKE 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat".

• matches exactly one character. Ex: LIKE 'Lt' matches "Lot" and "Lit" but not "Lt" and "Loot".

Operators and Clauses - DISTINCT clause

Is used with a SELECT statement to return only unique or ‘distinct’ values.

Operators and Clauses - ORDER BY clause

A SELECT statement selects rows from a table with no guarantee the data will come back in certain order. THE ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order. The DESC keyword with the ORDER BY clause orders rows in descending order.

Numeric functions - Function

A function operates on an expression enclosed in parentheses called an argument, and returns a value.

Numeric functions - ABS(n)

Returns the absolute value of n

EX: SELECT ABS(-5)

returns 5

String functions

String functions manipulate string values.

String function - LOWER(s)

Returns the lowercase (s)

EX: SELECT LOWER (‘MYSQL’);

returns ‘mysql’

String function - TRIM(s)

Returns the string s without leading and trailing spaces

EX: SELECT TRIM(‘ test ‘);

returns ‘test’

Date and Time functions

Operate on DATE, TIME, and DATETIME datatypes