Question Help with basic structure please ...

[bfgweb]

Okay here I go, holding my hands up first and confessing to being totally at sea when trying to initially structure a database. Give me the task of querying, creating forms and generating reports I love it but the initial stages of normalising etc just drives me insane ...

I am trying to set up a database consisting of school student details; the usual sname, fname, dob etc

Each student will be taking at least 4 different UNITS of a course, eg Wordprocessing, graphics, spreadsheets and email. Each course consists of several tasks (dated) followed by an exam eg first unit WordProcessing:

WP, Task1(date), T2(date), T3, T4, ExamAttempt1(date), ExamAttempt2, PassDate

Second Unit Spreadsheets:
SP, T1, T2, T3, T4, ExamAttempt1, ExamAttempt2, PassDate etc

I also want to record end of year levels for all students across their various subjects eg Tom Smith - English Level 5, Maths Level 6, History Level 4, Geography Level 5, etc.

I would really appreciate someone helping me get my sanity back and show me that this is much simpler than i'm making it out to be.....

 

[recyan]

Till some one comes along, just a few thoughts, which you can clarify :

STUDENT
COURSE
UNIT
TASKS
EXAMS


One STUDENT can take One COURSE only.
One STUDENT can take many COURSES at the same TIME or over a period of TIME.

One COURSE can consist of only 4 UNITS.
One COURSE can consist of minimum 1 to many UNITS.

The TASK is essentially related to the UNIT of the COURSE that the STUDENT has enrolled for and not directly to the COURSE.
One UNIT can have 0 TASKS or many TASKS.
One UNIT can have minimum 1 or many TASKS.

The EXAM is essentially related to the UNIT of the COURSE that the STUDENT has enrolled for and not directly to the COURSE.
One UNIT can have 0 EXAMS or many EXAMS.
One UNIT can have minimum 1 or many EXAMS.

Thanks

 

[bfgweb]

Hi Recyan,

Thanks for quick reply, much appreciated.

• One STUDENT can take many COURSES over a period of TIME.
• One COURSE can consist of minimum 1 to many UNITS
• The TASK is essentially related to the UNIT of the COURSE
• One UNIT can have 0 TASKS or many TASKS
• The EXAM is essentially related to the UNIT of the COURSE
• One UNIT can have 0 EXAMS or many EXAMS

I take it then that I should construct 5 tables each one containing data relevant to each of the entities listed?

 

[recyan]

Till some one comes along, a few thoughts :

tblStudents
StudentID - PK

tblCourses
CourseID - PK

tblUnits
UnitID - PK

tblCoursesUnits
CourseUnitID - PK
CourseID - FK
UnitID - FK

tblStudentsCourseEnrollment
CourseEnrollmentID - PK
StudentID - FK
CourseID - FK
EnrollmentDate

tblStudentsCourseUnits
StudentsCourseUnitsID - PK
CourseEnrollmentID - FK
CourseUnitID - FK

tblStudentsTasks
StudentsTasksID - PK
StudentsCourseUnitsID - FK

tblStudentsExams
StudentsExamsID - PK
StudentsCourseUnitsID - FK

In addition, perhaps

tblTasks
related to tblUnits
& in turn
FK in
tblStudentsTasks

tblExams
related to tblUnits
& in turn
FK in
tblStudentsExams


Also check out the links provided by jdraw in
http://www.access-programmers.co.uk/forums/showthread.php?t=229568

Thanks

 

[bfgweb]

Thanks for the guidance Recyan. Will work on it over next couple of days and let you know how I get on .

Your time is much appreciated.