Database Fundamentals: ER Diagram, SQL Queries, Importance of ER Diagram, Legal Issues and Security Techniques

This report covers ER Diagram, SQL queries, importance of ER diagram, legal issues and security techniques in database fundamentals. It includes sample SQL queries for creating tables, inserting data, updating data and deleting data.

ITDA1001-Database Fundamentals
Assessment 2 – Assignment
5/27/2018
Student ID:
Module Tutor:

Report
Task 1
ER Diagram
(CS Odessa Corp. 2016)

Assumptions
1. Complete booking detail will be saved into the database e.g. booking date and time, event
date and time etc.
2. Customer feedback will also be saved into the database.
Task 2 A
Task 2 a (1)
/* Rajinder Singh. Q 2 a. (1) creating database */
create database PartyKids;
/* Rajinder Singh. Q 2 a. (1) creating table Customet */
CREATE TABLE Customer (
CustomerID int NOT NULL,
CustomerName varchar(50) NOT NULL,
[Address] varchar(50) NOT NULL,
Phone varchar(20) NOT NULL,
Email varchar(30) NOT NULL,
PRIMARY KEY (CustomerID)
);

Task 2 a (2)
/* Rajinder Singh. Q 2 a. (2) Insert data into customer table */
insert into Customer (CustomerID,CustomerName, [Address], Phone, Email )
values (1, 'John P Smith','12/1 Flinders St, Melbourne 3000',
'5635632662','[e-mail]'),
(2, 'James Parker','12 George St, Melbourne 3000',
'5635632662','[e-mail]'),
(3, 'Harry White','3 King St, Melbourne 3000',
'5635632662','[e-mail]'),
(4, 'Emily Parker','14/1 Smith St, Sydney 5000',
'5635632662','[e-mail]'),
(5, 'Noah Brown','13/1 Church St, Sydney 5000',
'5635632662','[e-mail]'),
(6, 'Max Hall','11/1 Black St, Sydney 5000', '5635632662','[e-mail]'),
(7, 'Joe White','18/1 Queens St, Sydney 5000',
'5635632662','[e-mail]');

Task 2 a (3)
/* Rajinder Singh. Q 2 a. (3) Show customer data */
select * from Customer;
Task 2 a (4)
/* Rajinder Singh. Q 2 a. (4) Change Address of John P smith Customer */
update Customer set [Address]='15/1 Flinders St, Melbourne 3000' where
CustomerName='John P Smith';
select CustomerName, [Address] from Customer;

Task 2 a (5)
/* Rajinder Singh. Q 2 a. (5) showing customer detail whose name start with J
*/
select * from Customer where CustomerName like 'J%' COLLATE
Latin1_General_BIN;
Task 2 a (6)
/* Rajinder Singh. Q 2 a. (6) showing customer of Victora */
select * from Customer where RIGHT([Address], 4)='3000';

Task 2 a (7)
/* Rajinder Singh. Q 2 a. (7) delete customer John P Smith */
delete from Customer where CustomerName='John P Smith';

Task 2 a (8)
/* Rajinder Singh. Q 2 a. (8) creating table booking and inserting data into
booking table */
CREATE TABLE Booking (
BookingID int NOT NULL,
EventType varchar(20) NOT NULL,
NoOfGuests int NOT NULL,
EventDateTime datetime NOT NULL,
BookingDateTime datetime NOT NULL,
TotalAmount int NOT NULL,
Discount int NULL,
Feedback varchar(50) NULL,
CustomerID int NOT NULL,
PRIMARY KEY (BookingID),
CONSTRAINT FK_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)
);
insert into Booking(BookingID, EventType, NoOfGuests, EventDateTime,
BookingDateTime, TotalAmount, Discount, Feedback, CustomerID)
values (1,'Birthday Party', 20, '10-10-2018','9-10-2017',200,5,'Good',2),
(2,'Birthday Party', 25, '11-10-2018','10-10-2017',300,5,'Good',3),
(3,'Fresher Party', 15, '11-10-2018','10-10-2017',200,7,'Excellent',3),
(4,'Farewell Party', 15, '9-9-2016','8-9-2016',250,5,'Excellent',4),
(5,'Birthday Party', 15, '9-9-2016','8-9-2016',250,5,'Excellent',4),
(6,'Birthday Party', 15, '9-9-2016','8-9-2016',250,5,'Excellent',4);

