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

The Database Design

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