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 y
ou 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.
