Microsoft SQL Server Solutions For Tasks 2a And 2b

ER Diagram Importance in Database Design

/*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);

/*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;

/*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;

/*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  2^nd  table called bookings */

CREATE TABLE Booking(

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,CustomerID )

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′),

(‘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*/

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′;

/*Task 2b solutions*/

/*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;

/*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;

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:

1. It has excellent visual representations.

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).

1. 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.

• 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.

1. 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.

1. 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.

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.

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.

1. Access rights.
2. Data integrity.

• Data and databases copyrights and patents.

1. Protections of data
2. Computing fraudulent activities.

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.

1. Databases accesses controls.

This is implemented by use of passwords that one require to use to open and perform any transactions (Ullman, 2016).

1. 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.

• Authenticating the database access.

The database users must be authenticated by the system to allow the access to the data and records.

1. 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.

1. 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.

1. Setting up the automated database backups.

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.

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:

McGraw-Hill Higher Education

Ullman,D.(2016). Principles of Database Systems. Berlin:

Springer publishers;