Law Associates in Melbourne

Questions: 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. Prepare the following: 1. 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. 2.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) 3. 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. Answers: ER Diagram Relations 1. Speciality Attribute Datetype Key Null/Not Null SpecialityID Number Primary Key NOT NULL Description VARCHAR(500) NOT NULL 2. Lawyer Attribute Datetype Key Null/Not Null LawyerID Number Primary Key NOT NULL Name VARCHAR(50) NOT NULL Address VARCHAR(100) NOT NULL Email VARCHAR(50) NOT NULL PhoneNo VARCHAR(50) NOT NULL BaseSalary Number NOT NULL SpecialityID Number Foreign Key NOT NULL 3. Legal Attribute Datetype Key Null/Not Null LegalID Number Primary Key NOT NULL Details VARCHAR(100) NOT NULL Status VARCHAR(10) NOT NULL 4. SpecialityArea Attribute Datetype Key Null/Not Null SpecialityAreaID Number Primary Key NOT NULL Description VARCHAR(100) NOT NULL 5. Partner Attribute Datetype Key Null/Not Null LawyerID Number Primary Key NOT NULL PartnershipPercentage Number NOT NULL SpecialityAreaID Number Foreign Key NOT NULL 6. Associate Attribute Datetype Key Null/Not Null LawyerID Number Primary Key NOT NULL CasePercentage Number NOT NULL 7. LegalProceeding Attribute Datetype Key Null/Not Null LegalID Number Primary/Foreign Key NOT NULL LawyerID Number Primary/Foreign Key NOT NULL WorkloadPercentage Number NOT NULL 8. SpecialAreaLeader Attribute Datetype Key Null/Not Null SpecialityAreaID Number Primary/Foreign Key NOT NULL LeaderID Number Primary/Foreign Key NOT NULL 9. Organization Attribute Datetype Key Null/Not Null LSANumber Number Primary Key NOT NULL Name VARCHAR(50) NOT NULL Address VARCHAR(100) NOT NULL Email VARCHAR(50) NOT NULL PhoneNo VARCHAR(50) NOT NULL 10. TrainingCourse Attribute Datetype Key Null/Not Null CourseID Number Primary Key NOT NULL Name VARCHAR(50) NOT NULL Duration Number NOT NULL StartDate Date NOT NULL EndDate Date NOT NULL LSANumber Number Foreign Key NOT NULL 11. Intern Attribute Datetype Key Null/Not Null LawyerID Number Primary/Foreign Key NOT NULL CourseID Number Primary/ Foreign Key NOT NULL Grade VARCHAR(2) NOT NULL CompletionDate Date NOT NULL SQL Statements for Training Courses CREATE TABLE Organization (LSANumber NUMBER(10), Name VARCHAR2(50), Address VARCHAR2(100), Email VARCHAR2(50), Phone VARCHAR2(12), CONSTRAINT Organization_LSANumber_pk PRIMARY KEY(LSANumber) CREATE TABLE TrainingCourse (CourseID NUMBER(4), Name VARCHAR2(30), Duration NUMBER(2) StartDate DATE, EndDate DATE, LSANumber NUMBER(10), CONSTRAINT TrainingCourse_CourseID_pk PRIMARY KEY(CourseID), CONSTRAINT TrainingCourse_LSANumber_fk FOREIGN KEY (LSANumber). REFERENCES Organization (LSANumber));