Problems:

Q 1)

Consider the following requirements for an airline tickets booking system. 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. For each passenger in a flight a menu is served. The different menu options have a code and a respective description. The menu can be of type vegetarian, low fat, and kosher. For each vegetarian and low fat menu, information about the protein level and Kcal, respectively, of the meal is recorded. Each kosher meal has to be approved by the kosher community.

a.       Draw the ER diagram above scenario by illustrating the attributes of the entities and relationships.                                                                   Marks 10

b.      Draw a star schema for the above ER diagram scenario and mention all facts necessary to calculate for the analysis of the company.                        Marks 10

Q 2) Explain Surrogate Keys in brief.                                                         Marks 10

Solution:

Assignment:

Draw the snow flake schema with possible hierarchies of the following scenario.

Sales System

In Lahore Mr. Muhammad bin Iqbal has departmental stores with headquarters at Gulberg III. Salesmen deal different types of Items; The Items are identified by their Id, name, description, category name and ManufacturerID. In category they also store the Description. In his stores all the transactions are kept for decision making against the customer, time, Manufacturer and the salesperson that made this sale with order number. Salesmen work in shifts and identified by id, name and Location in which he worked over the city in different Areas. Mr. Muhammad wants to view reports of total sale made by the different salesmen at different locations, Item spoiled and quantity on hand.

Solution:

Summer 2007_CS614_2_SOL

Assignment:

Consider the case of pre-join de-normalization. Figure-1(a) shows the schema before de-normalization, and Figure-1(b) shows the schema after de-normalization i.e. sales_master table completely inserted into the sales_detail table to create the new_sales_detail table as shown in Figure-1(b). Assume the following Meta data about the schema of Figure-1:

• 1:5 record count ratio between sales_master and sales_detail tables.
• 1 billion sales_master (5 billion sales_detail).
• 5 byte sales_ID.
• 40 bytes sales_master header and 50 bytes sales_detail header.

Q1: What is the storage requirement before de-normalization?

Q2: What is the storage requirement after de-normalization?

Q3: Give the query in SQL before de-normalization that answers the question “What was the total Rs sales amount between Independence day and Defense day in 2004?” {note: Independence day 14^th Aug. and Defense day 6^th Sep.}

Q4: Give the query in SQL after de-normalization that answers the question “What was the total Rs. sales amount between Independence day and Defense day in 2004?

Q5: Give the queries before and after de-normalization that answers the question “How many sales were executed between Independence day and Defense day in 2004?”
Summer 2007_CS614_1

Solution:

Summer 2007_CS614_1_SOL

In this part you will develop a software/tool to remove duplicates and to identify siblings in the given data file as follows:

Task:

1. Code/implement the BSN (Basic Sorted Neighborhood) method to remove duplicates.
2. Code/implement the BSN (Basic Sorted Neighborhood) method to identify the siblings (people who have the same father i.e. they are brothers and sisters of each other)

Guidelines:

.

1. You can use any high level programming language such as C++, Java or VB.
2. You can read/listen lecture no. 20 to refresh information about BSN method.
3. Your application will have GUI (Graphical User Interface) environment, console based application will not be acceptable.
4. For input, you can use data of Lahore campus provided as part of lab work.
5. Each group member will upload the report on the assignment page.
6. Deadline will not be extended.
7. The required report of this part must be uploaded/submitted before or on 13-06-2007

Requirement:

At the end of this part submit report_1 consisting of source code (fully commented) and the executable file

Report starting instructions:

First page of the report must have the following information:

1. Title of course, semester and submission date
2. Names and roll no/id. of project members.
3. Campus and name of the city

Report formatting instructions:

1. Report page size may be A4
2. 12-pt Times new Roman font
3. Single space and 1” top, bottom, left and right spacing
4. No spelling or grammar mistakes
5. Pages should be numbered (bottom center)
6. Add an index at the end of report
7. File name: Your file of source code must be saved with the following pattern. RPT_no_id_CS614.doc. e.g. RPT_1_ bc0201000101.do.

Spring 2007_CS614_5

Solution:

Spring 2007_CS614_5_SOL

Assignment

Q.1. What is the problem related to the reuse of primary keys? When does it usually occur? [10]

Q.2. What is the “clean as you go” method? Is this a good approach for data warehouse environment? [10]

Q.3. Name any four types of participants on the data quality team. What are their functions? [10]

Solution:

Spring 2007_CS614_4_SOL

Assignment

Q.1 Explain the difference between Star schema, Star query and Multi-star schema. [15]

Q.2 In what situation Snowflake schemas are better to use than the star schema’s. What are the advantages of using Star schema? [10]

Q.3 what are the three main types of parallelisms as implemented in ETL applications? [15]

Solution:

Spring 2007_CS614_3_SOL

Assignment:

Q.1 What is the difference b/w OLAP & OLTP, why do we keep warehouse on different server then of OLAP? [10]

Q.2 What are Microsoft OLAP cubes? [5]

Q.3 What types of join algorithms can you have? [5]

Solution:

Spring 2007_CS614_2_SOL

Assignment

