Logical Database Design
1. For the ER diagram you created in assignment 1, the artefact of the conceptual database design, map the ER model into the relational model according to how it was designed in the ER diagram. You may however first refine or completely re-do your ER diagram if necessary, and you are allowed to make use of any part of our above displayed ER diagram skeleton to incorporate into your design in any way you like if you feel your original design is not in a state to be implemented later. The actual assessment of this part is in the point 4 below. While there can be a variety of acceptable designs, we here attach one simplistic rough sketch for the comparison purpose.
2. Consider a greatly simplified core recruitment system whose ER diagram is shown on the right.
Draw the corresponding GRD, exhibiting all the primary keys and foreign keys. For simplicity, no other attributes nor multiplicity constraints are required.
Are the tables in the GRD all in 3NF?
For a particular casual job, if none of the (registered) casual staff meet the requirements on the corresponding expertise, will the primary key or foreign key constraints in your GRD prevent the database system from assigning an unqualified casual to the job? Briefly explain why.
For all the relations that arise from this ("first-cut") ER diagram, list all those (in schemas) that are already in 3NF. If there are some relations that are not in 3NF yet, list them as well.
3. Draw the global relation diagram for your final, revised, and normalised database design, and keep all the relevant details there. It should be in a form similar to Figure (page 516) of the textbook, but all the attributes should be kept there too. Include in the diagram all the primary keys, foreign keys, and the multiplicity constraints. Identify and discuss the potential data redundancies or anomalies that may still exist in your design, if any.
4. Physical Database Design and SQL Queries
For the physical implementation of the database, students are allowed to implement the following simplifications in their table and data design.
Availability for the casual staff doesn't have to be considered. That is, you don't have to worry about whether a casual staff works only on Mondays, or if he or she is going away for a whole month, or anything like these. Hence the casual staff can be assumed to be available all the time other than those time slots already booked by this same recruitment system for another casual work.
The job offer/acceptance tracking, or the negotiation between the School and the casuals, doesn't have to be considered. In other words, the ERD given at the top of this document would reduce to "a more simplistic design", the one on the right.
5. Create the database tables in SQL (runnable on the School's Microsoft SQL Server) for all the relations in your relation diagram, and enforce there all the relevant constraints including primary keys and foreign keys. Fill the tables with sufficient data - generally around 4 tuples or more per table, but should be sufficient to illustrate meaningfully the working of the general queries to be completed below. List the content of your tables with screenshots.
Screenshots of active windows (under Microsoft Windows) can be obtained by pressing CTRL-ALT-PRTSC keys together, see the example on the right. Your screenshots must be readable and contain your username as in the above example, and you may list several tables on a single screenshot as is the example on the right.
6. Write in SQL the commands to complete the following queries, and show your results in screenshots.
(a) Write a drop table statement so that it execution will delete all the tables you have created for this assignment. No partial mark will be given for this part, if the statement doesn't do the complete job
WARNING: Before you test this, you must first make sure that you have saved all the statements for the table creation and the record insertion etc in a separate SQL file saved outside the SQL Server.
This is to ensure that after the drop table statement deletes everything, you can re-create everything by running your saved SQL script. If
you are not sure, don't to this part.
(b) For all the casual staff, list their names and the corresponding mobile phone numbers.
(c) For all the casual staff who hold one or more qualifications, list their names and the corresponding qualifications.
(d) List who supervise which casual staff.
(e) For all casual staff, list their expertise (i.e. the subjects each staff is familiar with), the corresponding years of experience, and their self-rating on their competetiveness. Order the output in terms of the staff name, self rating and experience.
(f) For a given date, say 1 Oct 2014, list all the casual positions for that day that are still available, i.e. still under recruitment.
(g) For a given date, say 1 Oct 2014, list all the casual positions for that day, which of those positions is staffed by whom, which are yet to be staffed, the teaching timeslot and the venue. The output should be properly sorted.
(h) For all casual staff who have been recruited for a casual duty (of regular type), calculate the total number of hours worked or to be worked each week for each category of the positions, and the total number of weeks.
7. For each casual job, list all those casual staff who are suitable for the job, ignoring their availability for the timeslots. In other words, the suitability will not be affected by whether a casual can only work on certain days or he has already got a commitment for another casual job in the School.
8. For your final designed database, find a scenario in which data integrity can not be ensured by your current primary keys and foreign keys, nor by adding directly more of such keys. Write SQL statement/s that will determine if such a problem exists or not for any given state of the database.
9. A single plain-text file containing SQL statements for creating all the tables and making all the queries. The script should be executable on the School's Microsoft SQL Server, otherwise the corresponding marks in the above listed items will be deducted accordingly. Marks will be deducted for the corresponding questions if this SQL script in plain-text file is not submitted.
10. Each student must state explicitly who he or she once teamed up with if that person is not currently the group member for the submission, unless no shared work is involved. Students are not permitted to have shared work for this assignment with more than one person (the team member) including potential former team member, unless approved by the unit coordinator in writing.