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
FIELD	DATA TYPE	SIZE	Description	Example	Validation rule	Validation text	Default value	Drop down box?
Class ID	Short Text	6	Key	ENG12
Year	Number	Integer		2002	<2003	Not a valid year	2002
Subject ID	Short Text	5	Link to Subject	2PHY2				yes
Room	Short Text	3		T15
Teacher ID	Short Text	3	Link to Teachers	MNo				yes

 

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
FIELD	DATA TYPE	SIZE	Description	Example	Validation rule	Validation text	Default value	Drop down box?
Teacher ID	Short Text	3	Key	Mno
Title	Short Text	4		Mrs				yes
T Name	Short Text	15		Margaret
T Surname	Short Text	20		Northcote

 

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
FIELD	DATA TYPE		Description	Example	Validation rule	Validation text	Default value	Drop down box?
Student ID	Short Text	5	Double Key and Link	ceelk
Class ID	Short Text	6	Double Key and Link	ENG12

 

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 tested	Data to be Used	Expected Result	Confirmation 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=F	Age is calculated from the DOB field. Current is set to “yes” by default.	Worked as expected.
Enter a student into a class	Class ID =eg01a, Student ID=ceelk	New 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=ceelk	Error 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=it02	Report 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 ID	First Name	Surname	Street	Town/Suburb	Postcode	Phone	DOB	Gender	Current
breet	Tony	Breen	3 Longer St	Beaumont	5257	0414234566	18-Sep-84	M	Yes
ceelk	Kate	Ceely	124 Brackly St	Beaumont	5257	08 83321122	05-Feb-84	F	Yes
conne	Elizabeth	Conners	34 Grean St	Beaumont	5257	83574635	06-Jun-84	F	Yes
conrb	Billy	Conrads	45 Freel Rd	Highgate	5258	83442856	23-Nov-85	M	Yes
johnb	Billy	Johnson	1a Dreast Drv	Beaumont	5257	83645454	12-Feb-84	M	Yes
keerh	Henry	Keer	71 Traders Rd	Magill	5267	0414345243	02-Aug-84	M	Yes
planj	Jane	Plane	123 Ordins St	Beaumont	5257	83245667	18-Jul-85	F	Yes
reads	Susan	Ready	67 Underert Rd	Beaumont	5257	83452522	16-Mar-84	F	Yes
sittj	John	Sitter	12 Ready St	Beaumont	5257	83546528	12-Apr-82	M	No
smitb	Brad	Smith	67 Trenter Drv	Magill	5264	83446677	22-Oct-84	M	No
smitj	Joanne	Smith	12 Pretty St	Beaumont	5257	83425344	16-Mar-84	F	Yes
weerj	Joanne	Weer	23 Trimm Rd	Beaumont	5257	83465728	23-Apr-82	F	No

 

° Using the Student ID of these four students find their names.

 

 

 

STUDENTCLASSES – table

Student ID	Class ID
breet	eg01a
keerh	eg01a
smitb	eg01a
ceelk	eg02
conrb	eg02
keerh	it01
sittj	it01
breet	it02
conne	it02
keerh	it02
reads	it02
ceelk	it02s1
conrb	it02s1
smitb	it02s1
reads	its01
smitj	its01
weerj	its01
johnb	its02b
keerh	its02b
planj	its02b
johnb	phy02
keerh	phy02
planj	phy02
ceelk	phy02a
conrb	phy02a
planj	phy02a

 

¯ Using the Class ID of it02 find all the Student IDs.

There are 4 records.

 

 

 

TEACHERS – table

Teacher ID	Title	T Name	T Surname
Fbl	Miss	Frederina	Bloggs
Jcu	Mr	Jim	Cundy
Mno	Mrs	Margaret	Northcote
Pmi	Mr	Peter	Mitchell

 

­ Using the Teacher ID of Pmi find the details of this teacher.

 

CLASSES – table

