ER Diagram, SQL Queries and Database Security for Desklib

This article discusses ER Diagram, SQL Queries and Database Security for Desklib. It includes an ER Diagram, SQL queries for creating, inserting, updating, deleting and selecting data from the database. It also covers the purpose of ER Diagram and techniques for securing the database.

Task 1:
ER Diagram:
Task 2A:
1) Query:
/* Anmoldeep Kaur. Q1. Creating a database */
CREATE DATABASE PartyKids;
/* Anmoldeep Kaur. Q1. Creating a Customer table */
CREATE TABLE Customer (
CustomerID INT NOT NULL,
CustomerName VARCHAR (30) NOT NULL,
CustomerAddress VARCHAR (100) NOT NULL,
CustomerPhoneNumber VARCHAR (13) NOT NULL,
PRIMARY KEY (CustomerID));

Output:
2) Query:
/* Anmoldeep Kaur. Q2. Inserting Customer Data */
INSERT INTO Customer VALUES
(1, 'John P Smith', '12/1 Flinders St, Melbourne 3000', '757-414-1445'),
(2, 'Phillip D. Hawkins', '1921 Lyndon Street Allentown, PA 18101', '610-782-8197'),
(3, 'Ferdinand E. Fry', '2172 Commerce Boulevard Norfolk, NE 68701','402-518-8387'),
(4, 'Elizabeth C. Morton', '2245 Glen Street Owensboro, KY 42301', '270-315-8101'),
(5, 'Phyllis W. Thomas', '4629 Myra Street East Greenwich, RI 02818', '401-541-6737'),
(6, 'Cody O. Mascarenas', '4624 Adams Drive Bryan, TX 77803', '979-436-1193'),
(7, 'Freeman A. Edwards', '3290 Aviation Way Los Angeles, CA 90071', '213-988-4308');
Output:

3) Query:
/* Anmoldeep Kaur. Q3. Selecting Customer Data */
SELECT * FROM Customer;
Output:
4) Query:
/* Anmoldeep Kaur. Q4. Updating Customer Data */
UPDATE Customer SET CustomerAddress='15/1 Flinders Streett, Melbourne 3000'
WHERE CustomerName='John P Smith';
/* Anmoldeep Kaur. Q4. Selecting Customer Data */
SELECT CustomerName, CustomerAddress FROM Customer;

Output:
5) Query:
/* Anmoldeep Kaur. Q5. Selecting Customer Starts With J */
SELECT * FROM Customer WHERE CustomerName LIKE 'J%';
Output:

6) Query:
/* Anmoldeep Kaur. Q6. Selecting Customer in Victoria */
SELECT * FROM Customer WHERE CustomerAddress LIKE '%3000';
Output:
7) Query:
/* Anmoldeep Kaur. Q7. Deleting a Customer Data */
DELETE FROM Customer WHERE CustomerName LIKE 'John P Smith';
Output:

8) Query:
/* Anmoldeep Kaur. Q8. Creating Booking table */
CREATE TABLE Booking(
BookingID INT NOT NULL,
BookingDate DATETIME NOT NULL,
CustomerID INT NOT NULL,
BookingDays INT NOT NULL,
EventDate DATETIME NOT NULL,
TotalAmount MONEY NOT NULL,
Discount MONEY NOT NULL,
PaymentStatus VARCHAR(15) NOT NULL,
PaymentDate DATETIME,
PRIMARY KEY (BookingID),
CONSTRAINT FK_BookingCustomerID FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID));
/* Anmoldeep Kaur. Q8. Inserting Booking Data */
INSERT INTO Booking VALUES
(1, '9-13-2016', 3, 2, '9-23-2016', 1200, 0, 'Done', '9-25-2016'),
(2, '12-13-2016', 2, 4, '12-23-2016', 2500, 50, 'Done', '12-27-2016'),
(3, '1-12-2017', 4, 2, '1-17-2017', 500, 0, 'Done', '1-20-2017'),
(4, '3-23-2017', 4, 3, '3-28-2017', 700, 0, 'Done', '3-30-2017');
INSERT INTO Booking (BookingID, BookingDate, CustomerID, BookingDays,
EventDate, TotalAmount, Discount, PaymentStatus) VALUES
(5, '8-19-2017', 2, 5, '8-25-2017', 1000, 0, 'Progressing'),
(6, '3-19-2018', 3, 6, '3-25-2018', 3000, 500, 'Progressing');
Output:

