ITDA1001 Database Fundamentals: PartyKids Database System Design

This assignment provides a database solution for PartyKids, a party hire company, addressing their need to manage customer details, bookings, and asset locations. The solution includes creating a database schema with tables for customers, bookings, models, assets, booking_assets, and payments. SQL statements are provided for creating tables, inserting data, updating records, and querying the database to retrieve specific information, such as customers who rented inflatables, booking counts, and total payments. The assignment also discusses the importance of database modeling using entity-relationship diagrams and addresses legal issues related to storing customer credit card details, suggesting security measures like firewalls and user access privileges. Desklib offers similar solved assignments and study tools for students.

COVER PAGE

Task 1
Assumptions
A booking consists of one or more items.

Task 2
Task 2a
1. Create database
create database partykids;
--Amandep singh – 40564
Creating customer table
create table customer (
customerID smallint not null primary key,
firstname nvarchar(50) not null,
lastname nvarchar(50) not null,
address nvarchar(50) not null
);
--Amandep singh - 40564

2. Insert statements
insert into customer VALUES(1,'John p','Smith','12/1 Flinders St, Melbourne
2000');
--Amandep singh - 40564

insert into customer VALUES(2,'Jane','Riley','13/1 Flinders St, Melbourne
2000');
--Amandep singh - 40564
insert into customer VALUES(3,'Jhene','Aiko','12/2 Flinders St, Melbourne
1000');
--Amandep singh - 40564
insert into customer VALUES(4,'Martin','Lawrence','12/3 Flinders St,
Melbourne 2000');
--Amandep singh - 40564

insert into customer VALUES(5,'John','Wick','12/3 Flinders St, Melbourne
2000');
--Amandep singh – 40564
insert into customer VALUES(6,'Roy','Hibbert','12/3 Flinders St, Melbourne
3000');
--Amandep singh - 40564

3. Selecting all customers
select * from customer;
--Amandep singh - 40564
4. Change address of john smith and select name and address
update customer set address='15/1 Flinders St, Melbourne 3000' where
customerID=1;
--Amandep singh - 40564

select firstname, lastname, address from customer;
--Amandep singh - 40564
5. Customers whose name start with j
select * from customer where firstname like 'j%';
--Amandep singh - 40564

6. Details of customers who live in Victoria
select * from customer where address like '%3000';
--Amandep singh - 40564
7. Delete customer john p sith
delete from customer where customerID='1';
--Amandep singh - 40564

8. Add table
 Adding booking table
create table booking(bookingID smallint not null primary key,
customerID smallint not null,
bookingDate date not null,
customerFeedback nvarchar(500) not null
);
--Amandep singh – 40564
 Add relationship between booking table and customer table
alter table booking add constraint
booking_fk Foreign key (customerID)
references customer (customerID)
on delete NO ACTION;
--Amandep singh - 40564

 Inserting 6 records in the booking table
insert into booking values (1,2,'2018-05-05','5 stars');
insert into booking values (2,3,'2018-05-06','3 stars');
insert into booking values (3,3,'2018-05-07','2 stars');
insert into booking values (4,4,'2018-05-08','5 stars');
insert into booking values (5,4,'2018-05-09','4 stars');
insert into booking values (6,4,'2018-05-10','4 stars');
--Amandep singh - 40564
9. Delete a customer who has a booking

delete from customer where customerID=3;
--Amandep singh - 40564
10. Display all bookings grouped by customer
select bookingID,customerID,bookingdate,customerFeedback
from booking
group by customerID,bookingID,bookingdate,customerFeedback;
--Amandep singh - 40564
11. Select customer names and booking dates sorted with booking dates
select customer.firstname,customer.lastname,booking.bookingdate
from customer,booking