HS2021 Database Design And Use
Mar 13,23Question:
Assignment Description: In this assignment you will use the given case study (Case Study: City Jail) to design a database. You need to analyse the case study to identify database requirements and design a complete E-R Diagram in 3NF form. You will create tables using SQL in Oracle 12c based on your E-R Diagram.
Answer:
Introduction
Assignment
Database
City Jail Case Study
Table of Contents
| Title | Page No |
| ERD Diagram | 3 |
| SQL Table Creation | 4 |
| Conclusion | 7 |
| References | 8 |
| Appendix | 9 |
ERD Diagram
The ERD Diagram for the given case study related with city jail is shown below:
SQL Tables creation
Create table Aliases( Alias_id int, Criminal_id int, Alias
Varchar(15)
);
Create table Criminals( Criminal_id int, Last Varchar(10), First Varchar(10),
Street Varchar(10), City Varchar(10),
State Varchar(20),
Zip char(30),
Phone char(40),
V_Status char(2) default ‘n’ constraint Criminals_V_Status_Ck check(V_Status=’y’ or V_Status=’N’),
P_Status char(2) default ‘n’ constraint Criminals_P_Status_Ck check(P_Status=’y’ or P_Status=’n’)
);
Create table Crimes( Crime_id int, Criminal_id int,
Classification char(2) Constraints Crime_Classification_Ck check(Classification in(‘f’, ‘m’, ‘o’, ‘u’)),
Status char(3) Constraints Crimes_Status_Ck check(Status in(‘cl’, ‘ca’, ‘ia’)),
Hearing_Date date, Appeal_Cut_Date DATE
);
Create table Sentences( Sentence_id int, Criminal_id int, Prob_id int, Violations int,
Type char(2) constraint Sentences_Type_Ck check(type in(‘j’, ‘h’, ‘p’)), Start_Date date,
End_Date date
);
create table Prob_Officers( Prob_id int,
Last varchar(10), First varchar(15),
Street varchar(50), City varchar(60),
State varchar(6), Zip char(7),
Phone char(15), Email char(20),
Status varchar(2) Default ‘a’ constraint Prob_Officers_Status_Ck check Status in(‘a’, iI’)),
Mgr_id int
);
create table Crime_Charges(
Charge_Id int, Crime_Id int, Crime_Code int,
Charge_Status char(1) constraint Crime_Charges_Status_Ck check(Charge_Status in(‘pd’, ‘gl’, ‘ng’)),
Court_Fee Decimal(8,9), Fine_Amount
Decimal(8,9), Amount_Paid Decimal (8,9), Pay_Due_Date date
);
Create Table Crime_Officers( Crime_Id int, Officer_Id int
);
create table Officers( Officer_Id int, Last varchar(1), First varchar(10), Badge varchar(12),
PrecInt char(3), Phone char(10),
Status char(2) default ‘a’ constraint Officers_Status_Ck check(Status in(‘a’,
‘i’))
);
create table Appeals( Appeal_Id int, Crime_Id int, Filling_Date date, Hearing_Date date,
Status char(2) default ‘p’ constraint Appeals_Status_Ck check(Status in(‘p’, ‘a’, ‘d’))
);
create Crime_Codes( Crime_Code int, Code_Description varchar(25)
)
Conclusion
The above assignment was based on creating ERD Diagram and writing SQL queries for city jail case study.
References
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) From Relational Database Schem. International Journal of Database Theory and Application, 8(3), 15–26. https://doi.org/10.14257/ijdta.2015.8.3.02
Diederich, J., & Milton, J. (1988). New methods and fast algorithms for database normalization. ACM Transactions on Database Systems (TODS), 13(3), 339–365. https://doi.org/10.1145/44498.44499
S.N, K. (2020). Tuning SQL Queries for Better Performance. International Journal of Psychosocial Rehabilitation, 24(5), 7002–7005. https://doi.org/10.37200/ijpr/v24i5/pr2020703
Appendix
Normalization Table
0 responses on "HS2021 Database Design And Use"