Database Solution For Party Kids Business Essay.

Party Kids Rental Business

Party Kids is a company that rents out inflatables, tables, and chairs for children’s parties.

It is owned Ms Donta Pop and she has been very happy to see her business grow rapidly over the past 2 years. She has recently purchased dozens of new tables, a number of new types of inflatables and dozens of chairs.

To store it all, Ms Pop has rented out 2 new storage facilities, so that she now has one location on the east side of town, one central, and one in the west.

Ms Pop has been finding it impossible to keep track of customer details, bookings, and the storage location of her assets. She has been trying to manage with a paper system but has been making mistakes like hiring out an item twice for the same day. So, she has now hired you to shift her records into database.

You have interviewed Ms Pop and you believe you have enough information to begin planning the database. This is what you know:

• Ms Pop owns over 500 asset items. She is carrying:
• 3 inflatable models. She owns 20 inflatables in all.
• 3 chair models. She owns 100’s of chairs.
• 1 table model – a plastic folding table that seats 10 people. She has no plans to purchase a different model of table.
• Assets within a model category are all the same price. For example, for cushioned seats the price is $10 per day, and for small plastic chairs it’s $2 per day.

• Whenever Ms Pop purchases a new asset, she tags it. For chairs and tables this means a sticker with a unique number for each item. For inflatables it means marking it with a name e.g. ModelA#1.
• She wants to keep track of customer feedback about each single item and about the hiring event in general
• With regard to customer payments:
• Ms Pop passes on her bank details to customers over the phone (or in person) when the booking is made and customers pay directly into her account
• She only accepts lump sum payments (not partialpayments)
• She accepts payments before and after rental
• She will sometimes apply discounts to a booking, but never to individual assets
• Her assets can be stored in any of the 3 locations and each location can hold all assets
• She doesn’t want to store details about the manufacturers or distributors of her assets
• She doesn’t want to store details about her 3 storage locations.

Task 1 

1. Create an MS Word document named “Your Given Name – Your Student Number – Party Kids.docx" e.g. Harpreet – 123456 – Party Kids.docx.

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

a. Use Crow’s Foot Notation

b. Mark cardinality, existence, primary keys, and foreign keys.

c. Ensure the entities are in a normalised state

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.

e. Place your name and student number within the drawing

f. Insert that ER Diagram into your Word document. If you use Lucidchart or similar you could take a screenshot.

Task 2 

Create your solutions for following tasks in Microsoft SQL Server and:

• Write a T-SQL comment above each SQL statement you write, stating your name, the task you are solving, and the purpose of the code. For example, “Harpreet. Q1. Creating a database” and “Harpreet Q1: Creating a table”, above the statements required for question 1.
• Paste all the SQL code that you write into your MS Word document. That is, paste it as plain text
• Paste screenshots from Microsoft SQL Server into your MS Word document. The screenshots should show your code along with the relevant messages window and/or outputs window

Note:

- For screenshots, keep the images small by using tools like Windows-Shift-S or the MS Word Screenshot tool. Don’t clip the entire screen or a whole window.
- Note that you could gather up all of your solutions for Tasks 2A and Task 2b into 2 batches and run each batch in one hit.
- Where necessary, attach labels to the screenshots in your MS Word document so as to make it clear which screenshot or portion of a screenshot belongs to which solution.

Task 2a 

1. Write two CREATE statements. One to build a database named “PartyKids” and one to build within it a table named “Customer” which will store details about PartyKids customers. The columns should match the attributes listed in your ER Diagram. All columns should be required columns (that is, don’t allow NULL values).

Overview of the Business Problems

2. Use a single INSERT statement to create the details of 7 customers. Name one of the customers “John P Smith” and record his address as 12/1 Flinders St, Melbourne 3000. [Note: If you didn’t include address details in the Customer entity in your ER Diagram, do so now and then redo task 2a.1]2.

3. Use a SELECT statement to display full details of all customers.

4. Change the address of John Smith to 15/1 Flinders Street and then display the name and address only of all customers.

5. Display the details of all customers whose first name that starts with the letter “J”.

6. Display the details of all customers who live in Victoria. Note that all Victorian postcodes are in the 3000’s and no other states use the 3000’s.

7. Delete John P Smith from the database.

8. Add a 2nd table to your database.

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

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

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

Task 2b 

13. Create all the tables you have drawn up in your ER Diagram and build the relationships between 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.

Task 3

19. Ms Pop is curious to know why a computer expert like you would draw a diagram before using database software. Write 200 to 250 words explaining the purpose of creating your ER Diagram before building the database.

20. Ms Pop is thinking about accepting credit card payments from customers and keeping a record of the payments in her database. She is concerned about storing personal details such as credit card numbers and customer addresses. Write 150 to 200 words naming the main legal issue at play and the techniques you could use to help secure her database.

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]