Creating PartyKids Database
ER Diagram
Task 2a
1
PRINT ‘Creating database.’;
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 …’;
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′),
Creating Customer Table
(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;
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.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
