D426 Flashcards
Database Application
Software that helps business users interact with database systems.
Database Administrator
Secures the database and gives users access.
Query Processor
Interprets queries, creates a plan to modify the database or retrieve data.
Storage Manager
Translates the query processor instructions into system commands that modify or retrieve data.
Transaction Manager
Ensures transactions are properly executed. Prevents conflicts between transaction. Restores the database after a system failure.
Analysis Phase
Specifies database requirement. Creating entities, relationships and attributes.
Relationship
A link between entities.
Entity
Person, place, activity or thing.
Attribute
Describes an entity.
Logical design
Implements database requirements. Converts entities, relationships and attributes into tables, keys and columns.
Physical design
Adds indexes and specifies how tables are organized.
API
Application programming interface. Used to simplify the use of SQL with a general-purpose language.
MySQL Command-Line Client
A text interface used to return errors when SQL code is syntactically incorrect.
Tuple
A collection of elements enclosed in parentheses.
Table
Has a name, fixed tuple of columns and a varying set of rows.
Column
Has a name and a data type
Row
An unnamed tuple of values. Each value corresponds to a column and belongs to that columns data type.
Synonym for: Table
File, Relation
Synonym for: Row
Record, Tuple
Synonym for: Column
Field, Attribute
Business rule
Rules based on business policy and specific to a particular database.
Literals
Explicit values surrounded by quotation marks.
Keywords
Words with special meaning. ex SELECT, FROM, WHERE
Identifiers
Objects from the database like tables and columns.
Data Definition Language(DDL)
Defines the database schema. (CREATE, DROP, ALTER, TRUNCATE)
Data Manipulation Language(DML)
Commands that manipulate the data ( INSERT, UPDATE, DELETE, LOCK)
Data Query Language
Retrieves Data from the database (SELECT)
Data Control Language
Controls database user access.
Data Transaction Language
Manages database transactions
Table
Fixed sequence of columns and a set of rows
Column
Has a name and a data type
Row
An unnamed sequence of values
Cell
A single column of a single row
Data independence
Also called rule 7: Allows database admins to improve query performance or organizing the data without affecting query results.
DROP TABLE
Deletes all the tables rows from the database
Data type
A named set of values from which a column is drawn
TINYINT
1 byte -128 to 127
SMALLINT
2 bytes +- 32768
MEDIUMINT
3 bytes +-8388608
INT
4 bytes +-2147483647
BIGINT
8 bytes
UPDATE
Uses the SET statement to specify the new column values. WHERE is optional
DELETE
The FROM keyword is followed by the table name whose rows are deleted.
TRUNCATE
Deletes all rows from a table
MERGE
state selects data from one table and inserts the data to another table called the target
Primary Key
a column used to identify a row. Usually the tables first column
Simple primary key
Consists of a single column.
Composite primary key
consists of multiple columns
Foreign key
A column that refer to the primary key
Constraint
A rule that governs allowable values in a database.
BETWEEN
Provides an alternative way to determine if a value is between two other values.
LIKE
when used in a WHERE clause matches text against a pattern using the two wildcard characters % and .
ORDER BY
Orders rows in ascending order
TRIM
Removes white space.
Aggregate function
COUNT, MIN, MAX, SUM, AVG
HAVING
Used with the GROUP BY clause to filter group results.
JOIN
A SELECT statement that combines data from two tables.
ALIAS
follows the column name separates by an optional AS keyword.
INNER JOIN
Only matching left and right table rows
FULL JOIN
Selects all left and right table rows
LEFT JOIN
Selects all left table rows
RIGHT JOIN
Selects all right table rows.
OUTER JOIN
Only unmatched rows
UNION
Combines two results into one table
EQUIJOIN
Compares tables with the = operator
SELF JOIN
Joins a table to itself
Cross-join
Combines two tables without comparing columns. No ON clause
Subquery
A query within another query also called a nested or inner query
Materialized view
A view for which data is stored at all times.
WITH CHECK
The database rejects inserts and updates that do not satisfy the view query.
ER model
A high level representation of data requirements
Entity
A person, place, product, concept or activity
Relationship
A statement about two entities.
Attribute
A descriptive property of an entity
Entity type
A set of things. ex: All employees in a company
Relationship type
A set of related things Ex Employee-manages-department is a set
Attribute type
A set of values Ex All employee salaries.
Entity instance
An individual thing Ex The employee Sam Snead
Relationship Instance
A statement about entity instances Ex Maria Rodriguez manages Sales
Attribute instance
An individual value Ex The salary $35,000.
Analysis
Develops an ER model, capturing data requirements while ignoring implementation details.
Logical design
Converts the ER model into tables columns and keys
Physical design
Adds indexes and specifies how tables are organized on storage media.
IsA relationship
An identifying relationship
Partitions
supertype entity is a group of mutually exclusive subtypes
Crows foot notation
depicts cardinality as a circle(zero) a short line(one) or three short lines(many)
Intangible entity
A documented in the data model, but not tracked
PRIMARY KEYS
Simple. Primary key values should be easy to type and store.
Artificial key
A single-column primary key created by the database designer when no suitable single-column or composite primary key exists.
Functional dependence
Dependence of one column on another
Redundancy
is the repetition of related values in a table
Normal forms
Rules for designing tables with less redundancy
Boyce-Codd normal form
Idea for tables with frequent inserts, updates and deletes
Trivial dependencies
When the columns of A are a subset of the columns of B,
Normalization
Eliminates redundancy by decomposing a table into two or more tables.
Denormalization
Means intentionally introducing redundancy by merging tables.
Heap Table
no order is imposed on rows
Hash table
rows are assigned to buckets
Table scan
A database operation that reads table blocks directly
Index scan
Database operation that reads index blocks sequentially
Hit ratio
Filter factor or selectivity is the percentage of table rows selected by a query
