Class: BE(IT) Subject: DMDW Assignment II Semester I (2018-19)

MGM’s College of Engineering, Nanded.
Department of IT
Semester I (2018-19)

Class: BE(IT)       Subject: DMDW         Assignment II

1. List and describe the types of attributes used for data mining.

2. Suppose that the data for analysis includes the attribute age. The age 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. Calculate Mean, Median, Mode and Midrange of     
    the data.

3. Suppose we have the following values for salary (in thousands of dollars), shown in   
    increasing order: 30, 36, 47, 50, 52, 52, 56, 60, 63, 70, 70, 110. Calculate Variance and
    Standard Deviation.

4. Given two objects represented by the tuples (22, 1, 42, 10) and (20, 0, 36, 8):

     (a) Compute the Euclidean distance between the two objects.

     (b) Compute the Manhattan distance between the two objects.

5. Compare      (i) Discrete and continuous attributes.

                        (ii) Interval-scaled and ratio-scaled attributes.

6. Suppose that a hospital tested the age and body fat data for 18 randomly selected adults

    with the following results:

   Calculate the mean, median, and standard deviation of age and %fat.

7. Draw the boxplots for age and %fat for the data of Q.No.6 above.

8. Describe the major steps involved in data preprocessing.

9. Imagine that you need to analyze AllElectronics sales and customer data. You note that
    many tuples have no recorded value for several attributes such as customer income. Which
    methods will be employed to fill the missing values?

10. Define Noise in data. For the following data : 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. Use smoothing by bin means to
    smooth these data, using a bin depth of 3.

11. Compare min-max and z-score normalization.

12. Use these methods to normalize the following group of data:

                        200, 300, 400, 600,1000

(a) min-max normalization by setting min= 0 and max = 1

(b) z-score normalization

13. Define: (i) Support (ii) Confidence (iii) closed frequent itemset (iv) maximal frequent   
      itemset.

14. Discuss the steps of Apriori algorithm.

15. For the following transaction database, calculate the frequent itemsets using Apriori algoritm where the minimum support count is 2.

16. List the drawbacks of Apriori algorithm.

Faculty Incharge: Hashmi S A

Class: TE(IT) Subject: DBMS Assignment II Semester I (2018-19)

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

1. Classify the parts of SQL language.

2. Discuss the built-in data types supported by SQL? What is the difference between char and varchar in SQL?

3. What are the different integrity constraints supported by SQL? Explain briefly.

4. Design a database in SQL for the university database having following relations: Department, Course, Instructor, Section and Teaches. Use appropriate primary keys and foreign keys for the relations.

5. Categorize built-in set operations in SQL with examples.

6. Explain how the GROUP BY clause works. Differentiate between WHERE and HAVING clauses with the help of an example.

7. Construct queries for different Join Operations in SQL?

8. Explain the concept of nested queries with examples.

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

10. Analyze the purpose of view in SQL. Construct a SQL query for creating a view?

11. Create the following queries in SQL, using the university schema. (Refer Question 3.1 Korth 6^th edition)

a. Find the titles of courses in the Comp. Sci. department that have 3 credits

b. Find the IDs of all students who were taught by an instructor named Einstein; make sure 

there are no duplicates in the result.

c. Find the highest salary of any instructor.

12. Create the following inserts, deletes or updates in SQL, using the university schema.

(Refer Question 3.3 Korth 6^th edition)

a. Increase the salary of each instructor in the Comp. Sci. department by 10%.

b. Delete all courses that have never been offered (that is, do not occur in the section relation)

c. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same 

department, with a salary of $10,000.

13. Consider the following insurance database , where the primary keys are underlined. 

  (Refer Question 3.4 Korth 6^th edition)

person (driver id, name, address)
car (license, model, year)
accident (report number, date, location)
owns (driver id, license)
participated (report number, license, driver id, damage amount)

Construct the following SQL queries for this relational database.

a. Find the total number of people who owned cars that were involved in accidents in 2009.

b. Add a new accident to the database; assume any values for required attributes.

c. Delete the Mazda belonging to “John Smith”.

14. List the built-in aggregate functions in SQL with an appropriate example queries.

15. Explain scalar queries with an example.

16. Categorize the string matching operations in SQL with example queries.

Faculty Incharge: Hashmi S A

2018-19 Sem I BE IT DMDW Assignment I

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

1. Define Datawarehouse. What are its characteristics? Explain.
2. Compare OLTP and OLAP.

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

4. Define data cube. Explain OLAP operations that can be performed on data cube.
5. A data cube is having aggregate stocks of  5 branches , which OLAP operations can be used to find stock of each branch? Why?

6. A data cube is having marks of different subjects of students, and it was decided to find the total marks of each student, which OLAP operation is useful and why?

7. Compare star and snowflake schema for DW.
8. Explain fact constellation schema with an example.
9. Suppose a DW consists of four dimensions Year, State, Item, Manager and two measures Quantity_sold and Amount.Draw a star and snowflake schema.
10. Write SQL statements to create above star schema (Q No 9).
11. Explain starnet query model with an appropriate example.
12. Design a data warehouse for the results of a university having different affiliated colleges. Assume suitable dimensions.
13. Define data mining. Discuss the characteristics of DM output.
14. What are the steps of KDD process? Explain briefly.
15. Create a bitmap index for the following table.

TID	Branch	Class	Count
T1	IT	Distinction	25
T2	CSE	First	15
T3	Civil	First	15
T5	IT	Distinction	25
T6	CSE	Second	10

