当前位置 |首页 > 作业代写 > Essay代写栏目 >
分享这个代写网站给同学或者朋友吧!

 

 image.png

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

 

 

image.png

 

 


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.

 

 

image.png 

 

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.

 image.png

 

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

image.png 

 

 

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

image.png 

 

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.

 image.png


 

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

image.png 

 

                           

 

 

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

image.png 

 

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.


代写