ITDA1001 Database Fundamentals: Party Hire Case Study Assignment

This assignment provides a comprehensive database solution for a party hire company, addressing the need to manage customer details, bookings, and asset locations efficiently. The solution includes creating a database schema with tables for customers, bookings, models, assets, booking_assets, and payments, along with appropriate relationships and constraints. SQL queries are provided to insert data, update records, retrieve information based on various criteria (e.g., customers who rented inflatables, booking days per customer, total amount paid by each customer), and address data management challenges. The assignment also discusses the importance of starting with entity-relationship diagrams for database design and legal issues related to storing credit card details, along with suggested security measures. Desklib provides a platform to explore similar solved assignments and past papers for students.

COVER PAGE

Task 1
Assumptions
A booking results to only one payment although the booking may have one or more items.

Task 2
Task 2a
1. Create database and customer table
--Name: gursevak singh
--studnet ID: 40485
create database partykids;
--Name: gursevak singh
--studnet ID: 40485
create table customer (
customerID smallint not null primary key,
firstname nvarchar(50) not null,
lastname nvarchar(50) not null,
address nvarchar(50) not null
);

2. Insert records into customers table.
--Name: gursevak singh
--studnet ID: 40485
insert into customer VALUES(1,'John','Smith','12/1 Flinders St, Melbourne
2000'),
(2,'Peter','Griffin','13/1 Flinders
St, Melbourne 2000'),
(3,'Lois','Griffin','12/2 Flinders
St, Melbourne 1000'),
(4,'Brian','Griffin','12/3 Flinders
St, Melbourne 2000'),
(5,'Meg','Griffin','12/3 Flinders St,
Melbourne 2000'),
(6,'jr Stweie','Griffin','12/3
Flinders St, Melbourne 3000');
3. Selecting all customers
--Name: gursevak singh
--studnet ID: 40485
select * from customer;

4. Change address of john smith and select name and address
--Name: gursevak singh
--studnet ID: 40485
update customer set address='15/1 Flinders St, Melbourne 3000' where
customerID=1;
--Name: gursevak singh
--studnet ID: 40485
select firstname,lastname,address from customer;

5. Customers whose name start with j
--Name: gursevak singh
--studnet ID: 40485
select * from customer where firstname like 'j%';

6. Details of customers who live in Victoria
--Name: gursevak singh
--studnet ID: 40485
select * from customer where address like '%3000';
7. Delete customer john p sith
--Name: gursevak singh
--studnet ID: 40485
delete from customer where customerID='1';
8. Add tables

 Adding booking table
--Name: gursevak singh
--studnet ID: 40485
create table booking(bookingID smallint not null primary key,
customerID smallint not null,
bookingDate date not null,
customerFeedback nvarchar(500) not null
);
 Add relationship between booking table and customer table
--Name: gursevak singh
--studnet ID: 40485
alter table booking add constraint booking_fk1 Foreign key (customerID)
references customer (customerID) on delete NO ACTION;

 Inserting 6 records in the booking table
--Name: gursevak singh
--studnet ID: 40485
insert into booking values (1,2,'2018-05-05','not bad'),
(2,3,'2018-05-
06','not bad'),
(3,3,'2018-05-
07','not bad'),
(4,4,'2018-05-
08','not bad'),
(5,4,'2018-05-
09','not bad'),
(6,4,'2018-05-
10','not bad');
9. Delete a customer who has a booking
--Name: gursevak singh
--studnet ID: 40485
delete from customer where customerID=3;

10. Display all bookings grouped by customer
--Name: gursevak singh
--studnet ID: 40485
select bookingID,customerID,bookingdate,customerFeedback
from booking
group by customerID,bookingID,bookingdate,customerFeedback;

11. Select customer names and booking dates sorted with booking dates
--Name: gursevak singh
--studnet ID: 40485
select customer.firstname,customer.lastname,booking.bookingdate
from customer,booking
where booking.customerID=customer.customerID
order by bookingdate desc;
12. Customers who have not made a booking
--Name: gursevak singh
--studnet ID: 40485
select * from customer where customerID not in (select customeriD from
booking);

Task 2b
13. create other database tables
--Name: gursevak singh
--studnet ID: 40485
create table model(
modelID smallint not null primary key,
type nvarchar(50) not null,
price decimal not null
);
create table assets(
itemID smallint not null primary key,
modelID smallint not null,
constraint assets_fkq foreign key (modelID) references model (modelID) on
delete no action
);
create table booking_assets(
itemID smallint not null,
bookingID smallint not null,
constraint ba_pk primary key (itemID,bookingID),
constraint ba_fk foreign key (itemID) references assets (itemID),
constraint ba_fk2 foreign key (bookingID) references booking (bookingID)
);
create table payment(
paymentid smallint not null primary key,
bookingID smallint not null,
amount decimal not null,
paymentDate date not null,
discount decimal not null,
constraint payment_fk foreign key (bookingID) references booking
(bookingID)
);