ASSIGNMENT ONE     15 marks
    Due Friday 23/9/2011 at 5pm

    Released on 3/08/2011, last updated 3/08

     
    This assignment is a group assignment.

    This assignment needs to be completed in group of 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. Students not willing or not able to form a group in a timely manner will have to do the complete assignment on his or her own.

    It is anticipated that most students will finish forming their assignment group within 2 weeks, that is, within semester week 3 and week 4.

    Database Modelling

    In this assignment, you are asked to design a database to support the management of a beauty parlour. The major business requirements are summarised below in the Mini Case: Beauty Salon. 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. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation, etc. The ER diagram should in general have more than 5 entity or relationship types and no more than 15. This means that students will have to selectively identify those most important entity types and relationship types for their work and for the fulfillment of the key business functionalities. More concretely, your deliverables should include

    1. Executive Summary on the enterprise need in succinct terms, and on what has actually been done in your assignment and to what extent. It must be concise and right to the point. (1 mark)
    2. A list of supplementary business rules or assumptions that underpin your particular database design. When pertinent, link them to the part of your database design with short comments. (2 marks)
    3. A detailed ER diagram for the model, highlighting primary keys, multiplicity constraints, generalisation/specialisation, important attributes, and other pertinent details. (7 marks)
      • You must use the same notation scheme for the ER diagram as the textbook, and the ER diagram should be strictly in the sense the textbook uses. We note that past experiences show drawing your ER diagram on Microsoft Powerpoint gives one better control and flexibility when compared with drawing diagrams on Microsoft Word.
      • The ER diagram should include, among others, representative attributes for all entity types, proper subclassing, and correct participation multiplicities for the relationship types. It should be meaningfully and well designed, and should also include all relevant and necessary aspects (5 marks)
      • Among all the relationships in your design, find a relatively nontrivial one in terms of its multiplicity constraints and explain explicitly the meaning or implication of those multiplicity constraints. (0.5 mark)
      • Among all the weak entity types, explain for one of them why it is a weak entity type. (0.5 mark)
      • Among all the generalisation/specialisation cases in the ER diagram, find a relatively nontrivial one in terms of the subclassing complexity, and explain the meaning of the actual subclassing in this case and the mearning of the associated participation and disjoint constraints. (1 mark)
    4. A list of all the entity types and relationship types in the form of schemas. (2 marks)
      DATABASE SCHEMAS EXAMPLES:
      1. Staff(staffId, firstName, lastName, phone, mobile, email);
      2. Provides(therapistId, serviceId);
      ...
      
    5. A list of functional dependencies, with comments when pertinent. (2 marks)
    6. A list of 5 most important (and mutually dissimilar as much as possible) queries in English (not SQL) a beauty parlour as a business will typically make through this database system, and an indication on which of these queries are feasible (i.e. achievable) under your current design. (1 mark)
    7. A statement on the work distribution in percentage (e.g. 50% for David and 50% for Louise) agreed among all the group members. If this statement is absent, then it will be assumed that all group members have made equal amount of contribution to the assignment solution. Achieving a 50%/50% work distribution is also the goal of this team work; the person who contributes less than 50% may result in having less mark than the other team member.
    8. A hardcopy of your electronic submission is also due at the same time as the electronic submission, and needs to be delivered on your own campus. More precise locations will be announced at vUWS in the due course. However, a student may choose to deliver the hardcopy on the Monday immediately following the deadline date regardless whether this is a mid-semester break. The hardcopy must be the same as the submitted softcopy. The electronic submission is the official submission, submitting a hardcopy without submitting the electronic copy within the due date will be deemed NOT having submitted the assignment.

      NOTE: If a hardcopy is not received by the above specified date for a student, then the student will eventually receive only the mark for the assignment derived from a marking sheet, and will not be able to get the more detailed feedback that could be otherwise written to your submitted hardcopy.

    Mini Case: Beauty Salon

    Beauty Salon is a system to be designed to manage the booking and the payment of a single beauty parlour.

    Beauty Therapists: A beauty parlour has a number of staff members most of which are beauty therapists. Some beauty therapists are well-established professionals while others may just be the trainees. Different thereapists may also have different availability for work. For instance, some may not work on Saturdays while others might be on leave for a few weeks.

    Beauty Care and Treatments: There are a number of beauty treatments or services a therapist can undertake, including manicure, pedicure, waxing, threading, facials and massages, to name a few. Different therapists may be able to provide different ranges of specific services, depending on the actual individuals. Some (item-based) services such as waxing will be charged per item while others (time-based) such as massage will be charged per half an hour or per hour. The fees for item-based services are fixed across all the therapists. But the fees for time-based services may vary among the therapists due to their different level of expertise.

    Treatment Venues: When a booking is being made, it is possible to agree on a specific venue to conduct the services. Such venues could be other similar parlours or shops at which a particular beauty therapist also works.

    Clients: Each regular client or patron will typically have her own client record set up on the system, and this will allow her to easily make a booking or make a payment. However, a non-regular customer will also be able to turn up in the beauty parlour and request a service to be done to her. Such customers don't have to create their client profiles and can still receive the services when they just turn up in the parlour, provided there are suitable therapists available at the parlour at the time.

    Payments: The payment for the services of each booking or appointment will also be recorded. The payment can be made in cash, or via a credit card. The payment details will be recorded accordingly.

    Business Activities: For the typical business activities, the Beauty Salon system will allow one to view which appointments have been made on any given day for any particular therapist, list all the available therapists for a given period of time, browse all the payments for a given day, and many more.

    Note on Submission

    • This assignment must be submitted electronically via vUWS before the due date. No email submissions will be accepted.
    • It is the students' responsibility to retrieve and keep all their submission receipts. If in doubt, consult your tutors well before the submission due date.
    • Submitted files may be zipped together as a single zip file (but not as a zipx file), if a student wishes to do so. However, no other file compression or file archiving formats will be accepted for the submission.
    • Each group must submit exactly one copy of their assignment solution electronically by one of the team members. If the other group member really wants to submit it as well due to whatever reasons, then the name of the submitted files must start with "please_ignore_" (such files will not be treated as regular submissions and will be ignored during the marking). Otherwise 1 mark may be deducted for the duplicated electronic submission.
    • Each submission must be accompanied by a declaration of the ownership of the submitted work as described in the unit outline and learning guide. No signature is however required for the electronic submissions. Please note that an examiner or lecturer/tutor has the right not to mark this assignment if a pertinent declaration is not present in your submission.
    • Late submissions will attract a daily incremented late penalty of 10% per day.
    • Electronic submission on the due date after 5pm before 12 midnight will still be accepted without penalty. However, any submission failure in that period due to either the student faults or the fault or malfunction of the School's or UWS' servers will not be accepted as the legitimate reasons for a late submission. Beware that School's servers often need to be shut down for maintenance from late Fridays or just before public holidays.