Best thing about being a business intelligence consultant is that my work is closely related to real-life scenarios and objects that I use daily . Every time when i come across new concepts or even problems , I have always been able to map in my world . Such is our topic for today-Role of Keys In Database Management System .
When we hear the word “KEY” the first thing that pops-up is an image of a door. Right? After reading this you will think of relation/tables now . Just as a key is used to access the contents behind the door , which can be a room , car or a security safe. Keys in DBMS is used to access the contents/tuples ( Rows ) of tables . That should be enough with the analogy I think . Time to jump to explanations and let’s try to understand different types of Keys used .
Before you read further , this article is a pre-requisite for coming articles on Normalisation & Joins .We already have articles on what is a database? Database Management System , Data Modelling , Database Relationship and most recently , We solved a case study as an example for University Management System by designing an ERD . You can go through them as well .
DBMS Keys are as follows :
- Primary Key
- Foreign Key
- Composite Key/ Composite Primary Key
- Candidate Key
- Key-Attribute & Non-Key Attribute
PRIMARY KEY :
Attribute which is unique and can identify all records in table. This is one of the most important attributes . Primary key in another table , has a potential to be a foreign key provided they have same data types and cannot be NULL
Hence , while designing a DBMS database designers keeps all primary keys of same data type and data length which is a good practice .
Refer to image at the bottom for visual example
FOREIGN KEY :
Attribute which is referenced to the primary key attribute of another table. This is used to create relationships between two tables. For example , In below image Dept_ID in Student table is a foreign key from Table Department which also has the same Attribute with exact same data type and data length. Important point is that , that Student.Dept_ID is mapped to the Department.Dept_ID and both tables have these attributes in it.
Foreign Key can be duplicated and NULL.
COMPOSITE PRIMARY KEY
Some cases demand that a table is identified by combining more than one column. The result is the composite primary key. It has same properties as of Primary key.
Student.StudentID and Student.EnrollNumber we can use these two as a composite key. Can we add the Studnet.Roll Number ? Comment below with your answer and reason
Selection of a primary key can sometimes be a tricky thing to do . A primary key should not only be able to identify each record but also it should be usable across all tables as a foreign key , if needed be. Hence , table can have more than one attribute with this property . These attributes are candidate keys. They are also called alternate or secondary keys
Can you identify more keys in below image? Awaiting your comment
KEY ATTRIBUTE & NON KEY ATTRIBUTE :
All attributes that are some kind of keys are key attributes and rest of them are non-key attributes.
See below diagram and find all keys . In case you have any question just feel free to contact us!
Now that you know these keys and how they behave. We are ready to move towards Normalisation , We will learn normalisation in detail with the University Management Case Study. We will apply these concepts on ERD and Relational Model.
If this was useful and helpful Share with your friends and Subscribe to get the latest articles in your mailbox!