Assignment title: Information

Law Associates is a large legal practice based in Melbourne. The practice employs nearly forty 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 or an intern. The practice stores the following information for all lawyers: Name, address, telephone, email, base salary and one area of speciality. 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 expert) in that area. For associates, the practice also stores details about the percentage of cases the associate has won. Partners and Associates engage in legal proceedings. The following details about each legal proceeding are stored: identifier, procedure details and the status of the legal proceeding. Each partner or associate may be engaged in a number of legal proceedings and each legal proceeding may have up to five lawyers (partners or associates or both). We also store the percentage of workload of each lawyer in each legal proceeding. 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, email and Law Society accreditation number for all training organisations. When a lawyer completes a course, the grade received on that course is recorded. Finally, 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. 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. (15 Marks) 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. (15 Marks) 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. (10 Marks) 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. 1. Five marks awarded for each correct SQL statement. 2. Alternative approaches to the model answer could be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL). 3. Part marks may be awarded if an answer only has a small problem or an alternative solution is presented that works but is not ideally optimised / constructed. 4. There are 6 questions for the total of 30 marks. Below is a reproduction of the ERD for the JustLee books database. It should help you navigate the tables in the database. The database script to create the tables is located on the Moodle site under the assignment specifications. If you have run a version of the script earlier in the semester please run it again to ensure that you are using the correct version of the tables. The ERD diagram may not necessarily have all the fields listed so you might have to refer to the description of the table by using the DESCRIBE command in Oracle. 1) Display the book title and the number of books sold where the profit from the book is more the 70%. The resulting list should display highest quantity of books title sold first in the list. Profit for a book is calculated as (retail – cost) / cost. 2) Display the book category, publisher name and average price for books which belong to the either a category of 'COMPUTER' or "CHILDREN' and where the average retail cost for the category is more than 50 dollars. Rename the calculated field 'AVERAGE_CAT_PRICE' and format with a leading '$' symbol and rounded to two decimal places. Order the result by book category ascending and average category price descending 3) Display the categories and the count of number of books in those categories where the category has the more books then the minimum number of books for all the categories. Sort the resulting set in category order ascending.