Database Solution For Party Kids Business

Party Kids Rental Business

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)

Overview of the Business Problems

    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),

Database Solution

    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-

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

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]