ITDA1001 Project: Database Design and Implementation for Party Hire

This project focuses on designing and implementing a database for a party hire business, addressing the challenges of managing customer details, bookings, and asset locations. The solution includes an ER diagram illustrating the entities and attributes of the database, along with SQL queries for creating tables (Customer, Booking, Items, Rents, Payments), populating them with data, and performing various operations such as updating records, deleting entries, and retrieving information. The project also discusses the importance of ER diagrams in database design and considerations for data security, particularly regarding the storage of sensitive information like credit card details. The implementation covers tasks like creating databases and tables, inserting and updating data, and performing queries to extract relevant information about customers, bookings, and rented items.

Running head: DATABASE CONCEPTS
Database Concepts
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 PartyHire;
GO
PRINT 'Creating table Customer ...';
CREATE TABLE Customer (

2
DATABASE IMPLEMENTATION
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, 'Derek Banas', '123 lindsay street Victoria 3000'),
(2, 'David Beckham', '123 london road Victoria 3000'),
(3, 'Cristiano', '56 portugal road'),
(4, 'Phillip Lahm', 'german road'),
(5, 'Chris Evans', '32 centre street Victoria 3000'),
(6, 'Jhonny Evans', '45 manchester street'),
(3, 'John P Smith', '12/1 Flinders St, Melbourne 3000');
SET IDENTITY_INSERT [dbo].[Customer] OFF

3
DATABASE IMPLEMENTATION
GO
3
Select * from Customer;
4
Print 'Updating columns';
UPDATE dbo.Customer
SET CustomerAddress = ' 15/1 Flinders Street'
WHERE CustomerName = 'John P Smith'
GO
Select CustomerName, CustomerAddress From Customer;

4
DATABASE IMPLEMENTATION
5
Select *
From Customer
Where CustomerName LIKE 'J%';
6
Select *
From Customer
Where CustomerAddress LIKE '%3000%';
7

5
DATABASE IMPLEMENTATION
Delete From Customer
Where CustomerName = 'John P Smith';
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),
);
GO
CREATE INDEX Booking_FKIndex1 ON Booking(CustomerID);
GO

6
DATABASE IMPLEMENTATION
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Booking] ON
Insert Into Booking (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-08', 'Average'),
(9, 2, '2018-12-09', 'Highly satisfying'),
(10, 2, '2018-05-01', 'Better than the previous one'),
(11, 4, '2017-09-29', 'Excellent'),
(12, 4, '2017-03-24', 'Fine'),
(13, 4, '2017-10-30', 'Great');
SET IDENTITY_INSERT [dbo].[Booking] OFF
GO
9
Delete From Customer
Where CustomerID = 2;

7
DATABASE IMPLEMENTATION
10
Select BookingID, BookingDate, CustomerFeedback
From Booking
Group By CustomerID;
11
Select Customer.CustomerName, Booking.BookingDate
From Customer inner join Booking on Customer.CustomerID = Booking.CustomerID
Order By Booking.BookingDate DESC;
12

8
DATABASE IMPLEMENTATION
Select Customer.CustomerName
From Customer
Where Customer.CustomerID not in
( Select Customer.CustomerID
From Customer inner join Booking on Customer.CustomerID = Booking.CustomerID);
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 ...';

9
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(RentID),
FOREIGN KEY(ItemID)
REFERENCES Items(ItemID),
FOREIGN KEY(CustomerID)

10
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)
REFERENCES Customer(CustomerID)

11
DATABASE IMPLEMENTATION
);
GO
CREATE INDEX Booking_FKIndex2 ON Payments(RentID);
GO
CREATE INDEX Booking_FKIndex3 ON Payments(CustomerID);
GO
14
PRINT 'Populating the Customer table'
SET IDENTITY_INSERT [dbo].[Customer] ON
Insert Into Customer (CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Derek Banas', '123 lindsay street Victoria 3000'),
(2, 'David Beckham', '123 london road Victoria 3000'),
(3, 'Cristiano', '56 portugal road'),
(4, 'Phillip Lahm', 'german road'),
(5, 'Chris Evans', '32 centre street Victoria 3000'),
(6, 'Jhonny Evans', '45 manchester street'),
(3, 'John P Smith', '12/1 Flinders St, Melbourne 3000');