CSC220 Lab 5 MySQL

Creating the Databases and the Users

• This section is just for completeness and does not have to be performed. Only the admin of the database can create databases. Just read this section and start working on the next one.

• for all accounts from aa to ao, run the query

CREATE DATABASE `220a-ac`CHARACTER SET utf8 COLLATE utf8_general_ci;

• Similarly, create users with same name

 INSERT INTO mysql.user ( HOST, USER, PASSWORD, select_priv, insert_priv,
      update_priv, delete_priv,  create_priv, drop_priv, reload_priv, shutdown_priv, 
      process_priv,  file_priv, grant_priv, references_priv, index_priv, alter_priv, 
      show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, 
      execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv,
      create_routine_priv, alter_routine_priv, create_user_priv )
      VALUES ( 'localhost', '220a-ac', PASSWORD('AStrangeWordUsedForPassword'), 
      'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
      'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );

• Then make that user get all privileges on the database with the same name

  DELETE FROM mysql.db WHERE USER='220a-ac' AND HOST='localhost';
  DELETE FROM mysql.tables_priv WHERE USER='220a-ac' AND HOST='localhost';
  DELETE FROM mysql.columns_priv WHERE USER='220a-ac' AND HOST='localhost';
  DELETE FROM mysql.procs_priv WHERE USER='220a-ac' AND HOST='localhost';
  INSERT INTO mysql.db(HOST, db, USER, Select_priv, Insert_priv, Update_priv, "
          Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv,"
          Alter_priv, create_tmp_table_priv, lock_tables_priv, Create_view_priv, Show_view_priv,"
          Create_routine_priv, Alter_routine_priv, Execute_priv) "
         VALUES( 'localhost', '220a-ac', '220a-ac', 'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',"
          'Y', 'Y', 'Y', 'Y' );

Login to the Database

This section is only visible to computers located at Smith College

Create Tables

• You are going to create 3 tables: users, orders, and items
  • users contains an Id and a name field.
  • orders contains an Id for the order, an Id for the user ordering, and an item ordered
  • items contains and Id, a name, and a price