Itda1001 Database Fundamentals: Party Hire Assessment Answers
Party Hire Case Study
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.
Task 1
- Create an MS Word document named “Your Given Name– Your Student Number – Party Kids.docx" e.g. Harpreet – 123456 – Party Kids.docx.
- Create an ER Diagramfrom the above scenario, being sure to:
- Use Crow’s Foot Notation
- Mark cardinality, existence, primary keys (with an underline), and foreign keys (with the letters “FK” in brackets).
- Ensure the entities are in a normalised state
- 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.
- Place your nameand student number within the drawing
- 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, “ 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 codealong with the relevant messages window and/or outputs window
Task 2a
- 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).
- 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]
- Use a SELECT statement to display full details of all customers
- Change the address of John Smith to 15/1 Flinders Street and then display the name and address onlyof all customers
- Display the details of all customers whose first name that starts with the letter “J”
- 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.
- Delete John P Smith from the database
- 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.
- Use a DELETE statement to try to delete a customer who has a booking. It should not be possible.
- Display all bookings grouped by customer. Display full booking details, but don’t show any customer details.
- 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.
- Display the names of customers who have not yet made a booking.
Task 2b
- 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.
- 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.
- Display the names of all customers who have rented inflatables
- Display the names of all customers who have rented chairs orinflatables
- Display a count of the number of booking days of each customer
- Display the total amount of money received from each customer. That is, display one one value per customer.
Task 3
- 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
- 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.
Answer:
Task 1
ER Diagram
Task 2
Task 2a
1
PRINT 'Creating database.';
CREATE DATABASE PartyHire;
GO
PRINT 'Creating table Customer ...';
CREATE TABLE Customer (
CustomerID INTEGER NOT NULL IDENTITY ,
CustomerName VARCHAR(45) ,
CustomerAddress VARCHAR(150) ,
PRIMARY KEY(CustomerID));
GO
2
PRINT 'Populating the Customer table'
SET IDENTITY_INSERT [dbo].[Customer] ON
Insert Into Customer(CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Derek Banas', '123 lindsay street Victoria 3000'),
(2, 'David Beckham', '123 london road Victoria 3000'),
(3, 'Cristiano', '56 portugal road'),
(4, 'Phillip Lahm', 'german road'),
(5, 'Chris Evans', '32 centre street Victoria 3000'),
(6, 'Jhonny Evans', '45 manchester street'),
(3, 'John P Smith', '12/1 Flinders St, Melbourne 3000');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
4
Print 'Updating columns';
UPDATE dbo.Customer
SET CustomerAddress = ' 15/1 Flinders Street'
WHERE CustomerName = 'John P Smith'
GO
Select CustomerName, CustomerAddress From Customer;
6
Select *
From Customer
Where CustomerAddress LIKE '%3000%';
8
PRINT 'Creating table Booking ...';
CREATE TABLE Booking (
BookingID INTEGER NOT NULL IDENTITY ,
CustomerID INTEGER NOT NULL ,
BookingDate DATE ,
CustomerFeedback VARCHAR(150) ,
PRIMARY KEY(BookingID),
FOREIGN KEY(CustomerID)
REFERENCES Customer(CustomerID),
);
GO
CREATE INDEX Booking_FKIndex1 ON Booking(CustomerID);
GO
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Booking] ON
Insert Into Booking (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-08', 'Average'),
(9, 2, '2018-12-09', 'Highly satisfying'),
(10, 2, '2018-05-01', 'Better than the previous one'),
(11, 4, '2017-09-29', 'Excellent'),
(12, 4, '2017-03-24', 'Fine'),
(13, 4, '2017-10-30', 'Great');
SET IDENTITY_INSERT [dbo].[Booking] OFF
GO
10
Select BookingID, BookingDate, CustomerFeedback
From Booking
Group By CustomerID;
11
Select Customer.CustomerName, Booking.BookingDate
From Customer inner join Booking on Customer.CustomerID = Booking.CustomerID
Order By Booking.BookingDate DESC;
12
Select Customer.CustomerName
From Customer
Where Customer.CustomerID not in
( Select Customer.CustomerID
From Customer inner join Booking on Customer.CustomerID = Booking.CustomerID);
Task 2b
13
PRINT 'Creating table Customer ...';
CREATE TABLE Customer (
CustomerID INTEGER NOT NULL IDENTITY ,
CustomerName VARCHAR(45) ,
CustomerAddress VARCHAR(150) ,
PRIMARY KEY(CustomerID));
GO
PRINT 'Creating table Items ...';
CREATE TABLE Items (
ItemID INTEGER NOT NULL IDENTITY ,
ItemName VARCHAR(150) ,
ItemType VARCHAR(150) ,
Price FLOAT ,
PRIMARY KEY(ItemID));
PRINT 'Creating table Rents ...';
CREATE TABLE Rents (
RentID INTEGER NOT NULL IDENTITY ,
ItemID INTEGER NOT NULL ,
CustomerID INTEGER NOT NULL ,
NumberOfDays INTEGER ,
Price FLOAT ,
PRIMARY KEY(RentID),
FOREIGN KEY(ItemID)
REFERENCES Items(ItemID),
FOREIGN KEY(CustomerID)
REFERENCES Customer(CustomerID)
);
GO
CREATE INDEX Booking_FKIndex2 ON Rents(ItemID);
GO
CREATE INDEX Booking_FKIndex3 ON Rents(CustomerID);
GO
PRINT 'Creating table Payments ...';
CREATE TABLE Payments (
PaymentID INTEGER NOT NULL IDENTITY ,
RentID INTEGER NOT NULL ,
CustomerID INTEGER NOT NULL ,
Amount FLOAT ,
PRIMARY KEY(PaymentID),
FOREIGN KEY(RentID)
REFERENCES Rents(RentID),
FOREIGN KEY(CustomerID)
REFERENCES Customer(CustomerID)
);
GO
CREATE INDEX Booking_FKIndex2 ON Payments(RentID);
GO
CREATE INDEX Booking_FKIndex3 ON Payments(CustomerID);
GO
14
PRINT 'Populating the Customer table'
SET IDENTITY_INSERT [dbo].[Customer] ON
Insert Into Customer (CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Derek Banas', '123 lindsay street Victoria 3000'),
(2, 'David Beckham', '123 london road Victoria 3000'),
(3, 'Cristiano', '56 portugal road'),
(4, 'Phillip Lahm', 'german road'),
(5, 'Chris Evans', '32 centre street Victoria 3000'),
(6, 'Jhonny Evans', '45 manchester street'),
(3, 'John P Smith', '12/1 Flinders St, Melbourne 3000');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Booking] ON
Insert Into Booking (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-08', 'Average'),
(9, 2, '2018-12-09', 'Highly satisfying'),
(10, 2, '2018-05-01', 'Better than the previous one'),
(11, 4, '2017-09-29', 'Excellent'),
(12, 4, '2017-03-24', 'Fine'),
(13, 4, '2017-10-30', 'Great');
SET IDENTITY_INSERT [dbo].[Booking] OFF
GO
PRINT 'Populating the Items table'
SET IDENTITY_INSERT [dbo].[Items] ON
Insert Into Items (ItemID, ItemName, ItemType, Price)
VALUES
(1, 'Arm resting', 'inflatable', 16),
(2, 'Foldong', 'chairs', 36),
(3, 'Portable', 'inflatable', 24);
SET IDENTITY_INSERT [dbo].[Items] OFF
GO
PRINT 'Populating the Rents table'
SET IDENTITY_INSERT [dbo].[Rents] ON
Insert Into Rents (RentID, ItemID, CustomerID, NumberOfDays, Price)
VALUES
(1, 1, 1, 54, 12),
(2, 2, 2, 87, 23),
(3, 3, 4, 84, 13);
SET DENTITY_INSERT [dbo].[Rents] OFF
GO
PRINT 'Populating the Payments table'
SET IDENTITY_INSERT [dbo].[Payment] ON
Insert Into Payment (PaymentID, RentID, CustomerID, Amount)
VALUES
(1, 1, 1, 11),
(2, 2, 2, 12),
(3, 3, 4, 99);
SET IDENTITY_INSERT [dbo].[Payment] OFF
GO
15
Select Customer.CustomerName
From Customer Inner Join Rents on Customer.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable';
16
Select Customer.CustomerName
From Customer Inner Join Rents on Customer.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable' Or Items.ItemType ='Chairs';
17
Select Customer.CustomerName, COUNT (Booking.BookingDate)
From Customer Inner Join Booking on Customer.CustomerID = Booking.CustomerID
Group by Customer.CustomerName;
18
Select Customer.CustomerName, SUM (Payments.Amount) As TotalAmount
From Customer Inner Join Payments on Customer.CustomerID = Payments.CustomerID
Group by Customer.CustomerName;
Task 3
19
For the database implmetation an ER diagram is very essential. In addition to his it should also be noted that the designing of a database without an ER diagram becomes very difficult for the designer. The ER Diagram is fundamentally a kind of flowchart that aides in giving the plan to the individual who is outlining the database about entities and attributes that are to be executed in the database. The outline of the ER chart likewise gives the database designer the idea of the datatypes that are to be executed in the framework. The ER Diagram likewise helps the association in keeping up the information and actualizes the business rules of the framework. 20
The organization is looking to implement a database for the business procedures but the storage of the credit card details can become very risky for the organization to store the details. The storage of the details of the card would likewise make them helpless and the consumer loyalty of the organization would be hampered by this. Nonetheless, it ought to be noticed that the storage of the addresses of the clients would not hamper the association to a great extent anyway it ought to be remembered that the association should not release the data to the externals to the system. Subsequently, there are two distinct characteristics that have two unique criteria totally and henceforth the circumstance requires the database designer to deal with the circumstance as needs be.
Buy Itda1001 Database Fundamentals: Party Hire Assessment Answers Online
Talk to our expert to get the help with Itda1001 Database Fundamentals: Party Hire Assessment Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
