EXPERIMENT NOT ANY 2
SIMPLE FACT TABLE FOR UNIVERSITY DATABASES
AIM: -Creation of sizing table and fact stand for College or university Database. THEORY: -
In data warehousing, a dimension stand is one of the set of companion tables to a truth table. The fact table includes business specifics or actions and overseas keys which will refer to candidate keys (normally primary keys) in the dimensions tables. Despite fact tables, the aspect tables consist of descriptive qualities (or fields) which are commonly textual domains or under the radar numbers that behave just like text. These attributes are designed to serve two critical functions: query constraining/filtering and query result arranged labeling. Dimension attributes are meant to be:
вЂўVerbose - labels consisting of full words,
вЂўComplete - no lacking values,
вЂўDiscretely valued -- only one benefit per line in dimensional table, вЂўQuality assured - no misspelling, no not possible values.
Dimensions table series are distinctively identified with a single essential field. It is recommended that the key discipline is a simple integer for the reason that key value is definitely meaningless and is also only utilized to be sign up for fields between fact and dimension tables.
In info warehousing, an undeniable fact table includes the measurements, metrics or facts of any business process. It is often located at the center of the star programa or asnowflake schema, between dimension desks. Fact furniture provide the (usually) additive principles that become independent variables by which dimensional attributes are analyzed. Fact tables are often defined simply by theirgrain. The grain of a fact stand represents the most atomic level by which the important points may be defined. The materials of a Boutique Sales truth table may be stated as " Sales volume simply by Day by Product simply by Store". Every record in this fact table is consequently uniquely identified by a day time, product and store. Various other dimensions might be members of this fact stand (such as location/region) but these add not the uniqueness of the truth records. These types of " affiliate dimensions" allow for additional pieces of the impartial facts but generally provide information at a higher level of aggregation (a place contains many stores). A well known fact table may contain possibly detail level facts or facts which were aggregated (fact tables that contain aggregated truth is often instead called synopsis tables). Pre-Requisite: DBMS, RDBMS.
CREATION OF OLTP TABLES:
SQL> CREATE DESK Student53(S_Name VARCHAR2(54), Roll_No NUMBER(10) PRIMARY CRUCIAL, C_Name VARCHAR2(54), Branch VARCHAR2(54)); Table developed.
SQL> PUT IN INTO Student53 VALUES ('Karan', 09, 'ASP. NET', 'IT'); 1 line created.
SQL> CREATE STAND Prof53(P_Name VARCHAR2(54), Prof_Id NUMBER(10) PRIMARY CRUCIAL, Branch VARCHAR2(54)); Table made.
SQL> INSERT INTO Prof53 VALUES ('Sharvari R', 10, 'COMPS' ); SQL> put in into boutadmin values(003, 'Shreya', 'Nerul', 25000); 1 line created.
SQL> CREATE DESK Dept53( Dept_No NUMBER(2) PRIMARY KEY, D_Name VARCHAR2(54), HOD VARCHAR2(54)); Stand created.
SQL> INSERT IN TO Dept53
IDEALS (1, 'IT', 'Satish Varma');
1 line created.
SETUP OF VENTURE DATAMART
CREATION OF DIMENSION FURNITURE
1)Student Sizing table
SQL> CREATE STAND Student53(S_Name VARCHAR2(54), Roll_No NUMBER(10) PRIMARY IMPORTANT, C_Name VARCHAR2(54), Branch VARCHAR2(54)); Table made.
SQL> PLACE INTO Student53 VALUES ('Karan', 09, 'ASP. NET', 'IT'); 1 line created.
2)Professor dimension stand
SQL> MAKE TABLE Prof53(P_Name VARCHAR2(54), Prof_Id NUMBER(10) PRIMARY KEY, Branch VARCHAR2(54)); Table created.
SQL> INSERT INTO Prof53 PRINCIPLES ('Sharvari R', 10, 'COMPS' ); SQL> insert into boutadmin values(003, 'Shreya', 'Nerul', 25000); you row made.
3) Section table
SQL> CREATE DESK Dept53( Dept_No NUMBER(2) MAJOR KEY, D_Name VARCHAR2(54), HOD VARCHAR2(54)); Table created.
SQL> INSERT INTO Dept53 BELIEFS (1, 'IT', 'Satish Varma');
1 row made.