Assignment:

There are two types of Data Mining.

1. Predictive Data Mining

2. Descriptive Data Mining

Define and provide brief explanation of these two types. Give precise answer not more than 200 words for each type. Please also provide references

Solution:.

CS614_6_SOL

Scenario:

It is the OLTP database of a company name Ahmad Coverall Pakistan that takes orders from the customers and forwards these orders to the suppliers. Suppliers fulfill the order of the company and delivered the product. Company received the delivery and noted number of units sent by the vendor. Company verifies the delivery after checking the whole material and the damaged products are noted in Supplier Discard and sent back. In the same way customers received the units, verified and discard units sent to the company. There is a certain minimum level of the

stock that is available for the emergency requirements.  The owner of the company hires a team to build a data ware house so that he comes to know:

1. The name or ids of the suppliers whose orders are delayed and those whose orders are delivered in time.
2. Average delay in number of days in shipment of the orders by the company to the customer.
3. Average discards in the orders by the customer.
4. Average discards in the orders supplied by the supplier to the company.
5. Number of units currently available in stock.
6. Total number of orders ordered by the customers against a certain time period.
7. Total sale made by the company in a given period of time against one or more products.
8. Total number of orders placed by the customers to the company.
9. Which product is sold in certain region in certain time period?
10. Total products ordered to the company by the customers.
11. Total cost of the products after shipment.
12. Profit or money gained against certain orders in a specific time period.

These are the owner’s requirements, but your assignment is to give the answers of the following questions.

Assignment:

Draw star schema of this model by using fact tables named as Sales_­Fact and SupplierEfficiency­_Fact selecting the necessary dimensions from the given diagram.

Solution:

CS614_5_SOL

Assignment

(30)

1. Draw the snow flake schema with possible hierarchies of the above scenario

Sales System

In England Mr. Zaid has chain departmental stores with headquarters at London. Salesmen deal different type of products; the products are identified by their Id, name, description, category name. In category they also store the CatogeryDescription. In his stores all the transactions are kept for decision making against the customer, time and the salesperson that made this sale with order number. Salesmen work in shifts and identified by id, name and city in which he worked over the country in different states. Mr. Zaid wants to view reports of total sale made by the different salesmen at different locations and quantity on hand. Draw the snow flake schema with possible hierarchies of the above scenario.

Solution:

CS614_3_SOL

Assignment

Part (i): (25)

Draw an ER Diagram for flight Reservation System. Draw the Complete ER diagram of above scenario by illustrating the diagram with attributes of the entity and relationship.

Part (ii): (25)

Translate the ER diagram to a relational data model identifying the relational schemas, attributes, primary keys and foreign keys.

Flight Reservation System

Consider the following requirements for an airline tickets booking system and the related ER diagram. The system is composed of many airlines. Each airline is identified by a code, name and head quarter’s address. An airline has different mile programs, identified by a type_code, description, and starting date. The different types of flights offered by an airline have a unique number, day of the week, departure time, arrival time, origin, destination, and stops (if applicable). Passengers are allocated into flights and are identified by a number, name, address, sex and age. A passenger can subscribe for one mile program of a certain airline. The system records information about the passengers booked in a flight, together with the date, seat, pilot-name, and crewmembers of that flight. The system also maintains information about the miles accumulated by each passenger in a certain flight. This information is kept as a mile-order identified by a number, quantity and date. The booking can be done through a travel agency, identified by a code, name, address, contact person, and phone number.

Solution:

CS614_2_SOL

Assignment

Design a normalized database for Students. One non-normalized way to represent this data is with the following single table with given fields.  You normalized the table up to 3^rd Normal Form.

Students

Solution:

CS614_1_SOL

a.       Show the shortest form of the following IPV6 address: 2340:1ABC:119A:A000:0000:0000:0000:0000

b.      Show the original (unabbreviated) form of the following IPV6 address: 0:AA::0

c.       In IP datagram header format, what is the value of the data field given H.LEN value of 12 and TOTAL LENGTH value of 40,000?

