ISYS2390 DATABASE CONCEPTS 1
ASSIGNMENT 2
DATABASE CREATION AND MANIPULATION

Demonstrations: 21th and 25th October
Due Date: Sunday, 27th October
Weight: 25% (70 Points)

OVERVIEW

This assignment has two parts:

Part 1:
• Write queries to retrieve data from existing database.

Part 2:
• Create a schema based on the given scenario and ERD.
• Create tables and insert data into tables.
• Write SQL queries to retrieve data from your database.

The assignment is an individual task that will require an individual submission.

Please ensure that you submit your assignment to the Canvas (Assessments,
Assignment 2) by the due date.

EXTENSION OF TIME FOR SUBMISSION OF ASSESSABLE WORK
If you are prevented from submitting an assessment on time, by circumstances
outside your control, you may apply in advance for an extension to the due date of up
to seven calendar days. If you are seeking an extension of seven calendar days or
less (from the original due date) you must apply at least one working day before the
assessment deadline.

Complete an Application for extension of time for submission of assessable work
form and email it to your teacher and the course coordinator. If the application is
approved, you may be granted an assessment adjustment.

https://www.rmit.edu.au/students/student-essentials/assessment-and-
exams/assessment/extensions-of-time-for-submission-of-assessable-work







ISYS2390 DATABASE CONCEPTS 2
SPECIAL CONSIDERATION
If unexpected circumstances affect your ability to submit an assessment on time, and
you were not granted the extension, you can apply for special consideration. If the
application is approved, you may be granted an assessment adjustment.
https://www.rmit.edu.au/students/student-essentials/assessment-and-
exams/assessment/special-consideration


LATE ASSIGNMENTS:
• If you submit your work between 0-24 hours late with no approved special
consideration, you will get a 20% deduction in points.
• If you submit your work between 24-48 hours late, you will get a 50%
deduction in points.
• Beyond this, you will be awarded zero points.

ACADEMIC INTEGRITY

In addition to meeting assessment timelines academic integrity is about honest
presentation of academic work. Students must be accountable for the originality and
validity of assessment submission, and not assist others in any form of plagiarism or
cheating.
https://www.rmit.edu.au/students/student-essentials/assessment-and-
exams/academic-integrity



ISYS2390 DATABASE CONCEPTS 3

WHAT TO SUBMIT (27TH OCTOBER)
Part 1:
✓ Your scripts (queries1.sql ) file. It should contain your name, student number,
and queries.


Part 2:

✓ Word Document file (dba2.docx ). It should contain:
o The final schema. Every step in the mapping process must be shown.
o Screenshots of your tables in Oracle.


✓ Your scripts (create_tables.sql , insert_data.sql , and queries2.sql ).
They should contain your name, student number, and queries.


ISYS2390 DATABASE CONCEPTS 4

DEMONSTRATIONS (21TH - 25TH OCTOBER)

Demonstrations will be held during tutorial classes in Week 15. Available times for
demonstrations will be posted on the Canvas.
The purpose of this demonstration is to:
• Check you're on the right track to complete your second assignment.
• Verify that the work you submit is your own, you will need to demonstrate
your assignment to your teacher.

If you do not demonstrate your work, you will receive a zero (0) for the
assignment.

After the demonstration, you will have a few more days to complete your
assignment (make any changes suggested during the demonstration) and
submit the final version on Sunday, October 27.

What to bring to the demonstration:
1. Word Document file (dba2.docx).
2. Your scripts (create_tables.sql, insert_data.sql).
3. Queries (queries1.sql, queries2.sql). Note: These files can be incomplete,
but make sure that you completed or attempted at least 10 queries (5 in
queries1.sql and 5 in queries2.sql).
During the demonstration, you will be asked to run create_tables.sql and
insert_data.sql scripts using the SQL Developer, execute and explain two queries.
Queries will be chosen by your teacher.







ISYS2390 DATABASE CONCEPTS 5


PART 1 (30 POINTS)
SCENARIO – DATABASE FOR THE ASSOCIATE DEGREE IN IT PROGRAM
This practical two-year associate degree gives you the basic skills required to
perform a range of in-demand specialist IT functions such as technical support,
software development/installation, network engineering/administration and provides a
guaranteed pathway into a relevant bachelor’s degree.

Students belong to groups and are enrolled to courses. The courses are taught by
teachers.

ASSUMPTIONS
• A student belongs to one group only. Each group can have many students.
• A student is enrolled to many courses. Each course has many students enrolled
in it.
• A teacher can teach many courses. Each course is taught by many teachers.
• Each group has a mentor (teacher in-charge).
• Each course has a course co-ordinator (teacher in-charge).
• To complete a course a student must score at least 50 (numerical grade >=
50).
• To graduate (complete the AD006 program) a student must complete the
following eight courses.


DATABASE SCHEMA
TEACHERS (TeacherID, FirstName)
GROUPS (GroupID, Semester, Year, TeacherID*)
Note: TeacherID* (group mentor)
COURSES (CourseCode, Title, TeacherID*)
Note: TeacherID* (course co-ordinator)
STUDENTS (StudentID, FirstName, LastName, Phone, GroupID*)
TEACHER_COURSES (TeacherID*, CourseCode*, Hours)
STUDENT_COURSES (StudentID*, CourseCode*, NumericalGrade)

ISYS2390 DATABASE CONCEPTS 6



TASK 1_1:
Download “AD006 Database” from Canvas.
Assignments → Assignment 2→ AD006 Database.
Run two files: add006_create_tables.sql and add006_insert_data.sql.

TABLES



ISYS2390 DATABASE CONCEPTS 7



