Case: Parks and Recreation Department Training Database A local Parks and Recreation Department has called you, a database consultant, to create a database to track data about its employees and job assignments. Based upon interviews with the managers and office staff of the department, you find out that the database needs to support these queries and business rules: QUERIES Supported: · Given a specific employee, list the employee’s first and last names, employee ID and the employee's age. · Given a specific training course, by course number, list the location of the course, the type of training course, and the course description. · Given a training course number, list all employee IDs that have successfully taken the course for certification, including the certification date. · Once an employee has taken a course and is certified, he/she is assigned to a job. So given an employee ID, list the assignment location, the date the job was assigned, and the assignment responsibilities for each course number he/she certified in. · Given a training course number, list the most recent date that textbooks have been ordered. Include the number of copies, billed amount, ISBN, and book description. · Given a training course number, list each time textbooks have been order for the course. Include the order date, the billed amount and the ISBN for each order. BUSINESS RULES: · An employee must be certified through a training course before he/she's even assigned to a job (the DBMS should enforce this via Referential Integrity). · Employees may be certified in more than one course. · On any day, an employee could be assigned to more than one certified job. · Also, a certified employee could also be assigned to the same job more than once. · Finally, once an employee is certified in a training course, he/she DOES NOT have to go through the certification process for that course ever again.
Can you do this?