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.

DATABASE DESIGN IN MICROSOFT SQL SERVER 13
Since the ER diagram is graphically and diagrammatically represented, the various
entities, their attributes and relations are easily identified and thus reduce any chance of having
redundancy in the implemented database (Raghu , 2015).
ii. It acts as an effective tool of communication.
The designers, users and other professionals make use of the ER diagram to communicate and
show the expected database design and incase of error or change it is easy to change.
iii. It has high integration with relation database models.
The integration with the database models makes the database designers to convert the diagram to
database tables very easily.
iv. For easier data models conversions.
The designers find an easy task in the implementation of the database since they use the ER
diagram as the reference and they implement all the features using the respective database tools.
v. It simplifies the database designing.
The designer will use the notations to represent the entities and the attributes and also indicates
the clear relationship of the database entities.
3.2 Legal issue and the techniques to use to secure database
As the Ms Pop prepares to adopt the credit card payment from the customers ,there are various
legal issues and security techniques that require to be put in place this is to ensure the sensitive
company information and the customers financial details are secured from any unauthorized
access as discussed below.
3.2.1 Legal issues
The legal issues are the issues related to the information technology and if breached can lead to
prosecution and fine to the defaulter below are some of the legal issues to be considered.

DATABASE DESIGN IN MICROSOFT SQL SERVER 14
i. Access rights.
ii. Data integrity.
iii. Data and databases copyrights and patents.
iv. Protections of data
v. Computing fraudulent activities.
3.2.2 Security techniques
In order to ensure that the data is protected there are various techniques that require to be put in
place to ensure that the customers and the companies sensitive information is secured.
Below are some of the techniques that are used.
i. Databases accesses controls.
This is implemented by use of passwords that one require to use to open and perform any
transactions (Ullman, 2016).
ii. Performing database audits.
The database requires to be audited using the appropriate tool to detect if there is some data that
is leaking and is accessed by other unauthorized users.
iii. Authenticating the database access.
The database users must be authenticated by the system to allow the access to the data and
records.
iv. Encrypting the database.
The database is encrypted to hide the data identity to any of the unauthorized user and this
prevents any modifications to be made.
v. Setting up the applications security.
The database tools to be used to operate the database records should have some password only
the authorized users should access it.
vi. Setting up the automated database backups.

DATABASE DESIGN IN MICROSOFT SQL SERVER 15
In order to prevent any data loss the backup is used to easily restore the data that might get lost in
case the database fails.
4 References
Abraham, S.(2013). Database System Concepts .Michigan:
McGraw-Hill.
Bipin, D.(2014). An Introduction to Database Systems.Boston:
Addison-Wessley Publication.
Raghu ,R.(2015). Database Management Systems. New York:

DATABASE DESIGN IN MICROSOFT SQL SERVER 16
McGraw-Hill Higher Education
Ullman,D.(2016). Principles of Database Systems. Berlin:
Springer publishers;