Education In Pakistan

Papers, Notes, Books & Help For Students

UPDATED EDUCATIONAL NEWS INTERVIEW HELP FOR ALL JOBS ONLINE BOOKS SCHOLARSHIPS AVAILABLE INTERNSHIP JOBS

Tag: Data warehousing (CS614) Assignment # 1 May 2013

Data warehousing (CS614) Assignment # 1 May 2013

Data Warehousing (CS614) Assignment # 1 May 2013

VU Data warehousing (CS614) Assignment # 2 Unsolved

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:

 

 

Data Warehousing (CS614) Assignment # 1 May 2013

Education In Pakistan © 2016