Database Creation and Management for PartyKids

This article provides a step-by-step guide to creating and managing a database for PartyKids, an event management company. It includes examples of creating tables, inserting records, adding relationships, and securing confidential information. The article also explains the importance of database design modelling and normalization. Legal issues related to storing confidential information like credit card details are also discussed.

COVER PAGE

Task 1
Assumptions
A booking can have one or more items booked for that single booking.

Task 2
Task 2a
1. Create database
/*Gaurav- st id 40773 creating database */
Create database partykids;
/* Gaurav- st id 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
/*Gaurav-st id 40773 Inserting record 1*/
use partykids;
insert into customer VALUES(1,'John p','Smith','12/1 Flinders St, Melbourne
3000');
/*Gaurav-st id 40773 inserting records*/
insert into customer VALUES(2,'Angelina','Jolie','12/1 Flinders St, Melbourne
2000');

--Gaurav-st id 40773 inserting records
use partykids;
insert into customer VALUES(3,'Peter','Parker','12/2 Flinders St,
Melbourne 1000');
--Gaurav-st id 40773 inserting records
use partykids;
insert into customer VALUES(4,'Will','Smith','12/2 Flinders St, Melbourne
1000');

/*Gaurav-st id 40773 inserting records*/
use partykids;
insert into customer VALUES(6,'Jon','Snow','13/2 Flinders St, Melbourne
3000');
--Gaurav-st id 40773 inserting records
insert into customer VALUES(7,'Brianne','Tarth','13/2 Flinders St,
Melbourne 3000');

3. Selecting all customers
--Gaurav-st id 40773 select * customers
select * from customer;

4. Change address of john smith and select name and address
--Gaurav-st id 40773 update address
update customer set address='15/1 Flinders St, Melbourne 3000' where
customerID=1;
--Gaurav-st id 40773 select firstname, lastname and address
select firstname, lastname, address from customer;

5. Customers whose name start with j
--Gaurav-st id 40773 customers starting with j
select * from customer where firstname like 'j%';

6. Details of customers who live in Victoria
--Gaurav-st id 40773 customers living in victoria
select * from customer where address like '%3000';

7. Delete customer john p sith
--Gaurav-st id 40773 delete customer
delete from customer where customerID='1';
8. Add another table
 Adding booking table
--Gaurav-st id 40773 add booking table
create table booking(
bookingID smallint not null primary key,
customerID smallint not null,
bookingDate date not null,
customerFeedback nvarchar(500) not null
);

 Adding relationship
--Gaurav-st id 40773 add relationship
alter table booking add constraint booking_fk Foreign key (customerID)
references customer (customerID) on delete NO ACTION;
 Inserting 6 records in the booking table
--Gaurav-st id 40773 insert records
insert into booking values (1,2,'2018-05-05','Good Service');

--Gaurav-st id 40773 insert records
insert into booking values (2,3,'2018-05-06','Better than others');
--Gaurav-st id 40773 insert records
insert into booking values (3,3,'2018-05-07','quality support’);

--Gaurav-st id 40773 insert records
insert into booking values (4,4,'2018-05-08','Excellent');
--Gaurav-st id 40773 insert records
insert into booking values (5,4,'2018-05-09','Good support');

--Gaurav-st id 40773 insert records
insert into booking values (6,4,'2018-05-10','Best price');
9. Delete a customer who has a booking

--Gaurav-st id 40773 delete customer with a booking
delete from customer where customerID=4;
10. Display all bookings grouped by customer
--Gaurav-st id 40773 display booking 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
--Gaurav-st id 40773 select customer names
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
--Gaurav-st id 40773 customer without booking
select * from customer where customerID not in (select customeriD from
booking);

Task 2b
13. create remaining tables
--Gaurav-st id 40773 create model table
create table model(
modelID smallint not null primary key,
type nvarchar(50) not null,
price decimal not null
);

--Gaurav-st id 40773 create assets table
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-40773 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)

);
--Gaurav-st id 40773 create payment table
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
--Gaurav-st id 40773 add 3 records
insert into model values (1,'inflatables',100),
(2,'chair',20),
(3,'table',50);
--Gaurav-st id 40773 add 3 records
insert into assets values (1,1),
(2,2),
(3,3);

--Gaurav-st id 40773 add 3 records
insert into booking_assets values (1,1),
(2,2),
(3,3);
--Gaurav-st id 40773 add 3 records
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
--Gaurav-st id 40773 customers that 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

--Gaurav-st id 40773 customers that 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
--Gaurav-st id 40773
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

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;
Task 3
19. Reasons for drawing a diagram before using a database software
The process of drawing a diagram before implementing the database in a database software is
called database design modelling and it’s a very important step that should be undertaken
before the database is finally implemented in a database management system. There are many
reasons why database modelling using entity relationship diagram is important. The modelling
of a diagram using entity relationship diagrams is also referred to as top down approach. Top
down approach enables the database developer to get a better understanding of the database.
By reviewing the requirements and extracting all the tables, their attributes and the
relationships that exist between the tables the developer is able to make sure that all
requirements have been captured. An entity relationship diagram is then drawn to model the
database. By just looking at the entity relationship diagram, someone who has not read the
requirements of the database can be able to understand what the database is about. From the
entity relationship diagram, the database can be further modelled by doing normalization to
make sure that all tables are in their best normal form. Normalization is normally done in 3
steps; first normal form which involves elimination of repeating groups within a relation, second
normal form which involves elimination of partial dependencies and third normal form which
involves elimination of transitive dependencies. After applying all the three steps of
normalization the final entity relationship diagram can be then be drawn which can be used as
the reference when implementing the design of the database.

20. Legal issues and how the database could be secured
The business intends to accept credit card payments and then storing the customer’s credit card
details in the database. This additional feature to the database of the business will help make
making of payments easier as the customer will not be required to provide their credit card details
every time they do a booking but the details will just be retrieved from the database. This however
has legal issues on the business because credit card detail of the customers is very confidential
information and will require protection from unauthorized access. If the information is accessed and
distributed by unauthorized users this would have legal implications on the business as customers
could sue the business thus leading to expensive law suits which would in turn lead to the fall of the
business. If the company decided to store the credit card details of its customers then its
recommended that the database is installed in a very secure environment that will minimize the
chances of access by unauthorized users.