ITDA1001 Database Fundamentals: Party Hire Database Project

This project provides a database implementation for a party hire company named Party Kids. It includes an ER diagram and SQL scripts for creating and populating the database with tables for customers, bookings, items, rents, and payments. The project demonstrates how to create tables, insert data, update records, and perform various queries such as selecting customer names based on item types rented, counting bookings per customer, and calculating total payments. The importance of ER diagrams in database development and potential legal issues related to storing customer card details are also discussed. This document is available on Desklib, where students can access a wide range of solved assignments and past papers.

Running head: DATABASE IMPLEMENTATION
Database Implementation
Name of the Student:
Name of the University:
Author Note

1
DATABASE IMPLEMENTATION
Task 1
ER Diagram
Task 2
Task 2a
1
PRINT 'Creating database.';
CREATE DATABASE PartyKids;
GO
PRINT 'Creating table Customer ...';

2
DATABASE IMPLEMENTATION
CREATE TABLE Customer (
CustomerID INTEGER NOT NULL IDENTITY ,
CustomerName VARCHAR(45) ,
CustomerAddress VARCHAR(150) ,
PRIMARY KEY(CustomerID));
GO
2
PRINT 'Populating the Customer table'
SET IDENTITY_INSERT [dbo].[Customer] ON
Insert Into Customer(CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Cristiano Ronalado', '123 Porto street'),
(2, 'Pedro Rodriguez', 'benson road'),
(3, 'John Smith', '12/1 Flinders St, Melbourne 3000'),
(4, 'Adil Rashid', 'BJ road'),
(5, 'Michael owen', '32 centre street'),
(6, 'Robie Fowler', '45 benton street'),

3
DATABASE IMPLEMENTATION
(7, 'Lionel Messi', '56 avenue road');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
3
Select * from Customers;
4
Print 'Updating columns';
UPDATE dbo.Customer
SET CustomerAddress = ' 15/1 Flinders Street'
WHERE CustomerName = 'John P Smith'

4
DATABASE IMPLEMENTATION
GO
Select CustomerName, CustomerAddress From Customer;
5
Select *
From Customer
Where CustomerName LIKE 'J%';

5
DATABASE IMPLEMENTATION
6
Select *
From Customer
Where CustomerAddress LIKE '%3000%';

6
DATABASE IMPLEMENTATION
7
Delete From Customer
Where CustomerName = 'John P Smith';

7
DATABASE IMPLEMENTATION
8
PRINT 'Creating table Booking ...';
CREATE TABLE Booking (
BookingID INTEGER NOT NULL IDENTITY ,
CustomerID INTEGER NOT NULL ,
BookingDate DATE ,
CustomerFeedback VARCHAR(150) ,
PRIMARY KEY(BookingID),
FOREIGN KEY(CustomerID)
REFERENCES Customer(CustomerID),

8
DATABASE IMPLEMENTATION
);
GO
CREATE INDEX Booking_FKIndex1 ON Booking(CustomerID);
GO
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Booking] ON
Insert Into Booking (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-09', 'Excellent'),
(9, 2, '2017-12-24', 'It was okay'),
(10, 2, '2018-04-30', 'Fine'),
(11, 4, '2017-09-21', 'Excellent'),
(12, 4, '2018-03-28', 'Fine'),
(13, 4, '2017-09-01', 'It was okay');
SET IDENTITY_INSERT [dbo].[Booking] OFF
GO
9

9
DATABASE IMPLEMENTATION
Delete From Customers
Where CustomerID = 3;
10
Select CustomerID, BookingID, BookingDate, CustomerFeedback
From Bookings
Group By CustomerID, BookingID, BookingDate, CustomerFeedback;

10
DATABASE IMPLEMENTATION
11
Select Customer.CustomerName, Booking.BookingDate
From Customer inner join Booking on Customer.CustomerID = Booking.CustomerID
Order By Booking.BookingDate DESC;
12
Select Customer.CustomerName
From Customer
Where Customer.CustomerID not in
( Select Customer.CustomerID
From Customer inner join Booking on Customer.CustomerID = Booking.CustomerID);

11
DATABASE IMPLEMENTATION
Task 2b
13
PRINT 'Creating table Customer ...';
CREATE TABLE Customer (
CustomerID INTEGER NOT NULL IDENTITY ,
CustomerName VARCHAR(45) ,
CustomerAddress VARCHAR(150) ,
PRIMARY KEY(CustomerID));
GO
PRINT 'Creating table Items ...';