Prime Attributes in DBMS
Learn via video course
FREE
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
An attribute in a database management system (DBMS) is a feature that refers to a database component, such as a table. It could also refer to a database field. Attributes describe the occurrences in a database column.
Prime attributes in DBMS
- A prime attribute is one of the attributes that make up the candidate key. In addition to being called prime attributes, key attributes is another name for this type of attribute. It is also present in all of the candidate keys.
- A set of attributes that uniquely identify tuples in a table is known as a Candidate Key. Candidate Key is a super key with no attributes that are repeated.
- They are key attributes because they can be used to uniquely identify any of the table's records.
Non-prime attributes in DBMS
- Non-prime attributes are those attributes of the relationships that are not present in any of the possible candidate keys of the relation.
- They are also known as non-key attributes. A primary key is an attribute or group of attributes used to uniquely identify any record in a table. The values of a primary key cannot be duplicated.
- Non-prime (non-key) attributes are those that are not the primary key attributes. They can store a value an unlimited number of times. They are non-key attributes because they cannot be used to uniquely identify any of the table's records.
For Example, take a look at the following queries :
As RegNo is the primary key in the Student table, the initial query will return either a zero or one result (if 12345 is a registered number). Name is not a primary key. As a result, the second query will return all records with the name Kumar in them.
Example Of Prime Attribute In DBMS
Consider the following table :
| EMP_ID | NAME | SALARY | DESIGNATION | DEPT_NO |
|---|---|---|---|---|
| 666 | Smith | 12,000 | Clerk | 10 |
| 679 | Stefan | 50,000 | Bussiness Analyst | 40 |
| 685 | Yash | 84,600 | Manager | 40 |
| 691 | john | 12,000 | Clerk | 10 |
| 702 | Aditi | 18,000 | Salesman | 10 |
| 714 | Aditya | 47,000 | HR Analyst | 30 |
| Column Name | Description |
|---|---|
| EMP_ID: | As each individual will have a unique ID, it is considered a prime attribute. |
| NAME: | As two people with the same name can exist, it is considered a non-prime attribute. |
| SALARY: | As two people can have the same salary, it is a non-prime attribute. |
| DESIGNATION: | As two different people may have the same designation, it is a non-prime attribute. |
| DEPT_NO: | As two different persons with different designations can have the same department, it is a non-prime attribute. |
Example Of Non-Prime Attribute In DBMS
Consider the following table :
| STUDENT_ID | NAME | AGE | CITY | PHONE_NO |
|---|---|---|---|---|
| 66 | Aditya | 21 | Noida | 9535653232 |
| 67 | Shivam | 22 | Gurugram | 8353355366 |
| 68 | Yash | 19 | Jaipur | 7323598665 |
| 69 | Mohit | 23 | Lahore | 8656864365 |
| 70 | Aditi | 24 | Noida | 8786898665 |
| 71 | Aditya | 22 | Jodhpur | 6532324365 |
| Column Name | Description |
|---|---|
| STUDENT_ID: | As each individual will have a unique ID, it is a prime attribute. |
| NAME: | As two people with the same name can exist, it is considered a non-prime attribute. |
| AGE: | As two people can have the same age, it is a non-prime attribute. |
| CITY: | As two different people may live in the same city, it is a non-prime attribute. |
| PHONE_NO: | As no two persons will have the same phone number, it is a prime attribute. |
Difference Between Prime And Non-Prime Attributes
| Prime Attributes | Non-Prime Attributes |
|---|---|
| Prime attributes can be used to uniquely identify a tuple in the table because they have unique values. | Non-prime attributes can not be used to uniquely identify a tuple in the table because they can have the same values. |
| Prime Attributes are also known as Key Attributes. | Non-Prime attributes are often known as Non-key attributes. |
| For Example:- Registration number and ID number will be prime attributes in a schema because every person will have a unique ID and registration number. | For Example - Name and age will be non-prime attributes in a schema because any person can have the same name and age. |
Conclusion
- Prime attributes are essential components of candidate keys in a database table, uniquely identifying each tuple.
- They are also referred to as key attributes and are present in all candidate keys.
- Conversely, non-prime attributes do not uniquely identify tuples and are not part of candidate keys.
- While prime attributes ensure uniqueness, non-prime attributes can have duplicate values.
- Understanding the distinction between prime and non-prime attributes is crucial for effective database design and querying.