Demonstrate skills to analyse database requirements

Assignment Help Database Management System

Reference no: EM133788678

Assessment Description

In this group assessment, you will demonstrate skills to analyse database requirements, develop an entity relationship diagram, normalise the database, implement the database and use SQL to retrieve information from the database while also applying concepts of data privacy and security.

Overview
In this assignment, you are required to apply a normalization principle to the 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 customers to rent videos. They want to create a database to store the information about their videos, customer, renting details and rental fee charged. VR asked you to create a database for them which includes the following tables

Write the SQL queries to create the tables 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 provided 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 numberof videos borrowed by the customer.
Write a query to list each customer's first name, last name, the rented video nameand due date.
Write a query to create a view "BorrowDetails" for the query used in previous question and display the number of videos each customer has from the newly created view.

Presentation & Demonstration (Face-to-Face)

Location:
The presentation and demonstration will be conducted on campus in a face- to-face setting during Week 11 class.
Demonstration Schedule:
Your group will be assigned a 5 to 10 minutes time slot during Week 11 for the live demonstration.
Format:
Each group must present their response for Activity 1 using a PowerPoint presentation.
Each group member must demonstrate at least one task from Activity 2 and actively participate in the demonstration.
Prepare your all-necessary equipment (e.g., laptops, network setup) is ready for the live demonstration.
There will be a brief Q&A session with the lecturer.
Duration:
The entire session for each group, including the demonstration and Q&A, should last no longer than 10 minutes.

Group Responsibility: Ensure that all group members contribute equally to the assessment. Marks will be assigned based on both individual and group contributions.
Please ensure that you prepare adequately for the face-to-face presentation and demonstration to effectively showcase your project.

Reference no: EM133788678

Questions Cloud

Admitting diagnosis of cerebral vascular accident : Patient is an 82 year-old-female admitted to the nursing home with an admitting diagnosis of Cerebral Vascular Accident.

How are they similar and different from one another : Compare or contrast American food laws with those of 1 other country. How are they similar and/or different from one another?

Describe rates of diagnosis and treatment of mental health : Describe the rates of diagnosis and treatment of mental health disorders of the dominant and subcultural group in the U.S.

Acute onset shortness of breath : The patient is a 60-year-old white female presenting to the emergency department with acute onset shortness of breath.

Demonstrate skills to analyse database requirements : ICT503 Database Systems, Victorian Institute of Technology - Apply a normalization principle to the given unnormalized tables, create database using SQL

Discuss treatments with emphasis on pharmacologic treatments : Discuss Restless Leg Syndrome and Periodic Limb Syndrome. Discuss treatments with an emphasis on pharmacologic treatments.

Physical examination for athletic participation : A 10-year-old boy presents at his primary care clinic for a physical examination for athletic participation.

What is the cause of a ventricular septal defect : What is the cause of a ventricular septal defect? How does the defect create these problems? What diagnostic tests will be ordered?

Relationship between body and medication : Pharmacodynamics is the relationship between the body and the medication and what kind of reaction it could have.

User Account

All Pages