Database Fundamental: Creating and Managing a Party Rental Database

This article provides step-by-step instructions on creating and managing a party rental database using SQL statements and normalization techniques. It also discusses legal issues related to storing customer credit card information and provides suggestions for securing the database.

Name Gurpreet singh
student id 40738
Database fundamental

Task 1
Assumptions
A customer can book more than one item in one single booking thus a booking has one or more items.

Task 2
Task 2a
1. Create statemets
/*Gurpreet singh-40738 creating database*/
create database partykids;
/*Gurpreet singh-40738 creating database*/
use partykids;
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 statements
/*Gurpreet singh-40738 Inserting record 1*/
use partykids;
insert into customer VALUES(1,'John p','Smith','12/1 Flinders St, Melbourne
3000');

/*Gurpreet singh-40738 Inserting record 1*/
use partykids;
insert into customer VALUES(2,'Lebron','James','12/1 Flinders St,
Melbourne 2000');
/*Gurpreet singh-40738 Inserting record 1*/
use partykids;
insert into customer VALUES(3,'Tristan','THompson','12/2 Flinders St,
Melbourne 1000');
/*Gurpreet singh-40738 Inserting record 1*/
use partykids;
insert into customer VALUES(4,'Peter','Griffin','13/2 Flinders St,
Melbourne 3000');
/*Gurpreet singh-40738 Inserting record 1*/
use partykids;
insert into customer VALUES(5,'Lois','Griffin','13/2 Flinders St,
Melbourne 3000');
/*Gurpreet singh-40738 Inserting record 1*/
use partykids;
insert into customer VALUES(6,'Meeg','Griffin','13/2 Flinders St,
Melbourne 3000');
/*Gurpreet singh-40738 Inserting record 1*/
use partykids;
insert into customer VALUES(7,'Brian','Griffin','13/2 Flinders St,
Melbourne 3000');

3. Selecting all customers
/*Gurpreet singh-40738 selecting all customers*/
select * from customer;
4. Change address of john smith and select name and address
/*Gurpreet singh-40738 updating address of john smith*/
update customer set address='15/1 Flinders St, Melbourne 3000' where
customerID=1;
/*Gurpreet singh-40738 fetching name and adress*/
select firstname, lastname, address from customer;

5. Customers whose name start with j
/*Gurpreet singh-40738 selecting all customers whose name start with j*/
select * from customer where firstname like 'j%';
6. Details of customers who live in Victoria
/*Gurpreet singh-40738 selecting all customers wholive in victoria*/
select * from customer where address like '%3000';
7. Delete customer john p sith
/*Gurpreet singh-40738 delete jon p smith*/
delete from customer where customerID='1';
8. Add another table
 Adding booking table
/*Gurpreet singh-40738 add another table booking*/
create table booking(
bookingID smallint not null primary key,
customerID smallint not null,
bookingDate date not null,
customerFeedback nvarchar(500) not null
);
 Adding relationship
/*Gurpreet singh-40738 adding relationship between customer and
booking*/
alter table booking add constraint booking_fk Foreign key (customerID)
references customer (customerID) on delete NO ACTION;

 Inserting 6 records in the booking table
/*Gurpreet singh-40738 insert records to booking table*/
insert into booking values (1,2,'2018-05-05','Good experience');
/*Gurpreet singh-40738 insert records to booking table*/
insert into booking values (2,3,'2018-05-06','Best company');
/*Gurpreet singh-40738 insert records to booking table*/
insert into booking values (3,3,'2018-05-07','5 star service');
/*Gurpreet singh-40738 insert records to booking table*/
insert into booking values (4,4,'2018-05-08','The best service');
/*Gurpreet singh-40738 insert records to booking table*/
insert into booking values (5,4,'2018-05-09','Impressive');
/*Gurpreet singh-40738 insert records to booking table*/
insert into booking values (6,4,'2018-05-10','Needs improvement');

9. Delete a customer who has a booking
/*Gurpreet singh-40738 delete a customer who has a booking*/
delete from customer where customerID=3;
10. Display all bookings grouped by customer
/*Gurpreet singh-40738 select all bookings grouped by customer*/
select bookingID,customerID,bookingdate,customerFeedback from booking group by
customerID,bookingID,bookingdate,customerFeedback;
11. Select customer names and booking dates sorted with booking dates
/*Gurpreet singh-40738 select all customer names and booking dates sorted
with booking date*/
select customer.firstname,customer.lastname,booking.bookingdate from
customer inner join booking on booking.customerID=customer.customerID
order by bookingdate desc;

12. Customers who have not made a booking
/*Gurpreet singh-40738 customers who have not made a booking*/
select * from customer where customerID not in (select customeriD from
booking);
Task 2b
13. Build the remaining tables
/*Gurpreet singh-40738 create table model*/
create table model(
modelID smallint not null primary key,
type nvarchar(50) not null,
price decimal not null
);
/*Gurpreet singh-40738 create table assets*/
create table assets(
itemID smallint not null primary key,
modelID smallint not null
);
alter table assets add constraint assets_fk foreign key (modelID) references
model (modelID) on delete no action;

/*Gurpreet singh-40738 create table booking_assets*/
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)
);
/*Gurpreet singh-40738 create table payment*/
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.Add atleast 3 records
/*Gurpreet singh-40738 add 3 records to table model*/
insert into model values (1,'inflatables',100);
insert into model values (2,'chair',20);
insert into model values (3,'table',50);
/*Gurpreet singh-40738 insert into assets*/
insert into assets values (1,1),(2,2),(3,3);
/*Gurpreet singh-40738 insert into booking assets*/
insert into booking_assets values (1,1),(2,2),(3,3);
/*Gurpreet singh-40738 insert into payments*/
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
/*Gurpreet singh-40738 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';
16. All customers who have rented inflatables or chairs
/*Gurpreet singh-40738 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';
17. Count of booking days for each customer
/*Gurpreet singh-40738 cout of booking days for each customer chairs */
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;
18. Total amount paid for each customer
/*Gurpreet singh-40738 all customers who have rented inflatables or chairs */

select
customer.customerID,customer.firstname,customer.lastname,sum(payment.amount)-
sum(discount) 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;
Task 3
19. Why draw diagram before using database software
The purpose of drawing a diagram before using a database software is to model the database so as
to get a better understanding of the database. By modelling the database its easy to identify all the
entities and their attributes. This approach is known as top-down approach and usually involves
modelling a database using an entity relationship diagram. The process of modelling a database
involves coming identifying all the possible entities from the case study and their possible attributes.
All the entities are then mapped to an entity relationship diagram and depending on the
relationships that have been defined between the entities all the corresponding relationships are
shown with their optionality and cardinality. From the initial entity relationship diagram
normalization can be done to achieve all the relations is a state of 3NF. Normalisation involves
normalizing the tables through three steps;
 First normal form- this level involves eliminating the repeating groups in any of the
relation.
 Second normal form- this level involves eliminating partial dependencies that make a
relation to have more than one candidate key
 Third normal form- this level involves removing all transitive dependencies that make a
relation have more than one attribute determining another attribute.
After normalization which usually results to decomposition of some tables into smaller and more
normalized tables the final relations can be mapped into the final entity relationship diagram.
20. Legal issues and how the database could be secured
By accepting credit card payments the business can store details of each customer who uses a credit
card to make it easy for any subsequent payments that will be made by that customer. This will
make the billing process a more fast process for the business. However, there are legal issues
involved with this as credit card details of a customer are private and should be kept secure to make
sure they are not accessed by malicious users who may use them to exploit customers by taking
funds from their accounts. Thus the business should make sure that if this idea is implemented there

are various options they could use to make sure the details of the customers are kept secure. One of
the options is to store the details in an encrypted format to make sure that if they are accessed by
malicious users they ae not easily decoded. Another option is deploying the database behind a very
strong firewall such that accessing the database from outside by malicious users is prevented.