Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
39 Cards in this Set
- Front
- Back
What are the 4 components of the database system? |
1. Users 2. Database Application 3. DBMS 4. Database |
|
Referential Integrity Constraints |
A column from a database can reference to the primary key of another table. DBMS enforces this constraint. |
|
Integrated tables |
Tables that store both data & relationships among the data |
|
Normalization |
Determining how to effectively design the database (Should this data be in 1 table, or split into two?) |
|
Data Model |
The blueprint of the database process |
|
Database design |
The construction of the database in DBMS |
|
Database migration |
Adapting the database to new or changing requirements (deleting relationships, editing formats, etc) |
|
Who is E F Codd? |
Formed the concepts for normalization in relational databases. Organized by principles based on identified relations between various kinds of data. (keys) |
|
Database schema |
Complete logical view of the database, containing all the tables, all columns in each table, primary key, and foreign keys. ORDER(OrderNumber, SKU, Quantity) |
|
Data Sublanguage |
The SQL language - it only refers to the statements needed for creating/processing data & metadata |
|
Data Definition Language (DDL) |
statements used for creating tables, relationships, and other structures |
|
Data Manipulation Language (DML) |
statements used for querying, inserting, modifying, and deleting data |
|
SQL/Persistent Stored Modules (SQL/PSM) |
statements that extend SQL by adding procedural programming capabilities within the SQL framework (flow of control, variables, etc) |
|
Transaction Control Language (TCL) |
statements used to marked boundaries and control transaction behavior |
|
Data Control Language (DCL) |
statements used to grant and/or revoke permissions to users and groups |
|
SQL SELECT... |
specifies which columns are listed in the query |
|
SQL FROM... |
specifies which tables are listed in the query |
|
SQL WERE... |
specifies which rows are to be listed in the query, can be narrowed & also compounded into AND, OR operators narrowed: SELECT *FROM SKU_DATA WHERE Department = "Water Sports" AND Buyer = 'Nancy Meyers' OR Buyer = 'Joe Smith' |
|
SQL SELECT DISTINCT... |
specifies to remove all duplicate rows of data |
|
SQL ORDER BY... |
specifies which order to display the rows (defaults to ascending) can specify further with: ORDER BY Price DESC, OrderNo ASC |
|
SQL WHERE IN/NOT IN... |
To specify multiple values or exclude multiple values SELECT * FROM SKU_DATA WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin') SELECT *FROM SKU_DATA WHERE Buyer NOT IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin') |
|
SQL Wildcard |
% - Can be used to find values not 100% specified WHERE Buyer LIKE 'Pete%' |
|
SQL Underscore Wildcard |
specifies a single, unspecified character WHERE SKU LIKE '%2_ _' |
|
SUM, AVG, MIN, MAX, COUNT (AS) |
Mathematical functions used in the SELECT operator, AS can rename that column SELECT SUM(OrderTotal) AS OrderSum |
|
SQL JOIN... |
specifies where to join tables in the FROM operator |
|
Functional Dependencies |
where 1+ attributes determines the value of another attribute ObjectColor --> Weight ObjectColor --> Shape ObjectColor--> (Weight, Shape) |
|
Key |
A combination of one or more columns that is used to identify a particular row in a relation |
|
Composite Key |
keys that have two or more columns |
|
Candidate key |
determines all of the other columns in a relation |
|
Primary key |
the primary means for finding rows in tables in the DBMS |
|
Surrogate keys |
added to the table to serve as the primary key |
|
Foreign key |
the primary key of a table other than the one which its assigned to |
|
Overlapping candidate keys |
keys that share the same attribute (StudentID) |
|
Multi-valued dependency |
when a determinant is matched with a set of values Name --> --> Sibling |
|
SQL TOP... |
Used in the SELECT portion to grab the top number of rows SELECT TOP 5 * FROM SKU_DATA; |
|
SQL IS NULL... |
to return any Null values |
|
Entity |
an identifiable thing that users want to track (Sales, Orders, Customers, etc) |
|
Composite determinant |
Where a determinant has more than one attribute (StudentName, ClassName) --> (Grade) StudentName -x-> Grade ClassName -x-> Grade |
|
Inconsistent Values |
when different users data is not standardized DrinkName Coffee Cofee Coffeee |