Database Fundamentals: ER Diagram, Tables, and Queries

This article covers the basics of database fundamentals, including creating an ER diagram, creating tables, populating tables, updating columns, and querying data. It also discusses the importance of database security and ethical considerations. Course code ITDA1001.

Running head: DATABASE FUNDAMENTALS
ITDA1001 - Database Fundamentals
Name of the Student:
Name of the University:
Author Note

1
DATABASE FUNDAMENTALS
Task 1
ER Diagram
Task 2
Task 2a
1
PRINT 'Creating database.';
CREATE DATABASE PartyKids;
GO

2
DATABASE FUNDAMENTALS
PRINT 'Creating table Customer ...';
CREATE TABLE Customer (
CustomerID INTEGER NOT NULL IDENTITY ,
CustomerName VARCHAR(45) ,
CustomerAddress VARCHAR(150) ,
PRIMARY KEY(CustomerID));
GO

3
DATABASE FUNDAMENTALS
2
PRINT 'Populating the Customer table'
SET IDENTITY_INSERT [dbo].[Customer] ON
Insert Into Customer(CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Rayan Renolds', '123 denver street'),
(2, 'kevin spacey', '123 lee road'),
(3, 'John P Smith', '12/1 Flinders St, Melbourne 3000'),
(4, 'Phillip Lahm', 'BJ road'),
(5, 'Kevin Owens', '32 centre street'),

4
DATABASE FUNDAMENTALS
(6, 'Robie Fowler', '45 benton street'),
(7, 'Lionel Messi', '56 avenue road');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
3
Select * from Customer;

5
DATABASE FUNDAMENTALS
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
DATABASE FUNDAMENTALS
5
Select *
From Customer
Where CustomerName LIKE 'J%';

7
DATABASE FUNDAMENTALS
6
Select *
From Customer
Where CustomerAddress LIKE '%3000%';

8
DATABASE FUNDAMENTALS
7
Delete From Customer
Where CustomerName = 'John P Smith';

9
DATABASE FUNDAMENTALS
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

10
DATABASE FUNDAMENTALS
PRINT 'Populating the Booking table'
SET IDENTITY_INSERT [dbo].[Booking] ON
Insert Into Booking (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-08', 'Excellent'),
(9, 2, '2017-12-09', 'It was okay'),
(10, 2, '2018-04-01', 'Fine'),
(11, 4, '2017-09-24', 'Excellent'),
(12, 4, '2018-03-24', 'Fine'),
(13, 4, '2017-09-30', 'It was okay');

11
DATABASE FUNDAMENTALS
SET IDENTITY_INSERT [dbo].[Booking] OFF
GO
9
Delete From Customer
Where CustomerID = 2;
10
Select CustomerID, BookingID, BookingDate, CustomerFeedback
From Booking
Group By CustomerID;
11

12
DATABASE FUNDAMENTALS
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);

13
DATABASE FUNDAMENTALS
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 ...';

14
DATABASE FUNDAMENTALS
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)
REFERENCES Customer(CustomerID)
);
GO
CREATE INDEX Booking_FKIndex2 ON Rents(ItemID);
GO
CREATE INDEX Booking_FKIndex3 ON Rents(CustomerID);
GO

15
DATABASE FUNDAMENTALS
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

