Itda1001 Database Fundamentals Answers Assessment Answers

Create an ER Diagram from the above scenario, being sure to:

1. a) Use Crow’s Foot Notation
2. b) Mark cardinality, existence, primary keys (with an underline), and foreign keys (with the letters “FK” in brackets).
3. c) Ensure the entities are in a normalised state
4. d) Write any assumptions you make if you think it important to clarify the reasons for  building particular relationships, creating particular attributes, or leaving something out.
5. e) Place your name and student number within the drawing
6. f) Insert that ER Diagram into your Word document. If you use Lucidchart or similar you could take a screenshot.

Add a 2nd table to your database.

o It should store details about bookings made by a customer so name it “Booking” or something similar. If your ER diagram doesn’t show a customer entity linked to table that lists bookings (party hires) made by customers, then add one now. Ensure your Booking table contains, at the minimum, attributes to store the date of the booking and any customer feedback.

o Build a relationship between the Booking table and the Customer table and make it impossible to create a new Booking that doesn’t belong to an existing Customer.

o Insert the details of at least 6 bookings. Enter details into every column of each record. The bookings should all belong to just 3 customers – 1 for one customer, 2 for another, and 3 for the 3rd

9. Use a DELETE statement to try to delete a customer who has a booking. It should not be possible.
10. Display all bookings grouped by customer. Display full booking details, but don’t show any customer details.
11. Display the customer names and booking dates of all customers who have made a booking and sort them from the most recent booking to the least recent.
12. Display the names of customers who have not yet made a booking.
13. Create all the tables you have drawn up in your ER Diagram and build the relationshipsbetween them. Choose your data types carefully. Note that you will need to buid the tables on the “one” side before the tables on the “many” side.
14. Add at least 3 records to every table and then display them all. Note that you will need to insert records into the tables on the “one” side before inserting into tables on the “many” side.
15. Display the names of all customers who have rented inflatables
16. Display the names of all customers who have rented chairs or inflatables
17. Display a count of the number of booking days of each customer
18. Display the total amount of money received from each customer. That is, display one one value per customer.

Answer:

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

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:

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:

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

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:

• Excessive privileges to all users of the database
• Abuse of the privilege by the database user
• Through Malware
• Limited security
• 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:

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