July 23, 2014
 
 
RSSRSS feed

Apples and Oranges, Part II: A Linux DBMS Comparison - page 5

The Database Design

  • November 18, 1999
  • By Matthias Warkus

To compare the three database managers, I decided to write a test client in C for the purpose of performing transactions on the bookstore database. As a result, I implemented a couple of actions to be able to compare the APIs. For the performance comparisons, I then implemented them fully and added a noninteractive mode to the client so it could run on its own, generating random data and randomly executing transactions.

I decided to implement the following actions on the sample database:

  • Adding a new book: INSERT INTO BOOK (...) VALUES (...);
  • Deleting an existing book: DELETE FROM BOOK WHERE ARTICLE_NO=...;
  • Adding a customer: INSERT INTO CUSTOMER (...) VALUES (...);
  • Deleting a customer: DELETE FROM CUSTOMER WHERE CUSTOMER_NO=...;
  • A customer ordering books: INSERT INTO BOOKORDER (...) VALUES (...); INSERT INTO ORDER_POSITION (...) VALUES (...);;
  • A customer rating a book: INSERT INTO RATING (...) VALUES (...);
  • Changing an order's status: UPDATE BOOKORDER SET STATUS=... WHERE ORDER_NO=...;

Then, the following reports can be generated:

  • List of books: SELECT * FROM BOOK;
  • List of customers: SELECT * FROM CUSTOMER;
  • List of deliveries underway, sorted by status: SELECT * FROM BOOKORDER ORDER BY STATUS;
  • Profit margin for the books, with final average: SELECT RETAIL_PRICE-WHOLESALE_PRICE FROM BOOK; SELECT AVG(RETAIL_PRICE-WHOLESALE_PRICE) FROM BOOK;
  • Comments, ratings and average rating for a book: SELECT * FROM RATING WHERE ARTICLE_NO=...; SELECT AVG(SCORE) FROM RATING WHERE ARTICLE_NO=...;
Sitemap | Contact Us