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
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:
