CLICK HERE FOR VARIOUS NEW JOBS 
CLICK HERE FOR VARIOUS EDUCATIONAL NEWS 
CLICK HERE FOR NEW SCHOLARSHIPS 
CLICK HERE FOR ADMISSION NOTICES 
Click And Follow On Google+ To Get Updates
Please Wait 10 Seconds... OR You CanSkip

ADMISSION NOTICES
Scholarships

Scholarship-300x291

BUDGET 2014-15
budget_2014-2015
New Date Sheets
VU SOLVED ASSIGNMENTS
Recent Posts

Posts Tagged ‘Solved Data warehousing (CS614) Assignment # 1’

VU Solved Data warehousing (CS614) Assignment # 1

Solved Data Warehousing (CS614) Assignment # 1

Data warehousing (CS614)

Assignment # 1

  Total marks = 20

                                                                                       Deadline Date = Expired

Please carefully read the following instructions before attempting the assignment.

 

Rules for Marking

It should be clear that your assignment would not get any credit if:

 

  • The assignment is submitted after due date.
  • The submitted assignment does not open or file is corrupt.
  • The assignment is copied. Note that strict action would be taken if the submitted assignment is copied from any other student. Both students will be punished severely.

 

1)      You should consult recommended books to clarify your concepts as handouts are not sufficient.

2)      You are supposed to submit your assignment in .doc format. Any other formats like scan images, PDF, Zip, rar, bmp, docx etc will not be accepted

3)      You are advised to upload your assignment at least two days before Due date.

4)      This assignment file comprises of Seven (7) pages.

 

 

Important Note: 

 

Assignment comprises of 20 Marks. Note that no assignment will be accepted after due date via email in any case (whether it is the case of load shedding or emergency electric failure or internet malfunctioning etc.). Hence, refrain from uploading assignment in the last hour of the deadline, and try to upload Solutions at least 02 days before the deadline to avoid inconvenience later on.

 

For any query please contact: CS614@vu.edu.pk


 

Objective:

 

The objective of the assignment is to revise the old concepts as well as setting focus on the new ones. Self practice will not only clear your concepts further but will also provide a strong base in future.

 

Reference:

 

Please refer to the case study given at the end and complete the following tasks:

 

 

Task#1 [Marks: 5]:

 

Draw an Entity Relationship Diagram (ER Model) for the given case study along with proper associations and cardinalities.

 

Task#2 [Marks 5]:

 

Derive the tables from the ER model and normalize up to third normal form (3rd NF)

 

Task#3 [Marks 10]:

 

De-normalize the database (obtained after completing task#2) by using the “collapsing tables” technique.

Task#1[ERD]

 

 

Note: This is a high level ERD model. There can be other models with little bit difference based on the abstraction level.
Task#2

 

Jets

JetID Number Primary Key
JetName Text  
JetType Text  

 

Flights

FlightID Number Primary Key
FlightDate Date  
FlightJetID Number Foreign Key (Refers JetID in Table Jets)
FlightSource Text  
FlighDestination Text  
FlightTime Text  
RunWayID Number Foreign Key (Refers RunWayID in Table Runways)
ReservedBy Number Foreign key (Refers StaffMemberID in Table Staff)

 

 

Passengers

PassengerID Number Primary Key
PassengerName Text  
PassengerCell Text  

 

Staff

StaffMemberID Number Primary Key
StaffMemberName Text  
StaffMemberDesignation Text  
StaffMemberContact Text  

 

Tickets

TicketID Number Primary Key
TicketType Text  
PassengerID Number  
FlightType Number Foreign Key (Refers FlightID in Table Flights)

 

Announcements

AnnouncementID Number Primary Key
AnnouncementText Text  
AnnouncementStartDate Date  
AnnouncementExpDate Date  
StaffMemberID Number Foreign Key (Refers StaffMemberID in Table Staff)

 

Runway

RunwayID Number Primary Key
RunwayTrack Text  
TowerAssigned Number  

 

So an abstract graphical view of the database may look like:

 

Task#3

 

Table collapsing: table collapsing de-normalization technique is used on one-to-one and many-to-many relations. In ER model given in task-1 we do not have man-to-many relations, however, one-to-one relations were identified between “Tickets”, “Passengers” entities and “Runway”, “Flights” and “Jets” entities.

First we collapse the “Passengers” and “Tickets” tables. We suppose that “PassengerCell” attribute is unnecessary so finally the resultant “Tickets” table after collapsing becomes:

 

Tickets

TicketID Number Primary Key
TicketType Text  
PassengerID Number  
PassengerName String  
FlightID Number Foreign Key (Refers FlightID in Table Flights)

 

 

Next we collapse “Runway”, “Flights” and “Jets” tables

 

First collapsing “Runways” table with “Flights” (suppose we need “RunwayTrack” field only) we get:

 

Flights’

FlightID Number Primary Key
FlightDate Date  
FlightJetID Number Foreign Key (Refers JetID in Table Jets)
FlightSource Text  
FlighDestination Text  
FlightTime Text  
RunWayID Number Foreign Key (Refers RunWayID in Table Runways)
RunwayTrack String  
ReservedBy Number Foreign key (Refers StaffMemberID in Table Staff)

Then we merge “Jets” Table with “Flights”. The final structure of “Flights” table becomes:

 

Flights

FlightID Number Primary Key
FlightDate Date  
FlightJetID Number Foreign Key (Refers JetID in Table Jets)
JetName String  
JetType String  
FlightSource Text  
FlighDestination Text  
FlightTime Text  
RunWayID Number Foreign Key (Refers RunWayID in Table Runways)
RunwayTrack String  
ReservedBy Number Foreign key (Refers StaffMemberID in Table Staff)

 

 

Finally the abstract view may look like:

 

 

Solved Data Warehousing (CS614) Assignment # 1

ALL NEW RESULTS
Educational News

Updated Educational News

Categories
POSTS BY DATE
December 2016
M T W T F S S
« Sep    
 1234
567891011
12131415161718
19202122232425
262728293031