Data Management - Foundations - D426 WGU

Data

numeric , textual , visual or audio information that describes real-world systems.

varies in ways such as scope, format, access.

analog data

Historically most common before computers, encoded as continuous variations on various physical media.

digital data

most common today, recorded as 0's and 1's on electronic and magnetic media.

database designer

determines the format of each data element and the overall database structure

database user

a consumer of data in a database. can either use an application or submit queries directly to the database. they use, request and update data. to generate reports

transaction

a group of queries that must be completed or rejected as whole. will result in incomplete or incorrect data if not terminated as a whole.

Query processor

interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

Query optimization

to ensure the most efficient instructions are executed on the data from the processor.

Storage Manager

translates the query processor instructions into low-level file-system commands that modify or retrieve data.

indexes

used by the storage manager to quickly locate data

catalog

also called a data dictionary , a directory of tables, columns and indexes as well as other database objects.

relational database

a database that stores data in tables that consist of rows and columns, similar to a spreadsheet

SQL

Structured Query Language

NoSQL

A new generation of database management systems that is not based on the traditional relational database model.

query

command for a database that typically inserts, retrieves, updates and deletes data from the database.

crud

Create, Read, Update, Delete

statement

a database command such as a query that inserts , selects updates and deletes data

SELECT

Retrieves data from a table

UPDATE

modifies data in a table

data type

indicates the type of data that can be stored in a field of a column

data independence

A condition in which data access is unaffected by changes in the physical data storage characteristics.

database model

Describes the relationship between the data elements and provides a framework for organizing the data. contains 3 parts

data structures

operations

rules

relational model

A database model that describes data in which all data elements are placed in two-dimensional tables, called relations, which are the logical equivalent of files.

row

an unnamed tuple of values , each value corresponds to a column and belongs to the columns data type.

relational rules

part of the relational model and govern data in every relational database

data lake

An analytic database of raw, unprocessed data copied from multiple data sources.

in-memory database

A database management system that stores the entire database in random access memory (RAM).

replica

a cop of an entire database

data warehouse

a separate database optimized for analytics instead of operations

analytic data

Historical data used to make decisions in the present

operational data

data used for managing business processes, such as for processing transactions or for data analysis

distributed transaction

A database transaction that accesses data in several remote data processors in a distributed database.

Local Transactions

updates data on a single node of a distributed database

CREATE TABLE

A SQL command that creates a table's structures using the characteristics and attributes given.

CREATE TABLE Persons (

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

Data Transaction Language (DTL)

manages database transactions

Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data

Data Manipulation Language (DML)

A specific language, provided with a DBMS, which allows users to access and modify the data, to make queries, and to generate reports.

data query language (DQL)

Enables users to retrieve, sort, and display specific data from the database

Data Definition Language (DDL)

The language that allows a database administrator to define the database structure, schema, and subschema.

entity

An object or event that we want to keep track of

attribute

A property or characteristic of an entity or relationship type that is of interest to the organization.

binary relationship

A relationship between the instances of two entity types.

primary key

a column or group of columns that identifies a unique row in a table

Simple Primary Key

consists of a single column

composite primary key

consists of multiple columns

PRIMARY KEY (example_table_1,example_table_2));

Metadata

is data about the database, such as column names and the number of rows in each table. Database systems store metadata in the catalog and use metadata to process queries.

Oracle Database

type: relational

license : commercial

MySQL Database

type: relational

license : open source

SQL Server

type: relational

license : Commercial

PostgreSQL database

type: relational

license : open source

MongoDB

type: NoSQL

license : open source

database design

a specification of database objects such as tables, columns, data types, and indexes. also refers to the process used to develop the specification.

For large, complex databases, the process has three phases:

Analysis

Logical design

Physical design

Analysis

specifies database requirements without regard to a specific database system.

also called conceptual design, entity-relationship modeling, and requirements definition.

ER diagrams

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

Lines between rectangles represent relationships.

Text inside rectangles and below entity names represent attributes.

logical design

Rectangles represent tables. Table names appear at the top of rectangles.

Text within rectangles and below table names represents columns.

Solid bullets (●) indicate key columns.

Empty bullets (○) and arrows indicate columns that refer to keys.

physical design

this phase adds indexes and specifies how tables are organized on storage media

not used often , logical is more important.

API

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

MySQL Community

non-commercial , free

MySQL Enterprise

a paid edition for managing commercial databases.

MySQL Command-Line Client

a text interface included in the MySQL Server download

MySQL Workbench

GUI for mysql sever

tuple

an ordered collection of elements in parentheses

table

column

row

data type

Project

eliminates 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.

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.

the result of relational operations is always a...

Table

rules

logical constraints that ensure data is valid

Relational rules

unique primary key

unique column names

no duplicate data

The SQL standard is published jointly by the...

American National Standards Institute (ANSI) and the International Organization for Standardization (ISO)

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.

'String'

"String"

123

x'0fa2'

Keywords

Words with special meaning.

SELECT , FROM , WHERE

Identifiers

Objects from the database like tables, columns, etc.

City, Name, Population

Comments

Statement intended only for humans and ignored by the database when parsing an SQL statement.

-- single line comment /* multi-line Comment */

DML

Can insert a row into a table

DTL

Rollback database changes.

DQL

Select all rows from table Product.

DCL

Grant all permissions to user 'tester'.

DDL

Create table Product.

database system instance

is a single executing copy of a database system

DROP DATABASE DatabaseName

deletes a database, including all tables in the database.

CREATE DATABASE DatabaseName

creates a new database.

USE DatabaseName

selects a default database for use in subsequent SQL statements.

SHOW

provide information about databases, tables, and columns

Tables must obey these relational rules

Exactly one value per cell.

No duplicate column names.

No duplicate rows.

No row order.

In relational databases, query results are not dependent on physical storage. Data independence allows database administrators to tune storage for optimal performance at any time.

true

ADD (ALTER TABLE CLAUSE)

Adds a column

ALTER TABLE TableName

ADD ColumnName DataType;

CHANGE (ALTER TABLE CLAUSE)

Modifies a column

ALTER TABLE TableName

CHANGE CurrentColumnName NewColumnName NewDataType;

DROP ( ALTER TABLE CLAUSE)

Deletes a column

ALTER TABLE TableName

DROP ColumnName;

1ST OPERATER PRECEDENCE

-(UNARY)

2ND OPERATER PRECEDENCE

^

3RD OPERATER PRECEDENCE

* / %

4TH OPERATER PRECEDENCE

+ - (binary)

5TH OPERATER PRECEDENCE

= != < > <= >=