Database Systems: Users, Transactions and Logs

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

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

_________ ___________ must balance several priorities, including storage, response time, and support for rules that govern the data.

Develops programs or applications that will use the database.

_________ ___________ write applications that combine database query languages and general-purpose programming languages

Consumers of the data no the Database. Can access the DB through the app or query the database directly.

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

- Ensures transaction are process completely or not at all
- Prevents conflict between concurrent transactions
- Ensure transactions results are not lost

interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.
- two main functions: manages connections and compiles queries
- Has a Cache Manager that stores reusable information into main memory

- Translates the query processor instructions into low-level file-system commands that modify or retrieve data.
- All access to the database data must go through the _______ ________
- Query optimization ensures the most efficient instructions are sent to the _______ ________, but the _______ ________ can retrieve the data with efficient or inefficient instructions

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.

A file containing a complete record of all inserts, updates, and deletes.

A database that represents data as a collection of tables in which all data relationships are represented by common values in related tables

A language used to create and manipulate databases.

Format of data in a field, such as text, number, or date.
- Some data types are followed by 2 numbers and set of ().
- Example VARCHAR(10)
- DECIMAL(10,3) - 10 digits and 3 after the decimal

- Specifies the database requirements
- Requirements are represented by entities, relationships, and attributes

- Database requirements in a specific database system
- Converts entities, relationships into tables, keys, columns

- Adds indexes and specifies how tables are organized on storage media
- Logical design affects the results
- Physical design affects the performance

- Command Line Client is a text interface that's included in MySQL Download
- MySQL Server returns an Error Code and Description when an SQL statement is syntactically incorrect

A logical data structure in which all data elements within the database are viewed as being stored in the form of simple tables. DBMS packages based on the relational model can link data elements from various tables as long as the tables share common data elements.

- Relational Operation
- Selects an subset of rows

- Relational Operation
- Eliminates one or more columns in a Table

- Relational Operation
- Combines 2 tables by comparing columns

- Relational Operation
- Selects all rows
- You must have the same amount of columns in both select queries
- The results will get merged into the same columns despite the data types

SELECT first_name, last_name FROM customer
____________
SELECT address, district FROM address;

The SQL _______________ operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the _______________ results.

SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] _____________
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

- Relational Operation
- Selects rows that appear in one table BUT NOT another

- Similar to a FULL OUTER JOIN in MySQL

- Relational Operation
- Compute functions over multiple rows

All tables have a Primary Key which values cannot repeat

Different column names of the same table have different names

No two rows of the same table have identical values

Rules that are based on business policies and are specific to a database

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

Ex: CREATE is a ______ statement that creates a table
SQL sub languages

High-level, English-like, DBMS language that contains powerful, easy-to-use commands that enable users to retrieve, sort, order, and display data.

Ex: SELECT is a ______ statement that retrieves data from a table.
SQL sub languages

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

Ex: GRANT is a ______ statement used to give permissions to users.
SQL sub languages

- manages database transactions
- commits data to a database, rolls back data from a database, and creates save points

Ex: COMMIT is a ______ statement that saves a transaction to the database.
SQL sub languages

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 orders. Rows are not ordered. The organization of rows on a storage device, such as a disk drive, never affects query results.

Rule 4 from Rules Governing Tables "No row order":
Allows a DB Admin to improve query performance by changing the organization of data on storage devices... also called _________ ___________.

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

Used in a WHERE clause to determine if a value matches ONE or SEVERAL values

Operator provides an alternative way to determine if a value is between two other values. The operator is written value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue.

- Used in a WHERE clause
- Matches text using a pattern
- uses two wildcard characters % and _
- Case-insensitive pattern matching by default
- Case-sensitive if followed by a binary

- Used in a SELECT
- returns only a unique value

A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order).

Calculations such as Min, Max, Avg, and Sum that are performed on a group of records.

- Select statement that combines data from two tables
- Known as Left and Right table

is used to define a column alias.

Most common type of join; includes rows in the query only when the joined field matches records in both tables.

Return all rows when there is a match in ONE of the tables

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

A join that includes all of the rows from one table and only those rows from the other table that match the join field in the first table.

A join in which the joining condition is based on equality between values in the common columns. Common columns appear (redundantly) in the result table.

A join in which each row from one table is combined with each row from another table.

A query that is embedded (or nested) inside another query. Also known as a nested query or an inner query.

A subquery's WHERE clause reference a column from the outer query.

Copy or replication of data
Data actually stored
Must be refreshed periodically to match corresponding base tables

Can be identified by being a person, place, product, concept, or activity

Statement about two entities.

