Class: TE(IT) Subject: C N Assignment I

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

1.      Enlist and explain the properties of internet.

2.      Explain in detail application-level services of internet.

3.      Compare IP address with Ethernet address.

4.      Which service is used by a telephone call? Why?

5.      Change the following IPv4 addresses from binary to hexadecimal and decimal notation.

(i) 10111001  01101001  10010011  11101011 

(ii) 10101001  10010011  10010011  01111011

6.      Compare class A and class C IP addresses.

7.      Change the following IP addresses from dotted-decimal notation to binary and hex notation.

(i)                 130.66.55.88  (ii)  211.215.201.18  (iii)  171.185.112.115

8.      For the following addresses, find the class, the block and the range of addresses:

(i)                 15.1.1.1     (ii) 175.56.1.1  (iii) 221.120.74.1

9.      Find the error, if any, in the following IP addresses:

(i)                 115.375.79.85   (ii)  295.91.79.15  (iii)  60.74.155.266

10.  (a) Find the class of the following IP addresses, and give explanation for it:

(i)                 11000011  10000011   00011011 11111111

(ii)               00101001  00001011   00001011 11101111

(iii)             10010001 00101011   00001011 11101111

(b) One of the address in a block is 201.12.8.45.Find the number of addresses, the first address and the last address.

11.  A host with IP address 192.25.42.20 and physical address 0xb2efc5f7fe2c  has a packet to send to another host with 165.125.42.29 and physical address 0xf456e73f227c. The two hosts are on the same Ethernet. Construct ARP request and reply packets.

12.  Draw and explain the fields of an IP datagram. An IP packet has arrived with the first 8 bits as shown:

                                       ç  01100011

     The receiver discards the packet. Why?

13.   (a) In an IP packet, the value of HLEN is 7 (decimal) and the value of the total length field is 55 (decimal). How many bytes of data are being carried by this packet?

(b) In an IP packet, the value of HLEN is 0C (hex) and the value of the total length field is 0AFF (hex). How many bytes of data are being carried by this packet?

14.  Calculate the checksum for the following IP datagram

4	5	0	59
2FC			0	0
8	6		0
192.168.111.2
195.168.111.99

15.  An IP packet has arrived with the hexadecimal digits as shown below:

ç  450000380d3a00000201bb6ec0a802034a7de2f4

How many hops can this packet travel before being dropped, The data belong to what upper layer protocol, What is identification value, what is the value for checksum, what is source and destination address?

16.  What is the significance of identification, flags, and offset fields of IP datagram? A datagram of size 7520 bytes is transmitted on a network of MTU 1100 bytes. Calculate the offset values and range of data bytes present in each fragments. What is the status of flags bit in each fragment?

Additional Questions for practice

17.  What services are provided by network-level of internet? Discuss in detail.

18.  Draw and explain different fields of Ethernet frame.

19.  Compare connection-oriented and connectionless delivery systems.

20.  Enlist the advantages and drawbacks of packet-switching networks.

21.  What are the advantages of circuit-switching networks over packet-switching? Why?

22.  What connecting devices are used at layer-1, layer-2, and layer-3 in internet? Explain their characteristics and functions.

23.  What addresses are used at layer-1, layer-2, and layer-3 in internet? Explain briefly.

24.  Explain the application programs at your site that use TCP/IP.

25.  What is Ethernet address? What are its properties?

26.  What is an IP address? What are the desirable properties of IP address? What is signified by prefix and suffix of IP address?

27.  Explain classful addressing in TCP/IP.

28.  Draw and explain ARP and RARP protocol format.

29.  Explain the concept of (i) datagram encapsulation, (ii) network MTU, (iii) and multiplexing at IP and TCP layers.

30.  What is proxy ARP? Explain with example.

31.  What is ARP? Explain ARP cache timeout.

32.  Define : (i) Multihomed host (ii) Directed broadcast address (iii) Limited broadcast address (iv) Loopback address.

33.  What are the different options for IP datagram option field? Explain briefly. An IP packet has the value of HLEN as 1011 in binary. How many bytes of options are being carried by this packet?

34.  Draw the architecture of Internet. Explain its components.

35.  A desktop machine want to send a multicast message to all computers in a group. Which class of IP address will be used? Why?

Faculty Incharge: Hashmi S A

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