Homework #8: Building a Database

• This assignment will demonstrate:
  • Understanding a database schema
  • Building a database and tables in MySQL
  
  
• Main steps in this assignment:
  1. Log in to your hosting service's Control Panel.
  2. If you are using Freehostia, click on the "MySQL" icon in the "Manage your sites / databases" panel. You can also click on "Databases, MySQL Databases" in the top main menu.
  3. If you haven't already created your database, click the "ADD MYSQL" button on the MySQL page. After you have created your database, make sure you write down the database name and password.
  4. Make the five (5) tables that are in the class schema, in your database in the MySQL server on the class Web server.
  5. Make the ID fields (such as ProductID) as data type bigint fields that are auto_increment and index type Primary.
  6. Make the fields that will be a foreign key to be data type bigint. Also Select "INDEX" in the "Index" dropdown list.
  7. Make the "Order_Date" and "Delivery_Date" fields in the "Orders" table as date data types.
  8. Make the "tax_rate" field in the "Orders" table as a float field.
  9. Make the "Quantity" field in the "Order_Items" table, and the "Quantity_In_Stock" field in the "Products" table, as int data types.
  10. Make the "Order_Total" field in the "Orders" table, and the "Wholesale_Price" and "Retail_Price" fields in the "Products" table; as double data types.
  11. Make the "Password" field in the "Customers" table as a varchar(255) field.
  12. All of the other fields should be varchar data types with a length of 50. You may, if you wish, make the State field char with a length of 2, and the Zip field as char with a length of 10, but this is an optional setting. varchar will do for all of the text fields.
  13. Make the data relationships that are shown on the schema.
  14. Use the "Insert" tab in phpMyAdmin to add at least one record to your Suppliers table. Then put at least four (4) rows of data into your Products table. Use the SupplierID value from the Suppliers table, in the foreign key field of the Products table.


• After you have added some records to your Suppliers and Products tables, you can use the "Browse" tab for each table in phpMyAdmin to review the records.


• When you are satisfied that you have good data in the Suppliers and Products tables, you need to export these two tables and email to me the text files that you exported.
  1. In phpMyAdmin, click on the Suppliers table name on the left.
  2. Click on the "Export" tab.
  3. Click the "Go" button. A text file of type .sql will be downloaded to your computer.
  4. Repeat the previous steps for the Products table.
  5. Send me an email with the two .sql files attached.


• Checklist:
  • Did you make all five tables that are in the schema?
  • Did you make a Primary Key for each table?
  • Did you make all four data relationships that are in the schema?
  • Did you put at least one record into your Suppliers table, and least four records of data into your Products table?