Class ID	Room	Teacher ID	Subject ID	Year
it02s1	T5	Jcu	1CMP2	2002
eg01a	C2	Fbl	1ENG2	2001
eg02	C2	Fbl	1ENG2	2002
phy02a	T3	Jcu	1PHY2	2002
it01	T5	Mno	2ITT2	2001
it02	T4	Pmi	2ITT2	2002
its01	T5	Jcu	2ITY2	2001
its02b	T5	Jcu	2ITY2	2002
phy02	T4	Mno	2PHY2	2002

 

¬ Starting with the selection of Class ID it02

 

SUBJECTS – table

Subject ID	Subject Name	Year level
1CMP2	Computing Studies	11
1ENG2	English	11
1PHY2	Physics	11
2ITT2	Information Technology	12
2ITY2	Information Technology Studies	12
2PHY2	Physics	12

 

® 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 ID	First Name	Surname	Street	Town/Suburb	Postcode	Phone	DOB	Gender	Current
breet	Tony	Breen	3 Longer St	Beaumont	5257	0414234566	18-Sep-84	M	Yes
ceelk	Kate	Ceely	124 Brackly St	Beaumont	5257	08 83321122	05-Feb-84	F	Yes
conne	Elizabeth	Conners	34 Grean St	Beaumont	5257	83574635	06-Jun-84	F	Yes
conrb	Billy	Conrads	45 Freel Rd	Highgate	5258	83442856	23-Nov-85	M	Yes
johnb	Billy	Johnson	1a Dreast Drv	Beaumont	5257	83645454	12-Feb-84	M	Yes
keerh	Henry	Keer	71 Traders Rd	Magill	5267	0414345243	02-Aug-84	M	Yes
planj	Jane	Plane	123 Ordins St	Beaumont	5257	83245667	18-Jul-85	F	Yes
reads	Susan	Ready	67 Underert Rd	Beaumont	5257	83452522	16-Mar-84	F	Yes
sittj	John	Sitter	12 Ready St	Beaumont	5257	83546528	12-Apr-82	M	No
smitb	Brad	Smith	67 Trenter Drv	Magill	5264	83446677	22-Oct-84	M	No
smitj	Joanne	Smith	12 Pretty St	Beaumont	5257	83425344	16-Mar-84	F	Yes
weerj	Joanne	Weer	23 Trimm Rd	Beaumont	5257	83465728	23-Apr-82	F	No

 

 

 

 

STUDENTCLASSES – table

Student ID	Class ID
breet	eg01a
keerh	eg01a
smitb	eg01a
ceelk	eg02
conrb	eg02
keerh	it01
sittj	it01
breet	it02
conne	it02
keerh	it02
reads	it02
ceelk	it02s1
conrb	it02s1
smitb	it02s1
reads	its01
smitj	its01
weerj	its01
johnb	its02b
keerh	its02b
planj	its02b
johnb	phy02
keerh	phy02
planj	phy02
ceelk	phy02a
conrb	phy02a
planj	phy02a

 

¯ Find the records which have the students from ¬ in the classes from ­, but not phy02a.

There are 9 records.

 

 

TEACHERS – table

Teacher ID	Title	T Name	T Surname
Fbl	Miss	Frederina	Bloggs
Jcu	Mr	Jim	Cundy
Mno	Mrs	Margaret	Northcote
Pmi	Mr	Peter	Mitchell

 

CLASSES – table

Class ID	Room	Teacher ID	Subject ID	Year
it02s1	T5	Jcu	1CMP2	2002
eg01a	C2	Fbl	1ENG2	2001
eg02	C2	Fbl	1ENG2	2002
phy02a	T3	Jcu	1PHY2	2002
it01	T5	Mno	2ITT2	2001
it02	T4	Pmi	2ITT2	2002
its01	T5	Jcu	2ITY2	2001
its02b	T5	Jcu	2ITY2	2002
phy02	T4	Mno	2PHY2	2002

