Data Management Foundsations D426

Database Management System

Software that reads and writes data in a database. Secure, consistent, and available at all times.

Query

Request to retrieve or change data in a database

Query Language

Specialized programming language, designed specifically for database systems.

Database Applications

Software that helps business users interact with database systems.

Information Management System

software application that manages corporate data for a specific business function. Usually includes a database system as well as other ocmponents, such as a user interface, business logic, and interfaces to other systems.

Database Administrator

Responsible for securing the database system against unauthorized users.

Database Engineers

Determines the format of each data element and the overall database structure. Balance priorities such as storage, response time, and support for rules that govern the data. Technically challenging

Database Designer

Develops computer programs that utilize a database. Programmers write applications that combine database query languages and generall-purpose programming languages. Specialized challenge.

Database User

Consumer of the data. Request, update, or use stored data.

Transaction

A group of queries that must be either completed or rejected as a whole

Architecture

Describes teh interal components and the relationships between components.

Catalog or Data Dictionary

Directory of tables, columns, indexes, and other database objects.

Metadata

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

4 Common Queries

Insert, Select, Update, Delete (CRUD - create, read, update, delete)

SQL

Structured Query Language, standard query language of relational database systems.

SQL Statement

Complete, executable database command.

Three Phases of Database Design

Conceptual, Logical, Physical

Conceptual Design Phase

Specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes.

ER diagrams

How entities, relationships and attributes are depicted

• Rectangles with round corners represent entities

• Lines between rectangles represent relationships

• Text inside rectangles and below entity names represent attributes

Logical Design Phase

Implements database requirements in a specific database system. Converts entities, relationships, and attributes into tables, keys, and columns.

Table Diagram

How a logical design is depicted - similar to ER but more detailed

• Rectangles with square corners represent tables

• Text within rectangles and below table names represent columns

• Bullets indicate key columns

• Arrows between tables indicate columns that refer to keys. Arrow points to the table containing the key

Physical Design Phase

Adds indexes and specifies how tables are organized on storage media. Can be depicted in diagrams - but not commonly used.

Data Independence or Information Independence

Principle that physical design never affects query results and only the query processing speed.

Application Programming Interface - API

library of procedures or classes that links a host programming language to a database.

Cursor

Helps bridge the gap between SQL, which generates multiple rows in one statement, and host languages, which generally process one row at a time in a loop.

MySQL

Leading relational database system, sponsored by Oracle

Database Model

A conceptual framework for database systems with three parts

• Data structures - how organized

• Operations - manipulate data structures

  • Rules - govern valid data

Relational Model

Database model on a tabular data structure. Published in 19701 by E. F. Codd

Set

unordered collection of elements enclosed in braces
{a, b, c} and {c, b, a} are the same

• A table is a set of rows, the rows have no inherent order

tuple

ordered collection of elements enclosed in parentheses

• (a, b, c) and (c, b, a) are different

Terms used in database processing

Table, column, row, and data type

Relational Rules

Part of the relational model that govern data in every relational database

• Unique Primary Key - all tables have a primary key column, or group of columns, in which values may not repeat

• Unique Column Names - different columns of the same table have different names

• No duplicate rows - no two rows of the same table have identical values in all columns

Automated Script

A series of SQL statements that is executed repeatedly. Prepared in advance and saved in a file or as a database stored procedure.

Data Definition Language

Defines database structure - DDL

Data Query Language

Retrieves data - DQL

Data Manipulation Language

inserts, updates, and deletes data

Data Transaction Language

Manages transations

Data Control Language

Specifies user access to data. Primarily of interest to database administrators

Statement

A complete, executable instruction, ending with a semicolon.

Clause

Begins with a keyword, followed by additional language elements

SELECT

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

PROJECT

Selects one or more columns of a table

PRODUCT

lists all combinations of rows of two tables

JOIN

Combines two tables by comparing related columns

UNION

Selects all rows of two tables

INTERSECTS

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 or count

Table rules:

• Exactly one value per cell

• No duplicate column names

• No duplicate rows

• No row order

Transpose

Operation in a table where rows become columns and columns become rows

Primary Key

Column or a group of columns used to identify a row

Simple Primary Key

Consists of a single column

Composite Primary Key

Consists of multiple columns

Foreign Key

A column, or group of colums, that refer to a primary key. Data types fo both keys have to be the same but the names can be different. Foreign keys may be NULL

Referential Integrity

A relational rule that requires foreign key values are either fully NULL or match some primary key value

Partially NULL

A coposite foreign key value is partially NULL if some, but not all, columns are NULL

RESTRICT

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

SET NULL

Sets invalid foreign keys to NULL

SET DEFAULT

Sets invalid foreign keys to the foreign key default value

CASSCADE

Propagates primary key changes to foreign keys

ON UPDATE/ON DELETE

Followed by either RESTRICT, SET NULL, SET DEFAULT, or CASSCADE

Consraint

A rule that governs allowable values in a database. Based on relational and buisness rules and are implemented with special keywords in a CREATE TABLE statement.

UNIQUE

Ensures that values in a column or group of columns are unique

CHECK

Specififes an expression on one or more columns of a table. Satisfied with either TRUE or NULL

Expression

A string of oeprators, or operands, and parentheses that evaluate to a single value.

SELECT/FROM

SELECTS rows FROM a table. Can select more than one row. Must use commas to seperate selection in clause

Result Table

SELECT statement returns a set of rows called the result table

LIMIT

Limits the number of rows returned by a SELECT statement

Condition

A condition is an expression that evaluates to a logical value

Truth Tables

The value of logical expressions containing NULL operands is defined in truth tables

IN

Used in a where clause to determine if a value matches one of serval values

BETWEEN

alternate way to determine if a value is between two otehr values. Written as - value BETWEEN minValue AND maxValue. Same as value >= minValue AND value <= maxValue

LIKE

Used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

ORDER BY

Used to order selected rows by one or more columns in accending order. DESC is used with it to order decending.

Function/Argument

An expression enclosed in parentheses, called an argument, and returns a value.

COUNT()

Counts the number of rows in the set

GROUP BY

Clause consists of the GROUP BY keyword and one or more columns. Each simple or composite value of the column(s) becomes a group. Returns one row for each group.

HAVING

Used with GROUP BY to filter group results

JOIN

Select statment that combines data from two tables, known as the left table and the right table, into a single result

AS

Column name replaced with an alias. Alias follows the column name, separated by an AS keyword

INNER JOIN

Selects only matching left and right table rows

FULL JOIN

Left and right table rows, regardless of match

ON

Specifies the join columns

LEFT JOIN

Selects all left table rows, but only matching right table rows

RIGHT JOIN

Selects all right table rows, but only matching left table rows

OUTER JOIN

An outer join is any join that selects unmatched rows, including left, right and full joins

UNION

Combines the results of two SELECT clauses into one result table

Equijoin

Compares columns of two tables with the = operator

non-equijoin

Compares columns with an operator other than =, such as < and >

self-join

Joins a table to itself

CROSS-JOIN

Uses

Entity-relationship model

High-level representation of data requirements, ignoring implementation details. An entity-relationship model guides implementation in a particular database system.

Reflexive relationship

Relates an entity to itself

ER Diagram

Entity-Relationship Diagram (how entities, relationships, and attributes are depicted)

Entity

Person, Place, Product, Concept, or Activity

Relationship

Statement about two entities

Attribute

Descriptive property of an entity

What’s included in a glossary?

Names, synonyms, and descriptions

Entity Type

Set of things (all employees at a company) - Typically become a table