Designing and Querying an Employee Database: Explained with Examples
You have three tables (relations):
- employee — stores employee info: person_name, street, city
- works — stores job info: person_name, company_name, salary
- company — stores company info: company_name, city
1. What should be the primary keys?
Primary keys uniquely identify records in each table:
| Relation | Primary Key |
|---|---|
| employee | person_name — because each employee has a unique name (assuming no duplicates) |
| works | (person_name, company_name) — composite key because an employee can work for different companies (if allowed), or to uniquely identify the job record |
| company | company_name — each company is unique by name |
2. Find employees living in the city "Dhaka"
Relational Algebra:
πperson_name(σcity='Dhaka'(employee))
SQL Query:
SELECT person_name FROM employee WHERE city = 'Dhaka';
3. Find employees earning more than TK100,000
Relational Algebra:
πperson_name(σsalary > 100000(works))
SQL Query:
SELECT person_name FROM works WHERE salary > 100000;
4. Find employees living in "Miami" and earning more than TK100,000
Relational Algebra:
πperson_name ( σcity='Miami'(employee) ⨝ σsalary > 100000(works) )
SQL Query:
SELECT e.person_name FROM employee e JOIN works w ON e.person_name = w.person_name WHERE e.city = 'Miami' AND w.salary > 100000;
5. Find employees who work for companies located in the same city they live in
Relational Algebra:
πperson_name ( σemployee.city = company.city ( (employee ⨝ works) ⨝ company ) )
SQL Query:
SELECT DISTINCT e.person_name FROM employee e JOIN works w ON e.person_name = w.person_name JOIN company c ON w.company_name = c.company_name WHERE e.city = c.city;
Summary
- Primary keys ensure each record is uniquely identifiable.
- Relational algebra shows the theoretical logic of the queries.
- SQL implements the logic practically to get real data.
- Joining tables helps combine related information across your database.