16. Compare ROLAP, MOLAP and HOLAP.

Faculty Incharge: Hashmi S A

Semester I (2018-19) Class: TE(IT) Subject: DBMS Assignment I

One Important Key To Success is Self-Confidence....
An Important Key To Self-Confidence is Preparation.....!! 

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

1. Draw and explain DBMs system structure.

2. Compare DBMS with file-processing system.

3. Explain the difference between physical and logical data independence.

4. What are five main functions of a database administrator?

5. Write a short note on: Database Languages.

6. Define: i) Schema with example ii) Primary key iii) Candidate key and iv) Superkey.

7. A university registrar’s office maintains a data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) students, including student-id, name, and branch; (c) instructors, including identification number, name, department, and title. Construct an ER diagram for the registrar’s office.

8. Enlist, explain with examples attribute types used in an ER model.

9. What are the different mapping cardinalities used in ER model? Explain briefly.

10. 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.

11. Explain various operations of relational algebra.

12. What are the major components used to construct an ER diagram? Enlist and explain.

13. What are the fundamental relational-algebra operations? Explain with examples.

14. 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 “Mumbai”.

b. Find the names of all employees whose salary is less than Rs.11000.

c. Find the names of all employees who live in “Mumbai” and whose salary is less than Rs.11000.

15. Consider the following database:

employee (personname, street, city )

works (personname, company name, salary)

company (companyname, city)

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.

16. Explain the difference between weak and strong entity sets.

Faculty Incharge: Hashmi S A

DBMS Lab Manual

https://drive.google.com/file/d/1iEH8bD0qizMUQkJUYWJDMlSaX0P7E3Df/view?usp=sharing

Mobile TCP

Assignment II Class: BE (IT) Subject: MC Academic Year 2017-18 Sem II

    MGM's College of Engineering, Nanded.

    Department of IT

    Academic Year  2017-18  Sem II

    Assignment II

    Class: BE (IT)   Subject: MC

     1.      A person is enjoying music on Bluetooth headset, which physical link is used? Why? Explain the characteristics of this link.

    2.      A data is exchanged between a laptop and smartphone, which physical link is used? Why? Explain the characteristics of this link.

    3.      Define Bluetooth Piconet and Scatternet. How they are formed?

    4.      Draw and explain the Bluetooth protocol of stack.

    5.      Draw and explain the baseband states of a Bluetooth device.

    6.      What are the components of GSM architecture? Enlist and briefly explain with appropriate diagram.

    7.      Define handover in GSM networks. A mobile phone user has travelled from Nanded to Delhi for 3 days, which handover will take place and how? How mobile services will be provided to the user in Delhi and billing is done for the service?

     8.      How a SIM card gets authenticated in GSM networks ?

     9.      A GSM operator is using 890-915 MHz uplink frequency band, (i) how many channels are formed (ii) Size of each channel (iii) how many TDMA slots per channel are available?

     10.  What are the different logical channels in GSM? Explain their functions.

     11.  Enlist the building blocks of 3G system with their functions.

     12.  Draw the UTRAN architecture in 3G.

     13.  What are the different types of handover in 3G networks.

     14.  Compare UMTS and HSDPA.

     15.  Which are the additional components in LTE architecture not present in 3G? Why

     16.  Write short notes on : Characteristics of 4G.

Faculty Incharge: Hashmi S A

Class: TE (IT) Subject: CN Academic Year 2017-18 Semester II Assignment II

      MGM's College of Engineering, Nanded

      Department of IT

      Academic Year 2017-18  Semester II

      Assignment II 

      Class: TE (IT)        Subject: CN

      

      1.      Discuss Subnetting and Supernetting with example.

      2.      What are different types of ICMP Messages? An ICMP message has arrived with the header (in hexadecimal):

03 0310 20 00 00 00 00

What is the type of the message? What is the code? What is the purpose of the message?   

3.      Compare (i) TCP with IP (ii) TCP with UDP.

       4.      What are properties of transport layer services? Explain in brief.

       5.      Draw and explain the fields of TCP segment format. What is the maximum size of the TCP header? What is the minimum size of the TCP header?

      6.      What are the different control fields in TCP segment? What can you say about the TCP segment in which the value of the control field is one of the following:

a. 000000

b. 000001

c. 010001

d. 000100

e. 000010

f. 010010

      7.      What are the different options in TCP segment? If the value of HLEN is 0111, how many bytes of option are included in the segment?

      8.      The following is a dump of a TCP header in hexadecimal format.

                  (05320017 00000001 00000000 500207FF 00000000)

a. What is the source port number?

b. What is the destination port number?

c. What the sequence number?

d. What is the acknowledgment number?

e. What is the length of the header?

9.    What is Silly Window Syndrome? How it is solved in TCP?
10.  Draw and explain the format of UDP packet. The following is a dump of a UDP header
             in hexadecimal format.

                             CB84000D001C001C

a. What is the source port number?
b. What is the destination port number?
c. What is the total length of the user datagram?
d. What is the length of the data?

11. Define autonomous system. Which protocol is used for communication between
            autonomous systems?  

12. Contrast and compare distance vector routing with link state routing.
13. Draw and explain the fields of RIP message format.
14. Draw and explain the fields OSPF message format.
15. Change the multicast IP address 238.212.24.9 to an Ethernet multicast address.
16. Write short notes on : (a) IGMP  (b)  DHCP

        
  Faculty Incharge: Hashmi S A