Help with Normalization - Courses/Labs

[alpinegroove]

I am building a database that contains information relating to instructional staff in my program: instructors, teaching assistants, readers. The database helps keep track of contact information and generating contracts. It does not handle enrollment in courses.

This is the general structure:

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-txtAddress

tblRoles
-pkRoleID primary key, autonumber
-txtRole (e.g., Instructor, Teaching Assistant, Reader)

tblCourses
-pkCourseID primary key, autonumber
-lngCourseTitle
-txtCourseName
-txtLab1Time
-txtLab1Place
-txtLab2Time
-txtLab2Place
-txtLab3Time
-txtLab3Place

tblCoursePeopleRoles
-pkCoursePeopleRoleID primary key, autonumber
-fkCourseID foreign key to tblCourses
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles

The issue is with tblCourses, which currently isn't normalized (see how the labs are listed). This model works fine with one exception. In 99% of the cases, one Teaching Assistant teaches all of the labs, and I know to list all of the labs in the contract. In only one of my courses, the TA cannot teach all 3 labs, and a second TA is hired to teach one of the labs. The contracts in this case should not list all of the labs, but only the one they are hired to teach.

How can this be addressed?
Will I need another junction table to associate TAs with labs? How would that table relate to the existing junction table tblCoursePeopleRoles?

Thank you!

 

[jzwp22]

If you consider that a lab is just a course related to another course then it would go in tblCourses.

You would need a table to relate the main course with its lab

tblCourseLabs
-pkCourseLabID primary key, autonumber
-fkMCourseID foreign key to tblCourses (main course)
-fkLCourseID foreign key to tblCourses (the lab)

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-txtAddress

tblRoles
-pkRoleID primary key, autonumber
-txtRole (e.g., Instructor, Teaching Assistant, Reader)

tblCourses
-pkCourseID primary key, autonumber
-lngCourseTitle
-txtCourseName

Now a particular lab course may have multiple sections (as they were called when I was in college too many years ago to count). Each section had a different meeting place/room, days/times. Similarly, the main courses also had multiple sections.

tblCourseSections
-pkCourseSectionID primary key, autonumber
-fkCourseID foreign key to tblCourses
-SectionNo
-dteCourseSection (time of section for the course)
-Location (location of the section for the course)

Now if you have multiple people related to a course/section combination then they would be related using this table:

tblCourseSectionPeopleRoles
-pkCourseSectionPeopleRoleID primary key, autonumber
-fkCourseSectionID foreign key to tblCourseSections
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles

 

[alpinegroove]

Thank you. I will try to implement this. A couple of questions:

1. I don't see tblCourseLabs or fkCourseLabID anywhere in the tables that create the different combinations: tblCourseSections and tblCourseSectionPeopleRoles. How does that work? Don't I need to fkCourseLabID somewhere?

2. How would you set this up in terms of forms and data entry? One form to relate courses and labs, one to relate courses and sections, and one to relate course/section to people and role? Would all of these be forms with subforms?

Or would you do one form with multiple subforms?

Thanks again!

 

[jzwp22]

1. I don't see tblCourseLabs or fkCourseLabID anywhere in the tables that create the different combinations: tblCourseSections and tblCourseSectionPeopleRoles. How does that work? Don't I need to fkCourseLabID somewhere?

A lab is just another course so it is tied to its section directly. The logic behind tblCourseLabs is to relate the primary course to its corrsponding lab course. For example a chemistry lecture would be the primary and the chemistry lab would be related to it, but both are still considered couses so each will have their related section in tblCourseSections

2. How would you set this up in terms of forms and data entry? One form to relate courses and labs, one to relate courses and sections, and one to relate course/section to people and role? Would all of these be forms with subforms?

I would have a form based on tblCourses and within that a subform based on tblCourseLabs. You might add a field to tblCourses to distinguish main courses from lab course and then filter the main form to show only the main couses and the combo box in the subform to show only lab courses.

For course/sections/people, you would have a form/subform/subsubform setup.

 

[alpinegroove]

Thank you. This is really helpful, and I look forward to playing around with it.

 

[jzwp22]

You're welcome

 

[alpinegroove]

tblCourseLabs
-pkCourseLabID primary key, autonumber
-fkMCourseID foreign key to tblCourses (main course)
-fkLCourseID foreign key to tblCourses (the lab)

tblCourses
-pkCourseID primary key, autonumber
-lngCourseTitle
-txtCourseName

I think I am missing something. Both fkMCourseID and fkLCourseID in tblCourseLabs are linked to the same pkCourseID in tblCourses?

How do I accomplish that? Add tblCourseLabs twice to the Relationships windows and then link fkMCourseID in one and fkLCourseID in the other?

I would have a form based on tblCourses and within that a subform based on tblCourseLabs.

Since I now have two tblCourseLabs, which one do I use for this subform?

Thanks

 

[alpinegroove]

How do I accomplish that? Add tblCourseLabs twice to the Relationships windows and then link fkMCourseID in one and fkLCourseID in the other?

After creating a subfrom linked to tblCoursesLabs, when I went back to the look at the Relationships, a new table has appeared: tblCourses1. It is now linked to tblCoursesLabs and tblCoursesLabs1 is not linked to anything anymore.

I am still having trouble completely understanding tblCoursesLabs. Where would it come into play? When would I need to use the data from that table?

Sorry for not getting it...

 

[alpinegroove]

By the way, should I force referential integrity on any of these links?

 

[jzwp22]

I think I am missing something. Both fkMCourseID and fkLCourseID in tblCourseLabs are linked to the same pkCourseID in tblCourses?

Correct but not the same record in tblCourses.

How do I accomplish that? Add tblCourseLabs twice to the Relationships windows and then link fkMCourseID in one and fkLCourseID in the other?

