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)
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,
- 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.
- 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 preReqiusite 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.
startYear in professor table is same as year in student table
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.
- Schema Integration in DBMS
- Strategies for Schema design in DBMS
- Difference between Schema and Instance in DBMS
- Difference between Star Schema and Fact Constellation Schema
- Difference between Snowflake Schema and Fact Constellation Schema
- Difference between Star Schema and Snowflake Schema
- Finding Additional functional dependencies in a relation
- How to find the highest normal form of a relation
- Difference between Schema and Database
- Snowflake Schema in Data Warehouse Model
- Create, Alter and Drop schema in MS SQL Server
- Types of Keys in Data Warehouse Schema
- Star Schema in Data Warehouse modeling
- Need for DBMS
- Disadvantages of DBMS
- Difference between 1NF and 2NF in DBMS
- The CAP Theorem in DBMS
- Deadlock in DBMS
- Starvation in DBMS
- Difference between DDL and DML in DBMS
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.