解析Database留学生、Database编程讲解、辅导Database表设计、Database结构设计报告辅导留学生
辅导Python编程|解析C/
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
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.