""" employee_projects_db.py ======================== A bigger companion to employee_db.py. Same DEPARTMENT, JOBS, and EMPLOYEES tables, plus two more: PROJECTS and ASSIGNMENTS, and a fourth, PERFORMANCE_REVIEWS. Use this one once the basic joins and aggregates on EMPLOYEES feel comfortable and you want more shapes of SELECT statement to practice on, especially many to many joins and one to many outer joins. HOW TO USE IT ------------- Put this file in the same folder as your notebook, then run: from employee_projects_db import create_database connection, cursor = create_database() It is always safe to run again. The tables are dropped and rebuilt every time, so you can reset your data at any point by re running that one cell. This is a separate database file from employee_db.py (project5_company.db versus project5b_company.db), so you can have both open at once without one overwriting the other. THE FIVE TABLES ---------------- DEPARTMENT 6 rows same as employee_db.py JOBS 6 rows same as employee_db.py EMPLOYEES 24 rows same as employee_db.py PROJECTS 5 rows the projects the company is running ASSIGNMENTS 16 rows which employees are staffed on which projects PERFORMANCE_REVIEWS 18 rows review history, several employees per row A few things about the data are deliberate, because they open up SELECT statements the plain employee_db.py cannot practice: * ASSIGNMENTS is a bridge, or composite, entity: its primary key is the pair (emp_id, project_id) together, exactly the many to many pattern from Section 3, Composite, associative, and bridge entities. An employee can be staffed on more than one project, and a project almost always has more than one employee, which is exactly what makes a plain foreign key impossible here. * 10 of the 24 employees are not staffed on any project at all, which is the case a LEFT JOIN or a NOT EXISTS subquery is built to find. * 8 of the 24 employees have never had a performance review, and 2 employees (Carolyn Reyes and David Nguyen) have two reviews each, so COUNT and AVG behave differently per employee, not just per group. * Review ratings range from 2 to 5, giving HAVING something to filter on besides a row count. """ import sqlite3 DB_FILENAME = "project5b_company.db" # --------------------------------------------------------------------------- # The data # --------------------------------------------------------------------------- # (dept_id, dept_name, location, budget) DEPARTMENT_ROWS = [ (1, "Accounting", "Turlock", 450000), (2, "Engineering", "Modesto", 1200000), (3, "Sales", "Fresno", 800000), (4, "Human Resources", "Turlock", 300000), (5, "Marketing", "Stockton", 550000), # no employees yet (6, "Research", "Merced", 900000), # no employees yet ] # (job_id, job_title, min_salary, max_salary) JOBS_ROWS = [ (1, "President", 90000, 150000), (2, "Manager", 70000, 110000), (3, "Software Engineer", 65000, 120000), (4, "Accountant", 50000, 85000), (5, "Sales Rep", 40000, 90000), (6, "HR Specialist", 45000, 75000), ] # (emp_id, first_name, last_name, email, hire_date, salary, dept_id, job_id, manager_id) EMPLOYEES_ROWS = [ (101, "Carolyn", "Reyes", "creyes@company.com", "2015-03-12", 145000, 1, 1, None), (102, "Marcus", "Delgado", "mdelgado@company.com", "2016-06-01", 102000, 2, 2, 101), (103, "Priya", "Raman", "praman@company.com", "2017-02-15", 98000, 3, 2, 101), (104, "Tomas", "Okafor", "tokafor@company.com", "2018-09-10", 88000, 1, 2, 101), (105, "Elena", "Castillo", "ecastillo@company.com", "2019-01-07", 76000, 4, 2, 101), (106, "David", "Nguyen", "dnguyen@company.com", "2019-04-22", 112000, 2, 3, 102), (107, "Aisha", "Bello", "abello@company.com", "2020-07-13", 95000, 2, 3, 102), (108, "Jorge", "Medina", "jmedina@company.com", "2020-11-02", 88000, 2, 3, 102), (109, "Hannah", "Kim", "hkim@company.com", "2021-05-17", 82000, 2, 3, 102), (110, "Samuel", "Ortiz", "sortiz@company.com", "2022-03-28", 79000, 2, 3, 102), (111, "Leila", "Haddad", "lhaddad@company.com", "2022-08-15", 74000, 2, 3, 102), (112, "Ryan", "Foster", "rfoster@company.com", "2023-01-09", 68000, 2, 3, 102), (113, "Mei", "Chen", None, "2024-06-03", 66000, 2, 3, 102), (114, "Andre", "Silva", "asilva@company.com", "2019-08-19", 72000, 3, 5, 103), (115, "Bianca", "Moreau", "bmoreau@company.com", "2020-02-24", 68000, 3, 5, 103), (116, "Kwame", "Asante", "kasante@company.com", "2021-09-06", 64000, 3, 5, 103), (117, "Daniela", "Rossi", "drossi@company.com", "2022-04-11", 58000, 3, 5, 103), (118, "Victor", "Pham", "vpham@company.com", "2023-07-24", 52000, 3, 5, 103), (119, "Grace", "Adeyemi", None, "2024-10-14", 47000, 3, 5, 103), (120, "Nathan", "Brooks", "nbrooks@company.com", "2018-05-21", 81000, 1, 4, 104), (121, "Sofia", "Marchetti", "smarchetti@company.com","2020-10-05", 73000, 1, 4, 104), (122, "Isaac", "Levine", "ilevine@company.com", "2022-11-28", 62000, 1, 4, 104), (123, "Camille", "Dubois", "cdubois@company.com", "2021-03-15", 61000, 4, 6, 105), (124, "Omar", "Farouk", None, "2023-02-06", 54000, 4, 6, 105), ] # (project_id, project_name, client, budget, start_date) PROJECTS_ROWS = [ (1, "Website Redesign", "Acme Retail", 120000, "2025-01-15"), (2, "Inventory System", "Acme Retail", 250000, "2025-03-01"), (3, "Cloud Migration", "Valley Health", 400000, "2024-11-10"), (4, "Mobile App", "Sunrise Bank", 180000, "2025-06-01"), (5, "Data Warehouse", "Sunrise Bank", 300000, "2025-02-20"), ] # (emp_id, project_id, role, hours_per_week) -- composite primary key (emp_id, project_id) ASSIGNMENTS_ROWS = [ (102, 1, "Project Lead", 20), (102, 2, "Project Lead", 15), (106, 1, "Developer", 30), (107, 1, "Developer", 25), (106, 2, "Developer", 10), (108, 2, "Developer", 30), (109, 2, "Developer", 20), (103, 3, "Project Lead", 20), (114, 3, "Analyst", 25), (115, 3, "Analyst", 25), (116, 3, "Analyst", 15), (105, 4, "Project Lead", 15), (120, 4, "Analyst", 10), (104, 5, "Project Lead", 20), (110, 5, "Developer", 30), (111, 5, "Developer", 30), ] # (review_id, emp_id, review_date, rating, comments) -- rating is 1 (lowest) to 5 (highest) PERFORMANCE_REVIEWS_ROWS = [ (1, 101, "2025-01-10", 5, "Exceptional leadership"), (2, 101, "2025-07-10", 5, "Continues to exceed expectations"), (3, 102, "2025-02-15", 4, "Strong project delivery"), (4, 103, "2025-02-20", 4, "Consistently reliable"), (5, 104, "2025-03-01", 3, "Meets expectations"), (6, 105, "2025-03-05", 4, "Solid performance"), (7, 106, "2025-04-01", 5, "Outstanding technical work"), (8, 106, "2025-10-01", 4, "Great year, slight dip in Q3"), (9, 107, "2025-04-10", 3, "Meets expectations"), (10, 108, "2025-04-15", 2, "Needs improvement on deadlines"), (11, 109, "2025-05-01", 4, "Reliable contributor"), (12, 114, "2025-05-10", 5, "Excellent client communication"), (13, 115, "2025-05-15", 3, "Meets expectations"), (14, 120, "2025-06-01", 4, "Strong attention to detail"), (15, 121, "2025-06-05", 3, "Meets expectations"), (16, 123, "2025-06-10", 4, "Good onboarding progress"), (17, 110, "2025-07-01", 2, "Needs improvement"), (18, 111, "2025-07-05", 5, "Top performer this quarter"), ] # --------------------------------------------------------------------------- # The builder # --------------------------------------------------------------------------- def create_database(filename=DB_FILENAME, quiet=False): """Create the database and return (connection, cursor). filename : where to store the database. The default creates a file in the same folder as your notebook. Pass ":memory:" if you would rather it disappear when you close the notebook. quiet : pass True to skip the summary printout. """ connection = sqlite3.connect(filename) cursor = connection.cursor() for table in ["PERFORMANCE_REVIEWS", "ASSIGNMENTS", "PROJECTS", "EMPLOYEES", "JOBS", "DEPARTMENT"]: cursor.execute("DROP TABLE IF EXISTS " + table) cursor.execute(""" CREATE TABLE DEPARTMENT ( dept_id INTEGER PRIMARY KEY, dept_name TEXT NOT NULL, location TEXT, budget INTEGER ) """) cursor.execute(""" CREATE TABLE JOBS ( job_id INTEGER PRIMARY KEY, job_title TEXT NOT NULL, min_salary INTEGER, max_salary INTEGER ) """) cursor.execute(""" CREATE TABLE EMPLOYEES ( emp_id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT NOT NULL, email TEXT, hire_date TEXT, salary INTEGER, dept_id INTEGER, job_id INTEGER, manager_id INTEGER, FOREIGN KEY (dept_id) REFERENCES DEPARTMENT (dept_id), FOREIGN KEY (job_id) REFERENCES JOBS (job_id), FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (emp_id) ) """) cursor.execute(""" CREATE TABLE PROJECTS ( project_id INTEGER PRIMARY KEY, project_name TEXT NOT NULL, client TEXT, budget INTEGER, start_date TEXT ) """) cursor.execute(""" CREATE TABLE ASSIGNMENTS ( emp_id INTEGER, project_id INTEGER, role TEXT, hours_per_week INTEGER, PRIMARY KEY (emp_id, project_id), FOREIGN KEY (emp_id) REFERENCES EMPLOYEES (emp_id), FOREIGN KEY (project_id) REFERENCES PROJECTS (project_id) ) """) cursor.execute(""" CREATE TABLE PERFORMANCE_REVIEWS ( review_id INTEGER PRIMARY KEY, emp_id INTEGER, review_date TEXT, rating INTEGER, comments TEXT, FOREIGN KEY (emp_id) REFERENCES EMPLOYEES (emp_id) ) """) cursor.executemany( "INSERT INTO DEPARTMENT VALUES (?, ?, ?, ?)", DEPARTMENT_ROWS) cursor.executemany( "INSERT INTO JOBS VALUES (?, ?, ?, ?)", JOBS_ROWS) cursor.executemany( "INSERT INTO EMPLOYEES VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", EMPLOYEES_ROWS) cursor.executemany( "INSERT INTO PROJECTS VALUES (?, ?, ?, ?, ?)", PROJECTS_ROWS) cursor.executemany( "INSERT INTO ASSIGNMENTS VALUES (?, ?, ?, ?)", ASSIGNMENTS_ROWS) cursor.executemany( "INSERT INTO PERFORMANCE_REVIEWS VALUES (?, ?, ?, ?, ?)", PERFORMANCE_REVIEWS_ROWS) connection.commit() if not quiet: print("Database ready:", filename) print() for table in ["DEPARTMENT", "JOBS", "EMPLOYEES", "PROJECTS", "ASSIGNMENTS", "PERFORMANCE_REVIEWS"]: cursor.execute("SELECT COUNT(*) FROM " + table) print(" " + table.ljust(20), cursor.fetchone()[0], "rows") print() cursor.execute(""" SELECT COUNT(*) FROM EMPLOYEES WHERE emp_id NOT IN (SELECT emp_id FROM ASSIGNMENTS) """) print(" Employees on no project: ", cursor.fetchone()[0]) cursor.execute(""" SELECT COUNT(*) FROM EMPLOYEES WHERE emp_id NOT IN (SELECT emp_id FROM PERFORMANCE_REVIEWS) """) print(" Employees never reviewed: ", cursor.fetchone()[0]) print() print("You are ready to start writing queries.") return connection, cursor def show_columns(cursor, table): """Print the column names of one table. Handy if you forget a name.""" cursor.execute("SELECT * FROM " + table + " LIMIT 1") names = [description[0] for description in cursor.description] print(table, "columns:") for name in names: print(" ", name) if __name__ == "__main__": connection, cursor = create_database(":memory:") print() show_columns(cursor, "ASSIGNMENTS") show_columns(cursor, "PERFORMANCE_REVIEWS") connection.close()