Bahria University

Lahore Campus

Assignment # 1
Name: Aqsa Gulzar

Enrollment No: 03-134171-005

Program: BSCS(7A)

Semester: 7

Course title : Data Warehouse

Instructor Name: Sir Junaid

Date assigned: 06/03/2020

Date of submission: 24/04/2020

Q#1: Write down the drawbacks of the earlier existing decision support systems.
Ans:
Disadvantages of Decision Support Systems

 Information Overload: A computerized decision-making system may sometimes

result in information overload. Since it analyses all aspects of a problem, it leaves a
user in a dilemma what to consider and what not to consider. Not each bite of
information is necessary in decision making. But when it’s present, a decision maker
finds it difficult to ignore information that is not a priority.
 Too much Dependence on DSS: It is true that decision support systems are
integrated into businesses to make everyday decisions faster and more easily. Some
decision makers develop a tendency to depend too much on computerized decision
making and don’t want to apply their own brains. Clearly, there is a shift in focus and
decision makers may not hone their skills further because of excessive dependence on
DSS.
 Devaluation of Subjectivity: A decision support system promotes rational decision
making by suggesting alternatives basis the objectivity. While bounded rationality or
restricted irrationality plays a critical role in decision making, subjectivity cannot and
should not be rejected. A DSS promotes objectivity and relegates subjectivity, which
can have serious impact on a business.
 Overemphasis on Decision Making: Clearly the focus of computerized decision
making is on considering all aspects of a problem all the time, which may not be
required in many of the situations. It is essentially important to train the users to
ensure effective and optimal use of DSS.
 Cost of Development: The cost of decision-making decreases once a decision
support system is installed. But development and implementation of a DSS requires a
huge monetary investment. Customization may attract higher cost. If you’re on a tight
budget, you might not get a customized DSS specific to your needs.

Q#2: Justify that data warehouse is an environment not a product

Ans: Data warehouse an Environment, not a Product
A data warehouse is not a single software or hardware product you purchase to provide
strategic information. It is, rather, a computing environment where users can find strategic
information, an environment where users are put directly in touch with the data, they need to
make better decisions. It is a user-centric environment. The characteristics of this new
computing environment called the data warehouse:
 An ideal environment for data analysis and decision support
 Fluid, flexible, and interactive
 100 percent user-driven
 Very responsive and conducive to the ask–answer–ask–again pattern
 Provides the ability to discover answers to complex, unpredictable questions
A Blend of Many Technologies Let us re-examine the basic concept of data warehousing.
The basic concept of data warehousing is:
 Take all the data from the operational systems
  Where necessary, include relevant data from outside, such as industry benchmark
indicators Integrate all the data from the various sources
 Remove inconsistencies and transform the data
 Store the data in formats suitable for easy access for decision making Although a
simple concept, it involves different functions: data extraction, the function of loading
the data, transforming the data, storing the data, and providing user interfaces.

Q#3: Explain data warehouse Architecture in detail.

Ans: A data warehouse is the source of business truth developed by combining data from
multiple sources. It supports analytical reporting, and both structured and ad hoc queries.
Data warehousing systems, like home designs, have many different architectural options.
Some have Operational Data Stores (ODS), others are deployed with data marts. Some data
warehouse may reference finite set of source data, or as with most enterprise data
warehouses, reference a variety of internal and external data sources. Because constructing a
data warehouse is unique to the business use, we will look at the common layers found in all
data warehouse architecture. All data warehouse architecture includes the following layers:
 Data Source Layer
The first layer is the data source layer. As we know data warehouse is a collection of
data from different data sources. All the data sources from which the data is extracted
comes from data source layer. The data can be extracted from various layers for
example operational systems such as sales, HR, inventory, marketing and systems
data.
 Staging Layer
Data from ERP system and CRM systems extracted from data source layer into an
staging area using ETL tools. ETL stands for extract transform load once the data is
extracted into staging area then data is load into data warehouse.
 Data warehouse
Before loading data, the extracted data is transformed according to user requirement.
Basically, there are three things Meta data, summary data and raw data. Meta data is
basically the data about data, it is most important aspect about data warehouse.
Summary data to get information we load it using ETL tools. Raw data for discussion
makers to make discussion.
 Presentation layer
All the data came into warehouse used for analysis and reporting to make discussion.

Q#4: What is metadata? What are its different types?

Ans: Meta Data
Meta data is a data that provides information about the other data. In other words it is data
about data.
There are different types of metadata
i. Structural metadata
ii. Administrative metadata
iii. Descriptive metadata
1. Structural metadata
Structural metadata is data that indicates how a digital asset is organized like how page
numbers of books and chapters of books are organized. It also indicates a particular
asset is part of single or multiple collections. Examples include:
 Page numbers
 Sections
 Chapters
