Task 1

CREATE DATABASE PartyKids;

PRINT 'Creating  table Customer ...';

CREATE TABLE Customer (

  CustomerID INTEGER  NOT NULL   IDENTITY ,

  CustomerName VARCHAR(45)    ,

  CustomerAddress VARCHAR(150)      ,

PRIMARY KEY(CustomerID));

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'),

(6, 'Robie Fowler', '45 benton street'),

(7, 'Lionel Messi', '56 avenue road');

SET IDENTITY_INSERT [dbo].[Customer] OFF

Select * from Customer;

Print 'Updating columns';

UPDATE dbo.Customer  

    SET CustomerAddress = ' 15/1 Flinders Street'  

    WHERE CustomerName = 'John P Smith'  

Select CustomerName, CustomerAddress From Customer;

From Customer

Where CustomerAddress LIKE '%3000%';

Delete From Customer

Where CustomerName = 'John P Smith';

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),

CREATE INDEX Booking_FKIndex1 ON Booking(CustomerID);

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');

SET IDENTITY_INSERT [dbo].[Booking] OFF

Delete From Customer

Where CustomerID = 2;

Select CustomerID, BookingID, BookingDate, CustomerFeedback

From Booking

Group By CustomerID;

Select Customer.CustomerName, Booking.BookingDate

From Customer inner join Booking on Customer.CustomerID = Booking.CustomerID

Order By Booking.BookingDate DESC;

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

PRINT 'Creating  table Customer ...';

CREATE TABLE Customer (

  CustomerID INTEGER  NOT NULL   IDENTITY ,

  CustomerName VARCHAR(45)    ,

  CustomerAddress VARCHAR(150)      ,

PRIMARY KEY(CustomerID));

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 ,

  NumberOfDays INTEGER ,

  Price FLOAT      ,

PRIMARY KEY(ItemID),

  FOREIGN KEY(ItemID)

    REFERENCES Items(ItemID),

  FOREIGN KEY(CustomerID)

    REFERENCES Customer(CustomerID)

CREATE INDEX Booking_FKIndex2 ON Rents(ItemID);

CREATE INDEX Booking_FKIndex3 ON Rents(CustomerID);

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)

CREATE INDEX Booking_FKIndex2 ON Payments(RentID);

CREATE INDEX Booking_FKIndex3 ON Payments(CustomerID);

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

PRINT 'Populating the Booking table'

SET IDENTITY_INSERT [dbo].[Bookings] ON

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

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);

SET IDENTITY_INSERT [dbo].[Items] OFF

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

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

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';

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

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.

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 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.

References

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.

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.