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)
);

14.Adding records
--Name: gursevak singh
--studnet ID: 40485
insert into model values (1,'inflatable',100),(2,'chair',20),
(3,'table',50);
insert into assets values (1,1),(2,2),(3,3);
insert into booking_assets values (1,1),(2,2),(3,3);
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);

15. All customers who have rented inflatables
--Name: gursevak singh
--studnet ID: 40485
select customer.* from customer,booking,booking_assets,assets,model where
booking.customerID=customer.customerID
and booking_assets.bookingID=booking.bookingID and
assets.itemID=booking_assets.itemID
and model.modelID=assets.modelID and model.type='inflatables';

16. All customers who have rented inflatables or chairs
--Name: gursevak singh
--studnet ID: 40485
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';
17. Count of booking days for each customer
--Name: gursevak singh
--studnet ID: 40485
select
customer.customerID,customer.firstname,customer.lastname,count(booking.book
ingID)
FROM customer, booking where booking.customerID=customer.customerID
group by customer.customerID,customer.firstname,customer.lastname;

18. Total amount paid for each customer
--Name: gursevak singh
--studnet ID: 40485
select
customer.customerID,customer.firstname,customer.lastname,sum(paym
ent.amount)-sum(discount) as total
FROM customer,booking,payment where
booking.customerID=customer.customerID
and payment.bookingID=booking.bookingID
group by
customer.customerID,customer.firstname,customer.lastname;

Task 3
19. Justification for starting with a diagram instead of the database software.
The process of coming up with diagrams which are referred to as entity relationship diagrams is
called entity relationship modelling and a stage in database design that is done prior to the
actual database implementation is a database management software (DBMS). According to
good database design standards the first step to designing a database is to model the database
using either top-down approach or bottom-up approach. Use of top-down approach is the
approach that designs the database using entity relationship diagrams while bottom-up
approach uses normalization to get the entities from the business rules. These two approaches
are used interchangeably to help the database designer to understand the database
comprehensively thus making sure that all entities, their attributes and the relationships
between the entities are captured. Modelling the database before doing the actual
implementation makes the implementation process easy since everything about the database is
already stated thus the developer only has to make the tables based on the entities and the
relationships achieved during the modelling process. This helps save time and avoid errors which
could lender the database ineffective because of errors that could have been captured during
the design stage of the database development.
20. Legal issues resulting from saving of credit card details and how they could be solved
With the current and expected growth of the partykids business, the business will have to start
recording details of credit cards for customers who pay for their booking using credit cards. This
will help the business by making the payment process easy as all the information about a
customer is already in the database thus more orders will be processed and at a higher rate.
Storing customer’s credit card details can bring legal issues to the business because is the
information is accessed by malicious users like hackers, the information could be used to
defraud the customers and this would be blamed on the business as it is supposed to make sure
the customer’s information is safe from all risks. There have been many cases where companies
have gone broke because their private information has been accessed by hackers.
To prevent access of this confidential information, partykids will have to make sure that the
database is deployed in a very secure environment that is not easy for hackers or malicious
users to access. Apart from the secure deployment environment, the business should make sure
that the information is stored in an encrypted format that is not easy to decrypt if the malicious
users are able to bypass the secure environment.