2. Administrative Metadata
Administrative metadata relates to a technical source of a digital asset. It contains data
such as file type as well as when and how the asset was created.
For example, the date a digital asset was created and for photos, might include the
camera model used to take the photo, light source, and resolution.
3. Descriptive metadata
Descriptive metadata describes a resource for the purposes such as discover or the
identifications.
It includes elements such as tittle abstracts or keywords.

Q#5: What are the practical applications of data warehouse?

Ans: Applications of a Data Warehouse:
Data warehouses have deeply rooted applications in every industry which uses structured and
unstructured data from disparate sources for forecasting, analytical reporting, and business
intelligence, allowing for robust decision-making. Here are some major applications of data
warehouses across different industries:

1. Banking
 Identify the potential risk of default and manage and control collections.
 Performance analysis of each product, service, interchange, and exchange rates.
 Track performance of accounts and user data.
 Provide feedback to bankers regarding customer relationships and profitability.
2. Finance
 Evaluation of customer expenses trends.
 Maintain transparency in transactions.
 Predict/spot defaulters and act accordingly.
 Analyze and forecast different aspects of business, stock, and bond performance.
3. Government
 Maintain and analyze tax records, health policy records, and their respective
providers
 Prediction of criminal activities from patterns and trends
  Searching terrorist profile
 Threat assessment and fraud detection
4. Education
 Store and analyze information about faculty and students
 Maintain student portals to facilitate student activities
 Extract information for research grants and assess student demographics
 Integrate information from different sources into a single repository for analysis
and strategic decision-making
5. Healthcare
 Generate patient, employee, and financial records
 Share data with other entities, like insurance companies, NGOs, and medical aid
services
 Use data mining to identify patient trends
 Provide feedback to physicians on procedures and tests
6. Manufacturing
 Predict market changes and analyze current business trends
 Analyze previous and current market data
 Track customer feedback and identify opportunities for improvement
 Gather, standardize, and store data from various internal and external sources
 Identify profitable product lines and required product features
7. Retail
 Maintain records of producers and consumers
 Track items, their promotion strategies, and consumer buying trends (trend
analysis)
 Analyze sales to determine shelf space
 Understanding the patterns of complaints, claims, and returns
8. Services
 Maintenance of financial and employee records
 Customer profiling and screening
 Resource allocation and management
 Revenue patterns and profitability
9. Manufacturing
 Predict market changes and analyze current business trends
 Analyze previous and current market data
 Track customer feedback and identify opportunities for improvement
 Gather, standardize, and store data from various internal and external sources
 Identify profitable product lines and required product features

Q#6: Differentiate between following:

a) OlAP Vs. Data Mining
b) Data Warehouse Vs. Data marts
c) Operational Vs. Informational System
Ans:
a) OLAP Vs. Data Mining:
  OLAP is a design paradigm, a way to seek information out of the physical data
store. OLAP is all about summation. It aggregates information from multiple
systems and stores it in a multi-dimensional format. These could be a star schema,
snowflake schema or a hybrid kind of a schema.
 Data mines leverage information within and without the organization to aid in
answering business questions. They involve ratios and algorithms like decision
trees, nearest neighbor classification and mural networks, along with clustering of
data.
b) Data Warehouse Vs. Data marts:
 A Data Warehouse is a large repository of data collected from different
organizations or departments within a corporation. It helps to take a strategic
decision. The main objective of Data Warehouse is to provide an integrated
environment and coherent picture of the business at a point in time.
 A data mart is an only subtype of a Data Warehouse. It is designed to meet the
need of a certain user group. It helps to take tactical decisions for the business. A
data mart mostly used in a business division at the department level.
c) Operational Vs. Informational System:
 Operational data is the data you use to run your business. This data is what is
typically stored, retrieved, and updated by your Online Transactional Processing
(OLTP) system.
 Informational data is typically stored in a format that makes analysis much
easier. It is designed for informational purposes and analysis. Analysis can be in
the form of decision support queries, report generation, executive information
systems, and more in-depth statistical analysis.

Q#7: Explain the knowledge discovery phases. What do you mean by the word Prediction?
Ans: Knowledge Discovery Process (KDP)
Data mining is the core part of the knowledge discovery process.
KDP is a process of finding knowledge in data, it does this by using data mining methods
(algorithms) in order to extract demanding knowledge from large amount of data.
Knowledge Discovery Process may consist of the following steps :-
1. Data cleaning:
First step in the Knowledge Discovery Process is Data cleaning in which noise and
inconsistent data is removed
2. Data Integration:
Second step is Data Integration in which multiple data sources are combined.
3. Data Selection:
Next step is Data Selection in which data relevant to the analysis task are retrieved
from the database.
4. Data Transformation:
In Data Transformation, data are transformed into forms appropriate for mining by
performing summary or aggregation operations.
5. Data Mining:
 In Data Mining, data mining methods (algorithms) are applied in order to extract data
