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.
Components of a Relation Schema
- Relation Name: Name of the table that is stored in the database. It should be unique and related to the data that is stored in the table. For example- The name of the table can be Employee store the data of the employee.
- Attributes Name:Attributes specify the name of each column within the table. Each attribute has a specific data type.
- Domains: The set of possible values for each attribute. It specifies the type of data that can be stored in each column or attribute, such as integer, string, or date.
- Primary Key: The primary key is the key that uniquely identifies each tuple. It should be unique and not be null.
- Foreign Key: The foreign key is the key that is used to connect two tables. It refers to the primary key of another table.
- Constraints: Rules that ensure the integrity and validity of the data. Common constraints include NOT NULL, UNIQUE, CHECK, and DEFAULT.
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)
Relation Schema
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 aforeign 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
Operations And Constraint Violations In Relation Schema
Updates and retrieve are the two categories of operations on the relational schema. The basic types of updates are:
1. Insert: Insert operation is used to add a new tuple in the relation. It is capable of violating-
2.Delete: Delete operation is used to delete existing tuples from the relation. It can only violate the referential integrity constraint.
3. Modify: This operation is used to change the data or values of existing tuples based on the condition.
4. Retrive: This operation is used to retrieve the information or data from the relation Retrieval constraints do not cause a violation of integrity constraints.
Conclusion
A relation schema is the representation of the relational database that contain one or more table connecting to eachother. It provides a structured framework that defines the organization, attributes, and constraints of a table within the database. By ensuring data is systematically organized, a relation schema enhances data integrity, query efficiency, and overall database management. Understanding and designing effective relation schemas is crucial for anyone involved in database development and management.
Similar Reads
Schema Integration in DBMS
Definition: Schema integration is used to merge two or more database schemas into a single schema that can store data from both the original databases. For large databases with many expected users and applications, the integration approach of designing individual schema and then merging them can be
5 min read
What is Relationship in DBMS?
A database is a structured data set that is usually electronically written in a computer system and stored as data. With their primary purpose being to maintain, keep, and extract data correctly, databases are important. In many practical situations, data is never an isolated file, and its correlati
5 min read
What is Relationship Set in DBMS?
Relationship set in a Database Management System (DBMS) is essential as it provides the ability to store, recover, and oversee endless sums of information effectively in cutting-edge data administration, hence making a difference in organizations. In a Relational database, relationship sets are buil
4 min read
Relational Model in DBMS
The Relational Model represents data and their relationships through a collection of tables. Each table also known as a relation consists of rows and columns. Every column has a unique name and corresponds to a specific attribute, while each row contains a set of related data values representing a r
10 min read
What is Relationship Type in DBMS?
In DBMS (database management system) relationships are important concept that tells us how data is structured, stored and accessed, Just like in real life where relationships connect people (like relationship between parent and child) in same way DBMS link different data sets in a meaningful way. In
6 min read
What is Normalization in DBMS?
The normalization concept for relational databases, developed by E.F. Codd, the inventor of the relational database model, is from the 1970s. Before Codd, the most common method of storing data was in large, cryptic, and unstructured files, generating plenty of redundancy and lack of consistency. Wh
8 min read
Normalization Process in DBMS
Database Normalization is any systematic process of organizing a database schema such that no data redundancy occurs and there is least or no anomaly while performing any update operation on data. In other words, it means dividing a large table into smaller pieces such that data redundancy should be
8 min read
Degree of Relations in DBMS
We are living in a world where every entity has relationships with one other whether a living or non-living being. For example, you are a single entity but you share different relations with your family and friends. Even within a family, you are the son of your father at the same time you are also a
4 min read
Types of Relationship in Database
A relationship in a DBMS exists when a variable has a connection with the properties stored in different tables. Such relationships help the organization of entities intertwined with each other, ultimately enabling efficient data processing. They're exhibited usually via keys in a table, which is ei
4 min read
Tuple Relational Calculus (TRC) in DBMS
Tuple Relational Calculus (TRC) is a non-procedural query language used in relational database management systems (RDBMS) to retrieve data from tables. TRC is based on the concept of tuples, which are ordered sets of attribute values that represent a single row or record in a database table. TRC is
4 min read