Database Normalisation Questions Long
The purpose of composite keys in database normalization is to uniquely identify a record in a table when a single attribute is not sufficient. Composite keys are formed by combining two or more attributes/columns to create a unique identifier for each record.
The primary goal of database normalization is to eliminate data redundancy and ensure data integrity. By using composite keys, we can achieve this goal by creating relationships between tables and avoiding duplicate data.
Composite keys are particularly useful in situations where a single attribute cannot uniquely identify a record. For example, in a database for a university, a student table may have attributes such as student ID, first name, last name, and date of birth. In this case, a combination of student ID and date of birth can be used as a composite key to uniquely identify each student.
By using composite keys, we can establish relationships between tables through foreign keys. This allows us to create efficient and normalized database structures. For instance, in a university database, the student table can have a foreign key referencing the course table, indicating which courses the student is enrolled in. The composite key ensures that each student's enrollment in a particular course is uniquely identified.
Additionally, composite keys can also be used to enforce business rules and constraints. For example, in a sales database, a composite key consisting of the order ID and product ID can ensure that each order can only contain a specific product once.
In summary, the purpose of composite keys in database normalization is to provide a unique identifier for records when a single attribute is not sufficient. They help eliminate data redundancy, establish relationships between tables, and enforce business rules and constraints.