Based mostly on the following memo, create a database design for the Metropolis Jail.TIP: Take into account that the memo is written from an end-user perspective – not by a databasedeveloper!MEMOTo: Database ConsultantFrom: Metropolis Jail Info DirectorSubject: Establishing a Crime-Monitoring Database SystemIt was a pleasure assembly with you final week. I look ahead to working along with your firm tocreate a much-needed crime-tracking system. As you requested, our undertaking group has outlinedthe crime-tracking knowledge wants we anticipate. Our objective is to simplify the technique of trackingcriminal exercise and supply a extra environment friendly mechanism for knowledge Assessment and reporting. Pleasereview the knowledge wants outlined beneath and speak to me with any questions.Criminals: title, tackle, telephone quantity, violent offender standing (sure/no), probation standing(sure/no), and aliasesCrimes: classification (felony, misdemeanor, different), date charged, attraction standing (closed, canappeal, in attraction), listening to date, attraction cutoff date, (at all times 60 days after the listening to date),arresting officers (may be a couple of officer), crime codes (reminiscent of housebreaking, forgery, assault,and lots of extra), quantity of wonderful, court docket price, quantity paid, cost due date, and cost standing(pending, responsible, not responsible)Sentencing: begin date, finish date, variety of violations (reminiscent of not reporting to probationofficer, and lots of extra) and sort of sentence (jail interval, home arrest, probation)Appeals: attraction submitting date, attraction listening to date, standing (pending, permitted, and disapproved).Every crime case may be appealed as much as thrice.Law enforcement officials: title, zone, badge quantity, telephone contact, standing (energetic/inactive)Further info:A single crime can contain a number of legal expenses, reminiscent of housebreaking and assault.Criminals may be assigned a number of sentences. For instance, a legal is perhaps required toserve a jail sentence adopted by a interval of probation.Suppose you’re employed to construct a database for Metropolis Jail. Your first job is to design a database thatstores and organizes all info (crimes, criminals, sentencing, appeals, cops, and so on.)about the group.A. Generate a checklist of Enterprise Guidelines for the database.B. Write the relational schema, draw its dependency diagram, and determine all dependencies,together with all partial and transitive dependencies for 2nd and third Regular Kind.C. Given the above description, draw an ER diagram for the database. State any assumptions youmake for your ER-diagram to help your design. Embrace the following into your design:all the entity setsall the attributesall the relationship units utilizing the Crow’s Foot ModelMake positive that each one M:M relationships have been transformed utilizing associative tablesIdentify the whole major key for every entity.Establish all the international keys if wanted.D. Based mostly on the ER diagram you created, implement the database in Oracle. Insert 5 recordsinto every desk. Create the crucial relationships, major keys, and so on. Embrace the script fileand spool file in your submission.

Published by

Essays

View all posts