Microsoft SQL Server Database Designs

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.

MICROSOFT SQL SERVER DATABASE DESIGNS 13
The ER Diagram provides flexibility to the database developers, this by enabling them to
identify the relationships available in the database and in case a new relation between entities is
required it is then easily derived from the current ones immediately (Bipin, 2014).
Therefore the ER diagram is used as a database plan which enables creating of databases designs
that are required in the organization.
Legal issue and the techniques to use to secure database
In this case as Ms Pop is planning to introduce the payment through the credit cards and
eventually keep the customers payments details there are various legal issues and the security
techniques that are required to be put into consideration which includes the following:
Legal issues
i. Database base system copyrights.
After the data implementation Ms pop should ensure that the it is given copy rights licenses, this
is to ensure that no one can use the same dataset without her rights to access.
ii. Computer frauds.
There are various instances of fraud where the database can be subjected to which required to be
protected from which can be stealing of the customer’s identity.
iii. Database System hacking.
The database however is subjected to other issues where there are some malicious attackers who
try to access the database for malicious gains (Abraham, 2013).
iv. The database data protection.
The database require to be protected this is by ensuring there are security password and all
unauthorized users are barred from accessing it to ensure there is database integrity.
Database security techniques
There are various ways that can be used by the company to protect their data and customer data
as well which includes the following:

MICROSOFT SQL SERVER DATABASE DESIGNS 14
i. Regular assessments of the database vulnerability and classification of any sensitive
data.
ii. To monitor the database activities and uses in real time to identify any leaking data
and put immediate protective measures.
iii. Management of the users rights o access and withdraw any excess privilege to users
who are inactive.
iv. Ensuring that all web requests that are malicious are fully blocked.
v. Ensuring that the database are archived and encrypted properly.
vi. Ensure there is regular and automatic audit of the database system.
vii. In case the employees will be using the database they require to be well trained on
how to mitigate the database risks.

MICROSOFT SQL SERVER DATABASE DESIGNS 15
References
Abraham ,S.(2013). Database System Concepts .Michigan:
McGraw-Hill.
Bipin, D.(2014). An Introduction to Database Systems. Boston:
Addison-Wesley Publication.
Raghu ,R.(2015). Database Management Systems. New York:
McGraw-Hill Higher Education
Ullman,D.(2016). Principles of Database Systems. Berlin:
Springer publishers;