Faculty Course Relationship

View previous topic View next topic Go down

Faculty Course Relationship

Post  hunain on Tue Feb 04, 2014 11:22 pm

Guys,

I need your suggestions on how to handle the relationship between student course registration and faculty.

A student is registered in a section/courses which is essentially a fact table. A student can register in more than one course/section.

The section/courses can be taught by one or more instructors at different timings though.

How do I model a relationship between the student registration and faculty which are assigned to teach those sections.

Does Faculty assignment to section sound a fact table or dimension. If its a dimension table what would the surrogate be like.

appreciate your help!

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Faculty Course Relationship

Post  ngalemmo on Wed Feb 05, 2014 2:45 am

The section/faculty relationship is handled with a bridge table. It may contain an allocation factor if needed for contribution or FTE calculations.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Faculty Course Relationship

Post  nick_white on Wed Feb 05, 2014 4:03 am

BTW - Kimball's book has a whole chapter covering this topic of students/courses/faculties (Chapter 13 - Education). I would definitely recommend that you read it

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Faculty Course Relationship

Post  hunain on Thu Feb 06, 2014 3:52 am

Guys I am not sure If I quite I understand this yet. Adding a instructor group key in this table which would be same if 2 instructors are teaching the same section, however this group key cannot be a PK in this bridge table for sure,its an FK so what is the corresponding dimension table for the instructor group key. I understand that the bridge table is perfect for my scenario where I have a many to many relationship between my fact and dimension table. Are there any standards for naming bridge tables. e.g. Dim, Fact or _DM or _FT. I have also attached the article below.




add a bridge table with an instructor group key in either the fact table or as an outrigger on the course dimension, as introduced in Chapter 8: Customer Relationship Management. There would be one row in this table for each instructor who teaches courses on his own. In addition, there would be two rows for each instructor team; these rows would associate the same group key with individual instructor keys. The concatenation of the group key and instructor key would uniquely identify each bridge table row.

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Faculty Course Relationship

Post  nick_white on Thu Feb 06, 2014 6:11 am

The PK on the Bridge table (whether implied or explicitly defined) is the "Instructor Group Key + Instructor Key". There is no Instructor Group Dimension, the Instructor Group Key is just an artificial construct to enable a M:M join between the Fact and the Instructor Dimension.

At the risk of confusing things, an extension of this model would be if your business does have the concept of an "instructor group" that has attributes you wish to report on, you can create an Instructor Group Dimension which would then join to your fact table (and the bridge table would sit between the Group Dim and the Instructor Dim).

Naming convention: follow whatever you are using for existing tables: so if you name fact tables *_F and Dim tables *_D then call bridge tables *_B

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Faculty Course Relationship

Post  hunain on Sat Feb 08, 2014 2:02 am

Nick I have 2 keys in the bridge table.

1- Section Key
2- Instructor Key

Do I need to create a separate key called as Instructor Group Key in the bridge table.

Hunain

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Faculty Course Relationship

Post  nick_white on Mon Feb 10, 2014 3:41 am

No - you need to have Section Key in your Fact table which you will join on

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Faculty Course Relationship

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum