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

12
DATABASE IMPLEMENTATION
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
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
PRINT 'Populating the Items table'
SET IDENTITY_INSERT [dbo].[Items] ON

13
DATABASE IMPLEMENTATION
Insert Into Items (ItemID, ItemName, ItemType, Price)
VALUES
(1, 'Arm resting', 'inflatable', 16),
(2, 'Foldong', 'chairs', 36),
(3, 'Portable', 'inflatable', 24);
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, 54, 12),
(2, 2, 2, 87, 23),
(3, 3, 4, 84, 13);
SET DENTITY_INSERT [dbo].[Rents] OFF
GO
PRINT 'Populating the Payments table'

14
DATABASE IMPLEMENTATION
SET IDENTITY_INSERT [dbo].[Payment] ON
Insert Into Payment (PaymentID, RentID, CustomerID, Amount)
VALUES
(1, 1, 1, 11),
(2, 2, 2, 12),
(3, 3, 4, 99);
SET IDENTITY_INSERT [dbo].[Payment] OFF
GO

15
DATABASE IMPLEMENTATION
15
Select Customer.CustomerName
From Customer Inner Join Rents on Customer.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable';

16
DATABASE IMPLEMENTATION
16
Select Customer.CustomerName
From Customer Inner Join Rents on Customer.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable' Or Items.ItemType ='Chairs';
17
Select Customer.CustomerName, COUNT (Booking.BookingDate)
From Customer Inner Join Booking on Customer.CustomerID = Booking.CustomerID
Group by Customer.CustomerName;
18
Select Customer.CustomerName, SUM (Payments.Amount) As TotalAmount

17
DATABASE IMPLEMENTATION
From Customer Inner Join Payments on Customer.CustomerID = Payments.CustomerID
Group by Customer.CustomerName;
Task 3
19
For the database implmetation an ER diagram is very essential. In addition to his it
should also be noted that the designing of a database without an ER diagram becomes very
difficult for the designer. The ER Diagram is fundamentally a kind of flowchart that aides in
giving the plan to the individual who is outlining the database about entities and attributes that
are to be executed in the database. The outline of the ER chart likewise gives the database
designer the idea of the datatypes that are to be executed in the framework. The ER Diagram
likewise helps the association in keeping up the information and actualizes the business rules of
the framework. 20
The organization is looking to implement a database for the business procedures but the
storage of the credit card details can become very risky for the organization to store the details.
The storage of the details of the card would likewise make them helpless and the consumer
loyalty of the organization would be hampered by this. Nonetheless, it ought to be noticed that
the storage of the addresses of the clients would not hamper the association to a great extent
anyway it ought to be remembered that the association should not release the data to the
externals to the system. Subsequently, there are two distinct characteristics that have two unique

18
DATABASE IMPLEMENTATION
criteria totally and henceforth the circumstance requires the database designer to deal with the
circumstance as needs be.