Designing a Data Warehouse and a Data Mart Design a data warehouse schema for the medium-sized business application used in Assignments 4, 5, and 6. A full history of changes for every entity (table)...

Designing a Data Warehouse and a Data Mart

• Design a data warehouse schema for the medium-sized business application used in Assignments 4, 5, and 6. A full history of changes for every entity (table) is required as described in the Topic 8.1 Lecture Most data warehouses follow Summary Table architecture using history tables to track change history, but you may follow Star Schema architecture using Type 2 dimensions is you wish.


• Design a data mart (star) schema for your medium-sized business data warehouse schema. All dimensions must be Type 2, meaning a full history of changes is required as described in the Topic 8.1 Lecture under the heading "Handling History in Star Schema Data Warehouses.

Assignment_4_Instructor_Solution_with_generalized_hierarchy Instructor's Solution to Assignment 5 Organization and Individual Collapsed into Party Notation: IEn,m: Non-unique Index AKn,m: Unique (Alternate Key) Index n = index sequence number m = column sequence number CONTACT_MECHANISM_USAGE PARTY_ID CONTACT_MECHANISM_ID INTEGER INTEGER NOT NULL NOT NULL (FK) (FK) (IE1.1) CONTACT_USAGE_TYPE_CODE VARCHAR(5) NULL (FK) CONTACT_USAGE_TYPE CONTACT_USAGE_TYPE_CODE VARCHAR(5) NOT NULL CONTACT_USAGE_TYPE_DESCRIPTION VARCHAR(100) NOT NULL CONTACT_MECHANISM CONTACT_MECHANISM_ID INTEGER NOT NULL CONTACT_MECHANISM_TYPE_CODE CHAR(2) NOT NULL DEPARTMENT DEPARTMENT_ID INTEGER NOT NULL DEPARTMENT_NAME DIVISION_ID DEPARTMENT_MANAGER_EMPLOYEE_ID VARCHAR(100) INTEGER INTEGER NOT NULL NOT NULL NULL (FK) (FK) (AK1.1) (IE1.1) DIVISION DIVISION_ID INTEGER NOT NULL DIVISION_NAME DIVISION_MANAGER_EMPLOYEE_ID VARCHAR(100) INTEGER NOT NULL NULL (FK) (AK1.1) E_MAIL CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) E_MAIL_ADDRESS VARCHAR(255) NOT NULL EMPLOYEE_WORK_GROUP_ASSIGNMENT EMPLOYEE_ID WORK_GROUP_ID WORK_GROUP_ASSIGNMENT_EFFECTIVE_DATE INTEGER INTEGER DATE NOT NULL NOT NULL NOT NULL (FK) (FK) (IE1.1) WORK_GROUP_ASSIGNMENT_END_DATE DATE NULL EMPLOYMENT EMPLOYEE_ID INTEGER NOT NULL PAY_GRADE_CODE PARTY_ROLE_ASSIGNMENT_ID VARCHAR(20) INTEGER NOT NULL NOT NULL (FK) (FK) (AK1.1) ITEM ITEM_ID INTEGER NOT NULL ITEM_DESCRIPTION ITEM_LIST_PRICE VARCHAR(255) DECIMAL(7,2) NOT NULL NOT NULL PARTY PARTY_ID INTEGER NOT NULL PARTY_TYPE_CODE ORGANIZATION_NAME GIVEN_NAME MIDDLE_NAME FAMILY_NAME BIRTH_DATE CHAR(2) VARCHAR(100) VARCHAR(40) VARCHAR(40) VARCHAR(40) DATE NOT NULL NULL NULL NULL NULL NULL PARTY_ROLE_ASSIGNMENT PARTY_ROLE_ASSIGNMENT_ID INTEGER NOT NULL PARTY_ID PARTY_ROLE_TYPE_CODE ROLE_EFFECTIVE_DATE ROLE_TERMINATION_DATE INTEGER CHAR(3) CHAR(18) CHAR(18) NOT NULL NOT NULL NULL NULL (FK) (FK) (AK1.1) (AK1.3) (AK1.2) PARTY_ROLE_TYPE PARTY_ROLE_TYPE_CODE CHAR(3) NOT NULL PARTY_ROLE_TYPE_NAME VARCHAR(100) NOT NULL PAY_GRADE PAY_GRADE_CODE VARCHAR(20) NOT NULL PAY_GRADE_NAME VARCHAR(100) NOT NULL (AK1.1) POSTAL_ADDRESS CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) ADDRESS_LINE_1_TEXT ADDRESS_LINE_2_TEXT CITY_NAME STATE_PROVINCE_CODE POSTAL_CODE COUNTRY_CODE VARCHAR(255) VARCHAR(255) VARCHAR(128) CHAR(2) VARCHAR(20) CHAR(2) NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL PURCHASE_ORDER PURCHASE_ORDER_NUMBER INTEGER NOT NULL PARTY_ROLE_ASSIGNMENT_ID ORDER_DATE ORDER_STATUS_CODE INTEGER DATE CHAR(2) NULL NOT NULL NOT NULL (FK) (IE1.1) PURCHASE_ORDER_ITEM PURCHASE_ORDER_NUMBER ITEM_ID INTEGER INTEGER NOT NULL NOT NULL (FK) (FK) (IE1.1) ITEM_PURCHASE_UNIT_PRICE ITEM_PURCHASE_QUANTITY DECIMAL(7,2) INTEGER NOT NULL NOT NULL TELEPHONE CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) AREA_CODE PHONE_NUMBER PHONE_EXTENSION_NUMBER DEVICE_TYPE_CODE CHAR(3) VARCHAR(20) VARCHAR(9) CHAR(2) NULL NOT NULL NULL NOT NULL (IE1.2) (IE1.1) WORK_GROUP WORK_GROUP_ID INTEGER NOT NULL WORK_GROUP_NAME EMPLOYEE_ID DEPARTMENT_ID VARCHAR(100) INTEGER INTEGER NOT NULL NULL NOT NULL (FK) (FK) (AK1.1) (IE2.1) (IE1.1)