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:
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.
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 customerIn 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.customerNameThen 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.
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