Contributors: Omar Ebead (217243114), Shivani Srinivasan (220703294) Course: EECS 3421 – Database Systems Language: SQL
📘 Overview
This project demonstrates relational database design and query implementation for a company that manages jobs, employees, projects, and work assignments. It focuses on creating normalized tables, enforcing data integrity through constraints, and performing meaningful data analysis using SQL queries.
🧱 Database Schema Tables Created
JOB – Stores job details and hourly charge rates.
EMPLOYEE – Contains employee information, linked to job roles.
PROJECT – Holds project information and assigned employees.
ASSIGNMENT – Tracks employees’ work on projects, including hours and charges.
Key Relationships
EMPLOYEE.JOB_CODE → JOB.JOB_CODE
PROJECT.EMP_NUM → EMPLOYEE.EMP_NUM
ASSIGNMENT.PROJ_NUM → PROJECT.PROJ_NUM
ASSIGNMENT.EMP_NUM → EMPLOYEE.EMP_NUM
ASSIGNMENT.ASSIGN_JOB → JOB.JOB_CODE
🔐 Integrity Constraints
Primary Keys ensure each record is unique.
Foreign Keys maintain referential integrity across tables.
ON DELETE CASCADE removes dependent assignments when a project is deleted.
ON DELETE SET NULL prevents orphaned references when a job or employee is removed.
Domain Integrity enforced through appropriate data types and constraints (e.g., NUMERIC(6,2), DATE, VARCHAR(50)).
💾 Sample Features
Table creation and relationship definition.
Data insertion for all four entities.
Complex queries combining multiple joins and aggregations.
🔍 Example Queries
Employees hired before a given date.
Job details with employee names, sorted by job and last name.
Employees assigned to the “Evergreen” project.
Project summaries showing total employees, hours, and charges.
🚀 How to Run
Create a new database in your preferred SQL environment (MySQL, PostgreSQL, etc.).
Run the table creation scripts to build the schema.
Execute the INSERT statements to populate data.
Run the query scripts to verify relationships and outputs.
🧩 Purpose
This project demonstrates practical SQL skills including:
Designing normalized relational schemas
Enforcing data integrity and relationships
Writing queries for reporting and analysis