Brief Overview:
The application manages data of various aspects of the firm, like details of lawyers, their ongoing cases, employee earnings, partner earnings, firm earnings, firm expenditure etc. The Managing Partner of the firm lies at the topmost position in the hierarchy of stakeholders and has access to all the information in the database. All the other stakeholders i.e. Firm Partners, Employees (Associates, Paralegals, Secretaries, Office management, IT, HR, Finance and Accounting, Support staff etc.), Clients, Bar Council etc. have various levels of authorization and their views are tailored accordingly.
Problem statement
------------------------------------------------------------------
- Finding the best suited lawyer on the basis of track record, time taken to solve cases, fee etc.
- Helping young associates find their career forte on the basis of track record while working on cases in various legal fields.
Relationships
------------------------------------------------------------------
- customer isA User
- contact isA User
- An Individualclient isA customer
- A CompanyClient isA customer if there is a corporate
- Employee isA User
- An lawyer isA employee
- All other staff isA Employee
- Employee receive financial transaction
- Customer hasA legal cases
- Customer owes financial transaction to be paid as a fee or charge
- Customer hires lawyers
- Lawyer handles legal cases
- Legal Cases are documented by Legal Documents
- Legal cases demand invest ment of finances
- Hearing of legal cases is held On hearing Date
- User keeps tracks of schedule using calendar
- Court Hearing are displayedIn Calendar
Schema
--------------------------------------------------------------------------
-
lawyer (userID : char(10), emailID : varchar(256), phoneNumber : char(10), address**(streetName : varchar(256), pincode: varchar(10), buildingNumber : int, city : varchar(50), state : varchar(50))**, name(firstName : varchar(30), middleName : varchar(30), lastName : varchar(30)), dateOfBirth : date, experience : mediumint , gender : varchar(30), casesLost : int, casesSettled : mediumint, casesWon : mediumint, avgTimePerCase : mediumint, {specialization} : varchar(256), charges : mediumint, positionAtFirm : varchar(100), clientRating : mediumint, calendarID : char(10)).
-
otherStaff (userID: char(10), emailID : varchar(256), phoneNumber:char(10), address(streetName : varchar(256), pincode : varchar(10), buildingNumber : int, city : varchar(50), state : varchar(50)), name(firstName : varchar(30), middleName : varchar(30), lastName : varchar(30)), dateOfBirth : date, experience : mediumint, gender : varchar(30), positionAtFirm : varchar(100), salary : mediumint, calendarID : char(10)).
-
opposition (oppositionID : char(10), emailID : varchar(256), phoneNumber: char(10), name(firstName : varchar(30), middleName : varchar(30), lastName : varchar(30)), address(streetName : varchar(50), pincode : varchar(10), buildingNumber : int, city : varchar(50), state : varchar(50))
-
legalCases (caseID: char(15), dateOfFiling : date, dateOfLastActivity : date, status : varchar(256), duration : mediumint ,{flair} : varchar(256), plaintiff : varchar(256)).
-
courtHearing (caseID : char(10), time: time, courtRoom : varchar(256), nextHearingDate : date).
-
financialTransaction (transactionID : char(10), amount : mediumint, description : varchar(256), type : tinyint, dateOfPayment : date)
-
legalDocument (docID : char(10), createdOn : date, type : varchar(256) , visibility : tinyint, dateLastModified : date, caseID : char(15)).
-
calendar (calendarID : char(10), deadline: varchar(256), description : varchar(256))
-
clientCompanies (userID : char(10), isClient: tinyint, emailID : varchar(256), phoneNumber : char(10), address(streetName : varchar(256), pincode : varchar(10), buildingNumber : int, city : varchar(50), state : varchar(50)),name(firstName : varchar(30), middleName : varchar(30), lastName : varchar(30)), budget : mediumint, companyName : varchar(256), gstIN, fax : varchar(100), calendarID).
-
individualClients (userID : char(10), isClient: tinyint, emailID : varchar(256), phoneNumber : char(10), address(streetName : varchar(256), pincode : varchar(10), buildingNumber : int, city : varchar(50), state : varchar(50)),name(firstName : varchar(30), middleName : varchar(30), lastName : varchar(30)), budget : mediumint, dateofBirth : date, calendarID : char(256)).
-
handles (caseID : char(15), userID : char(10))
-
hasA (userID : char(10), caseID : char(15))
-
displayedIn (calendarID : char(10), caseID : char(15),time: time)
-
oweUs (transactionID: char(10), userID: char(10))
-
receive (transactionID:char(10) , userID: char(10))
-
user (userID:char(10) , password: char(8))
-
hire (userID : char(10), lawyerID : char(10))
-
against (oppositionID : char(10), caseID : char(15))
-
makes (transactionID:char(10), userID:char(10))
-
Invest (caseID : char(15), transactionID : char(10))
Lawyer
-------------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10) | Primary Key, Not null |
| firstName | varchar(30) | Not null except middleName which is optional, composite attribute (name) |
| middleName | varchar(30) | |
| lastName | varchar(30) | |
| dateOfBirth | date | Not null |
| gender | varchar(30) | Not null |
| charges | mediumint | Not null, check >= 2000 and <= 20000 |
| casesWon | mediumint | Not null |
| casesLost | mediumint | Not null |
| casesSettled | mediumint | Not null |
| experience | mediumint | Not null |
| emailID | varchar(256) | Not null |
| phoneNumber | char(10) | Not null |
| positionAtFirm | varchar(100) | Not null, check in Associate, Lawyer, Paralegal, Partner |
| avgTimePerCase | mediumint | |
| buildingNumber | int | Not null, composite attribute (address) |
| pincode | int | |
| streetName | varchar(256) | |
| city | varchar(50) | |
| state | varchar(50) | |
| specialization | varchar(256) | Not null, part of Primary Key |
| clientRating | mediumint | Not null, check between 0 and 10 |
| calendarID | char(10) | Not null, Foreign Key referenced by Calendar |
LegalCases -------------------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| caseID | char(15) | Primary Key, Not null |
| status (Active/Settled/Won/Lost) | varchar(256) | Not null, ("active","lost","won", "settled") |
| plaintiff | varchar(256) | Not null |
| dateOfFiling | date | Not null |
| lastDateOfActivity | date | Not null |
| duration | mediumint | Not null |
| flair | varchar(256) | Not null |
LegalDocuments
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| docID | char(10) | Primary Key |
| createdOn | date | Not Null |
| caseID | char(15) | Foreign Key referenced by LegalCases, part of Primary Key along with docID, Not Null |
| dateLastModified | date | Not null |
| visibility | tinyint | Not Null, check 0 or 1 |
| type | varchar(256) | Not Null |
ClientCompanies
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10)) | Primary Key |
| isClient | tinyint | Not null |
| firstName | varchar(30) | Not null except middleName which is optional, composite attribute (name) |
| middleName | varchar(30) | |
| lastName | varchar(30) | |
| phoneNumber | char(10) | Not null |
| emailID | varchar(256) | Not null |
| fax | varchar(100) | |
| buildingNumber | int | Not null, composite attribute (address) |
| pincode | varchar(10) | |
| streetName | varchar(256) | |
| city | varchar(50) | |
| state | varchar(50) | |
| budget | mediumint | Not null |
| companyName | varchar(256) | Not null |
| gstIN | varchar(11) | |
| calendarID | char(10) | Foreign Key referenced by Calendar |
CourtHearing
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| caseID | char(15) | Foreign Key part of Primary Key along with time, not null |
| time | time | Part of Primary Key along with caseID, Not null, check (time > '09:00' AND time < '19:00') |
| nextHearingDate | date | |
| courtRoom | varchar(256) | Not null |
DisplayedIn
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|
| userID | char(10) | Not null, Foreign key, part of Primary key along with caseID, whentt | | caseID | char(15) | Not null, Foreign key, part of Primary key along with userID, whentt | | whentt | datetime | Not null, Foreign key, part of Primary key along with userID, caseID |
IndividualClients
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10) | Primary Key |
| isClient | tinyint | Not null, check is '0' or '1' |
| firstName | varchar(30) | Not null except middleName which is optional, composite attribute (name) |
| middleName | varchar(30) | |
| lastName | varchar(30) | |
| phoneNumber | char(10) | Not null |
| emailID | varchar(256) | Not null |
| dateOfBirth | date | Not null |
| buildingNumber | int | Not null, composite attribute (address) |
| pincode | varchar(10) | |
| streetName | varchar(256) | |
| city | varchar(50) | |
| state | varchar(50) | |
| budget | mediumint | Not null, check != 0 |
| calendarID | char(10) | Foreign Key referenced by Calendar |
Handles
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| caseID | char(15) | Not null, Foreign Key part of Primary Key along with userID |
| userID | char(10) | Not null, Foreign Key part of Primary Key along with caseID |
HasA
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10) | Not null, Foreign Key part of Primary Key along with caseID |
| caseID | char(15) | Not null, Foreign Key part of Primary Key along with userID |
OtherStaff
-------------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10) | Primary, Not null |
| firstName | varchar(30) | Not null except middleName which is optional, composite attribute (name) |
| middleName | varchar(30) | |
| lastName | varchar(30) | |
| dateOfBirth | date | Not null |
| gender | varchar(30) | Not null |
| emailID | varchar(256) | Not null |
| phoneNumber | char(10) | Not null |
| experience | mediumint | Not null |
| salary | mediumint | Not null, check >= 2000 and <= 20000 |
| positionAtFirm | varchar(100) | Not null |
| buildingNumber | int | Not null, composite attribute (address) |
| pincode | varchar(10) | |
| streetName | varchar(256) | |
| city | varchar(50) | |
| state | varchar(50) | |
| calendarID | char(10) | Foreign Key referenced by Calendar |
Makes
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| transactionID | char(10) | Not null, Foreign Key part of Primary Key along with userID |
| userID | char(10) | Not null, Foreign Key part of Primary Key along with transactionID |
Invest
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| caseID | char(15) | Not null, Foreign Key part of Primary Key along with transactionID |
| transactionID | char(10) | Not null, Foreign Key part of Primary Key along with caseID |
FinancialTransaction
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| transactionID | char(10) | Primary Key |
| description | varchar(256) | |
| amount | mediumint | Not null, check not equal to 0 |
| type | tinyint | Not null, check is '1' or '0' for take and give respectively |
| dateOfPayment | date | Not null |
User
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10) | Primary key along with caseID, Not null |
| password | char(8) | Not null |
Against
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| oppositionID | char(10) | Foreign key, Primary key along with oppositionID, Not null |
| caseID | char(15) | Foreign key, Primary key along with caseID, Not null |
Calendar
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10) | Primary Key |
| whentt | datetime | Not null, part of Primary Key along with calendarID |
| name | varchar(256) | Not null |
| description | varchar(256) | Not null |
Hires
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| userID | char(10) | Not null, Foreign Key part of Primary Key along with lawyerID |
| lawyerID | char(10) | Not null, Foreign Key part of Primary Key along with userID |
Opposition
--------------------------------------------------------------------------
| Variable | Datatype | Integrity Constraints |
|---|---|---|
| oppositionID | char(10) | Primary Key |
| emailID | varchar(256) | Not null |
| phoneNumber | char(10) | Not null |
| firstName | varchar(30) | Not null except middleName which is optional, composite attribute (name) |
| middleName | varchar(30) | |
| lastName | varchar(30) | |
| buildingNumber | int | Composite attribute (address) |
| pincode | varchar(10) | |
| streetName | varchar(256) | |
| city | varchar(50) | |
| state | varchar(50) |
Type of cases we will be covering:
Divorce|Tenant Law|Crime|Civil|Real Estate|Bankruptcy|Finance|Family Law|Traffic Law|Wills and Probate|Child Custody|Medical Malpractice|Construction Law|Copyrights and Patents|Education|Felony|Health Insurance|Income Tax|LGBTQ Law|Oil and Gas|Shipping|Visitation Rights|Wrongful Death|Whistleblower Litigation|Sports Law|Restraining Orders|Military Law|Labour and Employment|Investments|Homicide|Politics|Communications and Media|Drug Crimes|Agricultural Law|Aviation Law
ER DIAGRAM
SCHEMA DIAGRAM
Users with different views
------------------------------------------------------------------
- Customer/Client
- Lawyer (Partners and Associates - people Practising law, taking clients)
- Paralegal (Research work, view law firm case history)
- Other Staff - Finance staff (Handle billing, transactions)
- Managing Partner
Queries for the Users
----------------------------------------------------------------------
- Customer/Client
--------------------------------------------------------------------------------------------
- Best lawyer on the basis of case flair - lawyer specialization, lawyer cases win-lose ratio, experience, avg time per case, charges, state default values for all otherwise input.
- View bills pending.
- View/ add/ delete events (including court hearings).
- View case details of all their cases.
- View lawyers by lawyer specialization, lawyer cases win-lose ratio, experience, avg time per case, budget, state.
- View their personal details.
- Lawyer
--------------------------------------------------------------------------------------------
- View/ add/ delete events (such as current court hearings related to ongoing cases).
- View case details of all their cases.
- View their personal details (financial history, track record).
- View basic details of peers (not salary).
- View allowed documents related to any case of the law firm.
- View data of all their clients.
- Paralegal
--------------------------------------------------------------------------------------------
- View their personal details.
- View/ add/ delete events.
- View all case details for all cases in the firm.
- View/ update allowed legal documents for all cases in the firm.
- Search for cases using flair, client details, etc.
- Other Staff - Finance staff
--------------------------------------------------------------------------------------------
- View their personal details.
- View/ add/ delete events.
- View/ update all financial transactions in the firm.
- Managing Partner
--------------------------------------------------------------------------------------------
- Everything above.
- Choose lawyer of the year on the basis of case win-lose ratio.
- Choose lawyer of the year on the basis of ratings.
Indexing/Hashes
--------------------------------------------------------------------------
Following are the search key attributes for index tables of different tables based on which attributes are accessed most.
| ENTITY | ATTRIBUTE(S) USED FOR INDEXING |
|---|---|
| Legal Cases | caseID, flair |
| User | userID |
| LegalDocuments | docID, caseID, visibility |
| Lawyers | userID, specialization, city, lastName, charges,experience, clientRating, avgTimePerCase |
| Opposition | OppositionID |
| Calendar | userID, whentt |
| Other staff | userID |
| Managing partners | userID |
| Against | caseID, oppositionID |
| ClientCompanies | userID |
| Clients | userID |
| CourtHearing | caseID, time |
| FinancialTransactions | transactionID |
| DisplayedIn | caseID, whentt, userID |
| Handles | userID, caseID |
| HasA | userID, caseID |
| Invest | transactionID, userID |
| Makes | transactionID, userID |
| IndividualClients | userID |
- A fully functional, interactive and aesthetic web application.
- Functionality to find best-suited lawyers for customers based on their preferences and requirements taken as input.
- Implemented indexing using the create index command in MySQL for efficient query handling.
- Implemented a total of 23 MySQL queries through embedded SQL and UI.
MAIN WEBSITE
WEBSITE BACKEND
PROJECT REPOSITORY
ID PASSWORD FOR TESTING STAKEHOLDERS :
Lawyer :
ID : A21r4a1b6X
Password : Z54#76kS
Paralegal :
ID : A21d1o5j7F
Password : K54#75dO
Customer :
ID : I21g4q3a0C
Password : H54#79sD
Other Staff :
ID : O21p5y3f5D
Password : Z54#76kS
Managing partners :
ID :** Harvey**
Password : Specter
Screenshots from the Web Application
-------------------------------------------------------------------------------
Web Application Homepage
Sign in Page
One of the Stakeholders Query Page
Ouput format of Queries
References for Templates used
- Design by StyleShout
- codepen.io
Database and Website hosted on Clever Cloud