Fact Table for University Database

Only available on StudyMode
  • Topic: Data warehouse, Top-down and bottom-up design, Fact table
  • Pages : 9 (1672 words )
  • Download(s) : 121
  • Published : January 22, 2013
Open Document
Text Preview
EXPERIMENT NO 2
FACT TABLE FOR UNIVERSITY DATABASE

AIM:-Creation of dimension table and fact table for University Database. THEORY:-
DIMENSION TABLE
In data warehousing, a dimension table is one of the set of companion tables to a fact table. The fact table contains business facts or measures and foreign keys which refer to candidate keys (normally primary keys) in the dimension tables. Contrary to fact tables, the dimension tables contain descriptive attributes (or fields) which are typically textual fields or discrete numbers that behave like text. These attributes are designed to serve two critical purposes: query constraining/filtering and query result set labeling. Dimension attributes are supposed to be:

Verbose - labels consisting of full words,
Descriptive,
Complete - no missing values,
Discretely valued - only one value per row in dimensional table, •Quality assured - no misspelling, no impossible values.
Dimension table rows are uniquely identified by a single key field. It is recommended that the key field is a simple integer for the reason that key value is meaningless and is only used to be join fields between the fact and dimension tables.

FACT TABLE
In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the center of a star schema or asnowflake schema, surrounded by dimension tables. Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by theirgrain. The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a Boutique Sales fact table might be stated as "Sales volume by Day by Product by Store". Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (a region contains many stores). A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables). Pre-Requisite: DBMS, RDBMS.

CREATION OF OLTP TABLES:
1)Student table
SQL>CREATE TABLE Student53(S_Name VARCHAR2(54) ,Roll_No NUMBER(10) PRIMARY KEY,C_Name VARCHAR2(54),Branch VARCHAR2(54)); Table created.
SQL>INSERT INTO Student53 VALUES ('Karan', 09, 'ASP.NET', 'IT'); 1 row created.

2)Professor table
SQL>CREATE TABLE Prof53(P_Name VARCHAR2(54) ,Prof_Id NUMBER(10) PRIMARY KEY,Branch VARCHAR2(54)); Table created.

SQL>INSERT INTO Prof53 VALUES ('Sharvari R', 10, 'COMPS' );SQL> insert into boutadmin values(003,'Shreya','Nerul',25000); 1 row created.

3)Department table
SQL> CREATE TABLE Dept53( Dept_No NUMBER(2) PRIMARY KEY ,D_Name VARCHAR2(54),HOD VARCHAR2(54)); Table created.

SQL> INSERT INTO Dept53
VALUES (1, 'IT', 'Satish Varma');
1 row created.

IMPLEMENTATION OF ENTERPRISE DATAMART
CREATION OF DIMENSION TABLES
1)Student Dimension table
SQL>CREATE TABLE Student53(S_Name VARCHAR2(54) ,Roll_No NUMBER(10) PRIMARY KEY,C_Name VARCHAR2(54),Branch VARCHAR2(54)); Table created.
SQL>INSERT INTO Student53 VALUES ('Karan', 09, 'ASP.NET', 'IT'); 1 row created.

2)Professor dimension table
SQL>CREATE TABLE Prof53(P_Name VARCHAR2(54) ,Prof_Id NUMBER(10) PRIMARY KEY,Branch VARCHAR2(54)); Table created.

SQL>INSERT INTO Prof53 VALUES ('Sharvari R', 10, 'COMPS' );SQL> insert into boutadmin values(003,'Shreya','Nerul',25000); 1 row created.
3) Department table
SQL> CREATE TABLE Dept53( Dept_No NUMBER(2) PRIMARY KEY ,D_Name VARCHAR2(54),HOD VARCHAR2(54)); Table created.
SQL> INSERT INTO Dept53 VALUES (1, 'IT', 'Satish Varma');
1 row created.

4)Course...
tracking img