Microsoft SQL Server Database Designs: Party Kids Database System

This assignment details the design and implementation of a Microsoft SQL Server database for a Party Kids system. It begins with an Entity-Relationship (ER) diagram illustrating the relationships between customers and bookings. The project then progresses to the implementation phase, including creating the database and tables for customers and bookings, along with inserting, updating, and deleting records using SQL queries. The solution demonstrates various SQL commands to display and manipulate database records, such as selecting customer details, filtering by name or postal code, and displaying booking information. Furthermore, the assignment explores the purpose of ER diagrams in database design and discusses legal issues and security techniques, such as data encryption, access control, and regular vulnerability assessments, to protect sensitive customer data, especially with the introduction of credit card payments. The project uses SQL to display and manipulate the database records and also includes the references used.

Running Head: MICROSOFT SQL SERVER DATABASE DESIGNS 1
Microsoft SQL Server database Designs:
Party Kids Database System Design
[Student Name]
[University Name]

CUSTOMER
Customer_ID (PK)
FullName
Addresses
City
PostalCode
phoneNumber
BOOKINGS
Booking_ID (PK)
BookingDate
ItemName
BookingDays
TotalAmount
CustomerFeedback
Customer_ID (FK)
M: 1
M: M
PARTYKIDS DATABASE ER DIAGRAM
MICROSOFT SQL SERVER DATABASE DESIGNS 2
TASK 1 PARTYKIDS DATABASE ENTITY RELETIONAL DIAGRAM.
(Raghu , 2015).

MICROSOFT SQL SERVER DATABASE DESIGNS 3
Table of Contents
TASK 1 PARTYKIDS DATABASE ENTITY RELETIONAL DIAGRAM..................................................................2
TASK 2 IMPLEMENTING PARTYKIDS DATABASE...........................................................................................4
TASK 2A: creating database and tables, inserting, updating and deleting tables records.......................4
TASK 2B :Displaying the database records.............................................................................................11
Task 3: Purpose of ER Diagram and Techniques to secure databases.......................................................12
Purpose of creating ER Diagram before building the database.............................................................12
Legal issue and the techniques to use to secure database....................................................................13
Legal issues.......................................................................................................................................13
Database security techniques............................................................................................................13
References.................................................................................................................................................15

MICROSOFT SQL SERVER DATABASE DESIGNS 4
TASK 2 IMPLEMENTING PARTYKIDS DATABASE
TASK 2A: creating database and tables, inserting, updating and deleting tables
records
/*Task 2a*/
/*Q1: create database PartyKids*/
CREATE DATABASE PartyKids;
/*Q1: create table customers*/
use partykids;
CREATE TABLE customers(
Customer_ID int NOT NULL PRIMARY KEY,
FullName varchar(255) NOT NULL,
Addresses varchar(255) NOT NULL,
City varchar(255) NOT NULL,
PostalCode int NOT NULL,
phoneNumber int NOT NULL); (Ullman, 2016).
/*Q2: insert values in table customers*/
insert into customers(Customer_ID,FullName,Addresses,City, PostalCode, phoneNumber)
values('1','John P Smith','12/1 Flinders St','Melbourne','3000','0718895897'),

MICROSOFT SQL SERVER DATABASE DESIGNS 5
('2','Paul S Sammy','12/1 Flinders St','Melbourne','3000','0729895897'),
('3','Ezra D Jones','13/1 Flinders St','Melbourne','5000','0779895897'),
('4','Patel N Simon','14/1 Flinders St','Melbourne','3000','0799895897'),
('5','Linus s Ismael','15/1 Flinders St','Melbourne','3000','0789895897'),
('6','Juliet w Osman','15/1 Flinders St','Melbourne','4000','0789800497'),
('7','Onesmas M Cyrus','15/1 Flinders St','Melbourne','3000','05899800497');
/*Q3: select all customers details*/
select * from customers;

MICROSOFT SQL SERVER DATABASE DESIGNS 6
/*Q4: change John Smith address to 15/1 Flinders Street*/
update customers set Addresses='15/1 Flinders Street' where FullName='John P Smith';
/*Q4: display name and address of all customers*/
select FullName,Addresses from customers;
/*Q5: display customers details whose firtname starts with letter j*/
select * from customers where FullName like 'J%' or FullName like 'j%' order by FullName;
/*Q6: display customers who leaves in victoria*/
select * from customers where PostalCode='3000';

