SECTION A (Data Modelling)
Law Associates is a large legal practice based in Sydney. You have been asked to design a data model for the practice based upon the following specification:
The practice employs nearly sixty lawyers who work in a wide variety of specialty areas. A speciality id and description is stored for each speciality. Each lawyer employed by the practice is classified as a partner, an associate, a junior or an intern. The practice stores the following information for all lawyers: Name, address, telephone, email, base salary and the one area of law in which that lawyer specialises. For partners, the practice also stores information about the percentage of the partnership held by the lawyer and the area of speciality that lawyer leads. Each area of speciality has one partner who acts as a leader (or resident expert and consultant) in that area. For associates, the practice also stores details about the percentage of cases the associate has won.
Juniors and interns undertake prescribed training courses and a record is kept. All training courses are registered and the name of the course, the duration (in days), the start date, the end date and details about the training organisation. Although a course is only offered by one training organisation, these organisations typically offer many courses. We store the name, address, telephone, email and Law Society accreditation number for all training organisations. When a lawyer completes a course, the grade received on that course is recorded.
We store details about the qualifications of all lawyers. Each lawyer may have many qualifications and a list of qualifications is stored, including the name of the qualification, its level (undergraduate or postgraduate) and the name of the university offering the qualification.
Each lawyer may take on many cases. A case is assigned to at least one lawyer but may be assigned to as many as four. If more than one lawyer is assigned to the case, one of the lawyers must be designated as a leader. Only seniors may be leaders of cases involving multiple lawyers. A case must have one or more clients but a client may initiate many cases over time. For case, we store a unique identification number, the estimated duration of the case, the client number/s of the clients involved, and the estimated start date. For client, we store an identification number, name and address. When any lawyer is assigned to a case, we store the date assigned and the date the lawyer leaves the case. A lawyer may become involved in a case on multiple occasions.
As cases can take years to resolve during the cases proceedings the layer in charge of the case may be unavailable as they be working on other cases. During this period the work associated with the case is assigned to the junior lawyer with mentoring from the layer originally assigned to the case. Upon the return of the principal lawyer to the case an evaluation is done of the assisting lawyer to record their performance for future evaluation. In the instance where a junior is assigned to a case where more than one lawyer is involved the other layers provide an evaluation of the junior lawyer as well. During the length of the case a number of junior layers could be assigned to assist with the case.
Prepare the following:
a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.
b) A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.
NOTE: We use PART MARKING for the relations and will mark four relations chosen at random.
c) A single SQL statement or multiple statements that create/s the table for the relation training courses for jurors and interns. All key and attribute constraints should be included and data types suitable to each attribute should be chosen.
SECTION B (Normalisation)
Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. Show your working and entitles for 1NF, 2NF and 3NF. You must use the Finkelstein methodology as used in the study book and tutorials.
PATIENT(patient id, name, address, age, gender, admitting doctor id, admitting doctor name, admitting doctor grade, date of admission, date of release, number of days in hospital, ((ward id, ward name, (( bed number, date arrived, date departed )) )), reason for admission)
Notes:
1. A new patient id is assigned for each admission to hospital
2. The admission to hospital must be authorised by only one admitting doctor.
3. During the hospital stay, the patient may be moved from ward to ward and from bed to bed within a ward.
4. Bed numbers are not unique within the hospital but are unique within the ward.
Attachment:- assignment2-specification2014v3.doc