BE IT CGPA DMDW Assignment I Sem I 2017-18

MGM’s College of Engineering, Nanded.
Department of IT
Semester I (2017-18)
Class: BE(IT)       Subject: DMDW         Assignment I

1. Enlist and explain characteristics of Data warehouse.

2. Define metadata. What are the contents of metadata in DW? Enlist and explain.

3. Draw the block diagram of DW architecture. Explain various components of DW architecture.

4. Enlist and explain the OLAP operations with appropriate example.

5. A data cube is having sales of different cities of India, and it was decided to find the total sales of each state, which OLAP operation is useful and why?

6. A data cube is having total marks of students, which OLAP operations can be used to find marks of each subject? Why?

7. Compare snowflake and fact constellation schema with appropriate example.

8. Suppose a DW consists of four dimensions  Time, Product, Branch, Manager and one measure Total_Sales. Draw a star and snowflake schema.

9. Write SQL statements to create the above star schema (Q No. 8).

10. Design a data warehouse for a regional weather bureau. The weather bureau has about 1000 probes, which are scattered throughout various land and ocean locations in the region to collect basic weather data, including air pressure, temperature, and precipitation at each hour. All data are sent to the central station, which has collected such data for more than 10 years. Your design should facilitate efficient querying and online analytical processing, and derive general weather patterns in multidimensional space.

11. Explain starnet query model with an appropriate example.

12. Describe the steps involved in data mining when viewed as a process of knowledge discovery.

13. Suppose your task as a software engineer at Big-University is to design a data mining system to examine

their university course database, which contains the following information: the name, address, and status

(e.g., undergraduate or graduate) of each student, the courses taken, and their cumulative grade point average (GPA). Describe the architecture you would choose. What is the purpose of each component of this

architecture?              

14. You design a data mining software system for an enterprise, how you evaluate the performance of your data mining software.

15. Create a bitmap index for the following table.

TID	City	Type	Sales
T1	Pune	Retail	10000
T2	Mumbai	Dealer	>50000
T3	Nagpur	Dealer	>50000
T5	Pune	Dealer	10000
T6	Mumbai	Retail	>100000

16. How is a data warehouse different from a database? How are they similar?

Faculty Incharge: Hashmi S A

TEIT CGPA DBMS Assignment I SEM I 2017-18

MGM’s College of Engineering, Nanded.
Department of IT
Semester I (2017-18)
Class: TE(IT)       Subject: DBMS         Assignment I

1. List four significant differences between a file-processing system and a DBMS.

2. Explain the concept of physical data independence, and its importance in database systems.

3. Describe at least 3 tables that might be used to store information in a social-networking system such as Facebook.

4. Explain the distinctions among the terms primary key, candidate key, and superkey.

5. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.

6. Design a database for an airline. The database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of future flights. Your design should include an E-R diagram, a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints.

7. Design a database for a world-wide package delivery company (e.g., DHL or FedEX). The database must be able to keep track of customers (who ship items) and customers (who receive items); some customers may do both. Each package must be identifiable and trackable, so the database must be able to store the location of the package and its history of locations.

Locations include trucks, planes, airports, and warehouses. Your design should include an E-R diagram, a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints.

8. Explain various operations of relational algebra.

9. Consider the following relational database:

employee (person name, street, city)

works (person name, company name, salary)

company (company name, city)

Give an expression in the relational algebra to express each of the following queries:

a. Find the names of all employees who live in city “Miami”.

b. Find the names of all employees whose salary is greater than $100,000.

c. Find the names of all employees who live in “Miami” and whose salary is greater than $100,000.

10. Consider the following database:

employee (personname, street, city )

works (personname, company name, salary)

company (companyname, city)

manages (personname, managername)

Give an expression in the relational algebra to express each of the following queries:

a. Find the names of all employees who work for “First Bank Corporation”.

b. Find the names, street addresses, and cities of residence of all employees who work for “First Bank Corporation” and earn more than $10,000.

c. Find the names of all employees in this database who live in the same city as the company for which they work.

11. For the above database (QNo. 10), write the expression in tuple calculus.

12. What is data type? What are the various data types supported by standard SQL?

13. What is the use of constraints? What are the different types of constraints that can be specified? Explain with examples.

14. Write SQL statements for queries in QNo 9 (a), (b), (c).

15. Write SQL statements for queries in QNo 10 (a), (b). (c).

16.Consider the following table called Persons

Pid	Last name	Firstname	Address	city
1	Hansen	Ola	Timetoeivn-10	Sandnes
2	Svendson	Tove	Brazil-50	Sandnes
3	Petterson	Kari	Storgt-20	Stavanger
4	Joseph	ole	Brazil-20	Sandnes

a. Write a query to select the persons with first name ‘Tove’ and last name ‘Svendson’?

b. Write a query to select the persons living in city that starts with S?

c. Write a query to select persons living in a city that contains the pattern ‘tav’?

Faculty Incharge: Hashmi S A