ICT701-RelationalDatabaseSystems-Task2 ATMC.pdf
ICT211 Task 2 2016v0 ICT701 Relational Database Systems Task 2 ICT701 Relational Database Systems Task 2 2 | P a g e Assessment and Submission Details Marks: 40% of the Total Assessment for the Course Due Date: as per Course outline Submit your assignment to the link under Assessment->Task 2 on Blackboard. The submission link will be open a week before the due date. Please follow the submission instructions provided. The assignment will be marked out of a total of 100 marks and forms 40% of the total assessment for the course. ALL code and reports will be checked for plagiarism by SafeAssign system provided by Blackboard automatically. Since database design is a creative process, more than one solution is possible. Solutions appearing too similar may be investigated for collusion. Refer to your Course Outline or the Course Web Site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines. Assignment submission extensions will only be made using the official Faculty of Arts & Business Guidelines. Requests for an extension to an assignment MUST be made to the course coordinator prior to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances. ICT701 Relational Database Systems Task 2 3 | P a g e 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: ICT701 Relational Database Systems Task 2 4 | P a g e 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. Table 1 AQF levels AQF Level Level of qualification required to teach Bachelor degree Level 7 Level 8 Bachelor Honours Degree, Graduate Certificate or Graduate Diploma Level 8 Level 9 Masters Level 9 Level 10 PhD Level 10 Level 10 ICT701 Relational Database Systems Task 2 5 | P a g e 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. ICT701 Relational Database Systems Task 2 6 | P a g e Specific Instructions As the organisation is new, there is no actual data yet. They plan to collect data via the Abbreviated CV (Appendix B). There is a sample completed Abbreviated CV (Appendix C). However, you must also create 5 additional sample data yourself to test the database you create. Basically, you will be inserting 5 fictional people and corresponding information into the database. You must use MySQL to develop the database. MS Access is not appropriate for any section of this assignment. You must use the ER notation that was taught in ICT701. Penalties will apply to incorrect notations. Submission The completed assignment is to be submitted to Blackboard by the due date. The assignment will be assessed according to the marking sheet. Late submission will be penalised according to the policy in the course outline. Please note Saturday and Sunday are included in the count of days late. Assignments (including code) will be checked for plagiarism via SafeAssign automatically in Blackboard. Solutions appearing too similar may be investigated for collusion. Please ensure you do your own work. ICT701 Relational Database Systems Task 2 7 | P a g e Appendix A Marking Sheet for ICT701 Task 2 Student name: Student ID: Items Maximum Marks Marks Obtained PART A: DESIGN (40 marks made up of) - ER Diagram (25 marks) o Completeness (participation & cardinality constraints & all relevant data represented) o Accuracy - Normalisation (10 marks) - Assumptions/Additional Information (5 marks) 40 PART B: IMPLEMENTATION (60 marks made up of) - SQL STATEMENTS: o CREATE TABLE (15 marks) ▪ Consider data duplication & appropriateness of table design o Integrity Constraints (5 marks) o INSERT Statements (10 Marks) o QUERIES – (25 marks total) o INDEX - (5 marks) 60 Total = 100 /40% OVERALL COMMENTS: ICT701 Relational Database Systems Task 2 8 | P a g e Appendix B – Staff Abbreviated CV Abbreviated CV –Teaching Staff Notes: • This abbreviated CV (Curriculum Vitae) is required for all teaching staff working in the organisation. • This abbreviated CV is required in addition to a copy of your standard CV (or resume). • All