ITDA1001 - Database Design and Implementation: Party Hire System

This report presents a database solution for Party Kids, a company specializing in party rentals. The solution includes an ER diagram outlining the entities and relationships within the database, assumptions made during the design process, and SQL queries for creating and manipulating the database. The SQL script covers creating tables for customers, bookings, inflatable models, chairs, tables, and items, along with constraints and relationships between these tables. It also includes queries for inserting, updating, and deleting data, as well as retrieving specific information such as customer bookings, booking timings, and customers who have rented specific items. The report also discusses the benefits of using ER diagrams in database design and addresses legal issues related to database security, including security techniques in DBMS like authentication, authorization, access controls, and encryption/decryption. This comprehensive solution aims to provide a robust and efficient system for managing customer details, bookings, and asset locations for Party Kids.

Student ID:
Student Name:

Report
Task 1
ER Diagram

Assumptions
1. Booking and event timings will be stored into the database.
2. Location of each item will be stored into the database.
(USA Informa 2018)
(Watt A. n.d.)

Task 2 A
Task 2 a (1)
/* Rohit Hasan . Q 2 a. (1) This command is creating database */
create database PartyKids;
use PartyKids;
/* Rohit Hasan . Q 2 a. (1) This command is creating table */
CREATE TABLE Customer (
CusID int NOT NULL,
CusName varchar(30) NOT NULL,
CusAddress varchar(40) NOT NULL,
CusPhone varchar(20) NOT NULL,
CusEmail varchar(30) NOT NULL,
PRIMARY KEY (CusID)
);
Task 2 a (2)
/* Rohit Hasan . Q 2 a. (2) This command is inserting data */
insert into Customer (CusID,CusName, CusAddress, CusPhone, CusEmail )
values (1, 'John P Smith','12/1 Flinders St, Melbourne 3000',
'6345123876','[e-mail]'),
(2, 'Nick Johnsberg','12/1 Patrick St, Melbourne 3000',
'6345123876','[e-mail]'),

(3, 'Max White','15/1 Patrick St, Melbourne 3000',
'6345123876','[e-mail]'),
(4, 'Diana Hadden','12/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]'),
(5, 'Edwick Parker','11/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]'),
(6, 'Hanry Smith','15/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]'),
(7, 'Michael Brown','19/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]');
Task 2 a (3)
/* Rohit Hasan . Q 2 a. (3) This command is displaying Customer data */
select * from Customer;

Task 2 a (4)
/* Rohit Hasan . Q 2 a. (4) This command is changing Address of John P smith
*/
update Customer set CusAddress='15/1 Flinders St, Melbourne 3000' where
CusName='John P Smith';
select CusName, CusAddress from Customer;
Task 2 a (5)
/* Rohit Hasan . Q 2 a. (5) This command is displaying Customer detail whose
name start with J */

select * from Customer where CusName like 'J%' COLLATE Latin1_General_BIN;
Task 2 a (6)
/* Rohit Hasan . Q 2 a. (6) This command is displaying Customers of Victora */
select * from Customer where RIGHT(CusAddress, 4)='3000';
Task 2 a (7)
/* Rohit Hasan . Q 2 a. (7) This command is deleting customer John P Smith */

delete from Customer where CusName='John P Smith';
Task 2 a (8)
/* Rohit Hasan . Q 2 a. (8) This command is creating booking table and
inserting data into that table */
CREATE TABLE Booking (
BookingID int NOT NULL,
EvtType varchar(20) NOT NULL,
NoOfGuests int NOT NULL,
EvtDateTime datetime NOT NULL,
BookDateTime datetime NOT NULL,
TotalAmt int NOT NULL,
Discount int NULL,
Feedback varchar(50) NULL,
CusID int NOT NULL,
PRIMARY KEY (BookingID),
CONSTRAINT FK_CusID FOREIGN KEY (CusID)
REFERENCES Customer(CusID)
);
insert into Booking(BookingID, EvtType, NoOfGuests, EvtDateTime, BookDateTime,
TotalAmt, Discount, Feedback, CusID)
values (1,'Birthday', 20, '3-3-2018','4-4-2018',300,5,'Best',2),
(2,'Birthday', 25, '10-10-2018','11-10-2017',400,5,'Nice',3),
(3,'Fresher', 15, '12-10-2018','12-10-2017',300,7,'Best',3),
(4,'Farewell', 15, '10-9-2016','12-9-2016',350,5,'Best',4),
(5,'Birthday', 15, '10-9-2016','12-9-2016',350,5,'Best',4),
(6,'Birthday', 15, '11-9-2016','12-9-2016',350,5,'Nice',4);

