Database Design & Implementation of PartyKid System using SQL Server

This project outlines the design and implementation of the PartyKid database system using Microsoft SQL Server. It includes an entity-relationship (ER) diagram illustrating the relationships between customers and bookings. The project details the creation of the database and tables, along with SQL queries for inserting, updating, deleting, and retrieving data. It covers legal issues related to data security, such as access rights and data integrity, and discusses security techniques like database access control, auditing, authentication, encryption, and automated backups. The project provides a comprehensive approach to database design, implementation, and security considerations for the PartyKid system.

Running Head: DATABASE DESIGNS USING MICROSOFT SQL SERVER 1
Database Designs Using Microsoft SQL Server:
PartyKid Database System
[Student’s Name]
[University’s Name]

DATABASE DESIGNS USING MICROSOFT SQL SERVER 2
Table of Contents
Task.1: Entity relational diagram.................................................................................................................3
TASK.2: Database implementations............................................................................................................4
Task 2.A: Create database and tables and perform inserting, updating and deleting operations on
tables.......................................................................................................................................................4
TASK 2.B: Retriving various database tables records.............................................................................10
Task 3: Purposes of E-R-Diagram and Techniques to secure databases....................................................11
The purpose to create an ER Diagram before the database is built.......................................................11
Legal issues and the techniques to use in order to secure databases...................................................12
Legal issues........................................................................................................................................12
Security techniques...........................................................................................................................13
References.................................................................................................................................................15

CUSTOMERS
CustomerIDs <PK>
Full_Names
Postal_Addresses
Town_Name
Postal_Codes
Contacts_Number
BOOKING
Booking_IDs <PK>
Book_Date
Items_Names
Days_Booked
Total_Amount
Customers_Feedbacks
CustomerIDs <FK>
1: M
M: M
THE DATABSES ENTITY RELETION DIAGRAMS
DATABASE DESIGNS USING MICROSOFT SQL SERVER 3
Task.1: Entity relational diagram
(Abraham, 2013).

DATABASE DESIGNS USING MICROSOFT SQL SERVER 4
TASK.2: Database implementations
Task 2.A: Create database and tables and perform inserting, updating and
deleting operations on tables.
/*Tasks 2.a Implementing Solution*/
/*Q.1: create database PartyKid*/
CREATE DATABASE Partykid;
/*Q.1: create table called customer*/
use partykid;
CREATE TABLE customer (
CustomerIDs int NOT NULL PRIMARY KEY,
Full_Names varchar(255) NOT NULL,
Postal_Addresses varchar(255) NOT NULL,
Town_Name varchar(255) NOT NULL,
Postal_Codes int NOT NULL,
Contacts_Number int NOT NULL);

DATABASE DESIGNS USING MICROSOFT SQL SERVER 5
/*Q.2: insert some record in customer table */
insert into customer(CustomerIDs ,Full_Names ,Postal_Addresses ,Town_Name , Postal_Codes,
Contacts_Number )
values('1','John P Smith','12/1 Flinders St','Melbourne','3000','00796566'),
('2','Peterson S Sammy','12/1 Flinders St','Melbourne','2000','115446666'),
('3','Erickson D Harm','10/1 Flinders St','Melbourne','3000','213454656'),
('4','Juliee N Stanly','20/1 Flinders St','Melbourne','4000','789275623'),
('5','Fideliza Z Tito','17/1 Flinders St','Melbourne','1000','078541966'),
('6','Juriahna A Onesmas','18/1 Flinders St','Melbourne','3000','072111565'),
('7','Anabel M Caroline','16/1 Flinders St','Melbourne','1000','589800565');
/*Q.3: select all the customers detail*/
select * from customer;

DATABASE DESIGNS USING MICROSOFT SQL SERVER 6
/*Q.4: change John Smith Postal_Addresses from 12/1 Flinders Street to 15/1 Flinders Street*/
update customer set Postal_Addresses ='15/1 Flinders Street' where Full_Names ='John P Smith';
(Bipin, 2014).
/*Q.4: display the Full_Names and postal addresses of all the customers*/
select Full_Names ,Postal_Addresses from customer;
/*Q.5: Display the customer’s detail whose first name starts with a letter “J”*/
select * from customer where Full_Names like 'J%' or Full_Names like 'j%' order by
Full_Names ;

DATABASE DESIGNS USING MICROSOFT SQL SERVER 7
/*Q.6: Display the customer who leave in victoria’s states*/
select * from customer where Postal_Codes='3000';
/*Q.7: Delete John P Smith from customer’s database tables */
delete from customer where Full_Names ='John P Smith';
/*Q.8: Add the second table called Bookings */
CREATE TABLE Bookings(
Bookings_IDs int NOT NULL PRIMARY KEY,
Book_Dates varchar(20) NOT NULL,
Items_Names varchar(255) NOT NULL,
Days_Booked int NOT NULL,
Total_Amount int NOT NULL,
Customer_Feedbacks varchar(255) NOT NULL,
CustomerIDs int FOREIGN KEY REFERENCES customer(CustomerIDs )
);

