Word Document Study Guide Flashcards
Database application
Software that helps users interact with database systems
Database administrator
Responsible for securing the database system against unauthorized users.
Enforces procedures for user access and availability.
Authorization
Database users should have limited access to specific tables, columns, or rows of database.
Rules
Database systems ensure data is consistent with structural and business rules.
Query Processor
Interprets queries, creates a plan to modify the database or retrieve data, and return query results to the application.
Performs query optimization to ensure the most efficient instructions are executed in the data.
Store Manager (storage engine)
Translates the query processor instructions into low-level file-system commands that modify or retrieve data.
Uses indexes to quickly locate data.
Transaction Manager
Ensures transactions are properly executed.
Prevents conflict between concurrent transactions.
Restores database to a consistent state in the event of a transaction failure.
Leading database products
INSERT
Inserts rows into a table
SELECT
Retrieves data from a table
UPDATE
Modifies data in a table
DELETE
delete rows from a table
CREATE TABLE
Creates a new table by specifying the table and column names.
Data Types (INT, DECIMAL, VARCHAR, DATE)
Indicates the format of column values
INT
Stores integer values
DECIMAL
Stores fractional numeric values
VARCHAR
Stores textual values
DATE
stores year, month, and day
Analysis (1st step)
Specifies database requirements without regard to a specific database system.
Requirements are represented as entities, relationships, and attributes
Alternative names: Conceptual Design, entity-relationship modeling, requirements definition
Logical Design (2nd step)
Implements database requirements in a specific database system.
Converts entities, relationships, and attributes into tables, key, and columns.
Physical Design (3rd step)
Adds indexes and specifies how tables are organized on storage media.
Affects query processing speed but not the query result
Data Independence
Principle that physical design never affects query results.
Entity
Person, place, activity, or thing
Relationship
Link between two entities
Attribute
Descriptive property of an entity
Application Programming Interface (API)
General-purpose language to simplify the use of SQL
MySQL Command-Line Client
Text interface included in the MySQL Server download
Error code
What MySQL Server returns when an SQL statement is syntactically incorrect or database cannot execute the statement
Tuple
Ordered collection of elements enclosed in parentheses. (a,b,c) and (c,b,a)
Table
has a name, a fixed tuple of columns, and a varying set of rows
Column
Has a name and a data type
Row
Unnamed tuple of values
Synonyms:
Table
File
Relation
Synonyms:
Row
Record
Tuple
Synonyms:
Column
Field
Attribute
Business rules
Based on business policy and specific to a particular database
Literals
Explicit values that are string, numeric, or binary.
‘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
Data Definition Language (DDL)
Defines the structure of the database
CREATE, ALTER, DROP
Data Query Language (DQL)
Retrieves data from the database
SELECT
Data Manipulation Language (DML)
Manipulates data stored in a database
INSERT, UPDATE, DELETE
Data Control Language (DCL)
Controls database user access
GRANT, REVOKE
Data Transaction Language (DTL)
Manages database transactions
SAVEPOINT, ROLLBACK, COMMIT
Cell
Single column of a single row
Rule 7
Called data independence
ALTER TABLE
Statement that adds, delete, modifies columns on an existing table.
Integer by byte (Integer Storage)
Operator
Operator Precedence
UPDATE statement
uses SET clause to specify the new column values.
DELETE statement
Deletes existing rows in a table.
FROM keyword is followed by the table name whose rows are to be deleted.
TRUNCATE
Deletes all rows from a table.
MERGE
Selects data from one table, called the source, and inserts data to another table, called the target.
Primary Key
Column, or group of columns, used to identify a row.
Usually the table’s first column.
Simple primary key
Consists of a single column
Composite primary key
Consist of multiple columns
Auto-increment column
Numeric column that is assigned an automatically incrementing value when a new row inserted
Errors when inserting Primary Key
Inserting values for auto-increment primary keys
Omitting values for primary keys that are not auto-increment columns
Overriding auto-increment for a _______ is usually a mistake
primary key
Foreign Key
Column, or group of columns, that refer to a primary key
When a _______ constraint is specified, the database rejects insert, update, and delete statements that violate _________
Foreign key
Referential Integrity
RESTRICT
Rejects an insert, update, or delete that violates referential integrity.
CASCADE
Propagates primary key changes to foreign keys
Constraint
Rule that governs allowable values in a database
Based on relational and business rules
Constraints are added and dropped with the ________ TableName followed by an ADD, DROP, or ______ clause
ALTER TABLE
CHANGE
BETWEEN
Provides an alternative way to determine if a value is between two other values
BETWEEN minValue and maxValue
LIKE operator, when used in a ______ clause, matches text against a pattern using the two wildcard characters “%”and “_“(underscore)
WHERE
%
Matches any number of characters
Ex: ‘L%t’ matches ‘Lot’, ‘Lift’
ORDER BY
Orders selected rows in ascending order
DESC keyword used with ORDER BY orders rows in descending order
ABS(n)
Returns absolute value of n
SELECT ABS (-5)
returns 5
LOWER(s)
Returns lowercase s
TRIM(s)
Removes leading and trailing white spaces
HOUR(t)
MINUTE(t)
SECOND(t)
Returns the hour, minute, or second from time t
COUNT()
Count the numbers of rows in the set
MIN()
Finds the minimum value in the set
MAX()
Finds the maximum value in the set
SUM()
Sums all the values in the set
AVG()
Computes the arithmetic mean of all the values in the set
HAVING
Used with GROUP BY clause to filter group results
A ____ is a SELECT statement that combines data from two tables, knowns as _____ and _____, into a single result.
join
left table
right table
The tables are combined by comparing columns from the left and right tables, usually with the ____ operator
=
AS
Replacing with an alias
INNER JOIN
Selects only matching left and right table rows
FULL JOIN
Selects all left and right table rows, regardless of match
LEFT JOIN
Selects all left table rows, but only matching right tables rows
RIGHT JOIN
Selects all right tables, but only matching left table rows
outer join
Any join that selects unmatched rows, including left, right, and full joins
UNION
Combines the two results into one 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
Combines two tables without comparing columns.
Uses a CROSS JOIN without an ON clause
Subquery
Query within another query
Called a nested query or inner query
Materialized View is a view for which data is _______. Whenever a base table changes, the corresponding view tables can also _____, so materialized views must be refreshed.
stored at all times
change
When WITH CHECK OPTION is specified …
database rejects inserts and updates that do not satisfy the view query WHERE clause.
entity-relationship model
High-level representation of data requirements, ignoring implementation details.
Reflexive relationship
Relates an entity to itself
entity-relationship diagram
ER diagram
Schematic picture of entities, relationships, and attributes
Entity Type
Type is a set
Set of things
Relationship Type
Type is a set
Set of related things
Attribute Type
Type is a set
Set of values
Entity Instance
Individual thing
Relationship Instance
Statement about entity instances
Attribute Instance
Individual value
Analysis steps
1- Discover entities, relationships, and attributes
2- Determine cardinality
3- Distinguish strong and weak entities
4- Create super type and subtype entities
Logical Design Steps
5- Implement entities
6- Implement relationships
7- Implement attributes
8- Apply normal form (eliminate redundancy)
Cardinality refers to ______ and _______ of relationships and _______
maxima
minima
attributes
Relationship maximum
Greatest number of instances of one entity that can relate to a single instance of another entity.
Relationship minimum
Least number of instances of one entity that can relate to a single instance of another entity.
In ER diagrams, attribute maximum and minimum follow the _____ name. The minimum appears in ________.
attribute
parentheses
A ______ entity is a subset of another entity type, called the _____ entity.
subtype
supertype
IsA
supertype entity identifies its subtype entities
Partition of a _____ entity is a group of mutually exclusive ______ entities
supertype
subtype
After entities, ____, ____, ____, and strong and weak entities are determined, the database designer looks for ____ and ____ entities.
relationships
attributes
cardinality
supertypes
subtypes
Crow’s foot notation
Depicts cardinality as a circle (zero), a short line (one), or three short lines (depicts a bird’s foot).
Intangible entity
Not tracked with data in the database
Artificial key
Single column primary key by the database designer when no suitable single-column or composite primary key exists.
Functional Dependence
Dependence of one column on another
Redundancy (normalization)
Repetition of related values in a table
Normal Forms
Rules for designing tables with less redundancy
Candidate Key
Simple or composite column that is unique and minimal.
Boyce-Codd normal form
Ideal for tables with frequent inserts, updates, and deletes.
Normalization
Eliminates redundancy by decomposing a table into two or more tables
Denormalization
Intentionally introducing redundancy by merging tables
Heap table
No order is imposed on rows
Optimize insert operations
Fast for bulk load of many rows
Hash table
Rows are assigned to buckets
Table scan
Database operation that reads table blocks directly, without accessing an index
Index scan
database operation that reads index blocks sequentially, in order to locate the need table blocks
Hit ratio (filter factor or selectivity)
Percentage of table rows selected by a query
Hash Index
Entries assigned to buckets
Bitmap Index
Grip of bits
Contains ones and zeroes
Tablespace
Database object that maps one or more tables to a single file
CREATE TABLESPACE