Task 2 a (9)
/* Rohit Hasan . Q 2 a. (9) This command is deleting a Customer from Cusomer
table */
delete from Customer where CusID=2;
Task 2 a (10)
/* Rohit Hasan . Q 2 a. (10) This command is displaying Cusomer bookings */
select CusID, count(BookingID) Bookings from Booking group by CusID;

Task 2 a (11)
/* Rohit Hasan . Q 2 a. (11) This command is displaying Customer booking
timings */
SELECT distinct Customer.CusName, Booking.BookDateTime
FROM Booking INNER JOIN
Customer ON Booking.CusID = Customer.CusID
order by Booking.BookDateTime desc;
Task 2 a (12)
/* Rohit Hasan . Q 2 a. (12) This command is showing Customer who did not do
any booking */
select CusName from Customer where CusID not in (select CusID from Booking);

Task 2 B
Task 2 b (13)
/* Rohit Hasan . Q 2 b. (13) Create all tables */
CREATE TABLE InflatableModel (
FModel varchar(15) NOT NULL,
FPrice int NOT NULL,
FQty int NOT NULL,
PRIMARY KEY (FModel)
);
CREATE TABLE Items (
FItmNo varchar(15) NOT NULL,
FLocation varchar(15) NOT NULL,
PRIMARY KEY (FItmNo)
);
CREATE TABLE Inflatables (
FItmNo varchar(15) NOT NULL,
FModel varchar(15) NOT NULL,
PRIMARY KEY (FItmNo),
CONSTRAINT FK_ItemsInflatable FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo),
CONSTRAINT FK_InfModels FOREIGN KEY (FModel)
REFERENCES InflatableModel(FModel)
);
CREATE TABLE ChairModel (
FModel varchar(15) NOT NULL,
FPrice int NOT NULL,
FQty int NOT NULL,
PRIMARY KEY (FModel)
);
CREATE TABLE Chairs (
FItmNo varchar(15) NOT NULL,
FModel varchar(15) NOT NULL,
PRIMARY KEY (FItmNo),
CONSTRAINT FK_ItmChairs FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo),
CONSTRAINT FK_ChairsModels FOREIGN KEY (FModel)
REFERENCES ChairModel(FModel)
);
CREATE TABLE [Tables] (
FItmNo varchar(15) NOT NULL,
PRIMARY KEY (FItmNo),
CONSTRAINT FK_ItmsTable FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo)
);
CREATE TABLE BookingItems (
FBookingID int NOT NULL,
FItmNo varchar(15) NOT NULL,
PRIMARY KEY (FBookingID,FItmNo),
CONSTRAINT FK_Bookings FOREIGN KEY (FBookingID)

REFERENCES Booking(BookingID),
CONSTRAINT FK_ItemsBookings FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo)
);
Task 2 b (14)
/* Rohit Hasan . Q 2 b. (14) Insert data */
insert into InflatableModel (FModel, FPrice, FQty)
values ('MIF1',30,300),
('MIF2',40,300),
('MIF3',20,200);
insert into ChairModel (FModel, FPrice, FQty)
values ('MCH1',20,300),
('MCH2',50,300),
('MCH3',40,200);
insert into Items(FItmNo, FLocation)
values (1,'Central'),
(2,'East'),
(3,'West'),
(4,'Central'),
(5,'East'),
(6,'West'),
(7,'Central'),
(8,'East'),
(9,'West');
insert into Inflatables(FItmNo, FModel)
values (1, 'MIF1'),
(2, 'MIF2'),
(3, 'MIF3');
insert into Chairs(FItmNo, FModel)
values (4, 'MCH1'),