DATABASE DESIGNS USING MICROSOFT SQL SERVER 8
/*Q.8: Insert six(6) records in the Bookings tables */
insert into Bookings(Bookings_IDs,Book_Dates ,Items_Names ,Days_Booked
,Total_Amount,Customer_Feedbacks,CustomerIDs )
values ('1','1-4-2018','tables','2','100','very-nice','7'),
('2','2-4-2018','chairs','2','200','very-lazarious','2'),
('3','3-4-2018','inflatables','3','1200','best-infators-ever','2'),
('4','4-4-2018', 'inflatables','2','800','very-classic','4'),
('5','6-4-2018','inflatables','2','800','so-enjoyable','4'),
('6','9-4-2018','chairs','4','400','best-quality-chairs','4');

DATABASE DESIGNS USING MICROSOFT SQL SERVER 9
/*Q.9:make a try to delete a customer with a Booking */
delete from customer where CustomerIDs ='2';
/*Q.10:Display all Booking grouped by customer.*/
Select Bookings_IDs,Book_Dates ,Items_Names ,Days_Booked
,Total_Amount,Customer_Feedbacks,CustomerIDs
as ID_NO from Bookings group by CustomerIDs ;
/*Q.11:Display the customer’s Full_Names and the Booking date of all the customer who have
already made some Booking*/
select cu.Full_Names ,bo.Book_Dates from customer cu,Bookings bo where cu.CustomerIDs
=bo.CustomerIDs order by bo.Book_Dates ;

DATABASE DESIGNS USING MICROSOFT SQL SERVER 10
/*Q.12:Display the Full_Names of a customer who had not made any Booking */
select Full_Names from customer where CustomerIDs ='1' or CustomerIDs ='3' or CustomerIDs
='5' or CustomerIDs ='6';
TASK 2.B: Retriving various database tables records
/*Task 2.b solution*/
/*Q.15:Displaying Full_Names of all customer who had rent inflatable*/
select cu.Full_Names from customer cu,Bookings bo where cu.CustomerIDs =bo.CustomerIDs
AND bo.Items_Names ='inflatables';
/*Q.16: Displaying Full_Names of all customer who had rent chairs or inflatables*/
select DISTINCT cu.Full_Names from customer cu,Bookings bo where cu.CustomerIDs
=bo.CustomerIDs AND (bo.Items_Names ='chairs' or bo.Items_Names ='inflatables') ;
/*Q.17:Displaying the counts of numbers of Booking days for each and every customer*/

DATABASE DESIGNS USING MICROSOFT SQL SERVER 11
select cu.Full_Names as Customer,count(bo.Days_Booked ) as TOTAL_BOOKINGS_DAYS
from customer cu,Bookings bo where cu.CustomerIDs =bo.CustomerIDs GROUP by
cu.Full_Names ;
/*Q.18:Display the total Amounts of moneys received from each and every customer.*/
select cu.Full_Names as Customer,sum(bo.Total_Amount) as TOTAL_TOTAL_AMOUNT
from customer cu,Bookings bo where cu.CustomerIDs =bo.CustomerIDs GROUP by
cu.Full_Names ;
Task 3: Purposes of E-R-Diagram and Techniques to secure databases
The purpose to create an ER Diagram before the database is built
The database design process requires the use of the ER diagrams as it is the database
implementation foot prints that show the clear image of the resultant database that is required to
be implemented, below are some of the purposes of E.R diagram in database design process.
i. Due to its’ excellent visual representations.
The database entity, attributes and relationships are identified with ease and thus eliminating any
redundant attributes while implementing the databases and this is due to the ER diagram
graphical and diagrammatic representation (Raghu , 2015).

DATABASE DESIGNS USING MICROSOFT SQL SERVER 12
ii. Since it is an effective communication tool.
The ER diagram helps the databases user, designer, and the professional team to communicate
and illustrate the designs of the expected databases and thus using the ER diagram errors are
easily identified and then modified.
iii. Due to its’ high integrations with relational databases model.
The ER diagram integrates the model of the database and thus the designer is enabled to do the
ER diagram to database tables with ease.
iv. Due to its’ ease in converting data model.
The ERD make it easy for the database designer to easily implement the database’s tables and
relationships using the selected database management tools.
v. Due to its ability to simplify the databases designing.
The database designers make use of the entity relation diagram notations while representing an
entity and its attributes and also show the relationship between the entities of the databases.
Legal issues and the techniques to use in order to secure databases
Ms Donta Pop is preparing to use the credit cards payments from her customers in near future,
however in order to maintain the security of the customer’s financial details some of aspects like
the legal issues and the security techniques has to be enforced and thus no unauthorized access
will be allowed to the company’s and customer’s financial and sensitive data records.
Legal issues
There information technology legal issues are set to protect the rights of the owners of the
resources and in case someone breaches the rules are prosecuted and charged with legal issues
crime.
Below are some of the legal issues in information technology.
i. Accessing right.
ii. Data integrities.