ITDA1001 Database: ER Diagram & SQL for Party Hire Database

This report presents a database solution designed for Party Kids, a company specializing in renting inflatables, tables, and chairs for children's parties. The solution includes an Entity-Relationship (ER) diagram illustrating the database structure, assumptions made during the design process, and SQL queries to create and manipulate the database. The SQL code covers creating the 'PartyKids' database and 'Customer' and 'Booking' tables, inserting customer data, updating records, and performing various queries such as selecting customers based on name or location, deleting records, and displaying booking information. Additionally, the report includes SQL code for creating tables related to the company's inventory (ModelInflatable, Items, Inflatable, ModelChair, Chair, Table, and BookingItems) and queries to retrieve information about rented items and customer bookings. The report also discusses the importance of ER diagrams in database development and legal issues related to data security, along with database security techniques.

ITDA1001-Database Fundamentals
Assessment 2 – Assignment
5/27/2018
Student ID:
Module Tutor:

Report
Task 1
ER Diagram
(CS Odessa Corp. 2016)

Assumptions
1. Complete booking detail will be saved into the database e.g. booking date and time, event
date and time etc.
2. Customer feedback will also be saved into the database.
Task 2 A
Task 2 a (1)
/* Rajinder Singh. Q 2 a. (1) creating database */
create database PartyKids;
/* Rajinder Singh. Q 2 a. (1) creating table Customet */
CREATE TABLE Customer (
CustomerID int NOT NULL,
CustomerName varchar(50) NOT NULL,
[Address] varchar(50) NOT NULL,
Phone varchar(20) NOT NULL,
Email varchar(30) NOT NULL,
PRIMARY KEY (CustomerID)
);

Task 2 a (2)
/* Rajinder Singh. Q 2 a. (2) Insert data into customer table */
insert into Customer (CustomerID,CustomerName, [Address], Phone, Email )
values (1, 'John P Smith','12/1 Flinders St, Melbourne 3000',
'5635632662','[e-mail]'),
(2, 'James Parker','12 George St, Melbourne 3000',
'5635632662','[e-mail]'),
(3, 'Harry White','3 King St, Melbourne 3000',
'5635632662','[e-mail]'),
(4, 'Emily Parker','14/1 Smith St, Sydney 5000',
'5635632662','[e-mail]'),
(5, 'Noah Brown','13/1 Church St, Sydney 5000',
'5635632662','[e-mail]'),
(6, 'Max Hall','11/1 Black St, Sydney 5000', '5635632662','[e-mail]'),
(7, 'Joe White','18/1 Queens St, Sydney 5000',
'5635632662','[e-mail]');

Task 2 a (3)
/* Rajinder Singh. Q 2 a. (3) Show customer data */
select * from Customer;
Task 2 a (4)
/* Rajinder Singh. Q 2 a. (4) Change Address of John P smith Customer */
update Customer set [Address]='15/1 Flinders St, Melbourne 3000' where
CustomerName='John P Smith';
select CustomerName, [Address] from Customer;

Task 2 a (5)
/* Rajinder Singh. Q 2 a. (5) showing customer detail whose name start with J
*/
select * from Customer where CustomerName like 'J%' COLLATE
Latin1_General_BIN;
Task 2 a (6)
/* Rajinder Singh. Q 2 a. (6) showing customer of Victora */
select * from Customer where RIGHT([Address], 4)='3000';

Task 2 a (7)
/* Rajinder Singh. Q 2 a. (7) delete customer John P Smith */
delete from Customer where CustomerName='John P Smith';

Task 2 a (8)
/* Rajinder Singh. Q 2 a. (8) creating table booking and inserting data into
booking table */
CREATE TABLE Booking (
BookingID int NOT NULL,
EventType varchar(20) NOT NULL,
NoOfGuests int NOT NULL,
EventDateTime datetime NOT NULL,
BookingDateTime datetime NOT NULL,
TotalAmount int NOT NULL,
Discount int NULL,
Feedback varchar(50) NULL,
CustomerID int NOT NULL,
PRIMARY KEY (BookingID),
CONSTRAINT FK_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)
);
insert into Booking(BookingID, EventType, NoOfGuests, EventDateTime,
BookingDateTime, TotalAmount, Discount, Feedback, CustomerID)
values (1,'Birthday Party', 20, '10-10-2018','9-10-2017',200,5,'Good',2),
(2,'Birthday Party', 25, '11-10-2018','10-10-2017',300,5,'Good',3),
(3,'Fresher Party', 15, '11-10-2018','10-10-2017',200,7,'Excellent',3),
(4,'Farewell Party', 15, '9-9-2016','8-9-2016',250,5,'Excellent',4),
(5,'Birthday Party', 15, '9-9-2016','8-9-2016',250,5,'Excellent',4),
(6,'Birthday Party', 15, '9-9-2016','8-9-2016',250,5,'Excellent',4);

Task 2 a (9)
/* Rajinder Singh. Q 2 a. (9) delete a customer from customer table */
delete from Customer where CustomerID=2;
Task 2 a (10)
/* Rajinder Singh. Q 2 a. (10) show customer bookings */
select CustomerID, count(BookingID) as 'Bookings' from Booking group by
CustomerID;

Task 2 a (11)
/* Rajinder Singh. Q 2 a. (11) show customer booking timings */
SELECT distinct Customer.CustomerName, Booking.BookingDateTime
FROM Booking INNER JOIN
Customer ON Booking.CustomerID = Customer.CustomerID
order by Booking.BookingDateTime desc;
Task 2 a (12)
/* Rajinder Singh. Q 2 a. (12) show customer who did not do any booking */
select CustomerName from Customer where CustomerID not in (select CustomerID
from Booking);

Task 2 B
Task 2 b (13)
/* Rajinder Singh. Q 2 b. (13) show all tables */
CREATE TABLE ModelInflatable (
Model varchar(10) NOT NULL,
Price int NOT NULL,
Quantity int NOT NULL,
PRIMARY KEY (Model)
);
CREATE TABLE Items (
ItemNo varchar(10) NOT NULL,
Location varchar(10) NOT NULL,
PRIMARY KEY (ItemNo)
);
CREATE TABLE Inflatable (
ItemNo varchar(10) NOT NULL,
Model varchar(10) NOT NULL,
PRIMARY KEY (ItemNo),
CONSTRAINT FK_ItemInflatable FOREIGN KEY (ItemNo)
REFERENCES Items(ItemNo),
CONSTRAINT FK_InflatableModel FOREIGN KEY (Model)
REFERENCES ModelInflatable(Model)
);
CREATE TABLE ModelChair (
Model varchar(10) NOT NULL,
Price int NOT NULL,
Quantity int NOT NULL,
PRIMARY KEY (Model)
);
CREATE TABLE Chair (
ItemNo varchar(10) NOT NULL,
Model varchar(10) NOT NULL,
PRIMARY KEY (ItemNo),