MICROSOFT SQL SERVER DATABASE DESIGNS 7
/*Q7: delete John P Smith from database */
delete from customers where FullName='John P Smith';
/*Q8: add 2nd table booking */
CREATE TABLE Booking(
Booking_ID int NOT NULL PRIMARY KEY,
BookingDate varchar(20) NOT NULL,
ItemName varchar(255) NOT NULL,
BookingDays int NOT NULL,
TotalAmount int NOT NULL,
CustomerFeedback varchar(255) NOT NULL,
Customer_ID int FOREIGN KEY REFERENCES customer(Customer_ID)
);

MICROSOFT SQL SERVER DATABASE DESIGNS 8
/*Q8: inserting 6 records in the booking table */
insert into
Booking(Booking_ID,BookingDate,ItemName,BookingDays,TotalAmount,CustomerFeedback,
Customer_ID)
values ('1','10-4-2018','inflatables','2','200','satisfied','2'),
('2','11-4-2018','chairs','1','150','very nice chairs','3'),
('3','12-4-2018','tables','1','170','best tables','3'),
('4','14-4-2018','chairs','3','300','very comfortable','5'),
('5','15-4-2018','inflatables','1','200','very entertaining','5'),
('6','17-4-2018','tables','1','170','excellent service tables','5');

MICROSOFT SQL SERVER DATABASE DESIGNS 9
/*Q9: trying to delete customer with bookings */
delete from customers where Customer_ID='2';
/*Q10:Display all bookings grouped by customer.*/
Select Booking_ID,BookingDate,ItemName,BookingDays,TotalAmount,CustomerFeedback,
Customer_ID as ID from Booking group by Customer_ID;

MICROSOFT SQL SERVER DATABASE DESIGNS 10
/*Q11:Display the customer names and booking dates of all customers who have made a
booking*/
select c.FullName,b.BookingDate from customers c,Booking b
where c.Customer_ID=b.Customer_ID order by b.BookingDate;
/*Q12:Display the names of customers who have not yet made a booking 1,4,6,7*/
select FullName from customer where Customer_ID='4' or Customer_ID='6' or
Customer_ID='7';

MICROSOFT SQL SERVER DATABASE DESIGNS 11
TASK 2B :Displaying the database records
/*Task 2b*/
/*Q15:Displaying the names of all customers who have rented inflatables*/
select c.FullName from customers c,Booking b where c.Customer_ID=b.Customer_ID AND
b.ItemName='inflatables';
/*Q16:Displaying the names of all customers who have rented chairs or inflatables*/
select DISTINCT c.FullName from customer c,Booking b where
c.Customer_ID=b.Customer_ID AND (b.ItemName='chairs' or b.ItemName='inflatables') ;
/*Q17:Displaying a count of the number of booking days of each customer*/
select c.FullName as Customer,count(b.BookingDays) as TOTAL_BOOKING_DAYS from
customers c,Booking b where c.Customer_ID=b.Customer_ID GROUP by c.FullName;

MICROSOFT SQL SERVER DATABASE DESIGNS 12
/*Q18:Display the total amount of money received from each customer.*/
select c.FullName as Customer,sum(b.TotalAmount) as TOTAL_AMOUNT from customers
c,Booking b where c.Customer_ID=b.Customer_ID GROUP by c.FullName;
Task 3: Purpose of ER Diagram and Techniques to secure databases
Purpose of creating ER Diagram before building the database
In the database design process the database entity relation diagram is drawn since it is of much
use to the developers, among the purposes of the database relational diagram includes the
following:
i. To provide visual representation of the database design
The ER diagram enables the designers of the database to implement database effectively since
they are able to clearly identify the data flow and the entities relationships.
ii. Enable the effectiveness in communication between the database designers.
The ER Diagram is designed using symbols of the attributes and the entities and these databse
designers are able to interpolate how the final database will look like.
iii. It is simply understood by the developers.
The ER diagram also is simple to analyze and thus after is analyzed the designers are able to
identify any required change or update.
iv. It enables the database to be flexible.