Task 2 a (9)
/* Rajinder Singh. Q 2 a. (9) delete a customer from customer table */
delete from Customer where CustomerID=2;
Task 2 a (10)
/* Rajinder Singh. Q 2 a. (10) show customer bookings */
select CustomerID, count(BookingID) as 'Bookings' from Booking group by
CustomerID;

Task 2 a (11)
/* Rajinder Singh. Q 2 a. (11) show customer booking timings */
SELECT distinct Customer.CustomerName, Booking.BookingDateTime
FROM Booking INNER JOIN
Customer ON Booking.CustomerID = Customer.CustomerID
order by Booking.BookingDateTime desc;
Task 2 a (12)
/* Rajinder Singh. Q 2 a. (12) show customer who did not do any booking */
select CustomerName from Customer where CustomerID not in (select CustomerID
from Booking);

Task 2 B
Task 2 b (13)
/* Rajinder Singh. Q 2 b. (13) show all tables */
CREATE TABLE ModelInflatable (
Model varchar(10) NOT NULL,
Price int NOT NULL,
Quantity int NOT NULL,
PRIMARY KEY (Model)
);
CREATE TABLE Items (
ItemNo varchar(10) NOT NULL,
Location varchar(10) NOT NULL,
PRIMARY KEY (ItemNo)
);
CREATE TABLE Inflatable (
ItemNo varchar(10) NOT NULL,
Model varchar(10) NOT NULL,
PRIMARY KEY (ItemNo),
CONSTRAINT FK_ItemInflatable FOREIGN KEY (ItemNo)
REFERENCES Items(ItemNo),
CONSTRAINT FK_InflatableModel FOREIGN KEY (Model)
REFERENCES ModelInflatable(Model)
);
CREATE TABLE ModelChair (
Model varchar(10) NOT NULL,
Price int NOT NULL,
Quantity int NOT NULL,
PRIMARY KEY (Model)
);
CREATE TABLE Chair (
ItemNo varchar(10) NOT NULL,
Model varchar(10) NOT NULL,
PRIMARY KEY (ItemNo),

CONSTRAINT FK_ItemChair FOREIGN KEY (ItemNo)
REFERENCES Items(ItemNo),
CONSTRAINT FK_ChairModel FOREIGN KEY (Model)
REFERENCES ModelChair(Model)
);
CREATE TABLE [Table] (
ItemNo varchar(10) NOT NULL,
PRIMARY KEY (ItemNo),
CONSTRAINT FK_ItemTable FOREIGN KEY (ItemNo)
REFERENCES Items(ItemNo)
);
CREATE TABLE BookingItems (
BookingID int NOT NULL,
ItemNo varchar(10) NOT NULL,
PRIMARY KEY (BookingID,ItemNo),
CONSTRAINT FK_Booking FOREIGN KEY (BookingID)
REFERENCES Booking(BookingID),
CONSTRAINT FK_ItemBooking FOREIGN KEY (ItemNo)
REFERENCES Items(ItemNo)
););
Task 2 b (14)
/* Rajinder Singh. Q 2 b. (14) Insert data into tables */
insert into ModelInflatable (Model, Price, Quantity)
values ('ModelI#1',30,100),
('ModelI#2',40,100),
('ModelI#3',50,50);
insert into ModelChair (Model, Price, Quantity)
values ('ModelC#1',20,100),
('ModelC#2',70,100),
('ModelC#3',50,50);

insert into Items(ItemNo, Location)
values (1,'Central'),
(2,'East'),
(3,'West'),
(4,'Central'),
(5,'East'),
(6,'West'),
(7,'Central'),
(8,'East'),
(9,'West');
insert into Inflatable(ItemNo, Model)
values (1, 'ModelI#1'),
(2, 'ModelI#2'),
(3, 'ModelI#3');
insert into Chair(ItemNo, Model)
values (4, 'ModelC#1'),
(5, 'ModelC#2'),
(6, 'ModelC#3');
insert into [Table](ItemNo)
values (7),
(8),
(9);
Insert into BookingItems (BookingID,ItemNo)
values (1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,1),
(3,2),
(3,4),
(3,5),
(3,6);

