ITDA1001 Database Fundamentals: PartyKids Database Project

This project provides a comprehensive database solution for the Party Kids case study. It includes an Entity Relationship Diagram (ERD) illustrating the database structure and SQL queries for creating, populating, and querying the database. The database design encompasses tables for Customers, Bookings, Models, AssetItems, Feedback, and AssetHired, with appropriate relationships defined using foreign keys. The SQL queries cover various operations such as creating tables, inserting data, updating records, deleting records, and complex queries involving joins and subqueries to retrieve specific information, such as customers who hired inflatables or chairs. The project also discusses the purpose of ER diagrams in database design and the legal and security issues related to storing credit card details, along with data security techniques like encryption and user authentication.

Task 1:
ER Diagram:
Task 2A:
1) Query:
/* Anmoldeep Kaur. Q1. Creating a database */
CREATE DATABASE PartyKids;
/* Anmoldeep Kaur. Q1. Creating a Customer table */
CREATE TABLE Customer (
CustomerID INT NOT NULL,
CustomerName VARCHAR (30) NOT NULL,
CustomerAddress VARCHAR (100) NOT NULL,
CustomerPhoneNumber VARCHAR (13) NOT NULL,
PRIMARY KEY (CustomerID));

Output:
2) Query:
/* Anmoldeep Kaur. Q2. Inserting Customer Data */
INSERT INTO Customer VALUES
(1, 'John P Smith', '12/1 Flinders St, Melbourne 3000', '757-414-1445'),
(2, 'Phillip D. Hawkins', '1921 Lyndon Street Allentown, PA 18101', '610-782-8197'),
(3, 'Ferdinand E. Fry', '2172 Commerce Boulevard Norfolk, NE 68701','402-518-8387'),
(4, 'Elizabeth C. Morton', '2245 Glen Street Owensboro, KY 42301', '270-315-8101'),
(5, 'Phyllis W. Thomas', '4629 Myra Street East Greenwich, RI 02818', '401-541-6737'),
(6, 'Cody O. Mascarenas', '4624 Adams Drive Bryan, TX 77803', '979-436-1193'),
(7, 'Freeman A. Edwards', '3290 Aviation Way Los Angeles, CA 90071', '213-988-4308');
Output:

3) Query:
/* Anmoldeep Kaur. Q3. Selecting Customer Data */
SELECT * FROM Customer;
Output:
4) Query:
/* Anmoldeep Kaur. Q4. Updating Customer Data */
UPDATE Customer SET CustomerAddress='15/1 Flinders Streett, Melbourne 3000'
WHERE CustomerName='John P Smith';
/* Anmoldeep Kaur. Q4. Selecting Customer Data */
SELECT CustomerName, CustomerAddress FROM Customer;

Output:
5) Query:
/* Anmoldeep Kaur. Q5. Selecting Customer Starts With J */
SELECT * FROM Customer WHERE CustomerName LIKE 'J%';
Output:

6) Query:
/* Anmoldeep Kaur. Q6. Selecting Customer in Victoria */
SELECT * FROM Customer WHERE CustomerAddress LIKE '%3000';
Output:
7) Query:
/* Anmoldeep Kaur. Q7. Deleting a Customer Data */
DELETE FROM Customer WHERE CustomerName LIKE 'John P Smith';
Output:

8) Query:
/* Anmoldeep Kaur. Q8. Creating Booking table */
CREATE TABLE Booking(
BookingID INT NOT NULL,
BookingDate DATETIME NOT NULL,
CustomerID INT NOT NULL,
BookingDays INT NOT NULL,
EventDate DATETIME NOT NULL,
TotalAmount MONEY NOT NULL,
Discount MONEY NOT NULL,
PaymentStatus VARCHAR(15) NOT NULL,
PaymentDate DATETIME,
PRIMARY KEY (BookingID),
CONSTRAINT FK_BookingCustomerID FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID));
/* Anmoldeep Kaur. Q8. Inserting Booking Data */
INSERT INTO Booking VALUES
(1, '9-13-2016', 3, 2, '9-23-2016', 1200, 0, 'Done', '9-25-2016'),
(2, '12-13-2016', 2, 4, '12-23-2016', 2500, 50, 'Done', '12-27-2016'),
(3, '1-12-2017', 4, 2, '1-17-2017', 500, 0, 'Done', '1-20-2017'),
(4, '3-23-2017', 4, 3, '3-28-2017', 700, 0, 'Done', '3-30-2017');
INSERT INTO Booking (BookingID, BookingDate, CustomerID, BookingDays,
EventDate, TotalAmount, Discount, PaymentStatus) VALUES
(5, '8-19-2017', 2, 5, '8-25-2017', 1000, 0, 'Progressing'),
(6, '3-19-2018', 3, 6, '3-25-2018', 3000, 500, 'Progressing');
Output:

