FACULTY OF HIGHER EDUCATION
Group Assignment
HS2021 Database Design and Use
Trimester 1 2018
Date Due: Friday Week 10 by 5:00 PM
Max 3 members per group
Marks: Weighting 25% (group mark 10% + individual mark 15%)
Group Number _____________
Student Name (Block letters) Student Number:
___________________________ _____________
___________________________ _____________
___________________________ _____________
| HOLMES INSTITUTE FACULTY OF HIGHER EDUCATION |
HS2021 Database Design and Use Trimester 1 2018 1
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.
Assignment Tasks:
Group component:
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.
HS2021 Database Design and Use Trimester 1 2018 2
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.
| Second letter of precinct Code | Description |
| A | Shady Grove |
| B | Centre City |
| C | Bay Landing |
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.
**NOTE: Group members may receive different marks, based on their individual
component.
HS2021 Database Design and Use Trimester 1 2018 3
Repost structure and assignment submission:
Content of your report
| | Execute all the task mentioned in “Assignment Tasks” section and provide all the SQL statements in the Assignment 2 Template. |
Assignment Submission Guideline:
| |
You need to submit a softcopy on Blackboard. Only one submission per group is allowed. |
Assignment Date Due: Week 10 Friday by 5:00 PM
Marks allocation:
| Assignment components | Description | marks |
| Quality of the database and team work – Group marks |
Students worked as a team and proper database designing guideline was followed to achieve requirements of the assignment. |
10% |
| Correctness of the query and appropriateness of the report — Individual marks |
All constructed and executed queries should run and retrieve/amend information as instructed. |
15% |
The post HS2021 Database Design and Use appeared first on My Assignment Online.