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

where booking.customerID=customer.customerID
order by bookingdate desc;
--Amandep singh - 40564
12. Customers who have not made a booking
select * from customer where customerID not in (select customeriD from
booking);
--Amandep singh - 40564
Task 2b
13. create other database tables
create table model(
modelID smallint not null primary key,
type nvarchar(50) not null,
price decimal not null
);
--Amandep singh – 40564

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
);
--Amandep singh - 40564
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)
);
--Amandep singh - 40564

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)
);
--Amandep singh – 40564
14.Adding records
insert into model values (1,'inflatable',100),

(2,'chair',20),
(3,'table',50);
--Amandep singh - 40564
insert into assets values (1,1),
(2,2),
(3,3);
--Amandep singh - 40564
insert into booking_assets values (1,1),(2,2),(3,3);
--Amandep singh - 40564

insert into payment values (1,1,200,'2018-05-08',0),(2,2,300,'2018-05-
09',10), (3,3,300,'2018-05-10',12);
--Amandep singh - 40564
15. All customers who have rented inflatables
select customer.* from customer inner join booking on
booking.customerID=customer.customerID
inner join booking_assets on booking_assets.bookingID=booking.bookingID inner
join assets on assets.itemID=booking_assets.itemID
inner join model on model.modelID=assets.modelID where
model.type='inflatables';
--Amandep singh - 40564

16. All customers who have rented inflatables or chairs
select customer.* from customer inner join booking on
booking.customerID=customer.customerID
inner join booking_assets on booking_assets.bookingID=booking.bookingID inner
join assets on assets.itemID=booking_assets.itemID
inner join model on model.modelID=assets.modelID where
model.type='inflatables' or model.type='chair';
--Amandep singh - 40564
17. Count of booking days for each customer
select
customer.customerID,customer.firstname,customer.lastname,count(booking.booking
ID) FROM customer inner join booking on booking.customerID=customer.customerID
group by customer.customerID,customer.firstname,customer.lastname;
--Amandep singh - 40564

18. Total amount paid for each customer
select
customer.customerID,customer.firstname,customer.lastname,sum(payment.amount)-
sum(discount) as total
FROM customer inner join booking on booking.customerID=customer.customerID
inner join payment on payment.bookingID=booking.bookingID
group by customer.customerID,customer.firstname,customer.lastname;
--Amandep singh - 40564
Task 3
19. Why start by drawing a diagram before using database software
It’s important to start by drawing a diagram which is commonly known as entity relationship
diagram before implementing the real database in the database software. The process of
drawing a diagram to describe the database is known as database modelling and it’s a very
important step as it helps give a deeper understanding of the database by making sure that all
the entities and their attributes are all derived from the case study or the set of requirements.
The entity relationship diagram achieved as a result of modelling the database also helps the
developers to understand the relationships that exist between different entities making up the
database. From the database the entities can be normalized to achieve entities that in 3NF.
These entities are then implemented to form the actual database. Modelling ensures that every
aspect of the database is foreseen by the developers before they start doing the
implementation thus it helps minimize cases of missing of critical requirements from the case
study or the requirements document.
20. Legal issues and how they could be solved
Partykids business intends to allow customers to use credit cards to make their payments. To
make the payment easier for the customers the business wants to keep a record of the credit
card details for each customer such that the customer can make subsequent payments without
having to provide the credit card details each time they make a subsequent booking using a
credit card. Storing the details of the credit cards of its customers is a bold and risky step for the

business as it could have legal implications on the business if the credit card details fell in the
wrong hands through access either from outside or even within the business. Every organization
should aim to safeguard the information of its customers and failing to do that could lead to the
fall of the business because of court cases arising from the loss of critical customer data.
Although the move is bold and risky, the end benefit is that it will make the process of making
payments by the customers easy. There are many ways that protection can be ensured to make
sure the information does not land on unauthorized users either within or outside the business.
One of the ways to ensure customer data is safe is safeguarding the database behind a firewall
and ensuring strict measures are followed when performing operations on the database. This
can be achieved by giving of privileges and rights to different users.