Sql Query

Scenario 1 - Tracking Pupil Allocation for Primary Schools

A set of local primary schools needs a database to identify the allocation of pupils and teachers to a class. The school and location of the school needs to be identified. Each school has a total number of pupils. Each school has a number of pupils in each class, one class in each room, and two classes for each year. Each teacher teaches only one class. Each classroom must be identified by floor and area of wing location. Each student needs to be identified by name, address, sex and birthdate.

ER Diagram for the Above Scenario.

Class

Course Number

Name of the Course

Class

Course Number

Name of the Course

Teacher

Name of the Teacher

Teacher ID

Teacher

Name of the Teacher

Teacher ID

Class Room Number

Room Number

Block Number

Class Room Number

Room Number

Block Number

School

Name of the school

Location of the School

School

Name of the school

Location of the School

Year

Year of the course

Year

Year of the course

Student

Student ID

Name

Address

Sex

DOB

Phone number

Student

Student ID

Name

Address

Sex

DOB

Phone number

Attributes of the Entities:

Student ID:

Attributes of Student ID are

Name

Address

Phone number

DOB

Sex

Year:

Attribute of this year is Year of the course

Course ID:

Attributes of this Entity is

Course Number

Name of the Course

School ID:

Attributes of this Entity is

Name of the School

Location of the school

Class Room Number:

Attributes of this Entity is

Room Number

Block Number

Teacher:

Name of the teacher

Teacher ID

Relations between Entities:

* There is a one to many Relationship between School ID to Student ID

* There is a one to many Relationship between Student ID to Class.

* There is a one to many optional Relationship between Class ID to Room.

* There is a one to one Relationship between Class Room Number to Teacher...