Practice Test 3 Flashcards
Logical Constraints
rules of relational databases that ensure data is valid
A syntax error and error occurs when…
SQL is syntactically incorrect
INT
Whole integer value such as age
MySQL Command line interface
Text-based interface included in mySQL server
Data Transaction Language (DTL)
Can be used to roll back database changes
Database Control Language (DCL)
Used to manage/control database access (Grant, Revoke)
Which Database includes the world database during its installation?
MySQL
Data Manipulation Language (DML)
Used to manage data within databases (Select, Insert, Update, Delete)
Data Definition Language (DDL)
Used to manage and define database structures (Create, Alter, Drop, Truncate)
Data Query Language (DQL)
Used to query and retrieve data from a database (Select)
Operation order precedence
- exponentiation
- multplication/div
- add/sub
- comparison (=,<=,>=,etc)
- Between, when, case, then, else
- Not
- and
- OR
- SET =
Self Join
Table joins itself
Useful for comparing rows within the same table
Compares columns from left table to right table
=
Outer Join
Left/right outer join will return all the rows from the left/right table and only the matching rows from the other table.
Full outer join will return all rows if matching and Null values for the non matching rows
EquiJoin
combines rows that have equivalent values in specified columns
Used to combine rows form two or more tables based on a related column
Cross Join
Combines rows from both tables all into one new table
Outer query
Main query that can contain one or more subqueries(Nested queries)
Nested Query
Sub query that is a query within a query
Correlated Query
Sub query that references columns from the outer query
IN
allows you to specify multiple values in a WHERE clause
BETWEEN
Selects values within a given range
LIKE
used in a where clause to search for a specified value
OR
Used to combine multiple conditions in a WHERE clause
%
Used to represent zero or more characters when searching for a specified pattern using a LIKE operator
TRIM
Removes leading and trailing spaces from a string
REPLACE
Replaces all occurrences of a specified substring within a string with another substring
SUBSTRING
Extracts a part of a string, starting at a specificized position and for a specified length
ORDER BY
Sorts the entire result set.
Can be used with any columns in the result set.
Does not change the number of rows in the result set.
GROUP BY
Groups rows into summary rows.
Must be used with aggregate functions like COUNT, SUM, AVG, etc.
Reduces the number of rows in the result set to one per group.
Virtual view
Virtual table that provides a result set of query but doesnt store data itself
Snapshot View
Read only, static view of a database at a specific point in time
Denormalized View
Includes redundant data to improve read performance. Combines data from multiple tables into a single view to reduce the need for joins
Materialized View
Stores results of a query physically and periodically refreshes to stay up to date with the underlying data
TRUNCATE
Removes all rows from a table but doesn’t delete the actual table or remove data from a temp table
DELETE
Removes specific rows from a table
Auto-increment
Allows SQL to auto insert a field value
CHECK constraint
used to specify an expression that must be true for each row
RESTRICT
Used with foreign key constraints to prevent actions that would violate referential integrity
CASCADE
Specifies that changes made in the parent table propagate to the child table
Junction Table
Used to establish a many to many relationship between two tables using foreign keys
In a relational database what type of relationship is established using foreign keys?
A one to Many
What statement may be rejected due to a foreign key constraint?
Insert
Candidate Keys
Unique columns that aren’t technically primary keys
Trivial dependency
A functional dependency A→B, is considered trivial if B is a subset of A
EX: Table with attributes (StudentID, Name, AGE)
Trivial: (StudentID, Name) → StudentID
Non-Trivial: StudentID → Name
Merging tables
process of denormalization
Normalization
eliminates redundancy by decomposing a table into two or more tables in a higher normal form
Boyce-Codd normal form
optimal normal form for frequent inserts, updates, and deletes of data
ER Diagram symbols
- Rectangle or square - An entity
- Double rectangle - Weak entity
- Oval/circle - Attribute
- Diamond - Relationship between entities
- Crow’s foot - many (0 or more)
- single line - represents 1 or single (Optional)
- Double line - Represents one and only one (Mandatory)
- Circle with a line across - Represents zero or 1 (Optional)
IsA relationship
relationship or association between a supertype entity and its subtype entities
Entity
Real world object that can be identified and grouped
Attribute Maximum
represented on the relationship or outside the parenthesis.
Max number of relationships an entity can have with another
Attribute Minimum
Shown inside the the parenthesis or part of the relationship line
Min amount of instances two entities can have
Trend analysis
evaluating historical data to identify patterns or tends
Unique Identifier Analysis
Special Code or number assigned to object, person, thing to ensure each entry in a DB is unique.
Used for identifying primary keys
Dense Index
Index record for every key value
Sparse Index
Index record for only some of the key values
Multi-Level Index
indexing method that uses a hierarchy of indexes to manage large data sets
Secondary Index
Not the primary index and can contain duplicates
Bitmap Index
Grid of bits where each indexed row corresponds to a unique row
Hash index
Uses a hash function and key and buckets
