Moursey School Database Example
PROBLEM DEFINITION
The Client:
The client is Moursey School who require a database solution to store data about their students and the classes they take.
At the end of each school year, the timetable panel determines the teachers and classes for the following year. Students need to be entered into a home group and the subjects they wish to study for the following year. Lists are able to be produced to give to each teacher showing their classes and the students in the class.
Outcomes:
1. A data entry (input) to enter students into classes.
The administration staff would find the record of the class and then in the subform, enter in a student (from a list)
2. A list of students who do Year 12 Physics or any Information Technology subject in 2002, showing their names and which of these subjects they are doing.
The subject coordinator (teacher) is given this list to counsel students doing these subjects before they commence.
3. A list of students doing at least one Year 12 subject and the number of Year 12 subjects in which they are enrolled.
The year 12 coordinator will need to check that each student doing a year 12 subject is enrolled in the correct number of subjects.
4. A user selected class list showing students names and ages, the teacher and the room. The number of students in the class will be shown.
This list allows a teacher to have a printed copy of all students in their class and use it as a check list for attendance.
ANALYSIS
Outcome 1: A data entry (input) to enter students into classes.
Main form:
Data required:
Display: Class ID, Subject Name, Year, Year Level, Teacher, Room
From: Classes table, Subject table, Teacher table
Processing:
Where: All records found
Sort: Class ID
SubForm:
Data required:
Display: Student ID, First Name, Surname,
From: Students Table
Processing:
Where: Current = “yes”
Calculate: Name = [First name]+” “+[Surname]
Sort: Surname
Outcome 2: A List of Current Students doing Year 12 Physics or any Information Technology subject in 2002 showing which of these subjects they are doing.
Data required:
Display: Student, Year Level, Subject, Current, Class
From: Classes table, Subject table, Teacher table
Processing:
Where: Year level = 12 and Current = “yes” and Year level = 2002 and OR
Current= “yes” and year = 2002 and Class = [Information Technology]*
Sort: Student
Outcome 3: A list of students doing at least one Year 12 subject and the number of Year 12 subjects in which they are enrolled.
Data required:
Display: Teacher, Subject, Name, Year Level, Room, Student Name
From: Classes table, Subject table, Teacher table
Processing:
Where: Year =12 and current =yes
Calculate: Count of subject
Sort: Student
Outcome 4: A user selected class list showing students names and ages, the teacher and the room. The number of students in the class will be shown.
Data required:
Display: Teacher, Subject, Year Level, Room, Student
From: Teacher table, Subject Table, StudentClasses Table, Classes table, Student table
Processing:
Where: Class ID = [Parameter box]
Calculate: Teacher = [Title]+" "+Left([T Name],1)+". "+[T Surname]
Student = [Surname]+", "+[First Name]
Age = Int((Date()-[DOB])/365.2425)
Class Total = Count of Class ID
Sort: Sort Surname
DESIGN
Tables and Relationships
The Moursey school database will need to store data about the students, the classes that they attend and the subjects that they are studying. The database will also need to store data about the teachers and the rooms
The first source table will be Students. There is a record in the Students table for each student in the school. Each record will need to contain data about the student’s name, age and if they are a current student. The student’s address and other personal details will also be stored. The students Date of Birth will be stored and the age calculated from that. Each student is identified by a Student ID, this does not change from year to year.
The second source table will be Teachers. There is a record in the Teachers table for each teacher in the school. The database will store the teacher’s name, and address details. Each teacher is identified by a Teacher ID.
The third source table will be Subjects. There is a record in the Subjects table for each subject that is taught in the school. This will contain the name of the subject and the Year level. Each subject is identified by a Subject ID.
The fourth source table will be Classes. There is a record in the Classes table for each class in the school taught by a particular teacher and the subject that is taught. This will contain data about the year in which that class was taught and the room in which it was taught. This will allow the data to be stored over several years. Each class is identified by a class code, this changes every year.
Each class will have one teacher; however each teacher will teach many classes.
Each class will have one subject; however each subject will be taught in many classes.
Each class will have many students; each student will have many classes. This is a many-to-many relationship. This will be resolved by creating a transaction table, called StudentClasses, between the Students and Classes tables. The key from Students (Student ID) and the key from Classes (Class ID) will be added to this transaction table.
Table Relationship Diagram
Data Dictionaries
|
STUDENTS |
||||||||
|
FIELD |
DATA TYPE |
SIZE |
Description |
Example |
Validation rule |
Validation text |
Default value |
Drop down box? |
|
Student ID |
Short Text |
5 |
Key |
ceelk |
||||
|
First Name |
Short Text |
15 |
Kate |
|||||
|
Surname |
Short Text |
20 |
Ceely |
|||||
|
Street |
Short Text |
20 |
124 Brackly St |
|||||
|
Town/Suburb |
Short Text |
18 |
Beaumont |
Beaumont |
||||
|
Postcode |
Short Text |
4 |
5257 |
5257 |
||||
|
Phone |
Short Text |
15 |
08 83321122 |
|||||
|
DOB |
Date/Time |
Date of birth |
5/2/84 |
|||||
|
Current |
Yes/No |
0.125 |
yes |
yes |
||||
|
Gender |
Short Text |
1 |
F |
M or F |
Must enter M or F |
M |
yes |
This table contains data about students. Each student must have a unique ID. This will usually be the first 4 letters of their surname plus their first initial. Past students will still be on record, when a student leaves then Current will be changed to ‘No’.
|
SUBJECTS |
||||||||
|
FIELD |
DATA TYPE |
SIZE |
Description |
Example |
Validation rule |
Validation text |
Default value |
Drop down box? |
|
Subject ID |
Short Text |
5 |
Key |
2PHY2 |
||||
|
Subject |
Short Text |
30 |
Physics |
|||||
|
Year Level |
Number |
Byte |
12 |
>7 and <13 |
Please enter a valid year |
12 |
yes |
This table contains data about subjects. Each subject has a name and is identified by a particular syllabus. This syllabus may be taught over many years to many classes.
|
CLASSES |
|