MIDTERM_W1-W2.pptx

School of NATIONAL COLLEGE OF SCIENCE & TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT DATA SCHEMA ICT 026 WEEK 6 Prepared by: CALIX OLAGUER  School of NATIONAL CO...

School of NATIONAL COLLEGE OF SCIENCE & TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT DATA SCHEMA ICT 026 WEEK 6 Prepared by: CALIX OLAGUER  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT SCHEMA Schema is a logical description of the entire database. It includes the name and description of records of all record types including all associated data-items and aggregates  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT STAR SCHEMA Each dimension in a star schema is represented with only one-dimension table. This dimension table contains the set of attributes. The following diagram shows the sales data of a company with respect to the four dimensions, namely time, item, branch, and location. There is a fact table at the center. It contains the keys to each of four dimensions. The fact table also contains the attributes, namely dollars sold and units sold.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT SNOWFLAKE SCHEMA Some dimension tables in the Snowflake schema are normalized. The normalization splits up the data into additional tables. Unlike Star schema, the dimensions table in a snowflake schema are normalized. the item dimension table contains the attributes item_key, item_name, type, brand, and supplier-key. The supplier key is linked to the supplier dimension table. The supplier dimension table contains the attributes supplier_key and supplier_type.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT FACT CONSTELLATION SCHEMA A fact constellation has multiple fact tables. It is also known as galaxy schema. The following diagram shows two fact tables, namely sales and shipping. The sales fact table is same as that in the star schema. The shipping fact table has the five dimensions, namely item_key, time_key, shipper_key, from_location, to_location. The shipping fact table also contains two measures, namely dollars sold and units sold. It is also possible to share dimension tables between fact tables  School of NATIONAL COLLEGE OF SCIENCE & TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT ADVANTAGES OF ICT 026 PARTITIONING STRATEGY WEEK 7 Prepared by: CALIX OLAGUER  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT PARTITIONING STRATEGY Partitioning is done to enhance performance and facilitate easy management of data. Partitioning also helps in balancing the various requirements of the system. It optimizes the hardware performance and simplifies the management of data warehouse by partitioning each fact table into multiple separate partitions. This section contains the following topics: Bigger Databases Bigger Individual Tables: More rows in Tables. More Users Querying the system More Complex Queries  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Bigger Databases The ability to split a large database object into smaller pieces transparently simplifies efficient management of very large databases. Bigger Individual Tables: More Rows in Tables It takes longer to scan a big table than it takes to scan small table. Queries against partitioned tables may access one or more partitions that are small in contrast to the size of the table. More Users Querying the System With partitioning, users are more likely to query on isolated and smaller data sets.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT More Complex Queries If smaller data sets are being accessed, then complex calculations are more likely to be processed in memory, which is beneficial from a performance perspective and reduces the application's I/O requirements. Partitioning for Performance in a Data Warehouse Analyses run against the database should return within a reasonable amount of time, even if the queries access large amounts of data in tables that are terabytes in size.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Partitioning Pruning in a Data Warehouse In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list. THANK YOU! 

Show More Show Less