Intro to DBMS

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

Non Prime Attributes in DBMS

 

Note:

  • Prime attributes uniquely identify each tuple.
  • The other name of “Key” is “Attribute”.
  • Tuple is the name of one row record.

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.