9) Query:
/* Anmoldeep Kaur. Q9. Deleting a Booked Customer Data */
DELETE FROM Customer WHERE CustomerID = 4;
Output:
10) Query:
/* Anmoldeep Kaur. Q10. Selecting Booking Data */
SELECT * FROM Booking ORDER BY CustomerID;
Output:

11) Query:
/* Anmoldeep Kaur. Q11. Selecting Customer Booking By Booking Date */
SELECT CustomerName, BookingDate FROM Booking INNER JOIN Customer ON
Customer.CustomerID=Booking.CustomerID ORDER BY BookingDate;
Output:
12) Query:
/* Anmoldeep Kaur. Q12. Selecting Customer With No Booking */
SELECT CustomerName FROM Customer WHERE CustomerID NOT IN (SELECT
CustomerID FROM Booking);
Output:

Task 2B
13) Query:
/* Anmoldeep Kaur. Q13. Creating a tables */
CREATE TABLE Model(
ModelID VARCHAR(30),
ModelName VARCHAR(30),
ModelPrice MONEY,
PRIMARY KEY(ModelID));
CREATE TABLE AssetItem(
AssetID VARCHAR(30),
ModelID VARCHAR(30),
PRIMARY KEY(AssetID),
CONSTRAINT FK_AssetItemModelID FOREIGN KEY (ModelID) REFERENCES
Model(ModelID));
CREATE TABLE Feedback(
FeedbackID INT,
CustomerID INT,
AssetID VARCHAR(30),
Feedback VARCHAR(250),
PRIMARY KEY(FeedbackID),
CONSTRAINT FK_FeedbackCustomerID FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID),
CONSTRAINT FK_FeedbackAssetID FOREIGN KEY (AssetID) REFERENCES
AssetItem(AssetID));
CREATE TABLE AssetHired(
BookingID INT,
AssetID VARCHAR(30),
PRIMARY KEY(BookingID,AssetID),
CONSTRAINT FK_AssetHiredBookingID FOREIGN KEY (BookingID)
REFERENCES Booking(BookingID),
CONSTRAINT FK_AssetHiredAssetID FOREIGN KEY (AssetID) REFERENCES
AssetItem(AssetID));

Output:
14) Query:
/* Anmoldeep Kaur. Q14. Inserting Data */
INSERT INTO Model VALUES
('ModelA','Inflatable', 20),
('ModelB','Chair', 10),
('ModelC','Table', 5);
INSERT INTO AssetItem VALUES
('ModelA#1','ModelA'),
('ModelB#1','ModelB'),
('ModelA#2','ModelA'),
('ModelB#2','ModelB'),
('ModelC#1','ModelC');
INSERT INTO Feedback VALUES
(1, 3,'ModelA#1','Good Asset'),
(2, 2,'ModelB#2','Nice Chair'),
(3, 4,'ModelA#1','Good Quality Inflatable'),
(4, 5,'ModelC#1','Gud Wooden tabke'),
(5, 3,'ModelB#2','Good Chair');
INSERT INTO AssetHired VALUES
(1,'ModelA#1'),
(1,'ModelB#2'),

(2,'ModelB#1'),
(3,'ModelA#1'),
(4,'ModelC#1'),
(5,'ModelA#2'),
(6,'ModelB#1');
Output:
15) Query:
/* Anmoldeep Kaur. Q15. Selecting Customer who hired Inflatable */
SELECT CustomerName FROM CUSTOMER WHERE CustomerID IN (
SELECT CustomerID FROM (((Booking INNER JOIN AssetHired ON
Booking.BookingID =AssetHired.BookingID)
INNER JOIN AssetItem ON AssetItem.AssetID=AssetHired.AssetID) INNER JOIN
Model ON Model.ModelID= AssetItem.ModelID)
WHERE ModelName LIKE 'Inflatable');

Output:
16) Query:
/* Anmoldeep Kaur. Q16. Selecting Customer who hired chairs or inflatables */
SELECT CustomerName FROM CUSTOMER WHERE CustomerID IN (
SELECT CustomerID FROM (((Booking INNER JOIN AssetHired ON
Booking.BookingID =AssetHired.BookingID)
INNER JOIN AssetItem ON AssetItem.AssetID=AssetHired.AssetID) INNER JOIN
Model ON Model.ModelID= AssetItem.ModelID)
WHERE ModelName LIKE 'Inflatable' OR ModelName LIKE 'Chair');
Output: