For the given database user requirements as follows: Each course has one teacher

Question

For the given database user requirements as follows:

Each course has one teacher.
Course has course name and course number. Teacher has name, identification number, and salary.
Only one course can meet in a room at one time.
Room has room number, capacity, building number.
A teacher can be in only one room at one time.
Each student has one grade in each course.
Student has name and identification number.
A student can be in only one room at one time.

a. Design a relational schema in the third normal form (3NF) according to the given user requirements.
b. For each designed relational schema, please give the primary key and foreign key(s).

c. Please list all the functional dependencies in each schema.

Explanation / Answer

teacher table:

   TeacherName, TeacherIdno, Salary

Course table:

   CourseName, CourseNo

Room table:

    roomnumber, capacity, building number

student table:

   StudentName, StudentId

Teacher_course table:

   TeacherIdno( refers to Course Teacher table), CourseNo( refers to Course table)

Student_Courses table:

   StudentId( refers to Course Student table), CourseNo( refers to Course Course table), grade

Teacher_Room table:

   TeacherIdno( refers to Course Teacher table), roomnumber( refers to Course Room table)

Room_Course table:

roomnumber( refers to Course Room table), CourseNo( refers to Course Course table)

------------------------------------------------------------------

Primary keys are marked in red color

Foreign keys are marked in dark red color

Last four tables are functionally dependt on first four tables

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---