Background
A new training organization called ABC TechTraining is opening soon and they have approached you to help design their new database. They have just completed the refurbishment of the premises and are now looking at hiring staff to teach the many IT courses they have. They anticipate hiring a large number of staff within the first few months, around 200 - 300 staff annually. Due to the size and importance, they have asked you to look at developing just the staff database at this stage. They are aware they will need to expand the database to incorporate course and student information but are not looking at doing this now.
User Requirements
ABC TechTraining need a database that will help us track and manage the approval of teaching staff. The following are key things they need to track:
- Personal details including title, name, address, email, phone. Once a staff member is entered in the database, they are issued with a StaffID, which is just an autonumber.
- Qualifications. For each qualification the following needs to be recorded:
o Level - the AQF level of the staff must be recorded based on their highest qualification. See Table 1 below for AQF Levels
o Name of qualification
o Main subject area of qualification
o Institution
o Year awarded
- Relevant teaching experience
o Teaching period (Semester and year)
o Course(s) taught
o Organisation name and address
o Role in the course - can be one of the following: course coordinator, lecturer, tutor, instructor, teaching assistant, marker, facilitator
- Other employment experience
o Start and Finish dates
o Working status (Full-time/Part-time)
o Position title
o Employer name
o Duties
- List of publication(s). Should include the following:
o Year of publication
o Title of publication
o Journal Volume
o Type of publication - Can only be one of these: Journal, conference proceedings, books, book chapters
o Peer-refereed/reviewed - Yes or no is all that is recorded here.
o Research classification (Identified as either Research or Scholarship)
o Main field of education/discipline for each publication. Can only be 1 area.
- Approval to teach. The staff member must be approved to teach by the administration manager before they can start teaching. Approval details recorded include the following:
o Level approved to teach. Staff can only teach a level if they are qualified one level above. See Table 1 below
o Discipline(s) area approved to teach
o Who approved (one of the admin staff at ABC TechTraining)
o Location of teaching: There are currently two campuses for ABC TechTraining, Brisbane and Sydney.
o Approval date
o Review date
o Any notes on the approval
- List of documents filed for each staff member and who sighted them and when. For example, Resume, Certificates, Diplomas, Degrees etc.
- Any additional notes to be recorded and who recorded them.
- Reviews. Each year staff will be reviewed. The review date is one year from the approval date. The review will be conducted by the administration manager. All reviews will consist of an outcome which will determine if the staff member will be allowed to continue teaching with ABC TechTraining. Outcomes consist of: Continue, Warning, Terminate. Notes are to be recorded with each review. The date of the next review is then noted.
What you need to do
There are two parts to the assignment. Part A and Part B. Part A consists of the ERD diagram and any assumptions made. Part B is all the SQL code for your implementation of the database. Specifically, each part consists of the following:
For Part A you are to include a word document or PDF that contains:
- ER Diagram in Crows Foot notation (including primary & foreign keys)
- Ensure normalisation of database to 3rd Normal Form
- Assumptions that explain important design choices you made.
For Part B you are to submit
- A single plain text file, name _ABC.sql. In this file you are to include all the SQL for your implementation. This includes:
o CREATE TABLE statements including all integrity constraints, and actions on update and delete
o INSERT INTO statements for populating the database based on trial data from appropriate forms (if this must happen in a particular order then make sure you order it appropriately!). Note: There is a sample completed Abbreviated CV (Appendix C). However, you must come up with and insert 5 additional staff members and corresponding sample data yourself to test the database you created.
o The following queries and trigger:
- SELECT: List the Staff ID, title, name (first and last together) and highest qualification for all staff ordered by the lastname.
- SELECT: All staff that have not been approved to teach. Display the Staff ID, title, name (first and last together), approval notes.
- SELECT: All staff that have had no teaching experience
- SELECT: Display the total number of staff grouped by the AQF qualification level
- TRIGGER: When a staff member has been approved (i.e. when the staff approval date is updated - after update on staff table), the review date should automatically be inserted to be 1 year from the approval date.
o CREATE INDEX statements for the Staff and Approval tables.
Attachment:- Relational Database Systems.rar