ISYS2390 DATABASE CONCEPTS 8

TASK 1_2: WITH A REFERENCE TO “ADD006 DATABASE” CREATE SQL
QUERIES (QUERIES1.SQL) TO ANSWER THE FOLLOWING QUESTIONS.
(15X 2 = 30 POINTS)
Note:
For each SQL query that returns correct result you will get 2 (two)
points.
For each SQL query that does not return correct result you will get 0
(zero) points.

Q1 Display the name of the course coordinator for Networking 2 course.

Q2 How many students are enrolled to each group? Display the group ID and the
number of students enrolled.

Q3 Find the names of students who are not enrolled to any course.

Q4 For each course display the title and course coordinator name.

Q5 Display the names and phone numbers of all students who have missing results.

Q6 For each course, display the title, lowest grade, highest grade, and average
grade. Round average to two decimal places.

Q7 How many students completed Networking 1 course.

Q8 Display the names and ID numbers of all teachers teaching Database Concepts.

Q9 For each teacher, display the name and number of hours they are teaching.
Order by number of hours in descending order.

Q10 Display the names (first name, last name) of all students who completed the
program.

Q11 Display the name and group ID of the student with the highest average grade.

Q12 Frodo Baggins does not want to be in the same group as Bilbo. Transfer him to
group F19.

Q13 For all F18 students increase numerical grades for COSC2385 by 5 points.

Q14 Prompt the user to input studentid. Based on the user input, display all results
(course code and numerical grade) for the student.

Q15 Create a question that may be relevant to this database and write SQL query to
answer it. You must use join tables or subqueries.

Q16 - Challenge Question (Optional): Which courses is the group F18 mentor
teaching?





ISYS2390 DATABASE CONCEPTS 9
PART 2 (40 POINTS)
SCENARIO – “SUPER-FIT” SPORTSWEAR SHOPS
“Super Fit” is a chain of sportswear shops. A growing business requires a database
to work efficiently with data. They want you to design, build, and manage their
database.
A customer can buy items in the shop or order them.
The database keeps data about shops, items for sale, customers, orders, and
suppliers.
A customer will open an account (with personal and contact details) in one of the
shops.
The database keeps track on orders. Each order is tracked from the date it was
received up to the date of shipment.
Information about an item contains item name, colour, size, price, and number of
items in stock (itemquantityAvailable).
The shop needs information about suppliers of items.






















ISYS2390 DATABASE CONCEPTS 10
ERD


ISYS2390 DATABASE CONCEPTS 11
TASK 2_1: MAP THE ERD INTO SCHEMA USING NECESSARY STEPS.
WRITE A FINAL SCHEMA. (5 POINTS)
Every step in the mapping process must be shown.

TASK 2_2: CREATE DATABASE TABLES BASED ON THE FINAL SCHEMA. (5
POINTS)
You must create two scripts.
• The script create_tables.sql will create tables.
o Start the script with dropping all tables.
o Tables must have primary and foreign keys constraints with names.
o All tables must be created in a correct order. If a table references
another table, the second must be created first.
• The script insert_data.sql which will insert data in tables.
o Create at least 3 records in table SHOPS.
o Create at least 4 records in table SUPPLIERS.
o Create at least 6 records in table CUSTOMERS.
o Create at least 10 records in table ITEMS.
o Create at least 12 records in table ORDERS.
o Create at least 12 records in each joining table.
Note: A joining table is a table that sits between the two other tables of
a many-to-many relationship.

Note: Your teacher must be able to create tables and fill them with data by
running the scripts you provided. If scripts create exceptions/errors, no
marks will be allocated for this part.


TASK 2_3: CREATE SQL QUERIES (QUERIES2.SQL) TO ANSWER THE
FOLLOWING QUESTIONS. (15 X 2 = 30 POINTS)
Note:
For each SQL query that returns correct result (based on your data) you
will get 2 (two) points.
For each SQL query that does not return correct result (based on your
data) you will get 0 (zero) points.

Q1 Find the average price, and the total value of all items in the stock (table
items).

Q2 Show item description and price for all items that were sold. Display the
results in an ascending order based on the price.


Q3 Create a mailing label for each customer. Use concatenation to join multiple
columns.
For example: John Smith, 8 Lygon Street, Carlton, 3053

ISYS2390 DATABASE CONCEPTS 12
Q4 Which order has the longest dispatch time? Show the order number and the
number of days.

Q5 Raise the price of all red-coloured items (or any colour of your choice) by
10%.

Q6 Create a view to discount all items made by a particular supplier. Choose a
percentage of discount and a supplier.

Q7 List the item ID and item description for all items that were ordered on or
before any date that matches your data.

Q8 One of the items must be recalled (choose one item from your items table).
You have to call all customers who have bought that item and inform them about
the recall. To do that, you need a list of customers first names and phone
numbers. Write a query which will create the list.

Q9 A valid email address must contain an ‘@’ character. Display all information
about customers who did not provide a valid email address.

Q10 Prompt the user to input supplier id. Based on the user input, show the
supplier name and a full address.

Q11 Create a view (view_town) that contains all information about suppliers from
Melbourne (or any town that matches your database). Check the data in the
view_town.

Q12 Update any supplier phone in the view_town created in the previous query.
Check what happened with the data in the table suppliers. After that, update
supplier address for any supplier from Melbourne (or any town that matches your
database) in the table suppliers. Check what happened with the data in the
view_town.

Q13 For each customer that made an order, show the first name, email address,
and the description of all items ordered.

Q14 Display itemID and item description for items that are available in more than
one store.

Q15 Which item is the best seller (the highest number of items sold)? Display
description and price.