ProductsLogo
LogoStudy Documents
LogoAI Grader
LogoAI Answer
LogoAI Code Checker
LogoPlagiarism Checker
LogoAI Paraphraser
LogoAI Quiz
LogoAI Detector
PricingBlogAbout Us
logo

Database Implementation: ER Diagram, Tables, and Queries

Verified

Added on  2023/06/11

|19
|1655
|417
AI Summary
This article discusses the implementation of a database for PartyHire, including creating an ER diagram, tables, and queries. It also highlights the importance of an ER diagram in database design and the risks associated with storing credit card details. The article is relevant for students studying database concepts and implementation.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
Running head: DATABASE CONCEPTS
Database Concepts
Name of the Student:
Name of the University:
Author Note

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 PartyHire;
GO
PRINT 'Creating table Customer ...';
CREATE TABLE Customer (
Document Page
2
DATABASE IMPLEMENTATION
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
Document Page
3
DATABASE IMPLEMENTATION
GO
3
Select * from Customer;
4
Print 'Updating columns';
UPDATE dbo.Customer
SET CustomerAddress = ' 15/1 Flinders Street'
WHERE CustomerName = 'John P Smith'
GO
Select CustomerName, CustomerAddress From Customer;

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
4
DATABASE IMPLEMENTATION
5
Select *
From Customer
Where CustomerName LIKE 'J%';
6
Select *
From Customer
Where CustomerAddress LIKE '%3000%';
7
Document Page
5
DATABASE IMPLEMENTATION
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
Document Page
6
DATABASE IMPLEMENTATION
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
9
Delete From Customer
Where CustomerID = 2;

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
7
DATABASE IMPLEMENTATION
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
Document Page
8
DATABASE IMPLEMENTATION
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 ...';
Document Page
9
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(RentID),
FOREIGN KEY(ItemID)
REFERENCES Items(ItemID),
FOREIGN KEY(CustomerID)

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
10
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)
REFERENCES Customer(CustomerID)
Document Page
11
DATABASE IMPLEMENTATION
);
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');
Document Page
12
DATABASE IMPLEMENTATION
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

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
13
DATABASE IMPLEMENTATION
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'
Document Page
14
DATABASE IMPLEMENTATION
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
Document Page
15
DATABASE IMPLEMENTATION
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';

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
16
DATABASE IMPLEMENTATION
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
Document Page
17
DATABASE IMPLEMENTATION
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
Document Page
18
DATABASE IMPLEMENTATION
criteria totally and henceforth the circumstance requires the database designer to deal with the
circumstance as needs be.
1 out of 19
[object Object]

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

Available 24*7 on WhatsApp / Email

[object Object]