Database Design and Legal Issues - Desklib Report

This report covers ER Diagram, SQL commands for creating tables and inserting data, legal issues in database and security techniques in DBMS. It also discusses the benefits of ER diagram in database design.

Student ID:
Student Name:

Report
Task 1
ER Diagram

Assumptions
1. Booking and event timings will be stored into the database.
2. Location of each item will be stored into the database.
(USA Informa 2018)
(Watt A. n.d.)

Task 2 A
Task 2 a (1)
/* Rohit Hasan . Q 2 a. (1) This command is creating database */
create database PartyKids;
use PartyKids;
/* Rohit Hasan . Q 2 a. (1) This command is creating table */
CREATE TABLE Customer (
CusID int NOT NULL,
CusName varchar(30) NOT NULL,
CusAddress varchar(40) NOT NULL,
CusPhone varchar(20) NOT NULL,
CusEmail varchar(30) NOT NULL,
PRIMARY KEY (CusID)
);
Task 2 a (2)
/* Rohit Hasan . Q 2 a. (2) This command is inserting data */
insert into Customer (CusID,CusName, CusAddress, CusPhone, CusEmail )
values (1, 'John P Smith','12/1 Flinders St, Melbourne 3000',
'6345123876','[e-mail]'),
(2, 'Nick Johnsberg','12/1 Patrick St, Melbourne 3000',
'6345123876','[e-mail]'),

(3, 'Max White','15/1 Patrick St, Melbourne 3000',
'6345123876','[e-mail]'),
(4, 'Diana Hadden','12/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]'),
(5, 'Edwick Parker','11/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]'),
(6, 'Hanry Smith','15/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]'),
(7, 'Michael Brown','19/1 Patrick St, Sydney 5000',
'6345123876','[e-mail]');
Task 2 a (3)
/* Rohit Hasan . Q 2 a. (3) This command is displaying Customer data */
select * from Customer;

Task 2 a (4)
/* Rohit Hasan . Q 2 a. (4) This command is changing Address of John P smith
*/
update Customer set CusAddress='15/1 Flinders St, Melbourne 3000' where
CusName='John P Smith';
select CusName, CusAddress from Customer;
Task 2 a (5)
/* Rohit Hasan . Q 2 a. (5) This command is displaying Customer detail whose
name start with J */

select * from Customer where CusName like 'J%' COLLATE Latin1_General_BIN;
Task 2 a (6)
/* Rohit Hasan . Q 2 a. (6) This command is displaying Customers of Victora */
select * from Customer where RIGHT(CusAddress, 4)='3000';
Task 2 a (7)
/* Rohit Hasan . Q 2 a. (7) This command is deleting customer John P Smith */

delete from Customer where CusName='John P Smith';
Task 2 a (8)
/* Rohit Hasan . Q 2 a. (8) This command is creating booking table and
inserting data into that table */
CREATE TABLE Booking (
BookingID int NOT NULL,
EvtType varchar(20) NOT NULL,
NoOfGuests int NOT NULL,
EvtDateTime datetime NOT NULL,
BookDateTime datetime NOT NULL,
TotalAmt int NOT NULL,
Discount int NULL,
Feedback varchar(50) NULL,
CusID int NOT NULL,
PRIMARY KEY (BookingID),
CONSTRAINT FK_CusID FOREIGN KEY (CusID)
REFERENCES Customer(CusID)
);
insert into Booking(BookingID, EvtType, NoOfGuests, EvtDateTime, BookDateTime,
TotalAmt, Discount, Feedback, CusID)
values (1,'Birthday', 20, '3-3-2018','4-4-2018',300,5,'Best',2),
(2,'Birthday', 25, '10-10-2018','11-10-2017',400,5,'Nice',3),
(3,'Fresher', 15, '12-10-2018','12-10-2017',300,7,'Best',3),
(4,'Farewell', 15, '10-9-2016','12-9-2016',350,5,'Best',4),
(5,'Birthday', 15, '10-9-2016','12-9-2016',350,5,'Best',4),
(6,'Birthday', 15, '11-9-2016','12-9-2016',350,5,'Nice',4);