9) Query:
/* Anmoldeep Kaur. Q9. Deleting a Booked Customer Data */
DELETE FROM Customer WHERE CustomerID = 4;
Output:
10) Query:
/* Anmoldeep Kaur. Q10. Selecting Booking Data */
SELECT * FROM Booking ORDER BY CustomerID;
Output:

11) Query:
/* Anmoldeep Kaur. Q11. Selecting Customer Booking By Booking Date */
SELECT CustomerName, BookingDate FROM Booking INNER JOIN Customer ON
Customer.CustomerID=Booking.CustomerID ORDER BY BookingDate;
Output:
12) Query:
/* Anmoldeep Kaur. Q12. Selecting Customer With No Booking */
SELECT CustomerName FROM Customer WHERE CustomerID NOT IN (SELECT
CustomerID FROM Booking);
Output:

Task 2B
13) Query:
/* Anmoldeep Kaur. Q13. Creating a tables */
CREATE TABLE Model(
ModelID VARCHAR(30),
ModelName VARCHAR(30),
ModelPrice MONEY,
PRIMARY KEY(ModelID));
CREATE TABLE AssetItem(
AssetID VARCHAR(30),
ModelID VARCHAR(30),
PRIMARY KEY(AssetID),
CONSTRAINT FK_AssetItemModelID FOREIGN KEY (ModelID) REFERENCES
Model(ModelID));
CREATE TABLE Feedback(
FeedbackID INT,
CustomerID INT,
AssetID VARCHAR(30),
Feedback VARCHAR(250),
PRIMARY KEY(FeedbackID),
CONSTRAINT FK_FeedbackCustomerID FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID),
CONSTRAINT FK_FeedbackAssetID FOREIGN KEY (AssetID) REFERENCES
AssetItem(AssetID));
CREATE TABLE AssetHired(
BookingID INT,
AssetID VARCHAR(30),
PRIMARY KEY(BookingID,AssetID),
CONSTRAINT FK_AssetHiredBookingID FOREIGN KEY (BookingID)
REFERENCES Booking(BookingID),
CONSTRAINT FK_AssetHiredAssetID FOREIGN KEY (AssetID) REFERENCES
AssetItem(AssetID));

Output:
14) Query:
/* Anmoldeep Kaur. Q14. Inserting Data */
INSERT INTO Model VALUES
('ModelA','Inflatable', 20),
('ModelB','Chair', 10),
('ModelC','Table', 5);
INSERT INTO AssetItem VALUES
('ModelA#1','ModelA'),
('ModelB#1','ModelB'),
('ModelA#2','ModelA'),
('ModelB#2','ModelB'),
('ModelC#1','ModelC');
INSERT INTO Feedback VALUES
(1, 3,'ModelA#1','Good Asset'),
(2, 2,'ModelB#2','Nice Chair'),
(3, 4,'ModelA#1','Good Quality Inflatable'),
(4, 5,'ModelC#1','Gud Wooden tabke'),
(5, 3,'ModelB#2','Good Chair');
INSERT INTO AssetHired VALUES
(1,'ModelA#1'),
(1,'ModelB#2'),