patterns.
6. Pattern Evaluation:
In Pattern Evaluation, data patterns are identified based on some interesting measures.
7. Knowledge Presentation:
In Knowledge Presentation, knowledge is represented to user using many knowledge
representation techniques.

Prediction:
 Prediction is nothing but finding out the knowledge or some pattern from the large
amounts of data.
 For example, In credit card fraud detection, history of data for a particular person’s
credit card usage has to be analyzed . If any abnormal pattern was detected, then it
should be reported as ‘fraudulent action’.

Q#8: Discuss the significance of metadata.

Ans: Significance of metadata:
 Metadata is an effective mean of organizing electronic resources. Metadata means of
organizing electronic resources, which is an important use given the growth in Web-
based resources. For Web purposes, the information can be extracted and reformatted
through use of software tools.
 Another use of metadata is as a means of facilitating interoperability and
integrating resources. Using metadata to describe resources enables its
understanding by humans as well as machines.
 Metadata also facilitates digital identification via standard numbers that uniquely
identify the resource the metadata defines. Along these lines, another practice is to
combine metadata so that it acts as a set of identifying data that differentiate objects
or resources, supporting validation needs.
 Metadata an important way to protect resources and their future accessibility. It’s a
critical concern given the fragility of digital information and its susceptibility to
corruption or alteration. For archiving and preservation purposes, it takes metadata
elements that track the object’s lineage, and describe its physical characteristics and
behavior so it can be replicated on technologies in the future.

Q#9: Data warehousing is the only viable means to resolve the information crisis and to
provide strategic information. List five reasons to support this assertion and explain them.

Ans: Data warehouse is only viable solution because of the following reasons.
 It provides an integrated view of enterprise.
 It makes decision support transaction possible without disturbing Operating systems.
 It renders the organization information consistent.
 Presents an interactive source of strategic information which makes the historical
information easily available for strategic decision making.

Q#10: Is OLTP database design optimal for Data Warehouse? 

Ans: No. OLTP database is not designed optimal for data warehouse because:
 OLTP tables are normalized and it will consume additional time to queries to get
results.
 OLTP database is smaller and doesn’t contain data for years or so which needs to be
analyzed.
 OLTP system is an ER model not a dimensional Model.
 OLTP system can’t tolerate heavy overload of complex quires.

Q#11: Why data warehouse and transaction databases need to be different. 

Ans: Transaction Database Vs Datawarehouse Database
 Transaction Database is Relational Database with the normalized table, whereas
Datawarehouse is with demoralized Table.
 Transaction Database is highly volatile Designed to maintain transaction of the
business Where Data Warehouse is nonvolatile with periodic updates.
 Transaction Database is OLTP and Datawarehouse is for analysis.
 Transaction Database is functional data and database Data is subject oriented.

Q#12: Explain the steps of Normalization with the help of any example and perform third
normal form and try to de-normalize it.
Ans: Normalization
Normalization is the transformation of complex user views and data stores to a set of smaller, stable
data structures. In addition to being simpler and more stable, normalized data structures are more
easily maintained than other data structures.

The Three Steps of Normalization

Each step involves an important procedure, one that simplifies the data structure.
1. The first stage of the process includes removing all repeating groups and identifying
the primary key.
2. The second step ensures that all non-key attributes are fully dependent on the primary
key. All partial dependencies are removed and placed in another relation.
3. The third step removes any transitive dependencies. A transitive dependency is one in
which non-key attributes are dependent on other non-key attributes.

3NF: Eliminate Transitive Dependency

An entity is in the third normal form (3NF) if it is in the second normal form and all of its
attributes are not transitively dependent on the complete primary key. Transitive dependency
exists when a non-prime attribute depends on other non-prime attributes rather than In
depending upon the prime attributes or primary key. In other words: The third normal form
means that no attribute within an entity is dependent on a non-prime attribute that, in turn,
depends on the primary key.

For example:
|-----Primary Key----|
Course | Semester | #Places | TeacherID | TeacherName
|---------------------------------------------------------------|
IT101 | 2009-1 | 100 | 332 | Mr Jones |
IT101 | 2009-2 | 100 | 332 | Mr Jones |
IT102 | 2009-1 | 200 | 495 | Mr Bentley |
IT102 | 2010-1 | 150 | 332 | Mr Jones |
IT103 | 2009-2 | 120 | 242 | Mrs Smith |
Now it should be obvious that TeacherName is dependent on TeacherID - so this is not in
3NF. To fix this, we do much the same as we did in 2NF - take TeacherName out of this
table, and put it in its own, which has TeacherID as the key.
Primary Key |
TeacherID | TeacherName |
---------------------------|
332 | Mr Jones |
495 | Mr Bentley |
242 | Mrs Smith |
No redundancy.
One important thing to remember is that if something is not in 1NF, it is not in 2NF or 3NF
either. So each additional Normal Form requires everything that the lower ones had, plus
some extra conditions, which must all be fulfilled.