Non Prime Attribute in DBMS
In DBMS (database management system), the non prime attribute is characteristic of the table which cannot uniquely identify each tuple (row) in a table. Although non prime attribute is not helpful for a tuple identifier but it can provide detailed information about each tuple in that table.
Following is the descriptive diagram of non prime attribute
Note:
|
Example Tables: Non prime Attribute
Let discuss the examples of non prime attribute in detail.
Example 1: Students Table
Let’s consider a student table which contains the 5 attributes which includes Student-ID, Name, Age, subjects and GPA.
| Student-ID | Name | Age | Subjects | GPA |
| 1 | Oliver | 18 | Computer Science | 3.5 |
| 2 | William | 17 | Mathematics | 3.8 |
| 3 | Anderson | 22 | English | 3 |
- Non Prime Attributes: Name, Age, Subjects, GPA
- Prime Attribute: Student-ID
Example 2: Employees Table
Let’s consider an Employees table which contains the 5 attributes which includes Employee -ID, Name, Department, Salary and Hire-Date.
| Employee-ID | Name | Department | Salary | Hire-Date |
| 1 | Kane Williams | CS | 70000 | 2024-01-10 |
| 2 | Jone Hoda | HR | 90000 | 2022-02-15 |
| 3 | Anderson | English | 110000 | 2021-02-15 |
- Non Prime Attributes: Name, Department, Salary, Hire-Date
- Primary Key (PK): Employee-ID
Key points of Non Prime attribute
Let discuss some important key points about Non Prime attribute
1. Not Part of Candidate Keys or Primary Key
Non Prime attribute will never the part of any candidate key of a table. Candidate Keys are used to uniquely identifies each row in a table
- A candidate key is a set of one or more attributes (columns) in a database table that can uniquely identify any record in the table without any redundancy. It is possible for a table to have more than one candidate key’s.
- However, a primary key is a special candidate key selected by the database designer to uniquely identify every row in a table. primary will always be unique and not-null.
Example: Students Table
| Student-ID | Roll-No | Age | Subject | GPA |
| 1 | Alice | 20 | Computer Science | 3.5 |
| 2 | Bob | 22 | Mathematics | 3.7 |
| 3 | Charlie | 21 | Physics | 3.6 |
| 4 | Diana | 23 | Chemistry | 3.8 |
Explanation: In this table, Student-ID is the candidate key (primary key), while Roll-No, Age, subject and CPA are Non Prime attribute because they are not part of the candidate key.
2. Can Be Null
Non Prime attribute can be null in the table, unlike prime attributes.
Example: Products Table
| Product-ID | Name | Description | Price |
| 1 | Smartphone | Latest model | 700000 |
| 2 | Laptop | NULL | 120000 |
| 3 | NULL | Smart Device | 18000 |
Explanation:. In this table, Product-ID is the candidate key (primary key), while Name, Description, Price are Non Prime attributes
- Null values show the absence of data or the data is not currently available.
- Name and Description attributes contains the null values because these are Non Prime attributes.
3. No Uniqueness Constraint
Any Non Prime attribute column may have more than one similar value.
Table: Employees
| Employee-ID | Name | Department | Salary |
| 1 | Alice | HR | 50000 |
| 2 | Bob | HR | 60000 |
Explanation: The Department attribute is a Non Prime attribute without a uniqueness constraint, as multiple “HR” values belong to the same department attribute.
4. Helpful in Indexing (Filter)
Non Prime attribute is very useful for queries to search and filter based on some category. It improves the performance by speed up search and fast retrieval.
Example: Sales Table
| SaleID (PK) | Date | Quantity | Total-Price |
| 1 | 2024-03-01 | 2 | 1400 |
| 2 | 2024-03-02 | 1 | 1200 |
Explanation: Filtering data based on Non Prime attribute like Date, Quantity or Total-Price can improve the performance of queries that search or filters based on these attribute categories.
5. Impact on Data Quality
High-quality Non Prime attribute leads toward reliable record. Inaccurate or incomplete Non Prime attributes can lead to incorrect decisions and information’s.
Example: ItemSales Table
| SaleID (PK) | Item | Date | Quantity | Total-Price |
| 1 | Books | 2021-03-01 | 2 | 1400 |
| 2 | Smart device | 2023-03-02 | 1 | 1200 |
| 3 | Registers | 2024-03-02 | 5 | 20000 |
Explanation: The Non Prime attributes like item, date, Quantity and Total-Price are very important Non Prime attributes for decisions.
6. Normalization Influence
Non Prime attribute is mostly affected by the normalization. Normalization is helpful for rearranging attributes into separate tables to reduce redundancy (duplicate data). Redundancy occurs when the same information is copy to multiple places.
- Non Prime attribute can be separated into different tables to reduce the redundancy.
- Proper normalization ensures efficient organization of Non Prime attribute.
Example: CustomerOrders Table
| Order-ID | Customer-ID | Product-ID | Quantity | Order-Date |
| 1 | 1001 | 200 | 2 | 2024-01-01 |
| 2 | 1001 | 201 | 1 | 2024-01-02 |
| 3 | 1002 | 202 | 3 | 2024-01-03 |
| 4 | 1002 | 203 | 2 | 2024-01-04 |
Product-ID, Quantity, and Order-Date are the Non Prime Attributes. These attribute may be moved to a separate Addresses table.
Additional Information’s of Non Prime attribute
- Massive Data Types: Non Prime attributes can have a wide range of data types which may involve strings, numbers, dates, text, or even binary data. For example, student table may contains number, text, and many more types of data.
- Derived or Calculated: Some Non Prime attributes can be derived or calculated from other attributes. For example, an Age attribute can be calculated from a Date-Of-Birth attribute.