Question

Johnny decided to pursue a law degree. After graduating, he has teamed up with Jessie to form Johnny and Jessie's Law office.

As the team started growing and the business picked up, they realized they need to be able to print reports about the cases of their clients, the cases of each lawer, the cases in each court and with each judge. To do that they want to input the data when it is generated. Jessie also wants the system to warn her when the load of a lawyer becomes more than the double of the average load.

Johnny and Jessie hired you to design an information system to manage their clients and the cases they take.

4- Design the database . For each table you must provide the table name, list of fields, primary key, Foreign keys (when applicable).

Client name	Client address	Client phone	Case description	Case type	Attorney	Attorney Address	Attorney Phone	Court	Judge	Judge since
Franck House	45 blue avenue	01-222333	Office rental contract	Corporate	Ben Jelly	55 Laurier av.	01-999888	Beirut	Judge Thomas	2010
John Card	37 5th avenue	01-333444	Home rental contract	Personal	Peter Blake	152 Mont Royal	01-999777	Saida	Judge White	2005
Helena Mara	122 white boulevard	01-444555	Lawsuit against Tom Green	Personal	Ben Jelly	55 Laurier av.	01-999888	Saida	Judge White	2005
Kate White	63 blue avenue	01-555666	Merger contract	Corporate	Los Gatos	22 Britany	01-999666	Tripoli	Judge Greene	2016
Franck House	45 blue avenue	01-222333	Will	Personal	Los Gatos	22 Britany	01-999666	Beirut	Judge Thomas	2020
John Card	37 5th avenue	01-333444	Lawsuit against Ted Brown	International	Peter Blake	152 Mont Royal	01-999777	Beirut	Judge Thomas	2004
Helena Mara	122 white boulevard	01-444555	New product	Corporate	Ben Jelly	55 Laurier av.	01-999888	Tripoli	Judge Greene	2013
Kate White	63 blue avenue	01-555666	Establishing a company abroad	International	Los Gatos	22 Britany	01-999666	Beirut	Judge Thomas	2010
Franck House	45 blue avenue	01-222333	Lawsuit against XYZ	Personal	Lyla El Pajaro	23 Bernard	01-999555	Beirut	Judge Brown	2018

Answer 1

Tables

Client(ClientPhone,ClientName,ClientAddress)

Case(CaseType,CaseDescription, ClientPhone,AttorneyPhone,JudgeName)

Attorney(AttorneyPhone,AttorneyName,AttorneyAddress)

Judge(JudgeName,Court,JudgeSince)

underlined are primary keys and italicized are foreign keys.

Create Table Client(

ClientPhone varchar(10) NOT NULL,

ClientName varchar(50) NOT NULL,

ClientAddress varchar(100) NOT NULL,

Primary Key(ClientPhone)

);

Create Table Case(

CaseType varchar(15) NOT NULL,

CaseDescription varchar(200) NOT NULL,

ClientPhone varchar(10) NOT NULL,

AttorneyPhone varchar(10) NOT NULL,

JudgeName varchar(50) NOT NULL,

Primary Key(CaseType),

Foreign Key ClientPhone References Client(ClientPhone),

Foreign Key AttorneyPhone References Attorney(AttorneyPhone),

Foreign Key JudgeName References Judge(JudgeName),

);

Create Table Attorney(

AttorneyPhone varchar(10) NOT NULL,

AttorneyName varchar(50) NOT NULL,

AttorneyAddress varchar(100) NOT NULL,

Primary Key(AttorneyPhone)

);

Create Table Judge(

JudgeName varchar(50) NOT NULL,

Court varchar(50) NOT NULL,

JudgeSince date NOT NULL,

Primary Key(JudgeName)

);

Do ask if any doubt. Please up-vote.