Sample SQL Database Creation And Population
Creating the PartyKids Database
Task 1 ER DiagramTask 2 Task 2a
1
PRINT ‘Creating database.’;
CREATE DATABASE PartyKids;
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, ‘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’),
(7, ‘Lionel Messi’, ’56 avenue road’);
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
3
Select * from Customers;Print ‘Updating columns’;
UPDATE dbo.Customer
SET CustomerAddress = ‘ 15/1 Flinders Street’
WHERE CustomerName = ‘John P Smith’
GO
Select CustomerName, CustomerAddress From Customer;Select *
From Customer
Where CustomerName LIKE ‘J%’;6
Select *
From Customer
Where CustomerAddress LIKE ‘%3000%’;7
Delete From Customer
Where CustomerName = ‘John P Smith’;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-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
Delete From Customers
Where CustomerID = 3;10
Select CustomerID, BookingID, BookingDate, CustomerFeedback
From Bookings
Group By CustomerID, BookingID, BookingDate, CustomerFeedback;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 ,
Populating the Customer Table
NumberOfDays INTEGER ,
Price FLOAT ,
PRIMARY KEY(ItemID),
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 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’),
(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
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);
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’;
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’;Select Customers.CustomerName, COUNT (Bookings.BookingDate)
From Customers Inner Join Bookings on Customers.CustomerID = Bookings.CustomerID
Group by Customers.CustomerName;Select Customers.CustomerName, SUM (Payment.Amount) As TotalAmount
From Customers Inner Join Payment on Customers.CustomerID = Payment.CustomerID
Group by Customers.CustomerName;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.
