SQL Overview The directory contains a file people_info.csv. The columns are first_name middle_name last_name email employee_type,...

  

   

Transcribed Image Text:

SQL Overview The directory contains a file people_info.csv. The columns are first_name middle_name last_name email employee_type, which can be one of Professor, Lecturer, Staff. The value is empty if the person is a student. enrollment_year which must be in the range 2016-2023. The value is empty if the person is an employee. If enrollment_year is not NULL, the person is a Student and employee_type must be NULL. If employee_type is not NULL, then the person is a Employee and enrollment_year must be NULL. [33]: %%sql You must implement a two-table solution to the inheritance pattern. This means that your solution will have tables student and employee, and have a view people. You must demonstrate correct implementation by loading the data, using select statements, attempting insert/update/delete, ... [33] [] DROP SCHEMA IF EXISTS f23_hw2; CREATE SCHEMA f23_hw2; USE f23_hw2; * mysql+pymysql://root:***@localhost 5 rows affected. 1 rows affected. 0 rows affected. Tables and View You must implement the two tables and view, including reasonable data types and constraints. This must include an additional column uni that we explain below. You may add additional columns and triggers if that helps. 1. uni is the primary key. 2. employee_type must be one of Professor, Lecturer, Staff. 3. enrollment_year must be in the range 2016-2023 inclusive. 4. Only middle name can be null. 5. email must be unique over both student and employee. Note that this constraint should be checked when inserting new rows and updating existing rows. [34]: %%sql UNI uni: 1. Must be of the form fml####, where f is the first letter (lowercased) of the first name, m is the first letter of the middle name (if not NULL), 1 is the first letter of the last name, and #is a number. 2. For any combination of letters, the numbers following the letters must start at 1 and increase. That is, dff0001, ab0001, dff0002, cd0001, cad0001, cd0002, ... 3. You must implement a function to generate the uni. 4. You must implement triggers on the relevant tables to automatically set the uni during an insert. 5. You must implement triggers that adjust the uni if a person's initials change during an update. 6. You must implement logic that prevent inserts from directly setting the uni and updates from directly changing the uni. [ ]: %%sql Procedures You must implement four stored procedures. The implementations of the four procedures are almost identical. 1. The procedures are: A. create student (first_name, middle_name, last_name, email, enrollment_year, uni) B. create employee (first_name, middle_name, last_name, email, employee_type, uni) [ ]: %%sql C. update student (uni, first_name, middle_name, last_name, email, enrollment_year) D. update employee (uni, first_name, middle_name, last_name, email, employee_type) 2. For updates, the procedures ignore input parameters that are NULL and only apply the non-NULL values. The procedures do not update the uni. 3. The create procedures return the uni as an out value. Security You must create a new user general_user and use security to allow it to perform only SELECT and EXECUTE operations (i.e., no INSERT, DELETE, and UPDATE operations). [ ]: %%sql Data Insertion []: %%sql # The following assumes that you have created everything properly DELETE FROM f23_ hw2.student; DELETE FROM f23_hw2.employee; []: people_info = [] with open ("people_info.csv", "r") as in file: d_rdr = csv.DictReader (in_file) for r in d_rdr: people_info.append(dict(r)) people_info_df = pd.DataFrame (people_info) people_info_df [ ]: # If you have defined your procedures, functions, tables, and constraints correctly, you can use the function below # to load your data. def add_person (p): p is a dictionary containing the column values for either a student or an employee. www cur= sql_conn.cursor() # This function changes the data, converting to None. # So, we make a copy and change the copy. p_dict copy.copy(p) = for k, v in p_dict.items(): if v == : p_dict [k] = None # Is the person a student? # if p_dict['employee_type'] is None: # This provides a hint for what your stored procedure will look like. res = cur.callproc("f23_hw2.create_student", else: # The following are in parameters (p_dict['first_name'], # After the procedure executes, the following query will select the out values. res = cur.execute("""SELECT @_f23_hw2.create_student_5""") result = cur.fetchall() p_dict ['middle_name'], p_dict [last_name'], p_dict['email'], #The following does the same for employee. elif p_dict['enrollment_year'] is None: res = cur.callproc("f23_hw2.create_employee", p_dict['enrollment_year'], #The following are out parameters for uni. None)) sql_conn.commit() cur.close() return result []: for p in people info: add_person (p) res = cur.execute("""SELECT @_f23_hw2.create_student_5""") result= cur.fetchall(). (p_dict['first_name'],' p_dict ['middle_name'], p_dict [last_name'], p_dict ['email'l, p_dict ['employee_type'], None)) print("Something went wrong") result = None []: # Test that we loaded people %sql SELECT * FROM people []: # Test that we loaded students. %sql SELECT* FROM student []: # Test that we loaded employees $sql SELECT * FROM employee 1: 1: Testing Add your tests below. You should test 1. Successful execution of each procedure. 2. Execution of update procedures showing that your constraints prevent incorrect data entry and enforce the defined semantics and behavior. 3. general user can query the data and call the procedures, but cannot perform INSERT, UPDATE, or DELETE. SQL Overview The directory contains a file people_info.csv. The columns are first_name middle_name last_name email employee_type, which can be one of Professor, Lecturer, Staff. The value is empty if the person is a student. enrollment_year which must be in the range 2016-2023. The value is empty if the person is an employee. If enrollment_year is not NULL, the person is a Student and employee_type must be NULL. If employee_type is not NULL, then the person is a Employee and enrollment_year must be NULL. [33]: %%sql You must implement a two-table solution to the inheritance pattern. This means that your solution will have tables student and employee, and have a view people. You must demonstrate correct implementation by loading the data, using select statements, attempting insert/update/delete, ... [33] [] DROP SCHEMA IF EXISTS f23_hw2; CREATE SCHEMA f23_hw2; USE f23_hw2; * mysql+pymysql://root:***@localhost 5 rows affected. 1 rows affected. 0 rows affected. Tables and View You must implement the two tables and view, including reasonable data types and constraints. This must include an additional column uni that we explain below. You may add additional columns and triggers if that helps. 1. uni is the primary key. 2. employee_type must be one of Professor, Lecturer, Staff. 3. enrollment_year must be in the range 2016-2023 inclusive. 4. Only middle name can be null. 5. email must be unique over both student and employee. Note that this constraint should be checked when inserting new rows and updating existing rows. [34]: %%sql UNI uni: 1. Must be of the form fml####, where f is the first letter (lowercased) of the first name, m is the first letter of the middle name (if not NULL), 1 is the first letter of the last name, and #is a number. 2. For any combination of letters, the numbers following the letters must start at 1 and increase. That is, dff0001, ab0001, dff0002, cd0001, cad0001, cd0002, ... 3. You must implement a function to generate the uni. 4. You must implement triggers on the relevant tables to automatically set the uni during an insert. 5. You must implement triggers that adjust the uni if a person's initials change during an update. 6. You must implement logic that prevent inserts from directly setting the uni and updates from directly changing the uni. [ ]: %%sql Procedures You must implement four stored procedures. The implementations of the four procedures are almost identical. 1. The procedures are: A. create student (first_name, middle_name, last_name, email, enrollment_year, uni) B. create employee (first_name, middle_name, last_name, email, employee_type, uni) [ ]: %%sql C. update student (uni, first_name, middle_name, last_name, email, enrollment_year) D. update employee (uni, first_name, middle_name, last_name, email, employee_type) 2. For updates, the procedures ignore input parameters that are NULL and only apply the non-NULL values. The procedures do not update the uni. 3. The create procedures return the uni as an out value. Security You must create a new user general_user and use security to allow it to perform only SELECT and EXECUTE operations (i.e., no INSERT, DELETE, and UPDATE operations). [ ]: %%sql Data Insertion []: %%sql # The following assumes that you have created everything properly DELETE FROM f23_ hw2.student; DELETE FROM f23_hw2.employee; []: people_info = [] with open ("people_info.csv", "r") as in file: d_rdr = csv.DictReader (in_file) for r in d_rdr: people_info.append(dict(r)) people_info_df = pd.DataFrame (people_info) people_info_df [ ]: # If you have defined your procedures, functions, tables, and constraints correctly, you can use the function below # to load your data. def add_person (p): p is a dictionary containing the column values for either a student or an employee. www cur= sql_conn.cursor() # This function changes the data, converting to None. # So, we make a copy and change the copy. p_dict copy.copy(p) = for k, v in p_dict.items(): if v == : p_dict [k] = None # Is the person a student? # if p_dict['employee_type'] is None: # This provides a hint for what your stored procedure will look like. res = cur.callproc("f23_hw2.create_student", else: # The following are in parameters (p_dict['first_name'], # After the procedure executes, the following query will select the out values. res = cur.execute("""SELECT @_f23_hw2.create_student_5""") result = cur.fetchall() p_dict ['middle_name'], p_dict [last_name'], p_dict['email'], #The following does the same for employee. elif p_dict['enrollment_year'] is None: res = cur.callproc("f23_hw2.create_employee", p_dict['enrollment_year'], #The following are out parameters for uni. None)) sql_conn.commit() cur.close() return result []: for p in people info: add_person (p) res = cur.execute("""SELECT @_f23_hw2.create_student_5""") result= cur.fetchall(). (p_dict['first_name'],' p_dict ['middle_name'], p_dict [last_name'], p_dict ['email'l, p_dict ['employee_type'], None)) print("Something went wrong") result = None []: # Test that we loaded people %sql SELECT * FROM people []: # Test that we loaded students. %sql SELECT* FROM student []: # Test that we loaded employees $sql SELECT * FROM employee 1: 1: Testing Add your tests below. You should test 1. Successful execution of each procedure. 2. Execution of update procedures showing that your constraints prevent incorrect data entry and enforce the defined semantics and behavior. 3. general user can query the data and call the procedures, but cannot perform INSERT, UPDATE, or DELETE.