ITDA1001 Database Fundamentals: Party Hire Database Case Study

This assignment solution addresses a database fundamentals problem based on a party hire case study. It includes the creation of an ER diagram and SQL queries to manage customer details, bookings, and asset storage locations for Party Kids, a company renting out party supplies. The solution involves creating and populating tables for customers, bookings, items, rents, and payments, along with implementing update and delete operations. The assignment also discusses ethical considerations related to storing customer payment information and emphasizes the importance of data security. The document concludes with a bibliography of relevant database resources. Desklib provides this and many other solved assignments for students.

Running head: DATABASE FUNDAMENTALS
ITDA1001 - Database Fundamentals
Name of the Student:
Name of the University:
Author Note

1
DATABASE FUNDAMENTALS
Task 1
ER Diagram
Task 2
Task 2a
1
PRINT 'Creating database.';
CREATE DATABASE PartyKids;
GO

2
DATABASE FUNDAMENTALS
PRINT 'Creating table Customer ...';
CREATE TABLE Customer (
CustomerID INTEGER NOT NULL IDENTITY ,
CustomerName VARCHAR(45) ,
CustomerAddress VARCHAR(150) ,
PRIMARY KEY(CustomerID));
GO

3
DATABASE FUNDAMENTALS
2
PRINT 'Populating the Customer table'
SET IDENTITY_INSERT [dbo].[Customer] ON
Insert Into Customer(CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Rayan Renolds', '123 denver street'),
(2, 'kevin spacey', '123 lee road'),
(3, 'John P Smith', '12/1 Flinders St, Melbourne 3000'),
(4, 'Phillip Lahm', 'BJ road'),
(5, 'Kevin Owens', '32 centre street'),

4
DATABASE FUNDAMENTALS
(6, 'Robie Fowler', '45 benton street'),
(7, 'Lionel Messi', '56 avenue road');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
3
Select * from Customer;

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

6
DATABASE FUNDAMENTALS
5
Select *
From Customer
Where CustomerName LIKE 'J%';

7
DATABASE FUNDAMENTALS
6
Select *
From Customer
Where CustomerAddress LIKE '%3000%';

8
DATABASE FUNDAMENTALS
7
Delete From Customer
Where CustomerName = 'John P Smith';

9
DATABASE FUNDAMENTALS
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

10
DATABASE FUNDAMENTALS
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Booking] ON
Insert Into Booking (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-08', 'Excellent'),
(9, 2, '2017-12-09', 'It was okay'),
(10, 2, '2018-04-01', 'Fine'),
(11, 4, '2017-09-24', 'Excellent'),
(12, 4, '2018-03-24', 'Fine'),
(13, 4, '2017-09-30', 'It was okay');

11
DATABASE FUNDAMENTALS
SET IDENTITY_INSERT [dbo].[Booking] OFF
GO
9
Delete From Customer
Where CustomerID = 2;
10
Select CustomerID, BookingID, BookingDate, CustomerFeedback
From Booking
Group By CustomerID;
11