Use a form with a subform. The main form should be based on tblCourses and the subform on tblCourseLabs. You will need a combo box in the subform that is based on tblCourses (or a query that is based on tblCourses). Again, you can filter the main form to only show main courses and the combo box to only show labs (provided you have a field in tblCourses that distinguishes the 2 course types).

After creating a subfrom linked to tblCoursesLabs, when I went back to the look at the Relationships, a new table has appeared: tblCourses1. It is now linked to tblCoursesLabs and tblCoursesLabs1 is not linked to anything anymore

Access does that. Make sure one is linked to fkMCourseID and the other linked to fkLCouseID. You will need to enforce referential integrity for both relationships.

 

[alpinegroove]

Thanks for the clarification.
Some of my courses have multiple iterations, i.e., I have 3 Computer Science 1A courses, each having three different lab meetings (students only choose one lab).
Sorry for neglecting to mention that.
Can that be accommodated?

 

[jzwp22]

Can that be accommodated?

Absolutely.

I have 3 Computer Science 1A courses: 1 course with 3 sections, I assume
The computer science 1A course has an associated lab that lab has 3 sections (meetings)

 

[alpinegroove]

Clarification: I have three Computer Science 1A:
1. MWF 10-11
2. MWF 11-12
3. TuTh 11:30-1

Each course has three labs:

Course 1 (MWF 10-11) has 3 labs: M 11-12, M 12-1, and W 1-2)
Course 2 (MWF 11-12) has 3 labs: M 1-2, M 2-3, and M 3-4)
Course 3 (TuTh 11:30-1) has 3 labs: Tu 9-10, Tu 2-3, and Th 2-3)

Of course not all of them are taught by the same person.

Does that introduce a many-to-many relationship? Do I need a junction table somewhere?

 

[jzwp22]

What you are basically saying is that the main course has many sections and that the lab course has many sections and that a section of the main course is related to a section of the lab course. So a course/section is related to another course/section

tblRelatedCourseSections
-pkRelatedCourseSectionID primary key, autonumber
-fkPCourseSectionID foreign key to tblCourseSections
-fkSCourseSectionID foreign key to tblCourseSections

I used P & S just to distinguish the two fields.

Do you plan on capturing the meeting days and times as well? If so, a section has many days on which it meets. A day of the week can apply to many sections.

 

[alpinegroove]

Yes, basically each course needs to be associated with the three labs that go with it, and yes some courses are offered more than once.
I do need to capture meeting days and time, both for the courses and the labs.
I also need to capture compensation information for each course/section/person/role combination.

This is getting a bit too advanced for my skill level, so thank you for hanging in there. I am trying my best to make this work.

 

[alpinegroove]

I think I am understanding the tables and general data model. It's all the required forms I am struggling with.

 

[jzwp22]

basically each course needs to be associated with the three labs that go with it, and yes some courses are offered more than once.

Actually the course section needs to be associated with the lab section. The section has 3 meeting days/times.

Why don't you see if you can develop the structure to handle the meeting days/times on your own and then post your the structure.

Don't worry about the forms yet. It is more important to get the table structure set up properly.

 

[alpinegroove]

Why don't you see if you can develop the structure to handle the meeting days/times on your own and then post your the structure.
.

I will work on that...

 

[alpinegroove]

Please see the attached structure. I am attaching both a PDF of the Relationships and the actual database, in case that makes it easier to read.

Let me try to describe the set up here:

Courses/Labs:

Math 101
English 1
Spanish 2

Math 101 is offered 3 times during the same semester:
Lecture 1 - MWF 10-11
Lecture 2 - MWF 11-12
Lecture 3 - TuTh 9:30-11

Each lecture has 3 labs/discussions associated with it.

Sometimes the same person and sometimes different people teach the 3 lectures.

The person who teaches the lecture is called an Instructor, and the person who teaches the labs/discussions is called a Teaching Assistant (TA).

The person who teaches the lecture usually also teaches the labs/discussions associated with it. Sometimes, however, a different person is hired to teach the labs/discussions.

In rare cases, when one person cannot teach all of the labs/discussions for a given Lecture, a second person is hired to teach the labs/discussions the first person couldn't. In these cases, there is 1 Instructor and 2 TAs.

There is separate compensation for being Instructor and TA, even if it is done by the same person. So for a given course, the same person can be paid $5000 to teach the lecture (as Instructor) and $1000 to teach the labs/discussions (as TA).
Or one person can be paid $5000 to teach the lecture (as Instructor) and another person $1000 to teach the labs/discussions (as TA).

Contracts:

One of the goals of this database is to help us generate contracts.
Each person gets a separate contract for each course the person is associated with.

If the same person is both the Instructor and "TA" in the same course, their contract would list the compensation for both of those roles.

If the same person teaches another course, they would receive a separate contract for each course they teach.

If one person teaches the lecture (Instructor) and another teaches labs/discussion (TA), each would receive a a separate contract. There is a different template for TAs who are just TAs, i.e., who do not teach the lecture.

And there is a different contract template for Readers, who are associated with a Lecture.

I have already figured out how to generate contracts from Access, but I am mentioning this here because I will need queries that organize the data in a way that allows it to be merged into contract templates in Word.

I hope this makes sense. Thank you.

 

[jzwp22]

Reviewing your goal and your structure, you should be able to create a query or series of queries to get the information you need for the contract end of things.

With that said, the next step is to create your forms. I typically base each form on a single table or a query based on a single table. You will need a main form/subform design with the main form being based on a table making up the one side of the one-to-many relationship while the subform would be based on the table that makes up the many side of the relationship. For the many-to-many relationships, the subform would be based on the junction table while the main form would be based on either of the two tables joined to the junction table. The choice is up to you as to which one you pick.