Term
|
Definition
Relational Database Management
*It is a collection of Tables
*Each entity is stored in its own table |
|
|
Term
|
Definition
| The fields (columns)in a table |
|
|
Term
|
Definition
| Common columns in two or more tables (example= primary key of parent is used as a foreign key in child) |
|
|
Term
|
Definition
They are two dimensional tables where:
*Entries are single valued
*Each column (field/ attribute) has a distinct name
*All values in a column represent the same attribute domain
*Each row (record or tuple) is distinct
*Order of rows is immaterial |
|
|
Term
|
Definition
Query=view: questions represented in a visual way that the DBMS can recognize and process. It is the result af an action. Data only exists when query is run.
QBE:
Visual or graphic approach to writing queries
Used in Access |
|
|
Term
|
Definition
*Update
*Delete
*Append/ Insert
*Make-Table (CREATE): ETL functionality |
|
|
Term
|
Definition
| Extract, Transform, and Load New Table |
|
|
Term
What are the 8 Basic Command Descriptions in Relational Algebra?
(performed by SELECT function) |
|
Definition
* PROJECTION: Select the columns or attributes (distinct)
*SELCTION: Selects rows (where condition)
*CARTESIAN PRODUCT
*EQUI-JOIN
*UNION
*INTERSECTION
*DIFFERENCE
*DIVISION |
|
|
Term
|
Definition
Concatenates every tuple of one relation to every tuple of the second relation (MxN rows) Uses the * symbol in code.
Example: SELECT*FROM Orders, Part
all columns will be selected |
|
|
Term
| JOIN (Equi and Natural-Joins) |
|
Definition
The product of two or more Relations based on keys with certian tuples removed
Example: FROM ParentTable, ChildTable
WHERE PrimaryKeyParent=ForeignKeyChild |
|
|
Term
|
Definition
With this a query with no duplicate data is created. Very important in datawarehousing
*Combines two relations into a third
*Must be UNION compatible: same numbers and type of corresponding attributes |
|
|
Term
|
Definition
| a tuple is one record (one row). |
|
|
Term
|
Definition
A division process: C=A/B
Result contains "quotient" |
|
|
Term
|
Definition
| Extract, Transform, and Load |
|
|
Term
|
Definition
Entity Relationship Diagram
This is a relational database |
|
|
Term
| What is a Data Warehouse (DW)? |
|
Definition
A collection of integrated subject-oriented databases designed to support the DSS function where each unit of data is relevant to some moment in time. The Data Warehouse contains atomic data and lightly summarized data.
OR
A subject oriented, integrated, time-variant, nonvolatile collection of data in support of managements decision making process |
|
|
Term
|
Definition
| A Person, Place, or Thing, or Event |
|
|
Term
|
Definition
A property of an Entity
Example: Customer has a name, street, city etc. |
|
|
Term
| What are the SQL Commands? |
|
Definition
* Data Definition Language (DDL)
* Data Manipulation Language (DML)
* Data Control Language (DCL |
|
|
Term
| DDL (Data Definition Language) |
|
Definition
CREATE[obj], ALTER[obj], DROP[obj]
*[obj]: signifies an optimal operator
*{obj}: signifies a non-optimal operator
*obj can = table, index, synonym etc. |
|
|
Term
| DML (Data Manipulation Language) |
|
Definition
*INSERT (changes whole row), UPDATE, DELETE, SELECT (IUDS)
These commands are used more often |
|
|
Term
| DCL (Data Control Language) |
|
Definition
| *GRANT, REVOKE, COMMIT( multiple changes in the same process cycle not saved until "committed") |
|
|
Term
| What are SQL Aggregate Functions? |
|
Definition
They are a subset of Built-in Column Functions.
Example: Sum, Avg, Min, Max etc. |
|
|
Term
|
Definition
|
|
Term
|
Definition
Conceptually similar to a Book Index:
Sorts by key field
Pointers to location (inverted table)
SQL to create indext: CREATE INDEX CName_IDX
ON Customer (CustomerName); |
|
|
Term
|
Definition
| They are related to foreign keys and primary keys |
|
|
Term
| What is Entity Integrity? |
|
Definition
| no field that is part of the primary key may accept null values |
|
|
Term
| What is Referential Integrity? |
|
Definition
| If enforced, no orphans. If table A contains a foreign key matching the primary key of table B, then the values must match for some row in Table B or be null. |
|
|
Term
| What is 1st Normal Form (1NF)? |
|
Definition
*All Rows are unique
*Tables contian no repeating rows!
An un-normalized table contains a repeating group and all cells contain only one value |
|
|
Term
| What is 2nd Normal Form (2NF)? |
|
Definition
There are partial dependecies. It happens when:
* in 1NF and every non-key attribute is dependent on only a portion of the primary key
OR
*In 1NF and every non-key attribute is dependent on the entire primary key
example: 1NF tables with a single primary key are in 2NF |
|
|
Term
| What is 3rd Normal Form (3NF)? |
|
Definition
| It is when the table ins in 2NF and the only determinants contained are candidate keys. There are no transitive dependencies/ multivalued dependencies. |
|
|
Term
| What is 4th Normal Form (4NF)? |
|
Definition
| When table is in 3NF and has no multi-valued dependencies. A single PK determines multiple copies of the same data value. |
|
|
