CS614 Data Warehousing Assignment No 1 Solution Fall 2012

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

2NF:

                                                            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

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

3NF:

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

OFFICE_SUPERVISOR

Degree Supervisor Office # Degree  Supervisor  
Office # 4 Prof  Naveed
Office # 19 Prof Zeshan
Office # 35 Prof Zafar
Office # 40 Prof Akram
DOWNLOAD SOLUTION HERE
loading...