HS2021 Database design and use City Jail Case 2018

HS2021 Database Design and Use


Trimester 1 2018


City Jail Case Study- Buy Solution for only $100 AUD


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.

Case Study: City Jail

Your company receives the following memo.

To: Database Consultant

From: City Jail Information Director

MEMO

Subject: Establishing a Crime-Tracking Database System

It was a pleasure meeting with you last week. I look forward to working with your company create a much needed crime-tracking system. As you requested, our project group has outlined the crime-tracking data needs we anticipate. Our goal is to simplify the process of tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions.

Criminals: Name, address, phone number, violent offender status (yes/no), probation status (yes/no) and aliases.

Crimes: classification (felony, misdemeanour, other), date charged, appeal status (closed, can appeal, in appeal), hearing date, appeal cutoff date (always 60 days after the hearing date), arresting officers (can be more than one officer), crime codes (such as burglary, forgery, assault; hundreds of codes exist), amount of file, court fee, amount paid, payment due date, and charge status( pending, guilty, not guilty)

Sentencing: start date, end date, number of violations (such as not reporting to probation officer), and type of sentences (jail period, house arrest, probation)

Appeals: appeal filling date, appeal hearing date, status (pending, approved and disapproved)

Note: Each crime case can be appealed up to three times.

Police officers: name, precinct, badge number, phone contact, status (active/inactive)

Additional notes:

  A single crime can involve multiple crime charges, such as burglary and assault.

   Criminals can be assigned multiple sentences. For example, a criminal might be required to serve a jail sentence followed by a period of probation.

Assignment requirements:

   Create an Entity Relationship Diagram based on the memo. The E-R Diagram should include entities, attributes (columns), primary keys, and relationships between the entities. Make sure your E-R Diagram is normalised to Third Normal Form and is ready to be deployed in Oracle 12c SQL developer.

  Create all the tables using SQL queries in Oracle 12c SQL developer. Make

sure tables are created with attributes and constraints (i.e. PRIMARY KEY, FOREIGN KEY, CHECK constraints, etc.)

  Execute all SQL queries in Oracle SQL developer 12c to implement the “City

Jail” database in Oracle 12c. Please ensure all tables are created without errors.

Report structure and assignment submission:
  1. Content of your report

  2. Prepare a report which will detail an E-R diagram and justification your choice of entities and attributes.

  3. Explain the normalization process of the ER diagram in details. c. Write down all the assumptions you have considered while


preparing the ER diagram.
  1. Include all the CREATE TABLE statements in the Appendix.

  2. Assignment Submission Guideline:


You need to submit one Word document on Blackboard
  1. Assignment Date Due: Friday Week 6 Friday by 5:00 PM

Marking Guide:

Assignment components                                                                                      marks

Quality of ERD                                                                                                      30% Quality of other report tasks (normalisation process, assumptions)

20%

Quality of CREATE TABLE statements

TOTAL 50%

100%

Comments

Post a Comment

Popular posts from this blog

ACC030243 Corporate Governance SCU Assignment 2

ACC03043 Corporate Governance Assessment Task 2 Due Date:                          Sunday 5 August 2018 Length:                                2,000 words total (+/- 10%). Reference list and cover sheet details are not included in this word-limit total. Weighting:                        25% of total unit marks Assessment Criteria: Demonstration of knowledge of the issues and evidence of wide reading to support your analysis Demonstration of your ability to apply the knowledge to identify keys issues leading to your recommendations Evidence of sound reasoning and the exercise of professional judgement to support your recommendations Development and statement of concise recommendations for presentation to the AICD Overall structure and professional presentation of the report to the AICD High quality written communication of concepts and terms in ordinary English as not all readers of the report can be assumed to be specialists competent in corporate governance Case Study ‘
Read more

Accounting Assignment Solution

Image
Accounting Assignment Solution Are you an accounting student? Are you laden with tons with assignments, homework that is making your life immensely hectic? So, we Punjab assignment help are here to provide you the benefit of our accounting assignment Solution services, in order to get the services contact us at www.punjbassignmenthelp.com Well, you should know that you are just not the only one. Being an accountancy student is just not easy. It involves the business language of the society. Accounting involves maintenance of finances, investments and laying down all calculations for accounting assignments. A general guideline on Financial Accounting assignment solutions: Try to understand and learn the concepts well from the basic since the beginning. Just as soon as the matter starts in class, give full concentration as to what is being taught in classroom. This not only saves time but also gives you enough time to prepare. Always try to grab the concepts well. In case you are hav
Read more

CSG1207 | DATABASE DESIGN AND IMPLEMENTATION ONLINE STORE

Image
CSG1207 | DATABASE DESIGN AND IMPLEMENTATION ONLINE STORE | DATABASE COMPUTER ASSIGNMENT HELP Task 1 (Database Design) This task is to design a database for the given scenario on the following page. Your final database design should include approximately eight entities. Note: The scenario for this assignment is the same as the one from Task ¾ of Assignment 1. State any assumptions you have made regarding your database design at the starting of the database design document. Do not make any assumptions that significantly change the structure of the scenario, as this may make Task 2 of the assignment more inappropriate. You need to make assumptions that affect your database design. Finally, create a data dictionary with an entry for each entity in your database. The entries should list the entity name, a description of its purpose, a list of attributes, and details of any constraints applied to attributes. List the entries in your data dictionary in an applicable table creation order that
Read more