Assignment No # 3
Submitted By: Muhammad khawar ijaz
Registration ID: S2023388016
Course: Database Management System
Course Instructor: Mam Ammarah Abdullah
Program: ADP (ITM)
Key Component of DBMS..
1. Primary Key
Definition: A Primary Key uniquely identifies each record in a table. It
cannot have NULL values.
Example:
Consider a Product table where each product is identified by a unique
ProductID:
Product ProductNa Pric
ID me e
101 Laptop 800
Mobile
102 500
Phone
Headphone
103 100
s
In this case, ProductID is the Primary Key because it uniquely identifies
each product and cannot be NULL.
2. Foreign Key
Definition: A Foreign Key in one table points to a Primary Key in another
table, creating a relationship between the two.
Example
Consider an Orders table that records orders placed by customers:
Order Custome Product OrderDa
ID rID ID te
2024-01-
1 1001 101
01
2024-01-
2 1002 102
02
2024-01-
3 1001 103
05
o CustomerID is a Foreign Key referring to the CustomerID in the Customers
table.
o ProductID is a Foreign Key referring to the ProductID in the Product table.
These foreign keys help establish relationships between the Orders table and
the Customers/Product tables.
3. Unique Key
Definition: A Unique Key ensures that all values in the column (or combination of
columns) are unique. It can accept NULL values.
Example:
Consider a Users table where each user has a unique Username, but
PhoneNumber can be optional:
UserI Usernam PhoneNu
D e mber
1 john_doe NULL
jane_smit 123-456-
2
h 7890
987-654-
3 bob_jones
3210
o Username is a Unique Key because it must be unique for each user, but it can
allow NULL values for cases where the user doesn't have a username.
o PhoneNumber is also a Unique Key (if enforced), ensuring that no two users have
the same phone number.
4. Candidate Key
Definition: A Candidate Key is any column or set of columns that can
uniquely identify a record in a table. There can be multiple candidate keys, but
only one will be chosen as the Primary Key.
Example:
Consider a Student table:
Student
Email Name
ID
alice@example.co
1 Alice
m
2 bob@example.com Bob
charlie@example. Charli
3
com e
o Both StudentID and Email can uniquely identify a student, so they are both
Candidate Keys.
o StudentID may be chosen as the Primary Key, and Email will be an Alternate
Key.
5. Super Key
Definition: A Super Key is any set of columns that can uniquely identify
records in a table. It may include extra columns that are not necessary for
uniqueness.
Example:
Consider the same Student table:
Student
Email Name
ID
alice@example.co
1 Alice
m
2 bob@example.com Bob
charlie@example. Charli
3
com e
Here are some Super Keys:
o (StudentID) — This is a Candidate Key and thus a Super Key.
o (StudentID, Email) — This is a Super Key, though the extra column Email is
unnecessary.
o (StudentID, Name) — This is also a Super Key, though it includes unnecessary
extra columns.
6. Composite Key (Compound Key)
Definition: A Composite Key is a combination of two or more columns that
together can uniquely identify a record in a table.
Example:
Consider a Library system with a BookLoans table:
Student Book LoanDat
ID ID e
2024-01-
1 101
01
2 102 2024-01-
Student Book LoanDat
ID ID e
02
2024-01-
1 103
05
Here, StudentID and BookID together form a Composite Key, ensuring that a
student cannot borrow the same book multiple times on the same day.
Individually, neither column would be unique.
7. Alternate Key
Definition: An Alternate Key is a candidate key that was not chosen as the
primary key.
Example:
Consider a Product table:
Product ProductNa SK
ID me U
A12
101 Laptop
3
Mobile B45
102
Phone 6
Headphone C78
103
s 9
o ProductID is chosen as the Primary Key.
o SKU is a Candidate Key (can uniquely identify products) but is not chosen as the
primary key, so it is an Alternate Key.
8. Null Key
Definition: A Null Key is a column that can accept NULL values, typically
used for optional information.
Example:
Consider an Employees table:
Employe PhoneNu
Name
eID mber
123-456-
1 Alice
7890
2 Bob NULL
Charli
3 NULL
e
o PhoneNumber is a Null Key because it allows NULL values for employees who
don't have a phone number listed.
9. Secondary Key
Definition: A Secondary Key is a key used for indexing purposes, enabling
faster searches but not necessarily ensuring uniqueness.
Example:
Consider a Customers table:
Custome Customer
City
rID Name
1 Alice New York
Los
2 Bob
Angeles
3 Charlie Chicago
If you frequently search customers by their City, you might create an index on
the City column, making City a Secondary Key. This index would speed up
queries like "Find all customers in New York".