Task 2 a (9)
/* Rohit Hasan . Q 2 a. (9) This command is deleting a Customer from Cusomer
table */
delete from Customer where CusID=2;
Task 2 a (10)
/* Rohit Hasan . Q 2 a. (10) This command is displaying Cusomer bookings */
select CusID, count(BookingID) Bookings from Booking group by CusID;

Task 2 a (11)
/* Rohit Hasan . Q 2 a. (11) This command is displaying Customer booking
timings */
SELECT distinct Customer.CusName, Booking.BookDateTime
FROM Booking INNER JOIN
Customer ON Booking.CusID = Customer.CusID
order by Booking.BookDateTime desc;
Task 2 a (12)
/* Rohit Hasan . Q 2 a. (12) This command is showing Customer who did not do
any booking */
select CusName from Customer where CusID not in (select CusID from Booking);

Task 2 B
Task 2 b (13)
/* Rohit Hasan . Q 2 b. (13) Create all tables */
CREATE TABLE InflatableModel (
FModel varchar(15) NOT NULL,
FPrice int NOT NULL,
FQty int NOT NULL,
PRIMARY KEY (FModel)
);
CREATE TABLE Items (
FItmNo varchar(15) NOT NULL,
FLocation varchar(15) NOT NULL,
PRIMARY KEY (FItmNo)
);
CREATE TABLE Inflatables (
FItmNo varchar(15) NOT NULL,
FModel varchar(15) NOT NULL,
PRIMARY KEY (FItmNo),
CONSTRAINT FK_ItemsInflatable FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo),
CONSTRAINT FK_InfModels FOREIGN KEY (FModel)
REFERENCES InflatableModel(FModel)
);
CREATE TABLE ChairModel (
FModel varchar(15) NOT NULL,
FPrice int NOT NULL,
FQty int NOT NULL,
PRIMARY KEY (FModel)
);
CREATE TABLE Chairs (
FItmNo varchar(15) NOT NULL,
FModel varchar(15) NOT NULL,
PRIMARY KEY (FItmNo),
CONSTRAINT FK_ItmChairs FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo),
CONSTRAINT FK_ChairsModels FOREIGN KEY (FModel)
REFERENCES ChairModel(FModel)
);
CREATE TABLE [Tables] (
FItmNo varchar(15) NOT NULL,
PRIMARY KEY (FItmNo),
CONSTRAINT FK_ItmsTable FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo)
);
CREATE TABLE BookingItems (
FBookingID int NOT NULL,
FItmNo varchar(15) NOT NULL,
PRIMARY KEY (FBookingID,FItmNo),
CONSTRAINT FK_Bookings FOREIGN KEY (FBookingID)

REFERENCES Booking(BookingID),
CONSTRAINT FK_ItemsBookings FOREIGN KEY (FItmNo)
REFERENCES Items(FItmNo)
);
Task 2 b (14)
/* Rohit Hasan . Q 2 b. (14) Insert data */
insert into InflatableModel (FModel, FPrice, FQty)
values ('MIF1',30,300),
('MIF2',40,300),
('MIF3',20,200);
insert into ChairModel (FModel, FPrice, FQty)
values ('MCH1',20,300),
('MCH2',50,300),
('MCH3',40,200);
insert into Items(FItmNo, FLocation)
values (1,'Central'),
(2,'East'),
(3,'West'),
(4,'Central'),
(5,'East'),
(6,'West'),
(7,'Central'),
(8,'East'),
(9,'West');
insert into Inflatables(FItmNo, FModel)
values (1, 'MIF1'),
(2, 'MIF2'),
(3, 'MIF3');
insert into Chairs(FItmNo, FModel)
values (4, 'MCH1'),

