2017
Database Operations With Normalization DATABASE ASSIGNMENT HELP
Normalization Assignment: Case Study for Section A 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 specialty. 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 specializes. For partners, the practice also stores information about the percentage of the partnership held by the lawyer and the area of specialty 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. [Learn about Database Security Assignment] 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.
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
Section A: Data Modelling Ans A.a: ER Diagram for Lawyers
Ans A.b: List of Relations All the list of relations are shown in ER Diagram itself. Below are the list of tables used to solve the given case study. Database is created in 3NF. Case_Master
Column Name
Type & Size
Constraint
Description
Case_ID
Number(10)
PK
Unique Identifier of Case
Start_Date
Date
www.programmingassignmentshelp.net
Case Start Date
help@programmingassignmentshelp.net
End_Date
Date
Case End Date
Lawyer_Id
Number(10)
FK
Lawyer_Id from Lawyer_Master
Client_Id
Number(10)
FK
Client_Id from Client_Master
FK
Leader_Id from Lawyer_Master where Is_Leader is ‘Y’
Leader_Id
Number(10)
Estimated_Duration
Number(10)
Expected Duration for Case
Case_Transaction
Column Name
Type & Size
Constraint
Description
Case_ID
Number(10)
FK
Case_Id from Case_Master
Lawyer_Id
Number(10)
FK
Lawyer_Id from Lawyer_Master
AssignedDate
Date
EndDate
Date
FK
Leader_Id from Lawyer_Master where Is_Leader is ‘Y’
Leader_Id
Number(10)
Course_Master
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
Column Name
Type & Size
Constraint
Description
Course_Id
Number(10)
PK
Unique Identifier for Course
NameofCourse
Varchar(100)
Course Name
CourseDuration
Number(10)
Course Duration
Descriptiom
Varchar(200)
Course Description
Start_date
Date
Course Start_Date
End_date
Date
Course End Date
Tranining_Organization
Column Name
Type & Size
Constrain t
Description
Organization_Id
Number(10)
PK
Unique identifier for organization
Name
Varchar(100 )
Organization Name
Address
Varchar(100 )
Organization Address
Telephone
Number(20)
Organization Telephone
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
Varchar(100 )
Organization email
LawSocietyAccrediationNumbe r
Number(20)
Organization LawSocietyAccrediationNumbe r
Course_Id
Number(10)
FK
Course_Id from Course_Master
Tranining_Master
Column Name
Type & Size
Constraint
Description
Training_Id
Number(10)
PK
Unique identifier for tranining
Description
Varchar(100)
Course_Id
Number(10)
Duration
Number(10)
Lawyer_Id
Number(10)
FK
Lawyer_Id from Lawyer_Master
Organization_Id
Number(10)
FK
Organization_Id from Training_Organization
Course_id
Number(10)
FK
Course_Id from Course_Master
Traning Description
FK
Course_Id from Course_Master
Training Duration
Course_Transaction
Column Name
www.programmingassignmentshelp.net
Type & Size
Constraint
Description
help@programmingassignmentshelp.net
Transaction_Id
Number(10)
PK
Unique identifier for Course Transaction
Course_Id
Number(10)
FK
Course_Id from Course_Master
Training_ID
Number(10)
FK
Training_Id from Training_Master
Start_Date
Date
Course Start Date
End_Date
Date
Course End Date
Grade
Varchar(3)
Course Grade
Lawyer_Master
Column Name
Type & Size
Constraint
Description
Name
Varchar(100)
Name of Lawyer
Address
Varchar(100)
Address of Lawyer
Read more about Computer Programming Assignments Help Read More about Computer Network Assignment Help United Kingdom. Our online assignment writing help UK is especially dedicated for the students studying in all UK colleges and universities. Submit assignment to get the best quality assignment help.
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net