Database Design in Microsoft SQL Server: Party-Kids Database System

This assignment presents a detailed solution for a database design project implemented in Microsoft SQL Server. The solution begins with an Entity-Relational (ER) diagram illustrating the database structure for a "Party-Kids" system, including entities like CUSTOMERS and BOOKING, and their relationships. The core of the assignment involves database implementation, starting with the creation of a database and tables, followed by SQL queries to insert, update, and delete records. Various SELECT queries are demonstrated to retrieve specific data based on different criteria. Furthermore, the assignment explores the purpose of ER diagrams in database design, emphasizing their visual representation and communication capabilities. It also addresses legal issues related to database security and outlines several techniques, such as access controls, database audits, authentication, encryption, application security, and automated backups, to secure sensitive data and ensure data integrity. The solution references key database concepts and provides practical SQL code examples to illustrate each aspect of the assignment.

Running Head: DATABASE DESIGN IN MICROSOFT SQL SERVER 1
Designing Database in Microsoft SQL Server:
Party-Kids Database System
[Student Name]
[University Name]

DATABASE DESIGN IN MICROSOFT SQL SERVER 2
Table of Contents
1 Task 1 Entity relational diagram..........................................................................................................3
2 TASK 2 Database implementation.......................................................................................................4
2.1 Task 2A: Creating database and tables and performing insert, update and delete operations on
tables 4
2.2 TASK 2B: Retriving various database tables records..................................................................11
3 Task 3: Purpose of ER Diagram and Techniques to secure databases................................................13
3.1 Purpose of creating ER Diagram before building the database..................................................13
3.2 Legal issue and the techniques to use to secure database........................................................14
3.2.1 Legal issues........................................................................................................................14
3.2.2 Security techniques............................................................................................................14
4 References.........................................................................................................................................16

CUSTOMERS
CustomerID <PK>
Names
PostalAddress
Town
Postal_Code
ContactNumber
BOOKING
BookingID <PK>
Book_Date
Item_Name
DaysBooked
Amount
Customer_Feedback
CustomerID <FK>
1: M
M: M
THE DATABSE ENTITY RELETIONAL DIAGRAM
DATABASE DESIGN IN MICROSOFT SQL SERVER 3
1 Task 1 Entity relational diagram
(Abraham, 2013).

DATABASE DESIGN IN MICROSOFT SQL SERVER 4
2 TASK 2 Database implementation
2.1 Task 2A: Creating database and tables and performing insert, update and
delete operations on tables
/*Task 2a Implementation Solutions*/
/*Q1: creating database Party-Kids*/
CREATE DATABASE Party_Kids;
/*Q1: creating table called customers*/
use party_kids;
CREATE TABLE customers (
CustomerID int NOT NULL PRIMARY KEY,
Names varchar(255) NOT NULL,
PostalAddress varchar(255) NOT NULL,
Town varchar(255) NOT NULL,
Postal_Code int NOT NULL,
ContactNumber int NOT NULL);

DATABASE DESIGN IN MICROSOFT SQL SERVER 5
/*Q2: inserting some records in customers table */
insert into customers(CustomerID ,Names,PostalAddress,Town, Postal_Code, ContactNumber)
values('1','John P Smith','12/1 Flinders St','Melbourne','3000','00786566'),
('2','Peter S Sam','12/1 Flinders St','Melbourne','2000','115556666'),
('3','Erick D Harman','9/1 Flinders St','Melbourne','3000','225454656'),
('4','Julius N Stan','18/1 Flinders St','Melbourne','4000','789865623'),
('5','Fidel Z Titus','16/1 Flinders St','Melbourne','1000','078545566'),
('6','Juriah A Obama','17/1 Flinders St','Melbourne','3000','072145565'),
('7','Anastashia M Carol','15/1 Flinders St','Melbourne','1000','589896565');
/*Q3: selecting all the customers’s details*/
select * from customers;