describe characteristics of the entity

The first stage in the normalization process. It describes a relation depicted in tabular format, with no repeating groups and a primary key identified. All nonkey attributes in the relation are dependent on the primary key.

The second stage in the normalization process, in which a relation is in 1NF and there are no partial dependencies (dependencies in only part of the primary key).

A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies.

A table is in 4NF if it is in 3NF and contains no multiple independent sets of multivalued dependencies.

A normal form necessary to eliminate an anomaly where a table can be split apart but not correctly joined back together.
Also known as Project-Join Normal Form (PJ/NF).

A special type of third normal form (3NF) in which every determinant is a candidate key. A table in BCNF must be in 3NF. See also determinant.

3 Steps for normalizing
1. List all unique columns
2. identify dependencies on non-unique columns
3. Eliminate dependencies on non-unique columns

An attribute, or combination of attributes, that uniquely identifies a row in a relation.

- Performs best when row size is small relative to block size for two reasons:
1. improved query performance
2. Less wasted storage

- Also called columnar storage each block stores values from a single column; benefits:
1. Faster data access
2. better data compression
Relations Database that support columnar: PostgresSQL or Vertica

Row-oriented storage performs better than column oriented for transactions
- Stores data similar to linked lists
- when a row is removed that row is free space
- rows are unordered

- Identifies a sort column that determines physical row order
- Sorted tables are optimal for JOINS, SELECTS, WHERE and SELECTS with ORDER BY
- Sorted tables are optimized for read queries at the expense of inserts and update operations
- Can be sorted by a group of columns

Hash tables contain a table with data and a mapping function which assigns each entry a corresponding hash
- Rows are assigned to Buckets. A bucket is a block or group of blocks containing rows
- Hash Key is a column or Group of Columns, usually the Primary Key
- Hash Functions: computes the bucket containing row forms the Hash Key
- When a row is inserted into a full bucket, a new bucket is allocated and linked to the initial bucket

- Multi-Tables interleave rows of two or more tables in the same storage area
- Cluster Key is a column available in all interleaved tables
- __________ __________ can contain three or more tables as long as all table share the same column key
- Optimal for Joins on the cluster key, but not for most other queries. Since table clusters perform poorly for any types of queries, ______ _____ are not commonly used
- can container 3 or more tables, as long as all the tables share a common cluster key column. However, clusters on 3 or more tables are uncommon.

- Stores columns values and row pointers in a hierarchy
- Multi level Dense Index: has 3 levels
- Sparse Index: only contains entries for documentation that have indexed field, even if the indexed field contains a null value.

Faster than single level indexes on most queries

- A Branch: path from a top level block to the bottom level block is a branch
- A Balanced Branch: All branches are the same length
- Imbalanced Branch: Branches are not the same length

- Indexes are assigned to buckets
- A Bucket containing each index is determined by a hash function
- A hash key cannot be structure as a multi-level index

An index that uses a bit array (0s and 1s) to represent the existence of a value or condition.

A single- or multi-level index in which pointers to table blocks are replaced with primary key values.

Special function applied to a column to make it a specific value

- A database object that maps one or more tables to a single file
- DB Admins can manually create table spaces and assign one or more tables
- DB Admins can improve query performance by assigning frequently accessed tables to fast storage media

- A subset of table data
- Many partitions that don't overlap
- Horizontal Partitions: rows
- Vertical Partitions: columns
- Each partition is stored in a table space

- Range Partition
- List Partition
- Hash Partition

Partitions defined by range of field values
Could result in unbalanced distribution of rows
Like-valued fields share partitions

Based on predefined lists of values for the partitioning key

a table row is assigned to a partition by an algorithm and then maps the specified attribute value to a partition

specifies tables, columns, and keys.

specifies indexes, table structures, and partitions.

translates instructions generated by a query processor into low-level commands that access data on storage media
- Two main functions: transaction management and data access
- Transaction Management includes:
- concurrently systems
- recovery system
- Lock Manager
- Data Access: communicates with the file system and translates column, index reads, block addresses

- InnoDB
- MyISAM
- Memory

default storage engine installed by MySQL
- Indexes are B+Tree
- Primary indexes are auto created
- Secondary Index auto created on every Foreign Key
- Additional secondary indexes are created manually with the CREATE INDEX statement
- Tables with a primary key have stored structure. Tables with no primary key have heap structures

- Limited Transactions
- Locking capabilities
- analytics application with limited data updates

Stores all data in memory

