ASSIGNMENT TWO

15 marks

Due Fri Oct 27 2023 at 8pm

Released on 18/9/2023, last updated xx/10

This assignment should be done in a group of no more than 2 students.

This assignment needs to be completed in a group of no more than 2 student members. If such a group is formed, both members must come from the same campus, and also within the tutorial classes of the same tutor whenever possible. While lecturers and tutors will help as much as they can, it is essentially each student's own motivation and responsibility to form a group for this assignment. By default, each student is working in a group containing a single member of himself. Students may however make use of the student communication board to advertise their availability in seeking an assignment partnership.

In the rare case of one group member becoming seriously ill or uncontactable or not responding, the other member should consider forming a different group or working on his or her own for the assignment. As in real life, everyone should have a contingency plan, or Plan B, and failure of assignment partnership at any point of time will not be accepted as the legitimate reasons for an assignment extension. However, a student is obligated to properly notify his or her existing assignment partner in good time should it become imperative that the student need to terminate the existing partnership due to unexpected circumstances.

Marks are not necessarily always proportional to the complexity of the questions or the amount of time required to have the problems solved, as we may often need to allocate more marks to the more fundamental theories and techniques to ensure they are well covered.

Students enrolled in INFS2002 - Database Design and Development (Advanced) must also complete the advanced part by the same due date.

Otherwise the screenshots will be deemed having not been submitted.

Students' main document submitted for their answers to this Assignment must be written in

Microsoft Word, not in PDF.

Q1. Selected additional exercises (5 marks)

i. Complete Questions a and y in the Additional Exercises for Practical 8. (2=.5+1.5 marks) i. Complete Questions 8 in the Additional Exercises for Practical 9. (1 mark) iii. Complete Questions a and B in the Additional Exercises for Practical 10. (1=.5+.5 mark)

iv. Complete Questions a and B in the Additional Exercises for Practical 11. (1=.5+.5 mark)

Q2. Database modelling (5 marks) In this part, you are asked to design a database to support a Instant Recruitment System of casual staff for a school. The major business requirements are summarised below in the Mini Case: An Instant Recruitment System. You are asked to develop a detailed Entity-Relationship model for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made where applicable. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation if any, etc. You must use the same notation scheme for the ER diagram as the textbook (use UML notations as shown in the last page of the textbook, and don't use Crew Foot notations),

and the ER diagram should be strictly in the way the textbook uses for. For this assignment, you may consider making use of the drawing tool https://draw.io, or simply Microsoft PowerPoint. We note that our past experiences show drawing ER diagrams or relation diagrams on Microsoft Powerpoint gives one better control and flexibility when compared with drawing diagrams

with Microsoft Word. i. The ER diagram should include, among others, representative attributes for all entity types, proper subclassing if any, and correct participation multiplicities for the relationship types. It should be meaningfully and well designed, and should also include all relevant and necessary aspects, and indicate any supplementary business rules if you decide to introduce. In any case, supply at least 1 concise business rule that you feel quite important to your resulting database design. (3 marks) in. Map the above ER diagram into a global relation diagram (GRD). The GRD should be in a form similar to Figure 17.9 (page 554, or 516 for edition 5) 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. (2 marks) in. Please note that an ERD is the artefact of the conceptual database design phase, while a GRD is the artfact of the logical database design phase which relates to the relational models. As such, a good