Database Design: Timely Prosecution Services

NB: We do not resell your papers. Upon ordering, we do an original paper exclusively for you.

NB: All your data is kept safe from the public.

Table of Contents

Introduction
System Description
ER Diagram for the TPS system
Additional constraints
Relational Database Schema
Screen Shots
Improvements and Suggestions
Conclusion
References

Introduction

Timely Prosecution Services (TPS) is designed to take care of the requirements cited. TPS will have information of judges and their cases, defendant information, prosecution counsel information, defending counsels and multiple defendants are also maintained in the system. In order to support all the features that are required by the system, a detailed database design was undertaken. This has been produced as an Entity Relationship diagram below. Every entity has been identified and the relationship it has with the other entities are also identified in the System description part of this report.

The major issues taken into consideration are the queries that come up during usage of the system, which includes the cases under a particular judge or a prosecution counsel. Similarly, choice of querying has been provided to check the number of cases pending on a defendant and the number of defendants in a specific case. All these combinations have also been taken care of.

System Description

The following entities were identified in this project.

Judges
Defendants
Defense Counsels
Crimes
Court
Prosecution Counsel
Case

Every one of these entities are related to each other and the relationship between them is brought out in the following ER diagram. Only one judge is assigned to a case whereas more than crime may be present in a case. So is the relationship between the case and the defendant and from the defendant to the defense counsel. However, there is only one prosecution counsel and court allocated for every case. This is brought out in the ER diagram.

In order to ensure that the values pertaining to the zones or states are visible, when queries every case is recorded along with the state it is from. In this case, these are North, South, East and West. This information is also saved in the Case Schema. In every one of the tables, a primary key has been set to pick up random information. The primary key is indicated in the ER diagram as mandatory keys.

In the case of child tables which have foreign keys, it can be found that the two or more foreign keys join together to form the primary key for that table or schema. Since the defense counsel table could have multiple counsels for every one of the defendants in the case, the defense counsel is a child to the defendants table. The primary key for the tuples is a combination of caseid with defendant id along with defense counsel id. Every table has an unique id for locating the data without losing the uniqueness of the information.

ER Diagram for the TPS system

While making the ER diagram the following considerations have been done:

Judge to case is a one to one relationship.
Case to crime is also an one to many relationship
Case to defendant is also an one to many relationship
Defendant to defendant counsel is an one to many relationship
Case to court is a one to one relationship
Case to a prosecution counsel is also an one to one relationship.

All these criterion have been brought into the ER diagram shown below.

The entity relationship diagram indicates the entities and the relationships between them in addition to the attributes that are used. Based on the ER diagram a relational database schema has been created. The same is pictured below.

Additional constraints

Every prosecutor can have more than one case to handle. And similarly, there can be one defense counsel who can be defending more than one defendant in a case. Every defendant will have a defense counsel but there is no need that every defense counsel should be handling only one defendant in a case or in multiple cases. These are also considered in the design.

Relational Database Schema

Based on the entities and the relationships given above the relational database schema can be deduced as follows:

Schema Names

Case Id is the foreign key in most of the schemas referring the information. In case of the defense counsel alone there are two foreign keys which are used to refer to the schema rows.

Case Id is present in all the sub schemas to ensure that the link between the schemas exist all through the database. For every case, the needed information is related to one another. However, when a query on the number of cases a judge is handling is not a direct query. If the same is a routine query then a view may be set so that there is fixed link between the tables. This would also ensure that the information required is swiftly obtained without any delay in getting to it. For this purpose, the following queries have been identified:

Regionwise cases pending and case information. For this purpose, a view connecting the regions is created. This would bring together data pertaining to one single region. In the case details, the case status of True or False indicates whether the case is active or inactive. If it is false, the case is inactive and the same is not considered for the purpose.
Cases handled by a given prosecutor can be efficiently queried if there is a view that would link these factors alone. A query has been created and a view created to handle the same case.
- SELECT Prosecution.*, Cases.*, Court.CourtID
- FROM (Prosecution INNER JOIN Cases ON Prosecution.CaseID = Cases.CaseID) INNER JOIN Court ON Cases.CaseID = Court.CaseID
- WHERE ((“courtID”=inputnumber));
The most common types of crimes can be identified by using a suitable query and a view is not created for this purpose.
To know the status of the cases with the judges, a view is created linking the judges and to identify the most adjourned cases with a particular judge, the field, number of adjournments in a case is also used. This would help in identifying the number of adjournments given and the cases that were closed are identified using the active or not active flag in the case table.
State with the largest number of crimes in a given year.
- SELECT Crimes.CrimeID, Crimes.CaseID, Court.State, Court.CourtID, Court.CaseID, Court.FromDate, Crimes.CrimeID
- FROM Crimes INNER JOIN Court ON Crimes.CaseID = Court.CaseID
- WHERE (((Court.State)=”MN”) AND (Year(“fromdate”)=2004) AND ((Crimes.CrimeID)=Max(Count([crimes].[crimes]))));
The most common type of crimes in a given state
- SELECT Court.State, Court.CaseID, Crimes.CaseID, Crimes.CrimeID
- FROM Crimes INNER JOIN Court ON Crimes.CaseID = Court.CaseID
- WHERE (((Crimes.CrimeID)=Max((Count([crimes].[crimeID])))));
Most number of adjournments
- SELECT Cases.CaseID, Cases.Description, Cases.Noofadjnmnts
- FROM Cases
- WHERE (((Cases.Noofadjnmnts)=Max([cases].[noofadjnmnts])));
Judges with more than 10 adjournments in a given year
- SELECT Cases.CaseID, Cases.Description, Cases.Noofadjnmnts, Judge.JudgeNo, Judge.Name
- FROM Cases INNER JOIN Judge ON Cases.CaseID = Judge.CaseID
- WHERE ((Max([cases].[noofadjnmnts])>10));

