Table and Key
- A table consist of rows and column.
- A column (vertical) represent a single attribute.
- A row (horizontal) represent an single entry
- A primary key is a unique to table that is used to identify the unique row.
- A surrogate key is a key that has no mapping to real world.
- A natural key is a key that has mapping to real world for example an entry with Aadhaar number of a citizen.
For Example - Employee Table - PK: ID
+----+---------------+-----------+------------+----------+
| id | name | position | department | salary |
+----+---------------+-----------+------------+----------+
| 1 | John Doe | Manager | Sales | 65000.00 |
| 2 | Jane Smith | Developer | IT | 75000.00 |
| 3 | Alice Johnson | Analyst | Finance | 58000.00 |
| 4 | Bob Brown | Designer | Marketing | 52000.00 |
| 5 | Charlie White | Developer | IT | 77000.00 |
+----+---------------+-----------+------------+----------+For Example - Project Table - PK: project_id
+------------+--------------------+------------+------------+-------------+
| project_id | project_name | start_date | end_date | employee_id |
+------------+--------------------+------------+------------+-------------+
| 1 | Website Redesign | 2024-01-15 | 2024-04-15 | 2 |
| 2 | Data Analysis | 2024-02-01 | 2024-05-30 | 3 |
| 3 | Marketing Campaign | 2024-03-01 | 2024-06-01 | 4 |
| 4 | Product Launch | 2024-04-01 | 2024-08-01 | 1 |
| 5 | App Development | 2024-05-01 | 2024-10-01 | 5 |
+------------+--------------------+------------+------------+-------------+- A foreign key use a primary key of another table of a database.
- A foreign key define the relationship between the tables.
- A table can have more than one foreign key.
Foreign key Example
+------------+-------------+-----------+--------------------+
| project_id | employee_id | position | project_name |
+------------+-------------+-----------+--------------------+
| 1 | 2 | Developer | Website Redesign |
| 2 | 3 | Analyst | Data Analysis |
| 3 | 4 | Designer | Marketing Campaign |
| 4 | 1 | Manager | Product Launch |
| 5 | 5 | Developer | App Development |
+------------+-------------+-----------+--------------------+- A composite key is a combination of two or more columns in a database table that uniquely identifies each row. For example - project_id + employee_id together form a key.
Composite key Example
+------------+-------------+-----------+--------------------+
| project_id | employee_id | position | project_name |
+------------+-------------+-----------+--------------------+
| 1 | 2 | Developer | Website Redesign |
| 2 | 3 | Analyst | Data Analysis |
| 3 | 4 | Designer | Marketing Campaign |
| 4 | 1 | Manager | Product Launch |
| 5 | 5 | Developer | App Development |
+------------+-------------+-----------+--------------------+