Every School has many teachers and many students. Each student is assigned to on
ID: 3697718 • Letter: E
Question
Every School has many teachers and many students. Each student is assigned to one school and each teacher works for one school only. Each teacher teaches more than one subject but a subject may be taught by more than one teacher. The database should store the number of hours a teacher spent teaching a subject. Information held on each teacher includes his/her tax file number, name (first and last), gender and qualifications. The data held on each subject includes subject title and type. Each student can study more than one subject and a subject may be studied by more than one student. Data held on each student includes the student number, name (first and last), gender and date of birth. Each school is managed by one of its teachers. The database should keep track of the date he/she started managing the school. Data stored on each school includes the school’s code, name, address (town, street and postcode) and phone.
1. Draw ERD with key attributes. You can use any notation to draw the ERD. Must include legend.
2. Data dictionary for the above scenario.
3. Implement the database and populate with sample data for every table.
Explanation / Answer
Entity
Attribute
Type
size
Key
School
school_id
Number
5
pk
address_id
Number
5
fk
name
varchar
20
principal
varchar
20
Address
address_id
number
5
pk
address
varchar
50
pincode
number
6
phone_no
number
10
Principal
school_id
number
5
pk
teacher_id
number
5
pk
joining_date
date
8
Teacher
teacher_id
number
5
pk
school_id
number
5
fk
first_name
varchar
20
last_name
varchar
20
hours_spent
number
50
tax
_file_number
number
4
gender
varchar
5
qualifications
varchar
50
Student
student_id
number
5
pk
first_name
varchar
20
last_name
varchar
20
gender
varchar
4
date_of_birth
date
8
class_id
number
4
fk
Classes
class_id
number
4
pk
class_name
varchar
20
subject_id
number
3
fk
teacher_id
number
5
fk
Subject
subject_id
number
3
pk
subject_title
varchar
10
subject_type
varchar
10
Entity
Attribute
Type
size
Key
School
school_id
Number
5
pk
address_id
Number
5
fk
name
varchar
20
principal
varchar
20
Address
address_id
number
5
pk
address
varchar
50
pincode
number
6
phone_no
number
10
Principal
school_id
number
5
pk
teacher_id
number
5
pk
joining_date
date
8
Teacher
teacher_id
number
5
pk
school_id
number
5
fk
first_name
varchar
20
last_name
varchar
20
hours_spent
number
50
tax
_file_number
number
4
gender
varchar
5
qualifications
varchar
50
Student
student_id
number
5
pk
first_name
varchar
20
last_name
varchar
20
gender
varchar
4
date_of_birth
date
8
class_id
number
4
fk
Classes
class_id
number
4
pk
class_name
varchar
20
subject_id
number
3
fk
teacher_id
number
5
fk
Subject
subject_id
number
3
pk
subject_title
varchar
10
subject_type
varchar
10
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.