A well-maintained relational DBMS has a high level of data integrity. What features of a relational DBMS contribute towards this level of integrity?
Relational DBMS gives high level of data integrity, by using the following rules:
Entity Integrity: Entity integrity says which a prime attribute in a relation can not accept null values.
Referential Integrity: The referential integrity rule is related along with the foreign key concept. Let R1 and R2 are two relations where R1 is having an attribute(s) with primary key. Let R2 be having a foreign key, that refers to relation R1 via the same set of attributes. Then the value of the foreign key in a tuple in R2 relation must either be equal to primary key of a tuple in a relation R1 relation or be entirely NULL.
In a more common way, the referential integrity rule states in which every foreign key value must match a primary key value in a related table. Referential integrity ensures that we can correctly navigate among related entities> Referential Integrity is a state where as each foreign key value has matching primary key value. In other words, it guarantees that data in a dependent table has matching records in the table(s) on which it depends. With modern databases, referential integrity is enforced along with the use of primary and foreign keys, that will not allow insertions, deletions, or changes to data which violate the rules of integrity configured through the database administrator.
|
Student_id
|
X (6)
|
|
Subject_code
|
X (6)
|
|
Semester_code
|
X (6)
|
|
Subject_name
|
X(20)
|
|
Lecturer_id
|
X(8)
|
|
Lecturer-name
|
X(20)
|
|
Grade
|
X(6)
|
PRIMARY KEY (student_id, subject_code)
FOREIGN KEY (subject_code) REFERENCES TABLE subjects.
The student_grade table stores the information of the grade acquire through all the students in their respective subjects taught through a lecturer in a semester. The design of this table is not normalized and it would lead to several anomalies.
Every time a grade for a student is inserted for a few subject taught through a lecturer, subject_name and lecturer_name have also to be inserted. So this field will be repeated for all the students for a particular subject taught through a teacher. For example if there are five students who studied Mathematics subject taught through Mr. Ashok then these two values along with their Ids will be repeated within the relation. This will lead to redundancy.
If a change is needed in subject_name then it will be required to modify the data in both the tables student_grade and subjects, or else leading to a inconsistent state. For example if we wish to change the subject name of Maths to Mathematics in subjects table then this change has to be made in student_grade also.
If we declare a record in student_grade table, that have only one entry for a particular lecturer, then it will loose the information of that lecturer. Furthermore, if a new lecturer joins, his/her information can not be inserted in the relation unless he/she teaches a subject to the students of any semester.