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

Assignment II Class: BE(IT) Subject: MC Semester II (2016-17)

MGM’s College of Engineering, Nanded.
Department of IT
Semester II (2016-17)
Class: BE(IT)       Subject: MC         Assignment II
___________________________________________________________________

1. How the hidden terminal problem is solved in IEEE 802.11?
2. Draw and explain IEEE 802.11 MAC packet structure. A receiver receives the following first 10 bits in a packet:

           0001101101

What are the contents of this packet?

3. What are the functions of 802.11 MAC management layer?
4. Compare the power management in 802.11 infrastructure and ad-hoc networks.
5. Which physical interface is used in Bluetooth headset? Why?
6. Draw and explain Bluetooth protocol stack.
7. Define (i) Piconet (ii) Scatternet (iii) LMP and L2CAP.
8. What are the fields of Bluetooth packet header? If an acknowledgement of packet is required, then which fields are set?
9. Explain different entities of mobile IP with suitable diagram.
10. What is minimal encapsulation? Explain with suitable diagram.
11. How tunnelling works in mobile IP using IP-in-IP encapsulation?
12. How registration is done by mobile IP? Explain with appropriate diagrams.
13. What is encapsulation? Discuss the advantages and drawbacks of three encapsulation
     techniques in mobile IP.
14. Discuss the characteristics of M-TCP.
15. What is snooping TCP? What are its benefits?
16. What are advantages and disadvantages of I-TCP?

Faculty Incharge: Hashmi S. A.

Class: BE(IT) Subject: MC Assignment I

MGM’s College of Engineering, Nanded.
Department of IT
Semester II (2016-17)
Class: BE(IT)       Subject: MC         Assignment I
________________________________________________________

1.     What are the applications of mobile and wireless communication? Explain in detail.

2.     What are the design goals of wireless LAN? Why?

3.     What are the drawbacks of mobile and wireless communication? Explain.

4.     Give comparison between wireless networks and fixed networks.

5.     Give comparison between FDMA, TDMA, SDMA and CDMA.

6.     What are advantages of radio communication over IR communication?

7.     What are the different digital modulation techniques? Discuss in brief

8.     Compare 802.3 and 802.11 standards.

9.     Draw and explain IEEE 802.11 infrastructure-based architecture.

10. Draw and explain IEEE 802.11 ad-hoc architecture.

11.  State and explain the functions of 802.11 layers

12. What additional functions are required for wireless MAC protocol? Why?

13. Draw and explain IEEE 802.11 FHSS frame format.

14. Draw and explain IEEE 802.11 DSSS frame format.

15.  A ftp protocol delivers a video file of 100MB on 802.11 network having 10Mbps speed. Calculate the size of FHSS, DSSS frames and time required for transmission.

16. Calculate Ethernet frame size for the above transmission of Q.15.

      Faculty Incharge: Hashmi S A

Semester I (2016-17) Assignment II Subject: DMDW Class: BE(IT)

MGM’s College of Engineering, Nanded.
Department of IT
Semester I (2016-17)
Class: BE(IT)       Subject: DMDW         Assignment II
___________________________________________________________________________

1.      What is data mining? What features are expected from a of DM output.

2.      What are the DM primitives? Enlist and explain.

3.      What is KDD? Explain the stages of KDD with an appropriate example.

4.      Enlist applications of Data Mining and Association rule mining.

5.      Why data preprocessing is needed in DM? Explain the different forms of data preprocessing.

6.      Consider that you have to analyze Marks_of_ students. You find that many tuples have no recorded value for several attributes, such as customer total_marks. How can you fill the missing values for this attribute?

7.      Given a numerical attribute such as total_marks in the above data set, how can we smooth out the data to remove the noise?

8.      Suppose that the data for analysis includes the attribute total_amount. Which distributive, algebraic and holistic measures will be used for data analysis?

9.      (a) Define Min-max normalization. Suppose the minimum and maximum values for the attribute total_marks are 400 and 850, respectively. Using min-max normalization, transform and map value 760 to the range [0.0, 1.0].

