ICT503 – Database Systems Assignment 2

Group Assessment: 5 members Weightage: 50%

Due: Session 11
 

Assessment Details for Assessment 2:

Requirements

The assignment requires a group report submission and group video presentation with ppt. In the report and video presentation ppt, all members student Id, Student Name and contribution statement needs to clearly provided. Group leader need to submit the report document, ppt and presentation video. All others need to submit the group participation form to claim that you participated in the assessment. Failure to mention your contribution in group report or failure to submit individual group participation forum may result a “0” mark. In video presentation, all members face must be visible throughout the duration of the presentation and you need to share the ppt during the presentation.

Assessment Description

Overview

In this assignment, you are required to apply a normalization principle to given unnormalized tables, create database using SQL, perform CRUD operations on SQL database.

Activity 1: Normalization

Write a relational schema, and show the First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF) for the following tables. Choose appropriate attribute names while decomposing the composite attributes e.g., full name can be divided into firstname and lastname.

Activity 2: SQL Queries

Case Study:
Video Rental (VR) is a start-up business which allows customer to rent videos. They want to create a database to store the information of their videos, customer, renting details and rental fee charged. VR asked you to create a database for them which include the following tables

1. Write the SQL queries to create the tables the above. Use appropriate data types for each attribute and choose appropriate primary and foreign keys
2. 
   Write the Insert query to enter the following sample data into the tables created in Part a. The sample data is provide in the following tables.
3. Write the SQL queries for the tables created in Part a and Part b.
   1. Write a query to list all the videos where the cost of the video is between 50 and 75.
   2. Write a query to list the details of all customers last names that begin with the letters ‘Kne’
   3. Write a query to list the customer id, first name, last name, state and total number of videos borrowed by the customer.
   4. Write a query to list each customer first name, last name, the rented video name and due date.
   5. Write a query to create a view “BorrowDetails” for the query used in previous question and also display the number of videos each customer has from the newly created view

Activity 3: Presentation

You need to prepare a presentation ppt and do a group presentation(Each student 2 min-2:30 min).You can form a group with 5 students from your corresponding tutorial only. Also, you can conduct a zoom meeting for the presentation and all members faces must present throughout the meeting. You can record the meeting and active participation, clarity, sound, body language strong technical knowledge etc. will be evaluated.

Submission Instructions

• Your report can submit in .pdf format and presentation document in .ppt format. You can submit the group presentation recording link in both .pdf and .ppt documents. If the link is not openable in any scenario, there is a

chance of loss whole presentation mark. So, please make sure the link is openable for all those who are using.

• All submissions are to be submitted through Turnitin. Drop-boxes linked to Turnitin will be set up in Moodle. Assessments not submitted through these drop- boxes will not be considered. Submissions must be made by the end of Session 11.
• The Turnitin similarity score will be used to determine any plagiarism of your submitted assessment. Turnitin will check conference websites, Journal articles, online resources, and your peer’s submissions for plagiarism. You can see your Turnitin similarity score when you submit your assessments to the appropriate drop-box. If your similarity score is of concern, you can change your assessment and resubmit. However, re-submission is only allowed before the submission due date and time. You cannot make re- submissions after the due date and time have elapsed.

Note: All work is due by the due date and time. Late submissions will be penalized at 20% of the assessment final grade per day, including weekends.

Rubrics