TE(IT)-CGPA Subject: DBMS Assignment II

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

1. Explain various set operations available in SQL with examples.

2. Explain how the GROUP BY clause works. What is the difference between WHERE and HAVING clauses? Explain with the help of an example.

3. How are queries based on joins expressed in SQL? Explain the various join operations.

4. Explain the concept of nested queries with examples.

5. Discuss the different operators in SQL, which are used with subqueries/nested queries.

6. What are assertions? Why are they necessary?

7. What is the purpose of view in SQL? How can you create a view?

8. What are stored procedures? When are they beneficial? Give the syntax and example of creating a procedure and function in SQL.

9. What are triggers? How are they created? Explain with an example how triggers offer a powerful mechanism for dealing with the changes to database.

10. What do you understand by an embedded SQL? How are variables declared and used in an embedded SQL? Explain with examples.

11. What is the purpose of ODBC in SQL? How is it implemented? Explain with examples.

12. What is the purpose of JDBC in SQL? Explain with examples.

13.Consider the given tables

Loan

LoanNo           BranchName               Amount

L101                Mumbai                       1000

L103                Pune                            2000

Borrower

CustomerName           LoanNo

Sonam                         L101

Nandy                         L103

Anusha                        L103

Depositor

CustomerName           AccountNo

Anusha                        A102

Sonam                         A103

Nandy                         A104

(a)  What are the number of tuples present in the result of cross product of the above two tables?

(b) Write an SQL to Find all customers who have only accounts but no loans.

(c) Write an SQL to Find the names of all customers who have either an account or loan but not both.

14. Define functional dependency. Explain different Normal forms.

15.The given table is in BCNF form, convert it to the 4^th normal form.

Employee       Skill                             Language

Jones               Electrical                     French

Jones               Electrical                     German

Jones               Mechanical                  French

Jones               Mechnical                    German

Smith               Plumbing                     Spanish

16. Compute the closure of the following set F of functional dependencies

for relation schema R = (A, B, C, D, E).

A→BC

CD→E

B→D

E→A

Faculty Incharge: Hashmi S A

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