d.      What are the main advantages of IPV6 over IPV4?

Solution:

Spring 2009_CS610_5_SOL

Q.1 What is gratuitous ARP? [3]

Q.2 Find the net ID and host ID of the following IP Addresses. [8]

a) 117.34.4.8

b) 29.34.41.5

c) 23.67.12.1

d) 139.33.4.5

Q.3 Find the class of the following IP Addresses. [4]

a) 218.34.54.12

b) 238.34.2.1

c) 124.34.2.8

d) 139.14.6.8

Solution:

Spring 2009_CS610_4_SOL

Spring 2009_CS610_3

Solution:

Spring 2009_CS610_3_SOL

Q#1 Draw an example of ‘Cycle of Bridges’ with the segments below: [5]

Q#2 Answer the following questions:

i.        If we can extend the LAN then why we need a WAN?                          [3]

ii.      How can a bridge know whether to forward frames?                              [2]

iii.    Can the length of an Ethernet be increased to many segments of 500 meter each merely by adding a repeater to connect each additional segment?      [2]

iv.    How can a computer attach to a network that sends and receives bits faster than the computer’s CPU can handle them?                                             [3]

Spring 2009_CS610_2

Solution:

Spring 2009_CS610_2_SOL

Question 1 (Marks: 3+4+3)

Answer the following questions:

1. Explain what might happen if two stations are accidentally assigned the same hardware address?
2. If sharing reduces cost, why are shared networks used only for local communication?
3. Why wireless LAN can not use the same CSMA/CD mechanism that Ethernet uses

Question 2

Complete given table and answer yes or no.                                      (Marks: 10)

Solution:

Spring 2009_CS610_1_SOL

Q1. Suppose an ISP has class C address i.e. 193.5.48.0. If the ISP has three customers with only six computers each, then what IP addresses are assigned to three customers by using classless addressing?                                                                   [10]

Q2. What are two standard implementations to improve the computational efficiency in address resolution with table lookup?

Solution:

Spring 2008_CS610_4_SOL

Q1. See the fig given below and complete the entries of the Next Hop in the table ?

Q2. What are the differences between static and dynamic routing?                      (10)

Spring 2008_CS610_3

Solution:

Spring 2008_CS610_3_SOL

Q1. Provide 5 IEEE standards, describing in maximum 5 line (diagram can be provided) e.g. IEEE 802.2 LLC. [10 Marks]

Q2. What tools can be used to monitor and debug performance of a network, give example of these tools. [5 Marks]

Q3. What is the difference between host and end system? List the types of end system. Is web server an end system? [5 Marks]

Q4. The word protocol is used to describe diplomatic relations. Give an example of the diplomatic protocol. [2 Marks]

Solution:

Spring 2008_CS610_2_SOL

Question # 1 (Marks: 10)
Answer the following questions:
a. What sort of information difference that is provided when the ping and trace route commands are executed in the command prompt. Using Ping to measure the round-trip times to destinations on the Internet (e.g., to Web sites).What is the maximum round-trip time you encounter?
b. Describe mathematically that what is the benefit of sending data in packets rather then without packets?

Question # 2 (Marks: 10)
What are the details of frame transmission and reception that LAN interface handles?

Solution:

Spring 2008_CS610_1_SOL

Question#1                                                                                                     (Marks 10)

1. What are major problems in User Datagram Protocol (UDP) due to which we need Transmission Control Protocol (TCP)?
2. How does Transmission Control protocol (TCP) achieve reliability?
3. What are the main advantages of IPV6 over IPV4?
4. Can a router in the global Internet use static routing the same way a host does?

Question#2                                                                                                     (Marks 10)

1. Show the shortest form of the following IPV6 address: 2340:1ABC:119A:A000:0000:0000:0000:0000
2. Show the original (unabbreviated) form of the following IPV6 address: 0:AA::0

Solution:

Spring 2007_CS610_7_SOL