16
DATABASE FUNDAMENTALS
14
PRINT 'Populating the Customers table'
SET IDENTITY_INSERT [dbo].[Customers] ON
Insert Into Customers(CustomerID,CustomerName, CustomerAddress)
VALUES
(1, 'Rayan Renolds', '123 denver street'),
(2, 'kevin spacey', '123 lee road'),
(3, 'John P Smith', '12/1 Flinders St, Melbourne 3000'),
(4, 'Phillip Lahm', 'BJ road'),
(5, 'Kevin Owens', '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

17
DATABASE FUNDAMENTALS
Insert Into Bookings (BookingID, CustomerID, BookingDate, CustomerFeedback)
VALUES
(8, 1, '2017-11-08', 'Excellent'),
(9, 2, '2017-12-09', 'It was okay'),
(10, 2, '2018-04-01', 'Fine'),
(11, 4, '2017-09-24', 'Excellent'),
(12, 4, '2018-03-24', 'Fine'),
(13, 4, '2017-09-30', '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, 'lean back', 'inflatable', 26),
(2, 'flat', 'chairs', 34),
(3, 'flexible', 'others', 83);

18
DATABASE FUNDAMENTALS
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),

19
DATABASE FUNDAMENTALS
(2, 2, 2, 34),
(3, 3, 4, 43);
SET IDENTITY_INSERT [dbo].[Payment] OFF
GO

20
DATABASE FUNDAMENTALS
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

21
DATABASE FUNDAMENTALS
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;
18
Select Customers.CustomerName, SUM (Payment.Amount) As TotalAmount
From Customers Inner Join Payment on Customers.CustomerID = Payment.CustomerID
Group by Customers.CustomerName;

22
DATABASE FUNDAMENTALS
Task 3
19
The entity relationship diagram is created for the identification of the relationship between the
tables created for the development of the database. It acts as a flow chart and is created in
Microsoft Visio and it helps in adding new ideas concepts for the development of the database
solution. The entities and the attributes are linked with each other and the development of the
ER diagram helps the database development team to identify business rules of the organization
and develop the database aligning the needs. It helps in creating the database table and
identification of the constraint acting on each of the table.
20
For recording the payment of the customer in the database Mr. Pops can face many legal notice
because it would violate the legal rules and regulations. The privacy of the customer can be
affected and the details of the card cannot be stored and it helps in making the system
vulnerable and the customer satisfaction may not be meet. The ethical issues arising in the
solution should be mitigated for the development of the database solution. Storing the
different records of the customer such as the customer address, personal details and the other
information can help in securing the database of the organization. The different entity and the

23
DATABASE FUNDAMENTALS
attributed that can be hampered for storing the details of the customer should be analyzed and
it is the responsibility of the database handler for aligning the database table with the current
needs of the organization. The security of the information is the main criteria because the loss
of the customer information can have an negative impact on the growth of the organization.

24
DATABASE FUNDAMENTALS
Bibliography
Coronel, Carlos, and Steven Morris. Database systems: design, implementation, &
management. Cengage Learning, 2016.
Huang, J., H. Zhou, B. Zhang, and B. Ding. "The Development and Application of the
Orthopaedics Implants Failure Database Software Based on WEB." Zhongguo yi liao qi xie za
zhi= Chinese journal of medical instrumentation 39, no. 5 (2015): 324-6.
Hussain, Mohammed Waheed Uddin. "Design and development of an advanced database
system with multitenant architecture advanced security using transparent data encryption data
redaction." (2015).
Kelly, Anthony E., Richard A. Lesh, and John Y. Baek, eds. Handbook of design research methods
in education: Innovations in science, technology, engineering, and mathematics learning and
teaching. Routledge, 2014.
Klochkov, Yury, Elena Klochkova, Olga Antipova, Ekaterina Kiyatkina, Irina Vasilieva, and
Ekaterina Knyazkina. "Model of database design in the conditions of limited resources."
In Reliability, Infocom Technologies and Optimization (Trends and Future Directions)(ICRITO),
2016 5th International Conference on, pp. 64-66. IEEE, 2016.
Sharma, Shipra, and Balwinder Sodhi. "SDDNet: A Semantic Network for Software Design and
Development Domain Via Graph Database." In International Conference on Conceptual
Structures, pp. 80-88. Springer, Cham, 2016.

25
DATABASE FUNDAMENTALS
Simiu, Emil, Fahim Sadek, S. M. Diniz, Michael A. Riley, S. Jang, and L. W. Lu. Development of
Database-Assisted Design for Wind Loads: Current and Future Research. No. UJNR Proceedings.
2017.
Wongpun, Sukontip, and Sumanta Guha. "Design and Development of an Online Support
System for Elder Care." In Asian Conference on Intelligent Information and Database Systems,
pp. 653-663. Springer, Cham, 2018.