Session 7 Solution

additional information

What are DML and DDL? Why are they necessary?

DML: Is data manipulation Language, it is used mainly for following user operations:

  • ADD
  • DELETE
  • MODIFY
  • READ (SELECT)

DDL: is data description Language and is used for data description, data type, length of data, who created it etc..

Construct a query using SQL (see page 75) that will give names of all customers from Customer file

SELECT customer name

FROM CUSTOMER;

Ex: if the query was give the names of all customers that live in CHICAGO, query will be:

SELECT cust_name

FROM CUSTOMER

WHERE city = ‘CHICAGO’;

Q1. What is a Database, Data, DBMS and file? Describe some application that use databases. Clearly explain how database is used in those applications.

Data is raw facts, like student name, student address, GPA

Information: data that has been processed, like average GPA, maximum test score etc.

DBMS is collection of integrated data that can be shared by many people

File: is a collection of records

Database: A collection of related data

Q2. What is data warehouse ?

A data warehouse is logical collection of information gathered from many different sources like

Government

Competitors

Internal documents

Q3. Defining Queries for a Video Rental Store. Consider your local video rental store. It certainly has an operational database to support its online transaction processing (OLTP). The operational database supports such things as the adding of new customers, the renting of videos (obviously), the ordering of videos, and a host of other activities. Now, assume that the video rental store also uses that same database for online analytical processing (OLAP) in the form of creating queries to extract meaningful information. Develop CUSTOMER & MOVIE tables with attributes. If you were the manager of the video rental store, what kinds of queries would you build?

CUSTOMER table should have attributes related to customers. For example,

CUSTOMER (customer_name, Cust_address, date_mambership, customer_credit_card)

Movie table should have attributes related to monvies.

MOVIE (movie_name, no_of_copies, date_released, total_copies_available)

as a manager, you may be interested in knowing the following

.

  • List of repeat customers
  • Customers with consistent late charges.
  • Videos that haven’t rented in a given period of time.
  • “New release” movies that have experienced a decline in rentals (it may be time to move them off the premier shelves).
  • Movies with several copies that do not consistently have multiple copies rented out (it may be time to sell some of them).

The information in data warehouse is used to create business opportunities. It is used in executive decision making and creating business intelligence/

Q4. Visit the Internet and define what is BI? How can data be manipulated to create BI?

Business intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information and sometimes to the information itself. The purpose of business intelligence--a term that dates at least back to 1958--is to support better business decision making. source

Data in warehouse can be manipulated using statistical, mathematical and inference tools to extract patterns, relations and behaviors to create BI which can be used for business purposes.

For example, A BI analysis may show during Super bowl, pamper sales go up!!!! While husbands (or wives) are watching super bowl, their spouses go for shopping. It may make sense to

Have extra diapers in the store during super bowl. Or a BI may show that people living near schools with income between 20,000 to 50,000

have two or more kids buy our product. A company may look for such demographics in other part of the city to expand their business..