Design a normalized database for employees of “XYZ”. One non-normalized way to represent this data is with the following single table with given fields. Working/regular day of the “XYZ” is of 8 hours. If an employee works for more than 6 hours, it is considered his overtime. Wages of overtime is twice of the regular time.

Hint: Comma separates first name and second name of the employee.

The sample records are as follows:

Employee_XYZ

Assignment submission instructions:

Your assignment should be in doc format.

Solution:

Spring 2007_CS614_1_SOL

Assignment

Draw a Star Schema for Cakes & Bakes.

Cakes & Bakes operates an online transactional database for recording information about customer sales and inventory control. It records all the transactions that are processed, including not only the ordering and delivery of stock items from the various warehouses to the supermarkets, but also the recording of inventory changes as the barcode reader scans each item at the checkout. A database server is located in each store, as well as each distribution warehouse, and the data is replicated over high-speed WAN connections to the Headquarters. Mr. Ali wants to view reports of total sale made by the different salesmen at different locations,

product spoiled and quantity on hand in each warehouse. Draw the snow flake schema with possible hierarchies of the above scenario.

Assignment submission instructions:

]                 No need of Snowflake Schema.

]                 Assignment should be made by using SQL server editions and also attach a screen shot of the schema with .Mdf & .Log file.

Solution:

Special 2006_CS614_5_SOL

Assignment

(20)

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

Sales System

In Lahore Mr. Muhammad bin Iqbal has departmental stores with headquarters at Gulberg III. Salesmen deal different types of Items; The Items are identified by their Id, name, description, category name and ManufacturerID. In category they also store the Description. In his stores all the transactions are kept for decision making against the customer, time, Manufacturer and the salesperson that made this sale with order number. Salesmen work in shifts and identified by id, name and Location in which he worked over the city in different Areas. Mr. Muhammad wants to view reports of total sale made by the different salesmen at different locations, Item spoiled and quantity on hand. Draw the snow flake schema with possible hierarchies of the above scenario.

Solution:

Special 2006_CS614_4_SOL

Assignment:

Design an “excellent” entity-relationship diagram that describes the following objects in a university application: students, departments, sections taught in the present and future, and courses. Departments have a name that uniquely identifies the department. Students are identified by a unique social security number, zero, one or multiple e-mail addresses, and an optional Gpa (new students do not have a Gpa yet). Courses have a unique course number and a course title. Courses are offered in one or more sections at a particular time. Sections are identified by the time they are offered (e.g. 10:30am) and by the course they are associated with. Additionally sections are characterized by the class room the section is taught in. Only information concerning sections that are taught in the present or in the future is stored in the database. Students take a course in a particular semester and receive a grade for their

performance. Sometimes students take the same course again in a different semester. There are no limits on how many courses a student can complete, and on how many students completed a particular course. Each student is associated with a least one department. Some students are graduate students that are additionally characterized by their most recent GRE-score. Some graduate students work for a department and receive a salary for their services. Each department employs at most 75 graduate students; graduate students are not allowed to work for multiple departments.

Assignment submission instructions:

]                 Assignment should be in .doc format.

Solution:

Special 2006_CS614_3_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.

Employee Record

Solution:

Special 2006_CS614_2_SOL

Assignment:

1. Answers the following questions in precise manner and not acceptable if have more than three lines.

Ø      What is dimension modeling?

Ø      What is derived attributes?

Ø      When we add Redundant Columns”?

Ø      Give two different definations of data ware house.

Solution:

Special 2006_CS614_1_SOL

Semester Project

This will be a group project, consisting of not more than three students.

Part-I

In this part you will develop a software/tool to remove duplicates and to identify siblings in the given data file as follows:

Task:

1. Code/implement the BSN (Basic Sorted Neighborhood) method to remove duplicates.
2. Code/implement the BSN (Basic Sorted Neighborhood) method to identify the siblings (people who have the same father i.e. they are brothers and sisters of each other)

Guidelines:

.

1. You can use any high level programming language such as C++, Java or VB.
2. You can read/listen lecture no. 20 to refresh information about BSN method.
3. Your application will have GUI (Graphical User Interface) environment, console based application will not be acceptable.
4. For input, you can use data of Lahore campus provided as part of lab work.
5. You will send the names and IDs of each person in your group to Cs614 on or before 26-07-2006
6. Each group member will upload the report on the assignment page.
7. Deadline will not be extended.
8. The required report of this part must be uploaded/submitted before or on 04-08-2006

Requirement:

At the end of this part submit report_1 consisting of source code (fully commented) and the executable file

Report starting instructions:

First page of the report must have the following information:

1. Title of course, semester and submission date
2. Names and roll no/id. of project members.
3. Campus and name of the city

Report formatting instructions:

1. Report page size may be A4
2. 12-pt Times new Roman font
3. Single space and 1” top, bottom, left and right spacing
4. No spelling or grammar mistakes
5. Pages should be numbered (bottom center)
6. Add an index at the end of report
7. File name: Your file of source code must be saved with the following pattern. RPT_no_id_CS614.doc. e.g. RPT_1_ bc0201000101.do.

Warning

Any group found involved in cheating will get an `F` grade in is part.

CS614_7

Solution:

CS614_7_SOL