Relation Schema in DBMS

Relation schema defines the design and structure of the relation like it consists of the relation name, set of attributes/field names/column names. every attribute would have an associated domain. 

There is a student named Geeks, she is pursuing B.Tech, in the 4th year, and belongs to IT department (department no. 1) and has roll number 1601347 She is proctored by Mrs. S Mohanty. If we want to represent this using databases we would have to create a student table with name, sex, degree, year, department, department number, roll number and proctor (adviser) as the attributes. 

student (rollNo, name, degree, year, sex, deptNo, advisor)

Note –
If we create a database, details of other students can also be recorded. 

Similarly, we have the IT Department, with department Id 1, having Mrs. Sujata Chakravarty as the head of department. And we can call the department on the number 0657 228662 . 

This and other departments can be represented by the department table, having department ID, name, hod and phone as attributes.

department (deptId, name, hod, phone)

The course that a student has selected has a courseid, course name, credit and department number. 

course (coursId, ename, credits, deptNo)

The professor would have an employee Id, name, sex, department no. and phone number. 

professor (empId, name, sex, startYear, deptNo, phone)

We can have another table named enrollment, which has roll no, courseId, semester, year and grade as the attributes. 

enrollment (rollNo, coursId, sem, year, grade)

Teaching can be another table, having employee id, course id, semester, year and classroom as attributes. 

teaching (empId, coursed, sem, year, Classroom)

When we start courses, there are some courses which another course that needs to be completed before starting the current course, so this can be represented by the Prerequisite table having prerequisite course and course id attributes. 

prerequisite (preReqCourse, courseId) 

The relations between them is represented through arrows in the following Relation diagram, 

1. This represents that the deptNo in student table table is same as deptId used in department table. deptNo in student table is a foreign key. It refers to deptId in department table. 
   
2. This represents that the advisor in student table is a foreign key. It refers to empId in professor table. 
   
3. This represents that the hod in department table is a foreign key. It refers to empId in professor table. 
   
4. This represents that the deptNo in course table table is same as deptId used in department table. deptNo in student table is a foreign key. It refers to deptId in department table. 
   
5. This represents that the rollNo in enrollment table is same as rollNo used in student table. 
   
6. This represents that the courseId in enrollment table is same as courseId used in course table. 
   
7. This represents that the courseId in teaching table is same as courseId used in course table. 
   
8. This represents that the empId in teaching table is same as empId used in professor table. 
   
9. This represents that preReqCourse in prerequisite table is a foreign key. It refers to courseId in course table. 
   
10. This represents that the deptNo in student table is same as deptId used in department table. 
    

Note –
startYear in professor table is same as year in student table