1

I am trying to set up a basic Student's database in access. The main tables that I am using are Courses, Students, Staff and Modules.

Just to clarify a bit about my terminology:

  • My course is actually a University. (i.e. Economics Course)
  • My Modules are the actual courses referring to each University. (i,e, Economics Course include modules such as Maths, Statistics, Econometrics etc.).
  • My Staff are the teachers.
  • A module includes exactly 12 lectures.
  • A module is being offered in a specific semester(6 month period), A=Sept-Feb and B=Mar-Aug. One module can be offered only in one out of two semesters.

The relationships that I want to use are:

  1. A course has many students. (one-to-many)
  2. A course has many modules. (one-to-many)
  3. Maximum 1 staff member can lead 1 course. I need to know the date he got leadership of this course (mgrstartdate). (one-to-one) Staff can have only 1 specialization (i.e. John Smith is Mathematician) Staff can have many qualifications (i.e. "10 years previous experience" and "published articles")
  4. A staff member teaches many modules. (one-to-many)
  5. A student can register(undertake) in many modules. (many-to-many) In this case i need to know date of registration and the final grade(performance).
  6. A student can attend many modules. (many-to-many) In this case i need to get attendance date, attendance(yes/no) and time of arrival. (Time of arrival is the reason i am tracking attendance and not absence)
  7. A module includes 12 lectures. (one-to-many) A module is being offered in a specific semester(6 month period), A =Sept-Feb and B=Mar-Aug. One module can be offered only in one out of two semesters.

So here follows my tables and their relationships preview: Access Relationships preview

Does it look like as described on the relationships above? Is it functional to record student's performance, attendance and arrival time? I used Lecture table to keep track date/time for each module. Is that needed or i can just move all the attributed of lecture table into the module table?

Mahendra Gunawardena
  • 1,956
  • 5
  • 26
  • 45
SAk Panagiotis
  • 11
  • 2

1 Answers1

0

Looks mostly good to me.

  1. A staff member teaches many modules. (one-to-many)

It would surprise me if every module can only be teached by exactly one teacher. Shouldn't this be many-to-many?

I would attach Attends to Lecture instead of Module. Then you don't need the date again, and can compare Arrivaltime with l_time.
Although I doubt the practicality of that table. Is really someone going to record all attendances in such detail?

Are the indented fields in Module and Staff multi-valued fields? Most people advise against using them, e.g. Multivalued Fields a Good Idea? . Better put these into additional tables with one-to-many relations.

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80
  • Apparently, the relationship staff-module is many-to-many. However i will probably do what you said about attaching Attends to Lecture, In Lecture i have already replaced dateattended-attendance-arrival time with just 1 entry of date/time type and formal general type. The only multi-valued fields are specializations and qualifications that i already track them in separated tables. My current problem as i was entering data with this model it seems to be an integrity issue. – SAk Panagiotis Jul 05 '16 at 07:45
  • My current problem as i was entering data with this model it seems to be an integrity issue. -I registered students in courses. (Course-Student: one-to-many) -I registered modules in courses (Module - Course: one-to-many) -But when i am going to register modules on student it allows me to make any combinations without restrictions from the previous relationships. (Student-Module: many-to-many) Any clue why this is happening? It should be against my integrity rules although i use lookup for std_matericNo and m_code in table Undertakes. – SAk Panagiotis Jul 05 '16 at 07:50
  • With the current rules, there is no reason for the database to prevent that. If you want to enforce it, you must add `c_title` to Undertakes and both joins. Or do it in the frontend, by only listing modules with the same `c_title` as the current student. – Andre Jul 05 '16 at 08:03
  • Add c_title as a third "primary key" on table undertakes and attach it with course? In Students and Modules table already exists and is attached. – SAk Panagiotis Jul 05 '16 at 18:04