(b) Define z-score normalization. The mean and standard deviation of the values for the attribute total_marks are 440 and 89, respectively. Using z-score normalization to transform value 550.

10.  Suppose that the data for analysis includes the attribute marks. The marks values for the data tuples are (in increasing order) 13, 15, 16, 16, 19, 20, 20, 21, 22, 22, 25, 25, 25, 25, 30, 33,33, 35, 35, 35, 35, 36, 40, 45, 46, 52, 70.

(a) What is the mean of the data? What is the median?

(b) What is the mode of the data? Comment on the data’s modality (i.e., bimodal, trimodal, etc.).

11.  For the data set of Q.8 above

     (c) What is the midrange of the data?

           (d) Can you find (roughly) the first quartile (Q1) and the third quartile (Q3) of the
           data?

12. For the data set of Q.8 above

         (e) Give the five-number summary of the data.

         (f) Show a boxplot of the data.

         (g) How is a quantile-quantile plot different from a quantile plot?

13. Describe the differences between the following approaches for the integration of a 

     data mining  system with a database or DW system: no coupling, loose coupling,
     semitight coupling, and tight coupling.

14. Define (i) Association rule mining (ii) Support  (iii) Confidence

15. Discuss the essential steps of apriori algorithm.

16. For the following transaction database, find out the frequent itemsets with support ≥ 50 % and confidence ≥ 60 %.

Tid	Items
T1	M, O , N , L
T2	D, O, N
T3	M, A,  L, D
T4	N,T,B,X
T5	C,O,N, M
T6	O,N

    

Faculty Incharge: Hashmi S A

Semester I (2016-17) Class: BE(IT) Subject: DMDW Assignment I

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

1. Give comparison between Data warehouse and DBMs.
2. What are characteristics of data in data ware house? Explain.

3. What is data cube? Explain with example.

4. A Weekly sales of year 2010 of a company is present in an OLAP, and Sales Manager is          interested in Yearly sales of 2010, which OLAP operation is useful and why?
5. A Total Score of a Cricket Match is available in OLAP, the Team Manager wants to see the    performance of each individual player, which OLAP operation is needed and why?
6. Suppose that a data warehouse consists of the three dimensions Time, Doctor, and Patient, and
 the two measures count and Fee, where fee is charged by the Doctor for each OPD visit.

    Draw a star and snowflake schema diagram for the above data warehouse.

7. Write DMQL statements for creating above star schema (Q No. 6).

8. Enlist the features of snowflake and fact constellation schema with appropriate examples.
9. Draw the block diagram of DW architecture. Explain various components of DW architecture.

10. Give SQL statements for star and snowflake scheme of Q No. 6.

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

12. Draw and explain ROLAP, MOLAP and HOLAP models of DW.

13. A company is using DW to provide monthly summary information about its products and branch sales to the company managers. How many different aggregates would be required to fill data cube on product, branches and dates if there are 20 products, 3 branches and 2 years of weekly data?

14. Draw starnet query model for above DW (Q. No.13) by considering Shipper, Supplier and Manager Dimensions. 

15. What is bitmap indexing? What are the benefits of bitmap indexing? Create bitmap index for
      Performance and Team for the following table:

TID	Performance	Team
T1	Good	India
T2	Average	South Africa
T3	Excellent	India
T5	Average	England
T6	Good	England

16. Suppose that a data warehouse of Online Education consists of the four dimensions, Date, Learners, Location, and Course, and the two measures, count and charge, where charge is the fees that a learner pays when learning a course on a given date. Learners may be of students, professionals, or seniors, with each category having its own charge rate.
(a) Draw a star schema diagram for the data warehouse.
(b) Starting with the base cuboid [Date, Learners, Location, and Course],
      what specific OLAP operations should one perform in order to list
      the total charge paid by professional learners at VIT  Location in 2010?

Faculty Incharge: Hashmi S A