Create sample tables for the information system to be implemented at Mzansi
High School. Include the following details for the sample database:
• Specify table names.
• Specify fields for each table (up to 4 fields per table).
• Specify the primary keys for each table.
• Specify foreign keys wherever applicable.
• Populate the tables with at least two records.
(20)
1. Table: Students
Fields:
- student_id (Primary key)
- student_name
- grade_level
- date_of_birth
Records:
- student_id: 001, student_name: John Smith, grade_level: 10, date_of_birth: 2005-05-10
- student_id: 002, student_name: Sarah Wilson, grade_level: 11, date_of_birth: 2004-08-20
2. Table: Teachers
Fields:
- teacher_id (Primary key)
- teacher_name
- subject_taught
- years_of_experience
Records:
- teacher_id: 101, teacher_name: Mr. Johnson, subject_taught: Math, years_of_experience: 8
- teacher_id: 102, teacher_name: Mrs. Adams, subject_taught: Science, years_of_experience: 10
3. Table: Courses
Fields:
- course_id (Primary key)
- course_name
- teacher_id (Foreign key referencing Teachers table)
- grade_level
Records:
- course_id: 201, course_name: Algebra 1, teacher_id: 101, grade_level: 9
- course_id: 202, course_name: Biology, teacher_id: 102, grade_level: 10
4. Table: Grades
Fields:
- grade_id (Primary key)
- student_id (Foreign key referencing Students table)
- course_id (Foreign key referencing Courses table)
- grade
Records:
- grade_id: 301, student_id: 001, course_id: 201, grade: A
- grade_id: 302, student_id: 002, course_id: 202, grade: B+