DATABASE DESIGN IN MICROSOFT SQL SERVER 6
/*Q4: changing John Smith postaladdress from 12/1 Flinders Street to 15/1 Flinders Street*/
update customers set PostalAddress='15/1 Flinders Street' where Names='John P Smith'; (Bipin,
2014).
/*Q4: displaying the names and postal addresses of all the customers*/
select Names,PostalAddress from customers;

DATABASE DESIGN IN MICROSOFT SQL SERVER 7
/*Q5: displaying the customers details whose firstname start with letter J*/
select * from customers where Names like 'J%' or Names like 'j%' order by Names;
/*Q6: displaying the customers who leaves in victoria state*/
select * from customers where Postal_Code='3000';
/*Q7: deleting John P Smith from customers database table */
delete from customers where Names='John P Smith';
/*Q8: adding the 2nd table called bookings */
CREATE TABLE Booking(

DATABASE DESIGN IN MICROSOFT SQL SERVER 8
BookingID int NOT NULL PRIMARY KEY,
Book_Date varchar(20) NOT NULL,
Item_Name varchar(255) NOT NULL,
DaysBooked int NOT NULL,
Amount int NOT NULL,
Customer_Feedback varchar(255) NOT NULL,
CustomerID int FOREIGN KEY REFERENCES customers(CustomerID )
);
/*Q8: inserting 6 records in the booking table */
insert into
Booking(BookingID,Book_Date,Item_Name,DaysBooked,Amount,Customer_Feedback,Custom
erID )
values ('1','1-3-2018','tables','3','150','very nice','7'),
('2','2-3-2018','chairs','1','100','very lazarious','2'),
('3','3-3-2018',''inflatables'','1','400','best infators ever','2'),
('4','4-3-2018', 'inflatables','3','1200','very classic','4'),
('5','6-3-2018','inflatables','1','400','so enjoyable','4'),

DATABASE DESIGN IN MICROSOFT SQL SERVER 9
('6','9-3-2018','chairs','1','100','best quality chairs','4');
/*Q9:making a try of deleting a customer with a booking */
delete from customers where CustomerID ='4';
/*Q10:Displaying all bookings group by customers.*/
Select
BookingID,Book_Date,Item_Name,DaysBooked,Amount,Customer_Feedback,CustomerID
as IDNO from Booking group by CustomerID ;
/*Q11:Displaying the customers names and the booking dates of all the customers who have
made some bookings*/

DATABASE DESIGN IN MICROSOFT SQL SERVER 10
select c.Names,b.Book_Date from customers c,Booking b where c.CustomerID =b.CustomerID
order by b.Book_Date;
/*Q12:Displaying the names of customers who have not made any booking */
select Namesfrom customer where CustomerID ='1' or CustomerID ='3' or CustomerID ='5' or
CustomerID ='6';
2.2 TASK 2B: Retriving various database tables records
/*Task 2b solutions*/

DATABASE DESIGN IN MICROSOFT SQL SERVER 11
/*Q15:Display names of all customers who have rented inflatables*/
select c.Names from customers c,Booking b where c.CustomerID =b.CustomerID AND
b.Item_Name='inflatables';
/*Q16:To Display names of all customers who have rented chairs or the inflatables*/
select DISTINCT c.Names from customer c,Booking b where c.CustomerID =b.CustomerID
AND (b.Item_Name='chairs' or b.Item_Name='inflatables') ;
/*Q17:Display the count of the number of bookings days for each customer*/
select c.Names as Customer,count(b.DaysBooked) as TOTAL_BOOKING_DAYS from
customers c,Booking b where c.CustomerID =b.CustomerID GROUP by c.Names;

DATABASE DESIGN IN MICROSOFT SQL SERVER 12
/*Q18:Displaying total amount of money that is received from each customer.*/
select c.Names as Customer,sum(b.Amount) as TOTAL_AMOUNT from customers c,Booking b
where c.CustomerID =b.CustomerID GROUP by c.Names;
3 Task 3: Purpose of ER Diagram and Techniques to secure databases
3.1 Purpose of creating ER Diagram before building the database
In the database design there is need to develop the ER diagram since it acts like the foot print and
image of the database being designed, there are various purposes of the ER diagram as discussed
below:
i. It has excellent visual representations.