Perform crud operations on sql database

Assignment Help Database Management System

Reference no: EM133690653

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

Write the SQL queries to create the tables the above. Use appropriate data types for each attribute and choose appropriate primary and foreign keys.

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.

Write the SQL queries for the tables created in Part a and Part b.
Write a query to list all the videos where the cost of the video is between 50 and 75.
Write a query to list the details of all customers last names that begin with the letters ‘Kne'
Write a query to list the customer id, first name, last name, state and total number of videos borrowed by the customer.
Write a query to list each customer first name, last name, the rented video name and due date.
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.

Attachment:- Database Systems_Assessment.rar

Reference no: EM133690653

Questions Cloud

High risk for developing coronary artery disease : Both men and women are at high risk for developing coronary artery disease (CAD).

Virtual reality video games experience : Research has suggested that individuals who play virtual reality video games experience, lower levels of pain and discomfort

What you exported and the type of file you exported it to : Your manager has signed up for a weekly list of IP addresses and URLs that are significant. What you exported and the type of file you exported it to.

Diagnoses of atypical chest pain and hypertension : A 55-year-old obese male patient with Type 2 diabetes is being discharge with additional diagnoses of atypical chest pain, hypertension and hyperlipidemia

Perform crud operations on sql database : you are required to apply a normalization principle to given unnormalized tables, create database using SQL, perform CRUD operations on SQL database

What recommendations could be made based on the findings : Consider an employee who works at the big-box store stocking shelves overnight. What recommendations could be made based on the findings of your calculations?

Concerned about thin bones : A 48-year-old Asian American woman is concerned about thin bones.

What is an appropriate signature for the function : For simplicity you may assume that just adding s is enough to pluralize a word. What is an appropriate signature for this function?

Explain one way that you agree with the original posts : Explain at least one way that you agree with the original posts. Explain at least one way that you would disagree with or add to the original posts.

User Account

All Pages