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 ...';

12
DATABASE IMPLEMENTATION
CREATE TABLE Items (
ItemID INTEGER NOT NULL IDENTITY ,
ItemName VARCHAR(150) ,
ItemType VARCHAR(150) ,
Price FLOAT ,
PRIMARY KEY(ItemID));
PRINT 'Creating table Rents ...';
CREATE TABLE Rents (
RentID INTEGER NOT NULL IDENTITY ,
ItemID INTEGER NOT NULL ,
CustomerID INTEGER NOT NULL ,
NumberOfDays INTEGER ,
Price FLOAT ,
PRIMARY KEY(ItemID),
FOREIGN KEY(ItemID)
REFERENCES Items(ItemID),
FOREIGN KEY(CustomerID)

13
DATABASE IMPLEMENTATION
REFERENCES Customer(CustomerID)
);
GO
CREATE INDEX Booking_FKIndex2 ON Rents(ItemID);
GO
CREATE INDEX Booking_FKIndex3 ON Rents(CustomerID);
GO
PRINT 'Creating table Payments ...';
CREATE TABLE Payments (
PaymentID INTEGER NOT NULL IDENTITY ,
RentID INTEGER NOT NULL ,
CustomerID INTEGER NOT NULL ,
Amount FLOAT ,
PRIMARY KEY(PaymentID),
FOREIGN KEY(RentID)
REFERENCES Rents(RentID),
FOREIGN KEY(CustomerID)

14
DATABASE IMPLEMENTATION
REFERENCES Customer(CustomerID)
);
GO
CREATE INDEX Booking_FKIndex2 ON Payments(RentID);
GO
CREATE INDEX Booking_FKIndex3 ON Payments(CustomerID);
GO
14
PRINT 'Populating the Customers table'
SET IDENTITY_INSERT [dbo].[Customers] ON
Insert Into Customers(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'),

15
DATABASE IMPLEMENTATION
(6, 'Robie Fowler', '45 benton street'),
(7, 'Lionel Messi', '56 avenue road');
SET IDENTITY_INSERT [dbo].[Customers] OFF
GO
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Bookings] ON
Insert Into Bookings (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].[Bookings] OFF
GO

16
DATABASE IMPLEMENTATION
PRINT 'Populating the Items table'
SET IDENTITY_INSERT [dbo].[Items] ON
Insert Into Items (ItemID, ItemName, ItemType, Price)
VALUES
(1, 'cushion', 'inflatable', 10),
(2, 'small plastic', 'chairs', 2),
(3, 'flexible', 'others', 23);
SET IDENTITY_INSERT [dbo].[Items] OFF
GO
PRINT 'Populating the Rents table'
SET IDENTITY_INSERT [dbo].[Rents] ON
Insert Into Rents (RentID, ItemID, CustomerID, NumberOfDays, Price)
VALUES
(1, 1, 1, 79, 12),
(2, 1, 2, 87, 11),
(3, 2, 4, 56, 13);

17
DATABASE IMPLEMENTATION
SET IDENTITY_INSERT [dbo].[Rents] OFF
GO
PRINT 'Populating the Payments table'
SET IDENTITY_INSERT [dbo].[Payment] ON
Insert Into Payment (PaymentID, RentID, CustomerID, Amount)
VALUES
(1, 1, 1, 28),
(2, 2, 2, 34),
(3, 3, 4, 43);
SET IDENTITY_INSERT [dbo].[Payment] OFF
GO
15
Select Customers.CustomerName
From Customers Inner Join Rents on Customers.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable';

18
DATABASE IMPLEMENTATION
16
Select Customers.CustomerName
From Customers Inner Join Rents on Customers.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable' Or Items.ItemType ='Chairs';
17
Select Customers.CustomerName, COUNT (Bookings.BookingDate)
From Customers Inner Join Bookings on Customers.CustomerID = Bookings.CustomerID
Group by Customers.CustomerName;

19
DATABASE IMPLEMENTATION
18
Select Customers.CustomerName, SUM (Payment.Amount) As TotalAmount
From Customers Inner Join Payment on Customers.CustomerID = Payment.CustomerID
Group by Customers.CustomerName;

20
DATABASE IMPLEMENTATION
Task 3
19
It is very important that the ER diagram is developed before the development of the
database. It provides an idea to the developer who is developing the database. In addition to
this, the ER diagram also helps in the maintaining the business rules of the organization.
20
There is various type of legal issues that are to be faced if the records of the card details
of the customers are organized in the system. In addition to tis, the storage of the details of the
address might not bring about any legal issues, but the details of the card might have some
legal issues with it.