(5, 'MCH2'),
(6, 'MCH3');
insert into [Tables](FItmNo)
values (7),
(8),
(9);
Insert into BookingItems (FBookingID,FItmNo)
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)
/* Rohit Hasan . Q 2 b. (15) Display Cusomers who rented inflatables */
SELECT distinct Customer.CusName
FROM Booking INNER JOIN BookingItems ON Booking.BookingID =
BookingItems.FBookingID INNER JOIN
Customer ON Booking.CusID = Customer.CusID INNER JOIN
Items ON BookingItems.FItmNo = Items.FItmNo INNER JOIN
Inflatables ON Items.FItmNo = Inflatables.FItmNo;
Task 2 b (16)
/* Rohit Hasan . Q 2 b. (16) Display Cusomers who rented chairs or inflatables
*/
SELECT distinct Customer.CusName
FROM Booking INNER JOIN
BookingItems ON Booking.BookingID = BookingItems.FBookingID INNER JOIN
Customer ON Booking.CusID = Customer.CusID INNER JOIN
Items ON BookingItems.FItmNo = Items.FItmNo
where BookingItems.FItmNo in (select FItmNo from Inflatables) or
BookingItems.FItmNo in (select FItmNo from Chairs);

Task 2 b (17)
/* Rohit Hasan . Q 2 b. (17) Display Cusomers booking days */
SELECT Customer.CusID, Customer.CusName, count(Booking.BookingID)
'BookingDays'
FROM Booking INNER JOIN
Customer ON Booking.CusID = Customer.CusID
group by Customer.CusID, Customer.CusName;
Task 2 b (18)
/* Rohit Hasan . Q 2 b. (18) Display Cusomers with booking amount */
SELECT Customer.CusID, Customer.CusName, sum(Booking.TotalAmt) as TotalAmount
FROM Booking INNER JOIN
Customer ON Booking.CusID = Customer.CusID
group by Customer.CusID, Customer.CusName;

Task 3
Task 3 (19)
ER Diagram Benefits
Diagrams play a very important role in database design. It shows every process, activity and flow
very clearly. One of the most important diagrams in database design is ER diagram. It shows all
the entities that are required to make the database. All entities along with their attributes and
relations with other entities are also displayed in the ER diagram.
It is created at the very starting while designing the database. It helps the database developer in
making the database. All important features of the database can be shown pictorially by the ER
diagram. It models the entire database activity and the readers can easily understand the
complete database by only studying the ER diagram.
There are some other models also that show the database activities pictorially like network
model, hierarchical model but all models are not used like ER diagram. ER diagram is easy to
understand than other models.
If we start database designing with making ER diagram, the database developer may be confused
or missed some important entities or attributes, but if we make ER diagram in starting phase of
the database designing, it will be very easy to make database. There are very less chances of
skipping something if we use the ER diagram.
.

Task 3 (20)
Legal Issues in Database
While using a database, following legal issues may occur-
- The customer’s important detail may be leaked e.g. credit card detail etc. It is the major
legal issue that may be aroused while using the database. To protect the important data,
the database should be much secured. There are so many techniques to protect the data.
- The important detail may be shared by internetworking sites, and it is also a major legal
issue. A customer may claim about this.
Security Techniques in DBMS
There are lots of security techniques provided by the DBMS like below-
- Authentication
- Authorization
- Use access controls
- Encryption/decryption
All above mentioned techniques are very important for the safety point of view in the database.
Authentication process authenticates the users first before accessing the database while
authorization process gives authorization to the user to access the database.
In user access control, the user is given access to the database specific portions.
Encryption/decryption technique is used to store the data in encrypted form to protect the data
and can be retrieved by decrypting the encrypted data.

References
USA Informa (2018), SQL by Design: Why You Need Database Normalization. [online].
Available from:
http://www.itprotoday.com/risk-and-compliance/ibm-executives-press-us-lawmakers-not-adopt-
eu-privacy-law. [Accessed 1 June 2018].
Watt A. (n.d.). Chapter 11 Functional Dependencies. [online]. Available from:
https://opentextbc.ca/dbdesign01/chapter/chapter-11-functional-dependencies/. [Accessed 1 June
2018]