MySQL

 

 

There is a database set up for EBUS202, to be used for project #3.  It is a MySQL database, named EBUS202.  A 'read only' MySQL userid and password was given in class that you can use to explore the database using the MySQL command line, or establish a connection to it from a php script.   (Userid is 'students', password is 'littlesecret')

 

Here is a picture of it, a Database Entity Relationship Diagram:

EBUS202ERD.jpg (18087 bytes)

 

Three tables hold the data in this simple database.  A 'real' commercial database may have thousands of tables, and the FK/PK relationships may be quite complex. 

Here, the 'main table' is Donations.  It has the name & address of each donor, plus the amount of each donation.

The organization wants to be able to report Donations a few different ways:  by zip code, by Volunteer, by Region, by Date.   One way of handling requirements like these is to provide 'code tables', like Regions and Volunteers are here, to hold data about regions and volunteers.    

They also want the option of reporting summary data, or details.  Summaries are 'sub totals' only, where the detailed report  includes Regions', Volunteers', and Donors' names & addresses along with the amount of the donation.  'The programmer' can provide links on a webpage so that a user of the organization's website can run a program that makes the report.

Donations ending with '95 cents' are part of a special promotion and these members receive a tee-shirt along with the receipt for their donation.  This special report can be handled by a programmer easily.

 

Foreign Keys:

Two of the fields in the Donations table are 'Foreign Keys' that 'point to' the 'Primary Key' of another table:  RegionId & VolunteerId.  This is similar to the relationships in chapter 22 in the text, where publisherID and authorID are tables related to Titles this way.

 

Using MySQL at the linux command line:

'mysql' is the Linux command that takes you to the MySQL command line.  Use the -u and -p options so that MySQL will expect to prompt for UserId and PassWord, or find them in the command line. 

Slide1.JPG (37329 bytes)

The MySQL UserId and Password aren't the same as the UserId and Password you logged on with.  In our case, a 'fictitious' user named EBUS202 has been set up with 'select only' priviledges to the EBUS202 database.

Logging into MySQL gets you a 'mysql>' prompt:

Slide2.JPG (30443 bytes)

Your first response should probably be to 'use EBUS202;'  Then, you can issue 'show', 'describe', and 'select' statements.  Here are 'show tables;' and two 'describe' statements.

Slide3.JPG (47635 bytes)

 

Using the relationships among tables:

We'll be working on SQL statements using these tables in class. 

In a database where there are Foreign Key relationships SQL statements are used to 'Join' the tables. 

These statements can be made using the 'INNER JOIN' syntax shown in the text.  Or, the 'default' inner join can be shown in the FROM and WHERE clauses as shown here.

Slide4.JPG (41431 bytes)

 

Experiment with the select statements covered in class, and make your own to make different reports.

Next, we'll be mixing together MySQL and PHP to make a webpage that provides reports on the donations received by this organization...