Database Implementation for PartyKids

Verified

Added on  2023/06/11

|21
|1513
|397
AI Summary
This article discusses the implementation of a database for PartyKids, an event management company. It includes tasks such as creating tables, populating data, and executing queries. The article also highlights the importance of an ER diagram and legal issues related to storing customer data.
tabler-icon-diamond-filled.svg

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
Running head: DATABASE IMPLEMENTATION
Database Implementation
Name of the Student:
Name of the University:
Author Note
tabler-icon-diamond-filled.svg

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
1
DATABASE IMPLEMENTATION
Task 1
ER Diagram
Task 2
Task 2a
1
PRINT 'Creating database.';
CREATE DATABASE PartyKids;
GO
PRINT 'Creating table Customer ...';
Document Page
2
DATABASE IMPLEMENTATION
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, 'Cristiano Ronalado', '123 Porto street'),
(2, 'Pedro Rodriguez', 'benson road'),
(3, 'John Smith', '12/1 Flinders St, Melbourne 3000'),
(4, 'Adil Rashid', 'BJ road'),
(5, 'Michael owen', '32 centre street'),
(6, 'Robie Fowler', '45 benton street'),
Document Page
3
DATABASE IMPLEMENTATION
(7, 'Lionel Messi', '56 avenue road');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
3
Select * from Customers;
4
Print 'Updating columns';
UPDATE dbo.Customer
SET CustomerAddress = ' 15/1 Flinders Street'
WHERE CustomerName = 'John P Smith'
tabler-icon-diamond-filled.svg

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
4
DATABASE IMPLEMENTATION
GO
Select CustomerName, CustomerAddress From Customer;
5
Select *
From Customer
Where CustomerName LIKE 'J%';
Document Page
5
DATABASE IMPLEMENTATION
6
Select *
From Customer
Where CustomerAddress LIKE '%3000%';
Document Page
6
DATABASE IMPLEMENTATION
7
Delete From Customer
Where CustomerName = 'John P Smith';
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
7
DATABASE IMPLEMENTATION
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),
Document Page
8
DATABASE IMPLEMENTATION
);
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-09', 'Excellent'),
(9, 2, '2017-12-24', 'It was okay'),
(10, 2, '2018-04-30', 'Fine'),
(11, 4, '2017-09-21', 'Excellent'),
(12, 4, '2018-03-28', 'Fine'),
(13, 4, '2017-09-01', 'It was okay');
SET IDENTITY_INSERT [dbo].[Booking] OFF
GO
9
Document Page
9
DATABASE IMPLEMENTATION
Delete From Customers
Where CustomerID = 3;
10
Select CustomerID, BookingID, BookingDate, CustomerFeedback
From Bookings
Group By CustomerID, BookingID, BookingDate, CustomerFeedback;
tabler-icon-diamond-filled.svg

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
10
DATABASE IMPLEMENTATION
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);
Document Page
11
DATABASE IMPLEMENTATION
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 ...';
Document Page
12
DATABASE IMPLEMENTATION
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(ItemID),
FOREIGN KEY(ItemID)
REFERENCES Items(ItemID),
FOREIGN KEY(CustomerID)
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
13
DATABASE IMPLEMENTATION
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)
Document Page
14
DATABASE IMPLEMENTATION
REFERENCES Customer(CustomerID)
);
GO
CREATE INDEX Booking_FKIndex2 ON Payments(RentID);
GO
CREATE INDEX Booking_FKIndex3 ON Payments(CustomerID);
GO
14
PRINT 'Populating the Customers table'
SET IDENTITY_INSERT [dbo].[Customers] ON
Insert Into Customers(CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Cristiano Ronalado', '123 Porto street'),
(2, 'Pedro Rodriguez', 'benson road'),
(3, 'John Smith', '12/1 Flinders St, Melbourne 3000'),
(4, 'Adil Rashid', 'BJ road'),
(5, 'Michael owen', '32 centre street'),
Document Page
15
DATABASE IMPLEMENTATION
(6, 'Robie Fowler', '45 benton street'),
(7, 'Lionel Messi', '56 avenue road');
SET IDENTITY_INSERT [dbo].[Customers] OFF
GO
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Bookings] ON
Insert Into Bookings (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-09', 'Excellent'),
(9, 2, '2017-12-24', 'It was okay'),
(10, 2, '2018-04-30', 'Fine'),
(11, 4, '2017-09-21', 'Excellent'),
(12, 4, '2018-03-28', 'Fine'),
(13, 4, '2017-09-01', 'It was okay');
SET IDENTITY_INSERT [dbo].[Bookings] OFF
GO
tabler-icon-diamond-filled.svg

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
16
DATABASE IMPLEMENTATION
PRINT 'Populating the Items table'
SET IDENTITY_INSERT [dbo].[Items] ON
Insert Into Items (ItemID, ItemName, ItemType, Price)
VALUES
(1, 'cushion', 'inflatable', 10),
(2, 'small plastic', 'chairs', 2),
(3, 'flexible', 'others', 23);
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, 79, 12),
(2, 1, 2, 87, 11),
(3, 2, 4, 56, 13);
Document Page
17
DATABASE IMPLEMENTATION
SET IDENTITY_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, 28),
(2, 2, 2, 34),
(3, 3, 4, 43);
SET IDENTITY_INSERT [dbo].[Payment] OFF
GO
15
Select Customers.CustomerName
From Customers Inner Join Rents on Customers.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable';
Document Page
18
DATABASE IMPLEMENTATION
16
Select Customers.CustomerName
From Customers Inner Join Rents on Customers.CustomerID = Rents.CustomerID
Inner Join Items On Items.ItemID = Rents.ItemID
Where Items.ItemType = 'inflatable' Or Items.ItemType ='Chairs';
17
Select Customers.CustomerName, COUNT (Bookings.BookingDate)
From Customers Inner Join Bookings on Customers.CustomerID = Bookings.CustomerID
Group by Customers.CustomerName;
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
19
DATABASE IMPLEMENTATION
18
Select Customers.CustomerName, SUM (Payment.Amount) As TotalAmount
From Customers Inner Join Payment on Customers.CustomerID = Payment.CustomerID
Group by Customers.CustomerName;
Document Page
20
DATABASE IMPLEMENTATION
Task 3
19
It is very important that the ER diagram is developed before the development of the
database. It provides an idea to the developer who is developing the database. In addition to
this, the ER diagram also helps in the maintaining the business rules of the organization.
20
There is various type of legal issues that are to be faced if the records of the card details
of the customers are organized in the system. In addition to tis, the storage of the details of the
address might not bring about any legal issues, but the details of the card might have some
legal issues with it.
chevron_up_icon
1 out of 21
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]