CSE3330 Project #1

Friday October 5, before midnight
Worth 4% of the final grade

Description

This project must be done individually. No copying is permitted. This is the first project from a series of three. You are going to build a database application in three stages:

first project (worth 4% of the final grade): create and populate the database. Create the database in MySQL and insert some data.
second project (worth 8% of the final grade): formulate queries given in English into SQL and evaluate them against the database.
third project (worth 13% of the final grade): code the application program using C or Java.

Platform

You need to use phpMyAdmin to manage your database from a web browser. Open https://omega.uta.edu/myadmin/ in a web browser. You should be prompted for a username and password. Your MySQL username is your NetID user name and your password is your initial password that was created when you first started the university but in lowercase. It's not your current NetID password. If you require assistance with phpMyAdmin, please contact the OIT Computing Helpdesk. There is online documentation available from this web page. On the left-side menu, select the database under your user name. Then you can create new tables by specifying the table name and number of rows. Note that the default table storage engine in phpAdmin is MyISAM, which does not use real foreign keys. You should not use MyISAM. You should use InnoDB as a type for a table Storage Engine. Recall that each table must have exactly one primary key, which may consist of more than one attributes. To specify that an attribute is a foreign key, you go to the Structure menu of the table and select "Relation view". Then you edit the Internal Relations, which are basically the foreign keys.

Domain

You have been hired to set-up a database for www.cheapbooks.com, which sales books on the web (much like www.amazon.com). A requirements analysis that was conducted has identified a number things about the operations and goals of CheapBooks. Each book sold by CheapBooks has a title, a price, a year of issue, a publisher, and a unique ISBN. It is written by one or more authors, but, of course, each author may have written multiple books. Although not available to customers, CheapBooks has information about each author, which may include name, address, and phone number. Each book is stocked in book warehouses and CheapBooks wants to keep track how many copies of a book each warehouse has, in order to check it's availability and complete the customer orders. Each warehouse has a Code, a name, an address, and a phone number. When a customer starts a session with CheapBooks through its web site, she is assigned an empty shopping basket so that she can make multiple purchases each time. The customer session is then to select books and insert them into the shopping basket. The shopping basket may contain multiple copies of multiple books. Note that, at any time you may have multiple customers buying books and a particular customer may have used multiple shopping baskets. At checkout, the total price of the customer's shopping basket is calculated and the customer is charged (assume that shipping cost is zero). At checkout, each warehouse involved in the purchase is received a single shipping order that contains the shipping information of the customer, and for each book in her shopping basket stocked in this warehouse, the number of copies bought. When the customer's shopping session ends, all information about the shopping basket is removed but the information about the warehouse shipping order is kept.

Requirements

Create your CheapBook database using phpMyAdmin based on the following schema:

Author ( ssn, name, address, phone )                      info about a book author
Book ( ISBN, title, year, price, publisher )              info about a book
WrittenBy ( ssn, ISBN )                                   relates books with authors
Warehouse ( warehouseCode, name, address, phone )         info about a warehouse
Stocks ( ISBN, warehouseCode, number )                    what books a warehouse stocks
Customer ( customerName, address, email, phone )          info about a customer
ShoppingBasket ( basketID, customerName )                 relates customers with baskets
Contains ( ISBN, basketID, number )                       the content of a shopping basket
ShippingOrder ( ISBN, warehouseCode, customerName, number )   books shipped from a warehouse to a customer

In this schema, foreign keys have the same name as the primary keys they reference. More specifically, the foreign keys are:

WrittenBy.ssn → Author.ssn
WrittenBy.ISBN → Book.ISBN
Stocks.ISBN → Book.ISBN
Stocks.warehouseCode → Warehouse.warehouseCode
ShoppingBasket.customerName → Customer.customerName
Contains.ISBN → Book.ISBN
Contains.basketID → ShoppingBasket.basketID
ShippingOrder.ISBN → Book.ISBN
ShippingOrder.warehouseCode → Warehouse.warehouseCode
ShippingOrder.customerName → Customer.customerName

Then insert some data into the tables. Each table should contain at least four rows. Try to write your data in such way that you can get meaningful answers for various queries (such as "For each book, print the book ISBN, title, and price, along with the total number of copies of this book stocked in all warehouses"). You should declare all keys and foreign keys in your schema.

What to Submit

You must handin your relational schema as follows. From the phpMyAdmin, select your database and then select Export. Note that this exports the entire database, not a single table. From the Export form, check the Relations box at the "Add into comments" menu and the "Save as file" box at the bottom. Then push Go and save your file (the filename should be your username .sql). Then you use the following form to submit this file electronically. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated.

Last modified: 09/20/12 by Leonidas Fegaras