­ Find the class ID’s beginning with it or phy in 2002. Two of these are Physics and must be checked.

 

SUBJECTS – table

Subject ID	Subject Name	Year level
1CMP2	Computing Studies	11
1ENG2	English	11
1PHY2	Physics	11
2ITT2	Information Technology	12
2ITY2	Information Technology Studies	12
2PHY2	Physics	12

 

® 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 ID	First Name	Surname	Street	Town/Suburb	Postcode	Phone	DOB	Gender	Current
breet	Tony	Breen	3 Longer St	Beaumont	5257	0414234566	18-Sep-84	M	Yes
ceelk	Kate	Ceely	124 Brackly St	Beaumont	5257	08 83321122	05-Feb-84	F	Yes
conne	Elizabeth	Conners	34 Grean St	Beaumont	5257	83574635	06-Jun-84	F	Yes
conrb	Billy	Conrads	45 Freel Rd	Highgate	5258	83442856	23-Nov-85	M	Yes
johnb	Billy	Johnson	1a Dreast Drv	Beaumont	5257	83645454	12-Feb-84	M	Yes
keerh	Henry	Keer	71 Traders Rd	Magill	5267	0414345243	02-Aug-84	M	Yes
planj	Jane	Plane	123 Ordins St	Beaumont	5257	83245667	18-Jul-85	F	Yes
reads	Susan	Ready	67 Underert Rd	Beaumont	5257	83452522	16-Mar-84	F	Yes
sittj	John	Sitter	12 Ready St	Beaumont	5257	83546528	12-Apr-82	M	No
smitb	Brad	Smith	67 Trenter Drv	Magill	5264	83446677	22-Oct-84	M	No
smitj	Joanne	Smith	12 Pretty St	Beaumont	5257	83425344	16-Mar-84	F	Yes
weerj	Joanne	Weer	23 Trimm Rd	Beaumont	5257	83465728	23-Apr-82	F	No

° 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 ID	Class ID
breet	eg01a
keerh	eg01a
smitb	eg01a
ceelk	eg02
conrb	eg02
keerh	it01
sittj	it01
breet	it02
conne	it02
keerh	it02
reads	it02
ceelk	it02s1
conrb	it02s1
smitb	it02s1
reads	its01
smitj	its01
weerj	its01
johnb	its02b
keerh	its02b
planj	its02b
johnb	phy02
keerh	phy02
planj	phy02
ceelk	phy02a
conrb	phy02a
planj	phy02a

¯ Using the Class ID of its02b find all the Student IDs

There are 3 students

 

TEACHERS – table

Teacher ID	Title	T Name	T Surname
Fbl	Miss	Frederina	Bloggs
Jcu	Mr	Jim	Cundy
Mno	Mrs	Margaret	Northcote
Pmi	Mr	Peter	Mitchell

­ Using the Teacher ID of Jcu find the details of this teacher

 

CLASSES – table

Class ID	Room	Teacher ID	Subject ID	Year
it02s1	T5	Jcu	1CMP2	2002
eg01a	C2	Fbl	1ENG2	2001
eg02	C2	Fbl	1ENG2	2002
phy02a	T3	Jcu	1PHY2	2002
it01	T5	Mno	2ITT2	2001
it02	T4	Pmi	2ITT2	2002
its01	T5	Jcu	2ITY2	2001
its02b	T5	Jcu	2ITY2	2002
phy02	T4	Mno	2PHY2	2002

¬ Starting with the Class ID of its02b

 

SUBJECTS – table

Subject ID	Subject Name	Year level
1CMP2	Computing Studies	11
1ENG2	English	11
1PHY2	Physics	11
2ITT2	Information Technology	12
2ITY2	Information Technology Studies	12
2PHY2	Physics	12

 

® 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.

最先出自天才代写 database代写

合作：幽灵代写