CS614 Data Warehousing Assignment No 1 Solution & Discussion Due Date: 05-11-2012
| Q: Normalize the given table (on the next page) up to 3rd normal form.
Read the below Guidelines carefully before solving the assignment.
|
- Show the data along with table structure.
- Do not send the archived file (.rar etc).
- Understand the scenario on the basis of given data only.
- Give the reason, if relation cannot be converted in any Particular Normal Form (1st, 2nd, 3rd ).
- Don’t convert the relation in to 3rd normal form directly. Show each normal form of the relation.
DegreeProgram
|
Specialization
Course
|
Course Instructor
|
Degree Duration
|
Degree Supervisor
|
Degree SupervisorOffice #
|
Campus
|
MBA
|
Finance
|
Prof Sadiq
|
2 years
|
Prof Naveed
|
Office # 4
|
LHR-1
|
MBA
|
Marketing
|
Prof Wajid
|
2 years
|
Prof Naveed
|
Office # 4
|
LHR-2
|
MBA Executive
|
Finance
|
Prof Khan
|
1 year
|
Prof Naveed
|
Office # 4
|
LHR-1
|
MBA Executive
|
Marketing
|
Prof Nazar
|
1 year
|
Prof Naveed
|
Office # 4
|
LHR-2
|
BBA
|
Finance
|
Prof Usman
|
4 years
|
Prof Zeshan
|
Office # 19
|
LHR-1
|
BBA
|
Marketing
|
Prof Khizar
|
4 years
|
Prof Zeshan
|
Office # 19
|
LHR-2
|
MCS
|
ArtificialIntelligence
|
Prof Naeem
|
2 years
|
Prof Zafar
|
Office # 35
|
LHR-3
|
MCS
|
Networking
|
Prof Khalid
|
2 years
|
Prof Zafar
|
Office # 35
|
LHR-4
|
MIT
|
ArtificialIntelligence
|
Prof Naveed
|
2 years
|
Prof Akram
|
Office # 40
|
LHR-3
|
MIT
|
Networking
|
Prof Zaman
|
2 years
|
Prof Akram
|
Office # 40
|
LHR-4
|
BCS
|
ArtificialIntelligence
|
Prof Nawaz
|
4 years
|
Prof Zafar
|
Office # 35
|
LHR-3
|
BCS
|
Networking
|
Prof Fozia
|
4 years
|
Prof Zafar
|
Office # 35
|
LHR-4
|
BCS
|
Database
|
Prof Afifa
|
4 years
|
Prof Zafar
|
Office # 35
|
LHR-5
|
Solution:
Given table is already in 1NF:
Degree Program
|
Specialization
Course
|
Course Instructor
|
Degree Duration
|
Degree Supervisor
|
Degree Supervisor Office #
|
Campus
|
MBA
|
Finance
|
Prof Sadiq
|
2 years
|
Prof Naveed
|
Office # 4
|
LHR-1
|
MBA
|
Marketing
|
Prof Wajid
|
2 years
|
Prof Naveed
|
Office # 4
|
LHR-2
|
MBA Executive
|
Finance
|
Prof Khan
|
1 year
|
Prof Naveed
|
Office # 4
|
LHR-1
|
MBA Executive
|
Marketing
|
Prof Nazar
|
1 year
|
Prof Naveed
|
Office # 4
|
LHR-2
|
BBA
|
Finance
|
Prof Usman
|
4 years
|
Prof Zeshan
|
Office # 19
|
LHR-1
|
BBA
|
Marketing
|
Prof Khizar
|
4 years
|
Prof Zeshan
|
Office # 19
|
LHR-2
|
MCS
|
Artificial Intelligence
|
Prof Naeem
|
2 years
|
Prof Zafar
|
Office # 35
|
LHR-3
|
MCS
|
Networking
|
Prof Khalid
|
2 years
|
Prof Zafar
|
Office # 35
|
LHR-4
|
MIT
|
Artificial Intelligence
|
Prof Naveed
|
2 years
|
Prof Akram
|
Office # 40
|
LHR-3
|
MIT
|
Networking
|
Prof Zaman
|
2 years
|
Prof Akram
|
Office # 40
|
LHR-4
|
BCS
|
Artificial Intelligence
|
Prof Nawaz
|
4 years
|
Prof Zafar
|
Office # 35
|
LHR-3
|
BCS
|
Networking
|
Prof Fozia
|
4 years
|
Prof Zafar
|
Office # 35
|
LHR-4
|
BCS
|
Database
|
Prof Afifa
|
4 years
|
Zafar Prof
|
Office # 35
|
LHR-5
|
COURSE
Degree Program
|
Specialization Course
|
Course Instructor
|
Campus
|
MBA
|
Finance
|
Prof Sadiq
|
LHR-1
|
MBA
|
Marketing
|
Prof Wajid
|
LHR-2
|
MBA Executive
|
Finance
|
Prof Khan
|
LHR-1
|
MBA Executive
|
Marketing
|
Prof Nazar
|
LHR-2
|
BBA
|
Finance
|
Prof Usman
|
LHR-1
|
BBA
|
Marketing
|
Prof Khizar
|
LHR-2
|
MCS
|
Artificial Intelligence
|
Prof Naeem
|
LHR-3
|
MCS
|
Networking
|
Prof Khalid
|
LHR-4
|
MIT
|
Artificial Intelligence
|
Prof Naveed
|
LHR-3
|
MIT
|
Networking
|
Prof Zaman
|
LHR-4
|
BCS
|
Artificial Intelligence
|
Prof Nawaz
|
LHR-3
|
BCS
|
Networking
|
Prof Fozia
|
LHR-4
|
BCS
|
Database
|
Prof Afifa
|
LHR-5
|
Degree Program
|
Degree Duration
|
Degree Supervisor | Degree Supervisor Office # |
| MBA | 2 years | ProfNaveed | Office # 4 |
| MBA Executive | 1 years | ProfNaveed | Office # 4 |
| BBA | 4 years | Prof Zeshan | Office # 19 |
| MCS | 2 years | Prof Zafar | Office # 35 |
| MIT | 2 years | Prof Akram | Office # 40 |
| BCS | 4 years | Prof Zafar | Office # 35 |
COURSE table is already in 3NF so we convert only DEGREE table in 3NF
PROGRAM_DURATION
Degree Program
|
Degree Duration
|
| MBA | 2 years |
| MBA Executive | 1 years |
| BBA | 4 years |
| MCS | 2 years |
| MIT | 2 years |
| BCS | 4 years |
| Degree Supervisor Office # | Degree Supervisor |
| Office # 4 | Prof Naveed |
| Office # 19 | Prof Zeshan |
| Office # 35 | Prof Zafar |
| Office # 40 | Prof Akram |
