Term
|
Definition
are equivalent to dimension keys of a fact, with the exception that there is no other dimension information associated with a degenerate key. Degenerate keys are used in data analysis processes to group facts together.
|
|
|
Term
|
Definition
| is a dimension that is stored in the fact table rather than the dimension table. It eliminates the need to join to a Dimension table. |
|
|
Term
fact attributes
Non-additive |
|
Definition
- – facts cannot be aggregated across any dimension (degenerate)
|
|
|
Term
|
Definition
| facts (or measures) can be summed, averaged (aggregated) across all dimensions |
|
|
Term
Fact attributes
Semi-additive or Snap shot fact |
|
Definition
Inventories can be averaged but not summed. Can be aggregated across some, but not all dimensions. How can you tell the difference between a snap-shot star schema |
|
|
Term
Fact attributes
Factless fact table |
|
Definition
| contains no measures than can be aggregated |
|
|
Term
|
Definition
Conformed dimensions are those that are used in multiple star schemas. The dimensions are referenced by the same name and the attributes are also referenced by the same name. |
|
|
Term
slowly changing dimensions
Overwrite the value |
|
Definition
|
|
Term
Slowly changing dimensions
Add a dimension column
|
|
Definition
|
|
Term
Slowly changing dimensions
Add a dimension row |
|
Definition
|
|
Term
|
Definition
| Dimensions are often recycled for multiple applications within the same database. For instance, a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire". This is often referred to as a "role-playing dimension". |
|
|
Term
|
Definition
| a Primary Key that is part of the composite key of the fact table, good for very large rapidly changing dimension tables |
|
|
Term
|
Definition
| Primary Key is a foreign key in the related dimension table, not the fact table |
|
|
Term
Know the SQL (DDL) to Create tables and apply constraints |
|
Definition
CREATE TABLE course
(courseNo VARCHAR(10),
title VARCHAR(20),
Instructor VARCHAR(20),
CONSTRAINT Course_PK PRIMARY KEY (CourseNo),
CONSTRAINT course_FK1 FOREIGN KEY (Instructor) RFERENCES Instructor(Instructor name)
) go |
|
|
Term
Know the SQL (DDL) to Create sequences |
|
Definition
create sequence sessiondateSeq start with 1 increment by 1 nomaxvalue
go |
|
|
Term
Know the SQL for INSERTing records in a table |
|
Definition
INSERT INTO teachingassistant table VALUES
(030, 3490928, 0303) go |
|
|
Term
Know the SQL to INSERT data from a SELECT statement |
|
Definition
INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA' |
|
|
Term
|
Definition
| is better for higher cardinality attribute values and is very fast. On the downside, though, it uses more memory |
|
|
Term
|
Definition
| best for low cardinality attributes, where possible answers are few (boolean is best). |
|
|
Term
AGGREGATES
Fact table aggregates |
|
Definition
| Aggregates base-level fact data across one or many dimensions |
|
|
Term
AGGREGATES
Dimension table aggregates
|
|
Definition
| Summarizes dimension data along a single attribute such as CITY or STATE. |
|
|
