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 Class= Physics 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
FIELDDATA TYPESIZEDescriptionExampleValidation ruleValidation textDefault valueDrop down box?
Student IDShort Text5Keyceelk
First NameShort Text15Kate
SurnameShort Text20Ceely
StreetShort Text20124 Brackly St
Town/SuburbShort Text18BeaumontBeaumont
PostcodeShort Text452575257
PhoneShort Text1508 83321122
DOBDate/TimeDate of birth5/2/84
CurrentYes/No0.125yesyes
GenderShort Text1FM or FMust enter M or FMyes
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
FIELDDATA TYPESIZEDescriptionExampleValidation ruleValidation textDefault valueDrop down box?
Subject IDShort Text5Key2PHY2
SubjectShort Text30Physics
Year LevelNumberByte12>7 and <13Please enter a valid year12yes
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
FIELDDATA TYPESIZEDescriptionExampleValidation ruleValidation textDefault valueDrop down box?
Class IDShort Text6KeyENG12
YearNumberInteger2002<2003Not a valid year2002
Subject IDShort Text5Link to Subject2PHY2yes
RoomShort Text3T15
Teacher IDShort Text3Link to TeachersMNoyes
This table contains data about classes. Each class must have a unique ID even if two classes are the same subject and the same teacher they must have different ID’s.
TEACHERS
FIELDDATA TYPESIZEDescriptionExampleValidation ruleValidation textDefault valueDrop down box?
Teacher IDShort Text3KeyMno
TitleShort Text4Mrsyes
T NameShort Text15Margaret
T SurnameShort Text20Northcote
This table contains data about teachers. Each teacher must have a unique ID. This will generally be their first initial followed by the first two letters of their surname. Only current teachers will be stored.
STUDENT-CLASSES
FIELDDATA TYPEDescriptionExampleValidation ruleValidation textDefault valueDrop down box?
Student IDShort Text5Double Key and Linkceelk
Class IDShort Text6Double Key and LinkENG12
This is the transaction table between Students and Classes. A student can have many classes and a class will have many students. This involves bringing the keys from students and classes into the one table. The option of a double key will prevent a student being entered in the same class twice. However should a student repeat a year then the class next year will have a new Class ID.
DEVELOPMENT & VALIDATION
Validation Plan
Validation Plan
Element to be testedData to be UsedExpected ResultConfirmation of success
Enter a new student Student ID= weerj
Firstname=Joanne
Surname=Weer
Street=23 Trimm Rd
Town/Suburb=Beaumont
Postcode=5257
Phone=83465728
DOB=23/4/95
Gender=FAge is calculated from the DOB field.
Current is set to “yes” by default.Worked as expected.
Enter a student into a classClass ID =eg01a, Student ID=ceelkNew record created in StudentClassess table.Worked as expected.
Enter a student into a class they have already been entered into.Class ID =eg01a, Student ID=ceelkError message indicating that a duplicate value has been entered.Worked as expected.
Produce a list of current students do Year 12 Physics or any Information Technology subject in 2002 showing which of these subjects they are doing.
Report appears showing student names with the subject name and year level. Worked as expected
A list of students doing at least one Year 12 subject and the number of Year 12 subjects in which they are enrolled.
Report appears of all students doing a year 12 subject and the number of subjects is calculated for each student.Worked as expected
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.
Class ID=it02Report appears. Age is calculated and the total number of students is shown at the bottom.Worked as expected.
Validation of Outcomes
First Outcome - A data entry form to enter current students into classes.
The Subject Name and Year Level come from the SUBJECTS table.
The Teacher comes from the TEACHERS table and is a combination of the three fields, Title, T Name and T Surname.
The Class ID and Year come from the CLASSES table.
The Student ID, First Name and Surname come from the STUDENTS table.
These are linked through the STUDENTCLASSES table and this is shown in the following validation.
STUDENTS - table
Student IDFirst NameSurnameStreetTown/SuburbPostcodePhoneDOBGenderCurrent
breetTonyBreen3 Longer StBeaumont5257041423456618-Sep-84MYes
ceelkKateCeely124 Brackly StBeaumont525708 8332112205-Feb-84FYes
conneElizabethConners34 Grean StBeaumont52578357463506-Jun-84FYes
conrbBillyConrads45 Freel RdHighgate52588344285623-Nov-85MYes
johnbBillyJohnson1a Dreast DrvBeaumont52578364545412-Feb-84MYes
keerhHenryKeer71 Traders RdMagill5267041434524302-Aug-84MYes
planjJanePlane123 Ordins StBeaumont52578324566718-Jul-85FYes
readsSusanReady67 Underert RdBeaumont52578345252216-Mar-84FYes
sittjJohnSitter12 Ready StBeaumont52578354652812-Apr-82MNo
smitbBradSmith67 Trenter DrvMagill52648344667722-Oct-84MNo
smitjJoanneSmith12 Pretty StBeaumont52578342534416-Mar-84FYes
weerjJoanneWeer23 Trimm RdBeaumont52578346572823-Apr-82FNo
Using the Student ID of these four students find their names.
STUDENTCLASSES - table
Student IDClass ID
breeteg01a
keerheg01a
smitbeg01a
ceelkeg02
conrbeg02
keerhit01
sittjit01
breetit02
conneit02
keerhit02
readsit02
ceelkit02s1
conrbit02s1
smitbit02s1
readsits01
smitjits01
weerjits01
johnbits02b
keerhits02b
planjits02b
johnbphy02
keerhphy02
planjphy02
ceelkphy02a
conrbphy02a
planjphy02a
Using the Class ID of it02 find all the Student IDs.
There are 4 records.
TEACHERS - table
Teacher IDTitleT NameT Surname
FblMissFrederinaBloggs
JcuMrJimCundy
MnoMrsMargaretNorthcote
PmiMrPeterMitchell
Using the Teacher ID of Pmi find the details of this teacher.
CLASSES - table
Class IDRoomTeacher IDSubject IDYear
it02s1T5Jcu1CMP22002
eg01aC2Fbl1ENG22001
eg02C2Fbl1ENG22002
phy02aT3Jcu1PHY22002
it01T5Mno2ITT22001
it02T4Pmi2ITT22002
its01T5Jcu2ITY22001
its02bT5Jcu2ITY22002
phy02T4Mno2PHY22002
Starting with the selection of Class ID it02
SUBJECTS - table
Subject IDSubject NameYear level
1CMP2Computing Studies11
1ENG2English11
1PHY2Physics11
2ITT2Information Technology12
2ITY2Information Technology Studies12
2PHY2Physics12
Using the Subject ID of 2ITT2 find the details of the subject.
Second Outcome - An alphabetical list of all current students that do Year 12 Physics or any Information Technology subject in 2002, showing their names and which of these subjects they are doing.
This report is consistent with the validation.
There were 9 records selected. All of the subjects shown are either Information Technology or Yr 12 Physics.
The students are in alphabetical order.
The data is grouped correctly.
STUDENTS - table
Student IDFirst NameSurnameStreetTown/SuburbPostcodePhoneDOBGenderCurrent
breetTonyBreen3 Longer StBeaumont5257041423456618-Sep-84MYes
ceelkKateCeely124 Brackly StBeaumont525708 8332112205-Feb-84FYes
conneElizabethConners34 Grean StBeaumont52578357463506-Jun-84FYes
conrbBillyConrads45 Freel RdHighgate52588344285623-Nov-85MYes
johnbBillyJohnson1a Dreast DrvBeaumont52578364545412-Feb-84MYes
keerhHenryKeer71 Traders RdMagill5267041434524302-Aug-84MYes
planjJanePlane123 Ordins StBeaumont52578324566718-Jul-85FYes
readsSusanReady67 Underert RdBeaumont52578345252216-Mar-84FYes
sittjJohnSitter12 Ready StBeaumont52578354652812-Apr-82MNo
smitbBradSmith67 Trenter DrvMagill52648344667722-Oct-84MNo
smitjJoanneSmith12 Pretty StBeaumont52578342534416-Mar-84FYes
weerjJoanneWeer23 Trimm RdBeaumont52578346572823-Apr-82FNo
STUDENTCLASSES - table
Student IDClass ID
breeteg01a
keerheg01a
smitbeg01a
ceelkeg02
conrbeg02
keerhit01
sittjit01
breetit02
conneit02
keerhit02
readsit02
ceelkit02s1
conrbit02s1
smitbit02s1
readsits01
smitjits01
weerjits01
johnbits02b
keerhits02b
planjits02b
johnbphy02
keerhphy02
planjphy02
ceelkphy02a
conrbphy02a
planjphy02a
Find the records which have the students from in the classes from , but not phy02a.
There are 9 records.
TEACHERS - table
Teacher IDTitleT NameT Surname
FblMissFrederinaBloggs
JcuMrJimCundy
MnoMrsMargaretNorthcote
PmiMrPeterMitchell
CLASSES - table
Class IDRoomTeacher IDSubject IDYear
it02s1T5Jcu1CMP22002
eg01aC2Fbl1ENG22001
eg02C2Fbl1ENG22002
phy02aT3Jcu1PHY22002
it01T5Mno2ITT22001
it02T4Pmi2ITT22002
its01T5Jcu2ITY22001
its02bT5Jcu2ITY22002
phy02T4Mno2PHY22002
Find the class ID’s beginning with it or phy in 2002. Two of these are Physics and must be checked.
SUBJECTS - table
Subject IDSubject NameYear level
1CMP2Computing Studies11
1ENG2English11
1PHY2Physics11
2ITT2Information Technology12
2ITY2Information Technology Studies12
2PHY2Physics12
Find the subject names of these classes and check that the Physics classes are Year 12. One of the classes, phy02a is not Year 12.
Query Design
An alphabetical list of all current students who are 17, and do Year 12 Physics or any Information Technology subject in 2002, showing their names and which of these subjects they are doing.
12 Physics or IT query
The calculation of Age has been explained earlier
The word OR in the Outcome dictates the need for two lines of the Criteria.
Report Design
An alphabetical list of all current students who are 17, and do Year 12 Physics or any Information Technology subject in 2002, showing their names and which of these subjects they are doing.
12 Physics or IT report
The report is grouped by Surname with the detail of the subject studied beneath each name.
It also sorts by Surname though this cannot be seen here.
Fourth Outcome - 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 report is consistent with the validation.
I have checked that the ages are correct as of January 2002.
The class total of 3 is correct.
STUDENTS - table
Student IDFirst NameSurnameStreetTown/SuburbPostcodePhoneDOBGenderCurrent
breetTonyBreen3 Longer StBeaumont5257041423456618-Sep-84MYes
ceelkKateCeely124 Brackly StBeaumont525708 8332112205-Feb-84FYes
conneElizabethConners34 Grean StBeaumont52578357463506-Jun-84FYes
conrbBillyConrads45 Freel RdHighgate52588344285623-Nov-85MYes
johnbBillyJohnson1a Dreast DrvBeaumont52578364545412-Feb-84MYes
keerhHenryKeer71 Traders RdMagill5267041434524302-Aug-84MYes
planjJanePlane123 Ordins StBeaumont52578324566718-Jul-85FYes
readsSusanReady67 Underert RdBeaumont52578345252216-Mar-84FYes
sittjJohnSitter12 Ready StBeaumont52578354652812-Apr-82MNo
smitbBradSmith67 Trenter DrvMagill52648344667722-Oct-84MNo
smitjJoanneSmith12 Pretty StBeaumont52578342534416-Mar-84FYes
weerjJoanneWeer23 Trimm RdBeaumont52578346572823-Apr-82FNo
Using the Student ID of these 3 students find their names. From their DOB calculate their age as of January 2002. Billy and Henry are 17 and Jane is 16.
STUDENTCLASSES - table
Student IDClass ID
breeteg01a
keerheg01a
smitbeg01a
ceelkeg02
conrbeg02
keerhit01
sittjit01
breetit02
conneit02
keerhit02
readsit02
ceelkit02s1
conrbit02s1
smitbit02s1
readsits01
smitjits01
weerjits01
johnbits02b
keerhits02b
planjits02b
johnbphy02
keerhphy02
planjphy02
ceelkphy02a
conrbphy02a
planjphy02a
Using the Class ID of its02b find all the Student IDs
There are 3 students
TEACHERS - table
Teacher IDTitleT NameT Surname
FblMissFrederinaBloggs
JcuMrJimCundy
MnoMrsMargaretNorthcote
PmiMrPeterMitchell
Using the Teacher ID of Jcu find the details of this teacher
CLASSES - table
Class IDRoomTeacher IDSubject IDYear
it02s1T5Jcu1CMP22002
eg01aC2Fbl1ENG22001
eg02C2Fbl1ENG22002
phy02aT3Jcu1PHY22002
it01T5Mno2ITT22001
it02T4Pmi2ITT22002
its01T5Jcu2ITY22001
its02bT5Jcu2ITY22002
phy02T4Mno2PHY22002
Starting with the Class ID of its02b
SUBJECTS - table
Subject IDSubject NameYear level
1CMP2Computing Studies11
1ENG2English11
1PHY2Physics11
2ITT2Information Technology12
2ITY2Information Technology Studies12
2PHY2Physics12
Using The Subject ID of 2ITY2 find the details of this subject
Query Design
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.
Class List Query
There is a combo box called ‘Class’ which is on the Menu Form. It is used by the user to select the class needed for the Class List.
Student: [Surname]+", "+[First Name] Connects the Surname and First Name of the student. The new field called Student displays the entire name of the student.
Teacher: [Title]+" "+Left([T Name],1)+". "+[T Surname] Connects the Title to the left-hand letter of T Name (initial), with the T Surname. The new field called Teacher is a better way to display the teachers name.
Age: Int((Date()-[DOB])/365.2425) Age is calculated by subtracting the Date of Birth (DOB) from today’s date (Date()). This is in days and is divided by 365.2425 to get years. The function Int() is used to only show the whole number of years.
Report Design
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.
Class List Report
The information about Teacher, Subject, Year and Room is constant for one class and consequently appears in the Report Header.
=Count([age]) Is used to count the number of records in the field Age.
EVALUATION
The solution is very effective in its design and implementation. Each of the report is able to be displayed by selecting a button from the Outcomes in the Menu. The “Class List” report is produced by selecting from the list of classes from the combo box. The menu appears when the file is opened and the screen is maximised on opening.
The Student/Classes Form could be more user friendly by containing combo box of the classes to choose from or by providing a search option to locate the class required. Student’s photos could be included in the students form to help identify students when entering in their data. Parameter values could be used for the outcomes so that the user can enter the class and year level of the classes they wish to print. A final grade for each student completing a subject could be included so that it can be entered at the end of the year.
版权所有：编程辅导网 2021 All Rights Reserved 联系方式：QQ:99515681 微信：codinghelp 电子信箱：99515681@qq.com
免责声明：本站部分内容从网络整理而来，只供参考！如有版权问题可联系本站删除。
