Creating An ER Diagram And Tables For A Party Hire Business
Task 1
Task 1:
Task 2A:
- 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:
- 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:
- Query:
/* Anmoldeep Kaur. Q3. Selecting Customer Data */
SELECT * FROM Customer;
Output:
- 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;
Query:
/* Anmoldeep Kaur. Q5. Selecting Customer Starts With J */
SELECT * FROM Customer WHERE CustomerName LIKE ‘J%’;
Output:
- Query:
/* Anmoldeep Kaur. Q6. Selecting Customer in Victoria */
SELECT * FROM Customer WHERE CustomerAddress LIKE ‘%3000’;
Output:
- Query:
/* Anmoldeep Kaur. Q7. Deleting a Customer Data */
DELETE FROM Customer WHERE CustomerName LIKE ‘John P Smith’;
Output:
- 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:
- Query:
/* Anmoldeep Kaur. Q9. Deleting a Booked Customer Data */
DELETE FROM Customer WHERE CustomerID = 4;
Output:
- Query:
/* Anmoldeep Kaur. Q10. Selecting Booking Data */
Task 2A
SELECT * FROM Booking ORDER BY CustomerID;
Output:
- 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:
- Query:
/* Anmoldeep Kaur. Q12. Selecting Customer With No Booking */
SELECT CustomerName FROM Customer WHERE CustomerID NOT IN (SELECT CustomerID FROM Booking);
Output:
Task 2B
- 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:
- 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
- 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:
- 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:
- Query:
/* Anmoldeep Kaur. Q17. Selecting number of booking days of each customer */
SELECT CustomerID, SUM(BookingDays) As BookingDaysCount FROM Booking GROUP BY CustomerID;
Output:
- 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:
- 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:
- Visual representation of database helps to understand its structures and formulating strategies.
- It helps in understanding the database requirement before starting to develop the database
- It helps in analyzing the data and normalizing the database before developing them.
- It helps in providing multiple views of storing the data in database.
- It helps in business process re-engineering
- It helps to develop the high-quality database by developing the normalized database design.
- It resolves the ambiguity and avoids unnecessary processing of data
- It helps the students to understand the database concepts more effectively.
- A standard usage of symbols in the Entity Relationship Diagram (ERD) helps in the communication about the data in database without written explanation
- It helps in troubleshooting the problems involved in the database logic and deployment
- 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.
- 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:
- Excessive privileges to all users of the database
- Abuse of the privilege by the database user
- Through Malware
- Limited security
- Weak Audit trail
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:
- Abraham Silberschatz, Henry F. Korth and S. Sudarshan, “Database System Concepts”, Mc-Graw Hill Publishers, 2010.
- Alfred Basta, Melissa Zgola, Dana Bullaboy and Thomas Whitelock, “Database Security”, Cengage Learning, 2011.
