Database Designs Using Microsoft SQL Server: PartyKid Database System

This article discusses database designs using Microsoft SQL Server with PartyKid Database System. It covers creating tables, performing operations, and securing databases. It also explains the purposes of E-R diagrams and techniques to secure databases. The subject is Database Management and there is no specific course code, course name, college, or university mentioned.

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.

DATABASE DESIGNS USING MICROSOFT SQL SERVER 13
iii. Data and the database copy-right and patent.
iv. Protection of data.
v. Computing fraud activity.
Security techniques
To provide enough protection to the customers and company’s information some techniques and
measures are enforced to keep the customer’s and company’s information intact and
uncompromised.
The below are the techniques used to enforce security.
i. Database accessing control.
In this the systems user use username and passwords to login and transact(Ullman, 2016).
ii. Databases auditing.
This is used to determine if the company data is safe and not leaked to malicious people.
iii. Authentication to databases access.
This is technique used to authenticate the user’s access to the system to guard the data and
information.
iv. Encryption to the databases.
This technique hides the data integral information in order to prevent any attempt to modify it by
the malicious people.
v. Application’s security setups.
The database management tool is set some passwords and usernames to limit the access only to
the authorized group of users.
vi. Automating databases back-ups.
The data security is very essential so the database requires to be backed up frequently to allow
the restoration while the database get corrupted or fails.

DATABASE DESIGNS USING MICROSOFT SQL SERVER 14
References
Abraham, S.(2013). Database System Concepts .Michigan:
McGraw-Hill.

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