Example of Relation Schema

There is a student named Geeks, she is pursuing B.Tech, in the 4th year, and belongs to the IT department (department no. 1) and has roll number 1601347 Mrs. S Mohanty proctors her. 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 Table

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

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

Department Table

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)

Course Table

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

course (coursId, ename, credits, deptNo)

Professor Table

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

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

Enrollment Table

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 Table

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

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

Prerequisite Table

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, 

• This represents that the deptNo in student table is same as deptId used in department table. deptNo in student table is a foreign key. It refers to deptId in department table. 
• This represents that the advisor in student table is a foreign key. It refers to empId in professor table. 
• This represents that the hod in department table is a foreign key. It refers to empId in professor table. 
• 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. 
• This represents that the rollNo in enrollment table is same as rollNo used in student table. 
• This represents that the courseId in enrollment table is same as courseId used in course table. 
• This represents that the courseId in teaching table is same as courseId used in course table. 
• This represents that the empId in teaching table is same as empId used in professor table. 
• This represents that preReqCourse in prerequisite table is a foreign key. It refers to courseId in course table. 
• 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

Relation schema defines the design and structure of the relation or table in the database. It is the way of representation of relation states in such a way that every relation database state fulfills the integrity constraints set (Like Primary key, Foreign Key, Not null, Unique constraints) on a relational schema.

It consists of the relation name, set of attributes/field names/column names. every attribute would have an associated domain. In this article, we will discuss relational schema in detail along with proper examples.