Task 2 b (15)
/* Rajinder Singh. Q 2 b. (15) Display customers who rented inflatables */
SELECT distinct Customer.CustomerName
FROM Booking INNER JOIN BookingItems ON Booking.BookingID =
BookingItems.BookingID INNER JOIN
Customer ON Booking.CustomerID = Customer.CustomerID INNER JOIN
Items ON BookingItems.ItemNo = Items.ItemNo INNER JOIN
Inflatable ON Items.ItemNo = Inflatable.ItemNo;

Task 2 b (16)
/* Rajinder Singh. Q 2 b. (16) Display customers who rented chairs or
inflatables */
SELECT distinct Customer.CustomerName
FROM Booking INNER JOIN
BookingItems ON Booking.BookingID = BookingItems.BookingID INNER JOIN
Customer ON Booking.CustomerID = Customer.CustomerID INNER JOIN
Items ON BookingItems.ItemNo = Items.ItemNo

where BookingItems.ItemNo in (select ItemNo from Inflatable) or
BookingItems.ItemNo in (select ItemNo from Chair);
Task 2 b (17)
/* Rajinder Singh. Q 2 b. (17) Display customers booking days */
SELECT Customer.CustomerID, Customer.CustomerName, count(Booking.BookingID)
'Booking Days'
FROM Booking INNER JOIN
Customer ON Booking.CustomerID = Customer.CustomerID
group by Customer.CustomerID, Customer.CustomerName;
Task 2 b (18)
/* Rajinder Singh. Q 2 b. (18) Display customers with booking amount */
SELECT Customer.CustomerID, Customer.CustomerName, sum(Booking.TotalAmount) as
TotalAmount
FROM Booking INNER JOIN
Customer ON Booking.CustomerID = Customer.CustomerID
group by Customer.CustomerID, Customer.CustomerName;

Task 3
Task 3 (19)
Importance of ER Diagram
Making diagrams and showing all the important facts through diagrams is a very important point
in the database development. ER diagram helps in showing the entire database tables, attributes,
and relations between the tables.
It helps in showing the flow of the entire database. It is the basic diagram that helps in
understanding the complete database requirement and works as a blueprint before starting work
on the database.
It is very confusing if we show all the tables, attributes and relations by text. It will not be clear
at one glance. The users have to study the text completely but if we show all the stuff by ER
diagram, it will be very clear to users than text.
It is very effective and communicational diagram that shows everything pictorially. It is highly
integrated with relational model as it can be easily converted into relational model. There are so
many different types of models like hierarchical model, network model etc.
It can be converted into the above mentioned model. It is the best feature of the ER diagram that
makes it as important as it is used in every organization before implemented the database. There
are some different notations that can be used in designing the ER diagram like crows notation
etc.
Task 3 (20)
Legal Issues in Database

There are some legal issues in the database that may occur while using the database. Some of
them are as follows-
- There is lot of information of customers that is very important like credit card details etc.
that should be secured. If that is leaked by any reason, it may be very big legal issue.
- All customers’ detail should be secured. It should not be shared or leaked on any internet
source. Otherwise, the big legal issue may occur.
Database Security Techniques
There are lots of database security techniques as follows-
User Authentication
The user should be authenticated before giving access to the database resource.
Authorization
The users should be authorized on database resources after authentication. The users cannot
access unauthorized database resources.
Encryption/Decryption Techniques
Important data should be stored into the database in the encrypted form to reduce the chances of
hacking and whenever the data is required, the data will be received in the decrypted form.
(etutorials.org. 2018)
References
CS Odessa Corp. (2016). Design elements – ERD (crow’s foot notation) [online]. Available
from: http://www.conceptdraw.com/examples/crowfoot-notation [Accessed: 27-May-
2018]
etutorials.org. (2018). Chapter 12: SQL and RDBMS Security [online]. Available from:
http://etutorials.org/SQL/sql+bible/Part+V+Implementing+Security+Using+System+Cata
logs/Chapter+12+SQL+and+RDBMS+Security/ [Accessed 27-May 2018].