generates a result table that describes how a statement is executed by the storage engine
- select_type: SIMPLE, PRIMARY, SUBQUERY
- table: name of table described in row of ________ result table
- type: const, range, eq_ref, ALL
- possible_keys: all available indexes that might b used to process the query
- key: The index selected to process the query, NULL indicates a table scan is performed.
- rows: estimated number of rows read from table
- filtered: estimated number of rows selected by WHERE clause / estimated number of rows read from table.

- Creates the initial physical design
- Identifies slow queries
- Explains slow queries
- Creates and Drops indexes
- Partition Larger Tables

- Tools
- Storage Engine and Storage Manager
- File Sytem
- Monitor
- Audit

- Interact with the Database Users and Admins
- Sends queries to the Query Processor
- Include connectors
- API's
- Workbench
- Utility Programs

- Executes instructions
- Manages indexes
- Interacts with File System

- Collects and displays info on CPU
- Memory and index utilization
- Queries and results

Keeps track of all database changes

A query execution plan

Low-Level sequence of steps that specify exactly how to process a query

checks each query for syntax errors and converts valid queries to an internal representation.

examining multiple ways of executing the same query and choosing the fastest option

- Consists of data stored on storage media and organized in files
- Three types of data fro each database
- User Data
- Log Files
- Data Dictionary
- Catalog (Data Dictionary): Directory of tables, columns, keys and indexes

Computers interacting directly with End Users; Multi-Tier models

Servers managing resources like databases; Multi-Tier models

Authentication, Business logic; Multi-Tier models

Runs on multiple computers by a wide area network.

- Updates data on multiple nodes of a distributed system
- Either all nodes or no nodes must be successfully updates
- Databases commonly implement a two-phase commitments

- update data on a single node
- Distributed transactions are relatively slow
- Updates in distributed transactions are Synchronous
- Update in separate local transaction are Asynchronous

It is impossible in a distributed system to simultaneously provide consistency, availability and partition tolerance

Database approach in which the central database is replicated at each site.

- A catalog is a directory of information describing database objects such as tables, columns, keys and indexes
- Catalogs in a distributed database are structured in two ways
- Central Catalog: The entire catalog resides on a single node.
- Replicated Catalog: copy of the catalog resides on each node.

- Financial Value: Associated with increased profits
- Productivity Value: Associated with increased throughput
- Trust Value: Greater customer, employee, satisfaction
- Risk Value: associated with visibility into credit exposure

- Centralized repository of information
- Organized around relevant subject areas
- Used for data analysis NOT transactions
- Target location for integrating data from multiple sources

a data collection, smaller than the data warehouse, that addresses the needs of a particular department or functional area of the business

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

a database that is packaged with a programming language

Provides central authentication in a nonhomogeneous environment (SSO system between different environments, operating systems, and networks)

a storage repository that holds a vast amount of raw data in its original format until the business needs it

An entity that exists independently of other entity types.

An entity that depends on another entity for its meaning

refers to the maximum number of times an instance of one entity can be associated with an instance in the related entity
- Number of distinct values in the indexed column.

refers to the minimum number of times that an instance in one entity can be related to an instance in another entity.

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

A collection of data in a structured format.
In principal, databases can be stored on paper or even clay tablets.
In practice, however, modern databases are invariably stored on computers.

A software application that manages corporate data for a specific business function.

A ___________ __________, provide support for database management, security, and access.

A group of queries that must be either completed or rejected as a whole. Execution of some, but not all, queries results in inconsistent or incorrect data.

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

- A directory of tables, columns, indexes, and other database objects.
- The ________ just describes the database. The indexes in order to correctly restore the database changes. Reads are not needed to store database changes, however, and thus are not logged.

uses a two-dimensional table of rows and columns of data. Rows are records (also called tuples), and columns are fields (also referred to as attributes).

changes a table name

All ___________ _________ result in a table. The result ______ is not stored in the database but has the same structure as stored tables

Relational algebra is based on operation of mathematical set theory, such as __________, _________, and ________

logical constraints that ensure data is valid and conforms to business policy

Depending on how tables are specified, some relational rules can be violated. Ex: A table with no primary key can be created. However, rule violations often create business problems and should normally be avoided or automatically prevented.

commonly used in academic literature, is equivalent to a database data type

A __________ is an ordered collection of elements enclosed in parentheses. Ex: (a, b, c) and (c, b, a) are different, since __________ are ordered.

commonly used in file processing, is similar to a database row

commonly used in file process, is similar to a database column

commonly used in academic literature, is equivalent to a database table

Explicit values that are string, numeric, or binary
'String'
"String"
1234
x'0fa2'

Words with special meaning
Example: SELECT, FROM, WHERE

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

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

