|CPSC 343||Database Theory and Practice||Fall 2017|
We'll be using a relational DBMS called MySQL, which bills itself as the "world's most popular open source database". It is a well-established, commonly-used, multi-platform, free relational DBMS (though there are also paid versions with additional functionality).
The MySQL software package contains both a database server and a basic text-based client program which lets you type in SQL commands and queries. We'll be using a GUI client called MySQL Workbench to interact with the database server - among other features, MySQL Workbench provides a graphical interface for creating and editing tables and entering and modifying data.
MySQL Workbench is available on the lab computers (both in Lansing and Rosenberg). If you want to use it on your own computer, you will need to install it:
To connect to a database you haven't previously connected to, you must first create a new connection:
Click the "+" symbol next to "MySQL Connections".
Fill in the dialog box:
The connection should now appear in the list under "Open Connection to Start Querying". Double-click on the entry to open the connection, entering the password you've been given when prompted.
You should then see a window like the following:
If you want to change your password, go to the tab labeled "Query" or "SQL File" (likely the only tab open when you start MySQL Workbench for the first time), enter the SQL query
SET PASSWORD = PASSWORD('newpass');
and click on the "Execute" icon (). newpass should be replaced by the new password you want; it is case sensitive.
Close the SQL File tab and then the SQL Editor tab, which should return you to the main workbench screen. Try opening the connection again to make sure that your new password works.
Your task is to create a library database and populate that database with some data. The database will have the following tables:
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)
Key attributes are underlined. The foreign key constraints are the following:
To create a new database, click on the "New Schema" icon (). Name your database in the form username_library where username is replaced by your username. (You won't have permission to create a database that doesn't start with your username_.)
When you click "Apply", a window will pop up showing the SQL command(s) that will be executed; verify that this is what you want and continue until the dialog boxes are closed. You should now see the new database listed under "Schemas" on the left side of the window.
Right-click on the database name in the "Schemas" list and select "Set as Default Schema". This means that relation names will be assumed to be from this database unless you specify otherwise when writing a query.
Tables can be created in any order, with the restriction that tables referenced by foreign key constraints must be created before the constraint can be established. Let's start by creating the PUBLISHER table since it does not reference anything else.
To create a new table, click on the "New Table" icon (). Fill in the boxes at the top of the tab:
Next, specify the table's columns. One column, with a name of idPUBLISHER, is provided by default. Click on the various field values to change them:
Below the column entry area is a place where you can specify additional details of the column, including a comment. Normally the comment is much like a comment for a variable - it describes something important about the column that isn't readily apparent from its definition. For this assignment, use the comment field to include a brief rationale for why you chose the data type that you did for the column.
Repeat the process for the rest of the table's columns, including writing a comment with the rationale for your choices of data types. (Click on the blank row immediately below the last-specific column to add a new column.)
Click "Apply" when you are done. A window will pop up showing the SQL command(s) that will be executed; verify that this is what you want and click "Apply". You should now see the new tables listed under "Schemas" on the left side of the window. (Expand the little triangle tabs as needed.)
Close the table-creation tab.
Since the PUBLISHER table has been created, it is now possible to set up the foreign key constraint in BOOK that references the publisher name.
Create the BOOK table in the same manner as the PUBLISHER table, but note that both the referenced and referencing columns must have the same data type. Make sure you choose the same data type for the Publisher_name column in BOOK as you did for the Name column in PUBLISHER.
To specify the foreign key, click on the "Foreign Keys" tab near the bottom of the table-creation tab. Start by giving the new foreign key a descriptive and unique name (under "Foreign Key Name" - click in the first blank row) such as fk_BOOK_publisher - "fk" for "foreign key" (as opposed to another kind of constraint), "BOOK" for the referencing table, and "publisher" to indicate that this foreign key has to do with the publisher of the book. Now make sure the foreign key's name is highlighted and fill in the rest of the boxes:
Click "Apply" when you are done. A window will pop up showing the SQL command(s) that will be executed; verify that this is what you want and click "Apply". You should now see the new table and foreign key listed under "Schemas" on the left side of the window. (Expand the little triangle tabs as needed.)
Close the table-creation tab.
If you find that you need to modify the structure of an already-created table (e.g. to add or remove columns or change a column's data type), right-click on the table's name under "Schemas" on the left side of the window (expand the little triangle tabs as needed) and choose "Alter Table...". If the changes involve attributes used in a foreign key, you'll need to delete the foreign key, make your changes, and re-create the foreign key. Delete a foreign key by right-clicking on its name in the "Foreign Keys" tab in the table-creation tab for the referencing table.
You can get rid of a table by right-clicking the table's name under "Schemas" and choosing "Drop Table...".
To modify a table's data, locate the table's name under "Schemas" on the left side of the window (expand the little triangle tabs as needed), right-click on the table's name, and choose "Edit Table Data". (If there isn't an "Edit Table Data" option, instead choose "Select Rows".) Do this for the PUBLISHER table. You'll get a new tab with the top half of the area showing the query used to retrieve the data current in the table, and the bottom half of the area showing that data. (The PUBLISHER table is currently empty.)
In the bottom half of the window, click on the row showing only NULL values, then click on a particular field to enter new data. (This row doesn't really exist in the table; it's just a placeholder for entering new data.) Enter the following publisher:
DAW 375 Hudson St, New York, NY 10014 123-456-7890
If you find that you chose data types that won't accommodate this data, you'll have to edit the table's schema before you'll be able to insert the data.
Enter a second publisher:
Eos 10 East 53rd St, New York, NY 10022 212-207-7000
Click "Apply" when you are done, verify that the SQL command is correct, and click "Apply" again.
Create the rest of the tables and foreign key constraints specified in the library schema.
Also add the following books to the BOOK table:
1 Foreigner DAW 2 Invader DAW 3 Mission Child Eos 4 Fortress in the Eye of Time Eos
and the following authors to the BOOK_AUTHORS table:
1 C. J. Cherryh 2 C. J. Cherryh 3 Maureen F. McHugh 4 C. J. Cherryh
A sample solution called ex_library has been set up in MySQL. You should have access to it - use MySQL Workbench to connect to the MySQL server, then look in the "Schemas" section in the lower left portion of the window. ex_library should be visible.
To view how the solution tables were set up, go to the Query tab and type the SQL statement to view the table structure:
SHOW CREATE TABLE ex_library.BOOK;
(Replace BOOK with the names of the other tables to view those.) Click on the "Execute" icon () to execute the command, then choose "Form Editor" from the options on the right side of the window to get an easier-to-read view of the result - which is the SQL statement that created the table. You can create a second query tab (under the "File" menu) and do the same thing with your table (username_library.BOOK) so you can compare the two.
It is also possible to examine table structure by right-clicking on the table name in the Schemas view and choosing "Alter Table..." (You aren't actually going to change the table structure - you don't have permissions to do so anyway - but this will bring up the same view that you used to create your tables.) Viewing the SQL statement directly is better, however, as it is easier to miss settings that may be obscured in the table editor view.
You should have the same tables, attributes, and foreign keys as in the solution. You should also have the same basic choices for data types (integer vs fixed point vs floating point, character vs binary vs set, fixed length vs variable length for characters and binary, etc) or at least understand the reasons for the choices made in the solution, and should understand the rationale for the ON DELETE and ON UPDATE choices in the solution. Notes on the choices made:
Arbitrary IDs are often (non-negative) integers, so they were chosen to be integers and unsigned. The size of integer was based on a conservative upper bound on the expected number of each kind of thing in a large library system.
Most of the string fields are variable-length things with an expectation of a fair amount of variation, so VARCHAR was chosen. (The length was picked somewhat arbitrarily to hopefully accommodate the necessary values.) The exception is for phone numbers; the assumption was made that these are all US phone numbers so they are all the same length (10 digits + two characters) so CHAR was chosen to avoid the overhead of storing the actual length of each field.
Date_out and Due_date are dates, so DATE was chosen.
There is generally little harm in ON UPDATE CASCADE - if a referenced value is modified, this causes the referencing values to be updated accordingly.
ON DELETE CASCADE means that all of the referencing things are deleted if the referenced thing is deleted. Since there is no information stored for authors beyond the associated book (for example), it makes sense to delete the authors of a book if the book is deleted.
ON DELETE RESTRICT can often be useful as a precaution against accidental deletion as well as in cases where the referenced thing should not be deleted if there are things referencing it. For example, there's no reason to have information about loans from a particular branch if the branch is no longer in the database (and a loan with NULL for the Branch_id isn't very meaningful) - but if there are loans associated with a branch, should that branch really be deleted? ON DELETE RESTRICT for BOOK_LOANS.Branch_id prevents the deletion of a branch with loans - if a branch is to be deleted, its loans must be removed first.
ON DELETE SET NULL is useful when it is meaningful to keep the referencing entity even without a value for the relevant attribute(s). For example, it is still meaningful to have a book without publisher information (unlike a loan without branch information) so ON DELETE SET NULL was used for the foreign key BOOK.Publisher_name.
The MySQL server on 172.21.7.83 is only accessible on campus. If you are not on the campus network, you will either need to run VPN (information) or use port forwarding (which requires ssh). For port forwarding, run the following on your local machine:
ssh -CNL 9000:172.21.7.83:3306 firstname.lastname@example.org
Replace username with your CS department username, and enter your CS department password when prompted for a password. This command takes traffic sent to port 9000 on your local machine, sends it over the ssh connection to math.hws.edu, and tells math.hws.edu to then forward it to port 3306 (MySQL's port) on 172.21.7.83. You can change 9000 to something else if you want - it's just a port that is probably not being used by something else. Now run MySQL Workbench on your local machine and create a connection with localhost as the host name and 9000 as the port number.