当前位置:天才代写 > Essay代写,essay代写价格便宜机构-靠谱推荐 > 计算机代写 Database数据库IT项目代写

计算机代写 Database数据库IT项目代写

2018-09-29 08:00 星期六 所属: Essay代写,essay代写价格便宜机构-靠谱推荐 浏览:776

计算机代写 At the end of each school year, the timetable panel determines the teachers and classes for the following year.

 

计算机代写
计算机代写

 

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.

最先出自天才代写 database代写
合作:幽灵代写
 

天才代写-代写联系方式