(2,'ModelB#1'),
(3,'ModelA#1'),
(4,'ModelC#1'),
(5,'ModelA#2'),
(6,'ModelB#1');
Output:
15) Query:
/* Anmoldeep Kaur. Q15. Selecting Customer who hired Inflatable */
SELECT CustomerName FROM CUSTOMER WHERE CustomerID IN (
SELECT CustomerID FROM (((Booking INNER JOIN AssetHired ON
Booking.BookingID =AssetHired.BookingID)
INNER JOIN AssetItem ON AssetItem.AssetID=AssetHired.AssetID) INNER JOIN
Model ON Model.ModelID= AssetItem.ModelID)
WHERE ModelName LIKE 'Inflatable');

Output:
16) Query:
/* Anmoldeep Kaur. Q16. Selecting Customer who hired chairs or inflatables */
SELECT CustomerName FROM CUSTOMER WHERE CustomerID IN (
SELECT CustomerID FROM (((Booking INNER JOIN AssetHired ON
Booking.BookingID =AssetHired.BookingID)
INNER JOIN AssetItem ON AssetItem.AssetID=AssetHired.AssetID) INNER JOIN
Model ON Model.ModelID= AssetItem.ModelID)
WHERE ModelName LIKE 'Inflatable' OR ModelName LIKE 'Chair');
Output:

17) Query:
/* Anmoldeep Kaur. Q17. Selecting number of booking days of each customer */
SELECT CustomerID, SUM(BookingDays) As BookingDaysCount FROM Booking
GROUP BY CustomerID;
Output:
18) Query:
/* Anmoldeep Kaur. Q18. Selecting total amount of money received from each
customer*/
SELECT CustomerID, SUM((TotalAmount-Discount)) AS AmountReceived FROM
Booking GROUP BY CustomerID;
Output:

Task 3:
19. Purpose of ER Diagram:
Entity Relationship Diagram (ERD) is a data modeling diagram which pictorially represents the
entities, attributes of those entities and relationship among the entities of the database.
Some of the purpose of the Entity Relationship Diagram (ERD) is given below:
1) Visual representation of database helps to understand its structures and formulating
strategies.
2) It helps in understanding the database requirement before starting to develop the database
3) It helps in analyzing the data and normalizing the database before developing them.
4) It helps in providing multiple views of storing the data in database.
5) It helps in business process re-engineering
6) It helps to develop the high-quality database by developing the normalized database
design.
7) It resolves the ambiguity and avoids unnecessary processing of data
8) It helps the students to understand the database concepts more effectively.
9) A standard usage of symbols in the Entity Relationship Diagram (ERD) helps in the
communication about the data in database without written explanation
10) It helps in troubleshooting the problems involved in the database logic and deployment
11) By developing the ER diagram using standard notation, it can be converted to Structured
Query Language (SQL) queries more easily and quickly. Tools like the ERDPlus helps in
automatic conversion of the diagram to queries.
20. Database Security:
Legal Issues in Storing Credit Card Details in Database:
According to Verizon Data Breach Report (2012), 96% of data theft occurrence are from
database. Hackers and Malicious users of the database try to hack the database and extract the
high privileged information from the database. Some of the reasons why the credit card
information should not be stored in the database are given below:
1) Excessive privileges to all users of the database
2) Abuse of the privilege by the database user
3) Through Malware
4) Limited security
5) Weak Audit trail
Data Security Techniques:
Some of the security techniques to secure the confidential data in the database is as follows
Encryption Techniques:

It is required to encrypt the data through advanced encryption techniques like AES and
then store in the database. This make the data unusable to the hackers. On the requirement of the
specific data the encrypted data must be decrypted and used.
User Authentication:
The database user accessing the privileges information should be properly authenticated multiple
times before allowing the access to privileged information.
References:
1) Abraham Silberschatz, Henry F. Korth and S. Sudarshan, "Database System Concepts",
Mc-Graw Hill Publishers, 2010.
2) Alfred Basta, Melissa Zgola, Dana Bullaboy and Thomas Whitelock, "Database
Security", Cengage Learning, 2011.