The schema for the judge – case join is as follows:

Judges – Cases View	CaseId – Join created on this key	CaseDescription	Zone	Status T/F	Adjournments
Judges – Cases View	CaseId – Join created on this key	JudgeId	Name

The schema for the prosecutors query is as follows:

Prosecutor – Cases View	CaseId – key used for creating Join	CaseDescription	Zone	Status T/F	Adjournments
Prosecutor – Cases View	CaseId – key used for creating Join	ProsecutionId	Name

By using the above two views, most of the pending cases queries and the cases that are pending with a specific judge can also be found out. Similarly, the query on this view can also provide information on the number of cases closed by a judge. This will help in identifying how busy the judge was during the last few years. There could also be cases to know in which of the regions a defendant has a case pending on him. In those cases also an appropriate query can be created. The view created for the purpose is given below:

Defendant –Cases Join	CaseId – key on which join is created	CaseDescription	Zone	Status T/F	Adjournments
Defendant –Cases Join	CaseId – key on which join is created	DefendantId	Name

Filter is created on the Zone and on the status of the case, if the requirement is to know for a specific zone. The sort order is decided on the basis of the requirement either in the defendant order or if the filter condition is for a specific defendant and sorted on a zone wise, then the query is filtered on the specific clause and ordered by using the zone.

Additional queries and views can be created depending upon the need that comes up from the users.

Screen Shots

An opening menu was created to ensure that the user is able to comfortably work on the system. This comprises of the data creation in two levels. One when the master is created. This has the master data on the judges, prosecution counselors and other fixed information like the court details. This is done in one of the main options, termed the master data creation. The second option is the option to create the case details and the crime details. These two are variable ones and may carry information pertaining to every one of the master details given already.

A query menu and another one for the reports is also created. While the reports would be available on the printer, the queries will appear on the screens. The information is obtained by querying the database and the same is presented subsequently to the user. The query is formed using the views and the schemas already discussed.

The master entry screens are simpler and comprises only of the fields that have to be entered to complete a tuple in the schema for every one of the master tables. In the case of judges, the judge number, name and the case that they deal with are taken into consideration. There can be multiple entries for the same judge. But together with judge number and the case number there cannot be duplicates. Both together form the unique key for the table. Similarly, for the prosecution counselor too, prosecution counselor id along with case number will become a unique key whereas prosecution counselor id alone is not unique and will have multiple records. This is prompted by the entry screen while doing data entry. In the case of case details entry screen, it is more complex.

For a single case, there can be multiple defendants and for every defendant there can be multiple defending counsels. This is allowed by using the table structure and the frame that allows multiple entries in the screen for the defendant. The same way, the crime is also enterable multiple number of times. This meets all the conditions that are needed for the purpose. The rest of the information in the screen occurs only once in the case.

*Figure 5: Typical Query result screen.*

The typical query results screen is shown above. This is thrown up when the information is collected from the query screen and the results of the query is displayed in this format.

Improvements and Suggestions

There are a number of improvements and suggestions could be built in, in the software. There could be a number of additional fields that would maintain judgment details. The queries can also be based on the judgments so that if at any point in future if archives have to be referred for a specific judgment then the same can be used.

Conclusion

A design and implementation of a data base has been done. Oracle back end was employed for the purpose. The ER diagram has been presented and the details have been analyzed and presented. In addition, a few improvement and suggestions have also been presented. Distributed data management has been done by providing for the region wise accounting of the cases and querying information pertaining to various regions and comparing the results.

References

Alan Beaulieu, 2005, Learning SQL. O’Reilly.
Candace C Fleming & Barbara Von Halle, 1989, Handbook of Relational Database Design. Addison-Wesley Professional.
Michael J Hernandez, 2003, Database Design for Mere Mortals. Addision Wesley Professional.

Click Here To Order Now!