CPSC 343 Database Theory and Practice Fall 2017

Data Modification

This time you will populating the library database with data. The database schema is shown below.

BOOK(Book_id,Title,Publisher_name)
BOOK_AUTHORS(Book_id,Author_name)
PUBLISHER(Name,Address,Phone)
BOOK_COPIES(Book_id,Branch_id,No_of_copies)
BOOK_LOANS(Book_id,Branch_id,Card_no,Date_out,Due_date)
LIBRARY_BRANCH(Branch_id,Branch_name,Address)
BORROWER(Card_no,Name,Address,Phone)

Two versions of the database have been set up, named username_library2a and username_library2b. (username is your own username.) All of the tables have been created but no data has been loaded.

Note: Once you've connected to the server with MySQL Workbench, make sure to set the appropriate database as the default schema so that the statements you execute will apply to the correct database.


Data Modification in SQL

Data modification statements can be entered and executed in the Query tab, just like SELECT queries. Do not use the MySQL Workbench data editing interface for this section!

For this part, use username_library2a.

Inserting Data

Write and execute INSERT statements to insert the following data into the PUBLISHER table.

NameAddressPhone
DAW375 Hudson St, New York, NY 10014123-456-7890
Eos10 East 53rd St, New York, NY 10022212-207-7000

Importing Data

If there is very much data, typing in all the INSERT commands gets tedious. The LOAD DATA INFILE statement allows you to load an entire file into a table at once.

Updating and Deleting Data

Write and execute UPDATE and DELETE statements to do the following. Each task should be accomplished with a single statement and should be done in the order listed. If a statement fails, explain why.


Data Modification in MySQL Workbench

For this part, use username_library2b.

Inserting Data

Add publisher information to the PUBLISHER table:

Importing Data

Manually typing in data is impractical if there are more than a few rows. MySQL Workbench provides several data import (and export) options.

Updating and Deleting Data

The same result view that you used to insert data can also be used to edit data:

Carry out the following tasks one at a time. (Apply after each one instead of doing everything at once.) If something fails, explain why.


Checking Your Work

You can view the contents of the relevant table(s) after each operation to verify that the correct thing happened - write a SELECT query to retrieve all rows from the table, or right-click on the table name in the "Schemas" list and choose "Select Rows".


Valid HTML 4.01!