INFS1200 Module 3 Assignment

Due: 19 May 2023 @ 4:00 PM AEST

Weighting: 30%

Overview

The purpose of this assignment is to test your ability to use and apply SQL concepts to complete tasks in a real-world scenario. Specifically, this assessment will examine your ability to use SQL Data Manipulation Language to return specific subsets of information which exist in a database and Data Definition Language to create new relational schema. The assignment is to be done individually

Submission

All submissions for Sections A – C must be made through an electronic marking tool called Gradescope, which will also be used for providing feedback. You must record all your answers in the spaces provided in this document. Altering the format or layout of this document in anyway will attract penalties. All submissions must have the above boxes filled out to be identified. Section D is to be completed through the RiPPLE platform (link available on Blackboard).

Marking

The module 3 assignment is worth 30 course marks (of 100 course marks total for all assessment. The marking distribution per section is as follows:

1. Section A – SQL DDL: 4 marks

2. Section B – SQL DML (UPDATE, INSERT, DELETE): 5 marks

3. Section C – SQL DML (SELECT): 18.5 marks

4. Section D – RiPPLE Task: 2.5 marks

Plagiarism

The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour range from loss of grades in a course through to expulsion from UQ. You are required to read and understand the policies on academic integrity and plagiarism in the course profile (Section 6.1). If you have any questions regarding acceptable level of collaboration with your peers, please see either the lecturer or your tutor for guidance. Remember that ignorance is not a defence!

In particular, you are permitted to use generative AI tools to help you complete this assessment task. However, if you do, please provide complete copies of your interactions with the AI tool in the space provided at the end of your submission. Please note that if you use generative AI but fail to acknowledge this by attaching your interaction to the end of the assignment, it will be considered misconduct as you are claiming credit for work that is not your own.

5. Task

For this assignment you will be presented with the simplified schema of an event management application. The goal of the application is to track both the events attended by users and relationships between users and other users. The system is then able to use this data to effectively market recommended events to users based on the events their friends have attended. A sample database of this system has been provided here which will allow you to test your queries.

Assignment Specification

Events Inc. is a small start-up company which provides its users with an event tracking and recommendation platform for various local community activities. A simplified version of their database schema has been provided below including foreign key constraints.

Relational Schema

USER [id, first_name, last_name, date_of_birth, phone, email, nationality, significant_other]

EVENT [title, event_location, event_date, description, sponsor]

ATTENDS [user_id, title, event_location, event_date, travel_method]

FRIENDS [requestor, requestee, requested_date, accepted_date]

Foreign Keys

USER.significant_other references USER.id

ATTENDS.{title, event_location, event_date} references EVENT.{title, event_location, event_date}

ATTENDS.user_id references USER.id

FRIENDS.requestor references USER.id

FRIENDS.requestee references USER.id

For this assignment you will be required to write SQL queries to answer to complete the following tasks. Please use the submission boxes provided to record your answers. For queries with a returning relation of more than 10 tuples, you can use the LIMIT 10 clause to only capture the first 10 tuples of the table.

Example
Task	Return the first name and last name of all users.
Explanation	This query should return a table with two columns, one for first name and one for last name.
SQL Solution	SELECT first_name, last_name FROM USER LIMIT 10;
Output Screenshot

Section A – SQL DDL

Question 1
Task	Write a SQL DDL query to implement the following relational schema and associated foreign keys.
Explanation	The relational schema for this the table is as follows: Table: ATTENDS Column Data Type Allow Nulls? Primary Key? user_id INT No Yes title VARCHAR(50) No Yes event_location VARCHAR(50) No Yes event_date DATE No Yes travel_method {“Car”, “Bus”, “Train”, “Other”} No No Additionally, no user should be attending two different events on the same day The foreign keys for this new table are as follows: ATTENDS.user_id references USER.id ATTENDS.{title, event_location, event_date} references EVENT.{title, event_location, event_date} Foreign key constraints should be implemented such that: · Updates to an event title, date, or location are automatically updated in the ATTENDS table as well. · A User cannot be deleted if they are attending an event Note: You may wish to consult the MySQL documentation on the enum datatype. You may create this table using the name NEW_ATTENDS You may want to consult this tutorial and the use of the following command            COLLATE latin1_swedish_ci;
SQL Solution

Question 2
Task	Following a marketing research, Event Inc. has decided to target their events exclusively towards Millennials and Gen Z. Assuming all their users are currently from these generations, update the USER table to restrict the insertion of new users to Millennials and Gen Z.
Explanation	For the sake of this question, we use the date ranges below for Millennials and Gen Z: · Millennials: Born 1981-1996 · Gen Z: Born 1997-2012 The following resources may be useful when answering this question: Check constraints
SQL Solution

Section B – SQL DML (UPDATE, DELETE, INSERT)

Question 1
Task	Due to significant Olympic developments, car traffic in Woolloongabba is heavily restricted for the month of May 2023. Delete the attendee registration for anybody using a car to attend an event at ‘The Gabba’ in May.
Explanation	To identify events held at ‘The Gabba’ you should check for the presence of ‘gabba’ within the event_location. MONTH Function YEAR Function LOWER Function
SQL Solution

Question 2
Task	Coca-Cola has decided to increase their advertisement in Australia by taking over the sponsorship of any events that happens at the “Sydney Opera House”. Update the database to reflect this change.
SQL Solution

Section C – SQL DML (SELECT)

Question 1
Task	Find an alphabetical list of all the event locations where people are arriving via train.
SQL Solution
Output Screenshot

Question 2
Task	Find the first and last names of all users who have had a successful friend request in 2023
Explanation	A successful friend request will have a date in the accepted_date column
SQL Solution
Output Screenshot