Lab 9

Come see me to pick up your MySql Account info

For this lab you will write to a txt file named lab9.txt. This lab will consist of a series of questions and code snippet tasks.

1. What is a RDBMS?
2. What is a Database Schema?
3. What is an ER model?
4. What is a one to one relationship?
5. What is a one to many relationship?
6. What is a many to many relationship?
7. What is a tuple?
8. What is the use for a primary key?
9. Why should a primary key be unique?
10. What is SQL?
11. Write the SQL command that would create a table called 'Customers' that has attributes for 'c_id', 'c_firstName', 'c_lastName','c_address', 'c_city', 'c_state', 'c_zip', and 'c_email'. List the 'c_id' as an integer non null auto incrementing primary key attribute.
12. Write the SQL command that would alter the 'Customers' table by adding a new column for 'c_age' unsigned tiny integer.
13. Write the SQL command that would create a table called 'Products' that has attributes for 'p_id', 'p_title', 'p_description', 'p_price'. List the 'p_id' as an integer non null auto incrementing primary key attribute. Set the data type of 'p_price' to decimal(13,4).
14. Write the SQL command that would add at least 3 records to the 'Customers' table.
15. Write the SQL command that would add at least 3 records to the 'Products' table.
16. Write the SQL command that would create a table called 'Orders' that has attributes for 'o_id', 'o_cid', 'o_amount', 'o_date'. List the 'o_id' as an integer non null auto incrementing primary key attribute. Set the data type of 'o_amount' to decimal(13,4). Set the 'o_date' to an 8 byte data type that holds the format YYYY-MM-DD HH:MM:SS
17. Using the data you've provided for your Customers and Products table, write the SQL command to add at least 3 records to the 'Orders' table.
18. Write the SQL command that would select all of your Customers.
19. Write the SQL command that would select all of your Products.
20. Write the SQL command that would update one of your Products price to 49.99.
21. Write the SQL command that would select all of your Products that are less than $100.
22. Write the SQL command that would select all Orders where the order customer id is that of your first Customer.
23. Write the SQL command that would select all Orders above $100.
24. Write the SQL command that would delete your last Product record.
25. Assume there is a table called DropThisTable_GoneForever, write the SQL command that would drop that table.

Put this file into your ~/3680_S19/wk9/ depository directory, which I'll extract and copy at the time of the due date.