PostgreSQL Queries
Budget: ₹600 – ₹1,500 INR
Amazon plc is a bookshop that sells book both over the Internet and through retail
shops. Amazon maintains a central DB containing details of customers (both
individuals and retail bookshops), books and current orders. The central DB is
held on a server computer that processes transactions coming from Internet users or
from shops. Transactions are held in an input queue and processed by an
application program on the server.
write SQL statements for the transactions Assume that you are asked to
develop a pilot database application and naturally the exercise is greatly simplified
compared to a real business.
You will set up your ‘Amazon DB'
as a set of tables. You are required to write the Amazon server application
program and not the client software for use on the Internet or in the shops. For this exercise
you will simulate the queue of input transactions by reading a stream of transactions
from a text file.
You may, of course, use ur own facilities to develop ur program but the final
version must use PostgreSQL.
Application Program Functionality
The database comprises the following tables:
book (bno, title, author, category, price, sales)
customer (cno, name, address, balance)
bookOrder (cno, bno, orderTime, qty)
Notes - The above tables are designed based on the following requirements:
The book table holds data on each book that Amazon offers for sale.
The customer table holds details of all Amazon customers.
The bookOrder table holds details of current orders placed by Amazon customers.
The order details are archived periodically and the bookOrder table is then emptied.
Each order is for a single customer and for a single title but may be for multiple copies
of the title.
bno is an Amazon's six-digit book number (100000 <= bno <= 999999) used by them
to identify a book offered for sale.
title is the title of the book;
author is the author(s) of the book;
category is one of Science, Lifestyle, Arts or Leisure;
price is the selling price of the book;
sales is a count of the number of copies of the book which have been sold. This is zero
when a new book is inserted into the DB;
cno is a six-digit number (100000 <= bno <= 999999) used to identify a customer;
name is the name of the customer;
address is the address of the customer;
balance is the amount of money owed to Amazonia by the customer for books ordered.
This is always zero when a new customer is inserted into the database;
orderTime is a timestamp recording the instant in time when an order was put on the
Amazon database;
qty is the number of copies of a specific book ordered by a customer.
Transactions of interest: At this stage, the transactions of interest to us for
Amazon in this exercise are:
E. Place an order for a customer for a specified number of copies of a book. The
copies ordered are assumed to be sold and will have to be paid for. Books are not
supplied on 'sale or return' terms of business. Check if the book and customer is
already in the system. If there is book entry or customer information is not available
then create required entries and then perform the operation. Create a trigger.
F. Record a payment by a customer. The payment is subtracted from the customer's
balance.
G. Find details of customers who have current orders for a book with a given text
fragment in the book title. For example, find customers with orders for books with
'Python' in the title. This transaction produces a report with lines showing the full title
of a book ordered, the customer name and the customer address relevant to the
order. The report is to be sorted by title and then by customer name. Create a procedure.
H. Find details of books ordered by a specified customer. The report will show the
name of the customer followed by, for each book, the book number, title and author,
sorted by book number. Create a procedure.
shops. Amazon maintains a central DB containing details of customers (both
individuals and retail bookshops), books and current orders. The central DB is
held on a server computer that processes transactions coming from Internet users or
from shops. Transactions are held in an input queue and processed by an
application program on the server.
write SQL statements for the transactions Assume that you are asked to
develop a pilot database application and naturally the exercise is greatly simplified
compared to a real business.
You will set up your ‘Amazon DB'
as a set of tables. You are required to write the Amazon server application
program and not the client software for use on the Internet or in the shops. For this exercise
you will simulate the queue of input transactions by reading a stream of transactions
from a text file.
You may, of course, use ur own facilities to develop ur program but the final
version must use PostgreSQL.
Application Program Functionality
The database comprises the following tables:
book (bno, title, author, category, price, sales)
customer (cno, name, address, balance)
bookOrder (cno, bno, orderTime, qty)
Notes - The above tables are designed based on the following requirements:
The book table holds data on each book that Amazon offers for sale.
The customer table holds details of all Amazon customers.
The bookOrder table holds details of current orders placed by Amazon customers.
The order details are archived periodically and the bookOrder table is then emptied.
Each order is for a single customer and for a single title but may be for multiple copies
of the title.
bno is an Amazon's six-digit book number (100000 <= bno <= 999999) used by them
to identify a book offered for sale.
title is the title of the book;
author is the author(s) of the book;
category is one of Science, Lifestyle, Arts or Leisure;
price is the selling price of the book;
sales is a count of the number of copies of the book which have been sold. This is zero
when a new book is inserted into the DB;
cno is a six-digit number (100000 <= bno <= 999999) used to identify a customer;
name is the name of the customer;
address is the address of the customer;
balance is the amount of money owed to Amazonia by the customer for books ordered.
This is always zero when a new customer is inserted into the database;
orderTime is a timestamp recording the instant in time when an order was put on the
Amazon database;
qty is the number of copies of a specific book ordered by a customer.
Transactions of interest: At this stage, the transactions of interest to us for
Amazon in this exercise are:
E. Place an order for a customer for a specified number of copies of a book. The
copies ordered are assumed to be sold and will have to be paid for. Books are not
supplied on 'sale or return' terms of business. Check if the book and customer is
already in the system. If there is book entry or customer information is not available
then create required entries and then perform the operation. Create a trigger.
F. Record a payment by a customer. The payment is subtracted from the customer's
balance.
G. Find details of customers who have current orders for a book with a given text
fragment in the book title. For example, find customers with orders for books with
'Python' in the title. This transaction produces a report with lines showing the full title
of a book ordered, the customer name and the customer address relevant to the
order. The report is to be sorted by title and then by customer name. Create a procedure.
H. Find details of books ordered by a specified customer. The report will show the
name of the customer followed by, for each book, the book number, title and author,
sorted by book number. Create a procedure.