You are required to use the systems approach to investigate a business and propose business solution for the Holmes College-Case Study. You, as a team, are required to produce a business report between (2500 and 3500 words).

The following sections are suggestions for the groups' consideration:

1. Introduction
State the purpose of the report

2. Business Overview
Write up a brief overview of the company:
- Structure,
- Key Activities
- Problems,
- Gaps,
- Opportunities,
- Functional Decomposition

3. Analysis
- Current processes,
- Requirements Determination,
- DFD (Context, Level 0 and Level 1)
- Logical Modelling
o Design a decision table for a process in this system with all possibilities
o Draw a decision tree that reflects a process used in the system
- Data Modelling (ER Diagram)

4. Requirement Specification
- Functional and Non-Functional

5. Proposed Business Solution
- Forms, Reports, User Interface design

6. Project Feasibility
- Cost, Benefit, Risk Analysis

7. Implementation
- Options

8. Maintenance
- Maintenance Request
- Requests for Implementing Changes

9. Conclusions
- Recommend a key area for detailed analysis. Justify your suggestions

10. Appendices
- If any

For Group Report, students are required to use Microsoft VISIO tools to design Context & DFD diagrams and Data Modelling Process.

Construct an Entity Relationship Diagram using Microsoft VISIO tools based on the case study described below. The ER model should include the following:

- A brief description of the notation used in the model
- A statement of all business rules adopted in the development of the diagram
- A definition of all the entities
- An English interpretation of all the relationships
- The actual Entity Relationship Diagram showing all details (Modality, Cardinality, Primary and foreign Keys ... etc)
- All many to many relationships must be resolved and should not appear on the diagram as many to many. Any one to one relationships used should be justified)
- A List (at least 3) of the possible attribute associated with every entity identified.

Construct a Context Diagram, Function Decomposition & Data Flow Diagrams (Level 0 and level 1 only) based on the case study provided. There should be one level 0 diagram only, and for each function/process defined in the level 0 diagram, one level 1 diagram is required. (Might be pushing it to get a full set of level ones with the amount of information you have given them. You might consider asking for a couple of level 1 diagrams.)

All diagrams must be computer generated using VISIO tools and balanced in conformance to the rules and standards specified in lectures and tutorials.

Assignment Description:

In this assignment you will use your normalized "City Jail" database you have created in your assignment one. You will form a group (2-3 students) and implement your designed City Jail database using Oracle SQL developer 12c. You will also perform different types of SQL queries on it to sharpen your SQL skills to implement and manage the database. Please submit your assignment using the attached template.

Group component:

Assignment Tasks:

• Discuss within your group to find the best normalized "City Jail" database design to implement in Oracle database. Provide an Entity Relationship Diagram. You can re-use the E-R Diagram from assignment 1 and improve it based on your lecturer's feedback. The E-R Diagram should be created as a group.

• Write down all the SQL statements require to construct the entire City Jail database. Execute all SQL queries in Oracle SQL developer 12c to implement the "City Jail" database in Oracle 12c. You can re-use the CREATE TABLE statements from assignment 1 from assignment 1 and improve it based on your lecturer's feedback. The CREATE TABLE statements should be created as a group.

• Insert 5 different entries in each table. You can create fictitious data to create those entries.

• Create SQL statements to drop all the tables you have created as a group.
Individual component:

• Execute the following queries individually and provide your answers in the attached template:

o Group member One:
- Add a column named Mail_flag to the CRIMINALS table. The column should be assigned a data type CHAR (1).

- Set the Mail_flag column to ‘N' for all criminals who don't have a street address recorded in the database.

- Change the phone number for a criminal to 7225659032.

- Remove a specific criminal from the database. Make sure you remove all the associated information relevant to that criminal.

o Group member Two:
- List the following information for all crimes that have a period greater than 14 days between the date charged and the hearing date: crime ID, classification, date changed, hearing date, and number of days between the date charged and the hearing date.

- Produce a list showing each active police officer and his or her community assignment, indicated by the second letter of the precent code. Display the community description listed the following chart, based on the second letter of the precent code.

- Display the criminal name and probation start date for all criminals who have a probation period greater than two months. Also, display the date that's two months from the beginning the probation period, which will serve as a review date.

o Group member Three (Ignore this section if you are a group of 2): -
- List all criminals along with crime status and appeal status (if applicable). The report need to include the criminal ID, name, crime classification, date change, appeal filling date, and appeal status. Show all criminals, regardless of whether they have filled an appeal.

- List all criminals along with crime information. The reports need to include the criminal ID, name, crime classification, date charged, crime code, and fine amount. Include only crimes classified as "Other". Sort the list by criminal ID and date changed.

- Create an alphabetic list of all criminals, including criminal ID, name, violent offender, status, parole status, and any known aliases.

Attachment:- Assignment.rar