ITDA1001 Database Project: Party Hire System Design & Implementation

This project provides a database solution for Party Kids, a company renting out party supplies. 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, deleting records, and querying the database to retrieve specific information such as customers who rented inflatables, booking counts, and total payments. The project also discusses the importance of database design modeling using entity-relationship diagrams and addresses legal issues related to storing customer credit card details, emphasizing the need for robust security measures to protect sensitive information. The final section highlights the importance of drawing a diagram before using a database software, emphasizing the benefits of database modeling using entity relationship diagrams for better understanding, capturing requirements, and normalization.

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