Stores data exactly as the data appears in memory or computer files, bit for bit. The database manages binary data as a series zeros and ones. common binary data types include BLOB, BINARY, VARBINARY, and IMAGE.

Binary Arithmetic Operator
- The _______ operator return the remainder of one operand divided by another. The remainder is converted to an integer. The ____ is pronounced 'Modulo'

Either Unary or Binary arithmetic operator
The ______ operator usually has two numeric operands.
Ex 5.4 ____ 3.0.

Occasionally, the ____ operator changes the sign of a single numeric operand.
Ex: ___DegreesCentigrade

Binary Comparison operator
!= compares two operands of the same data type, and returns TRUE if the operands are different values. The <> operator is equivalent to ______ and most database support both operators

Unary Logical Operator
_____ takes a singe logical operand. _____ TRUE returns FALSE. _____ FALSE returns TRUE

Binary logical operator
_______ takes two logical operands and return on logical value. _____ returns TRUE when both operands are TRUE. ______ returns FALSE when either operand is FALSE

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

The clause that groups rows based on the specified column

The clause that limits a condition to the groups that are included

A join operator that links tables based on an equality condition that compares specified columns of the tables.

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

The process of linking a table to itself using table aliases. Useful in finding duplicate values in a single table.

A join in which each row from one table is combined with each row from another table.

Subqueries whose execution change according to the row being examined by the main query

Replacing a subquery with an equivalent join is called ____________ a query.
- Many subqueries can be rewritten as a join. Most databases optimize a subquery and outer query separately, whereas joins are optimized in one pass. So joins are usually faster and preferred when performance is a concern.

A set of mathematical principles that form the basis for manipulating relational table contents; the eight main functions are SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

A _________ of a super type entity is a group of mutually exclusive subtypes.

entities that have many common attributes and relationships

an entity that represents a special case of another entity, called a supertype

In generalization hierarchies, an entity or object
that logically contains subtypes. For example, EMPLOYEE is a
supertype of ENGINEER, ACCOUNTANT, and MANAGER

The process of applying rules to a database design to ensure that information is divided into the appropriate tables.

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed. Denormalization potentially yields data anomalies.

________ _______ are similar to sort columns, in that both determine the physical order of rows on storage media.

a file containing column values, along with pointers to rows containing the column value

a database operation that reads table blocks directly, without accessing an index.

a database operation that reads index blocks sequentially, in order to locate the needed table blocks.

also called filter factor or selectivity, is the percentage of table rows selected by a query.

also called a clustering index, is an index on a sort column
- Usually sparse indexes

also called a non clustering index, is an index that is not on the sort column
- Always dense indexes

contains an entry for every table row

contains an entry for every table block
- Much faster than dense indexes since _____ indexes have fewer entries and occupy fewer entries and occupy fewer blocks.

Νumber of table blocks = (number of rows × row size / table block size)

Example: (30,000,000 rows) × (200 bytesow) × (1 kilobyte/1,000 bytes) / (4 kilobyteslock) = 1,500,000 entries

search reads one index block per level plus selected table blocks. the number of levels = log baes fan-out (number of rows) rounded up.

reads all index blocks to determine table blocks containing selected rows, then reads the table block.

search reads on index block per level plus selected table blocks.
- the number of levels = log base fan-out (number of rows), rounded up.

splits remaining index blocks in half after each read, lobs is base 2.

similar to a hash partition, except the partition expression is determined automatically by the database

Singular Attributes remain in the initial table, but plural attributes move to a new weak table:
- The new table contains the plural attribute and a foreign key referencing the initial table
- The PK of the new table is the composite of the plural attribute and the foreign key.
- The new table is identified by the initial table, is the pK cascade and FK restrict rules are specified.
- The new table name consists of the initial table name followed the attribute name:

Attributes can be unique, required, or optional:
- Each unique attribute instance describes at most one entity instance
- Each entity instance has at least on required attribute instance.
- Each entity instance can have zero optional attribute instances

Dependence of one column on another is __________________.
- Reflects business rules
- Example: Each Student receives on letter grade in a course.
- Grade columns depends on the composite column (StudentID, CourseCode)

The ___________ of a database system describes the internal components and the relationships between components. At a high level, the components of most database systems are similar:

- Query Processor
- Storage Manager
- Transaction Manager
- Log
- Catalog (Data Dictionary)

A ________ is an unordered collection of elements enclosed in braces. Ex: {a, b, c} and {c, b, a} are the same, since _________ are not ordered.

DBMS language that changes database content, including data element creations, updates, insertions, and deletions

Ex: INSERT is a ______ statement that inserts data into a table.
SQL sub languages