CrimeCases: This entity contains the crime cases registered for the criminals. It contains the complete record about the case e.g. Hearing Date, Fee, Amount Paid etc.
Criminal: This entity contains the information about criminals e.g. Name, Address, Violation Status, Probation Status etc.
CrimeCaseCriminals: This entity contains the criminals in each crime case. There may be number of criminals in each crime case.
CrimeCharges: This entity contains the type of crime charges along with detail and code given to crime charges.
CrimeClassification: This entity contains the types of crime classifications.
CrimeCaseCharges: A crime case may have much number of charges. Therefore this table is containing the crime case along with its charges.
PoliceOfficer: This entity contains the information about police officers e.g. Badge Number, Name, Phone etc.
CrimeCaseOfficer: A crime case may have much number of officers. Therefore this table is containing the crime case along with its officers.
SentenceType: This entity contains the types of sentences along with detail.
CriminalSentence: This entity contains the sentences given to the criminals. A criminal may have number of sentence.
Appeal: This entity contains the detail of appeals for the criminal cases. A criminal case may have maximum three appeals.
Un-normalised data can be converted into first normal form by following process-
- Delete the repeated groups from the data and make new tables.
- Find the primary keys in each table.
First normal form data can be converted into second normal form by following process-
- Each table is in first normal form.
- Each table must have primary key and all other attributes depend upon the primary key only.
Third Normal Form
Second normal form data can be converted into third normal form by following process-
- Each table is in second normal form.
- Transitive depend should not be seen in any table.
All the tables mentioned in the ER diagram above are satisfying the third normalisation rules.
(P. Brombarg n.d)
The above ER diagram is showing the ER diagram of the third normalised data.
Additional Entities and Attributes
Following entities can be taken in the Crime Tracking Database System
- Prison (PrisonID, Name, Street, City, State, PostCode, Phone) It will contain the detail of prisons.
- Judges (ID, Name, Address, Phone, Rank, Qualification) It will contain the detail of judges.
- CriminalPrison (PrisonID, CriminalNumber,StartDate, EmdDate)
It will show the prison in which the criminal is available. It will store the duration of the criminal in the prison.
- CrimeCaseJudge (CrimeCaseNumber, JudgeID) It will show the crime case and the associated judge of the crime case.
- There may be number of criminals in a crime case.
- An appeal will be for a crime case not for a single criminal.
- There may be number of charges for one crime case.
- A criminal may be given any number of sentences. But in one case, one sentence will be given to one criminal.
- There may be number of officers involved in one crime case
- Litwin (n.d.), Fundamentals of Relational Database Design, [Online]. Available:https://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx. . [Accessed: 28-August-2017]
- Brombarg,(n.d.) SQL SERVER Database Normalization Basics for Developers, [Online]. Available: https://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-developers.aspx. [Accessed: 28-August-2017.