ER Diagram
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’,‘[email protected]‘),
(2, ‘Nick Johnsberg’,’12/1 Patrick St, Melbourne 3000′, ‘6345123876’,‘[email protected]‘),
(3, ‘Max White’,’15/1 Patrick St, Melbourne 3000′, ‘6345123876’,‘[email protected]‘),
(4, ‘Diana Hadden’,’12/1 Patrick St, Sydney 5000′, ‘6345123876’,‘[email protected]‘),
(5, ‘Edwick Parker’,’11/1 Patrick St, Sydney 5000′, ‘6345123876’,‘[email protected]‘),
(6, ‘Hanry Smith’,’15/1 Patrick St, Sydney 5000′, ‘6345123876’,‘[email protected]‘),
(7, ‘Michael Brown’,’19/1 Patrick St, Sydney 5000′, ‘6345123876’,‘[email protected]‘);
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 (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 (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-
Security Techniques in DBMS
There are lots of security techniques provided by the DBMS like below-
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:
https://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]
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download