Relationship Modeling [top]
Relational databases are based on tables, and how they correspond to each other. You can think of a table as a set of rows and columns. Each column represents a general attribute (i.e. "name of musical style"), and each row is an instance of that attribute ("rock", "jazz"). Tables are often represented in short form by simply listing the columns. For example, here's a table of musical styles in full form with all its data, and in its short form with just its attributes:
Musical style table data in full form, row-column format
Musical style table, with attributes only
Specifically, table attributes (i.e. the columns) are called fields, and the instances of these fields (i.e. the rows) are called records. If you look above, you can see that we have two fields (id, name) and there are 21 entries. Thus we have 21 records in our musical style table.
The main task of designing tables lies in what fields you decide to include as part of your table. Often, certain fields that you'd like to include may be better suited to being in their own table, and then being linked to the original table. One example of this would be if we had a table of audio recordings. Part of it might look like this:
and its data view might look like this:
But as we fill it with recordings, we find we are often repeating the same data over again in the "musical_style" field. So, to make our design more compact, we can separate musical style into its own table, such as the one we looked at above. We can then replace the musical_style field with the ID of the given musical style, such that our database is now two tables that look like this:
and the fields of the two tables in short form are now:
This type of mapping is called a one-to-many relationship as one recording has many possible musical styles of which it can be an example. The relationship of a book to a bookpage is another example of this, as there are many possible pages that can a part of a specific book. At WholeNote, lessons have their own table, and lesson pages occupy another. The field that links them is the lesson's ID, which is a required field for every lesson page (i.e. I am a lesson page and my lesson_id field tells me which lesson I belong to).
A field that uniquely identifies a specific row in a table is called a primary key. In general, I like to make an id field in most every table I make. It is always an integer that starts at 1, and that increments by 1 every time a new record is added. When you reference one table's ID in another table, the name of that field in the second table is called a foreign key. It doesn't function as the primary key since we already have one, but it gives us a way to uniquely identify how our second table relates to the first. In our above example, musical_style_id is the foreign key in the recordings table that binds it to the musical_style table.
The only problem with our current database is that it forces each recording to belong to one and only one musical style. Some might argue that Hello, Nasty is also a rock album, which means it could belong both to the rap style AND the rock style. This is an example of a many-to-many relationship. When this is the case, a third table is needed to define every possible relationship between the two existing tables, which in our example, is recordings and musical styles. We no longer need the foreign key in our recording table because our new third table will define the relationships and will do so totally using the "id" field of each table:
So as you can see, how you design your database is totally dependent on what kind of data you have. Each attribute or field that you want to include must be scrutinized in terms of its range of values and how this can best be modeled in your design.
Creating Your First Table in MySQL [top]
The database used for this class is the MySQL database. There is a web-based administrative tool for manipulating your database called PhpMyAdmin. In order to access it, go to:
and enter your NetId and password. When you hit the welcome page, you need to click on your NetId in the upper left-hand corner to get to the page where you can start creating tables or modifying any existing ones.
Now you can fully see your entire database and all the tables that are located within it. You can either administrate your existing tables or create a new one using the table creation tool. To make a new table, simply give it a name, and the number of fields. When you name your table, please do not use any spaces. The usual syntax is to use an underscore when you want a space, i.e. musical_style.
Once you have specified the name and number of fields in your table, it's time to define what each field is. You need to name each field and then decide what data type it is. For an ID field, it is always an "integer", its default is "1", "auto-increment" is selected, and it is always "primary". If we decided to make a table that held musical styles, we could also define a field called "name". For the names of musical styles, "varchar" is always a good bet, but we also have to define its maximum length in characters. "128" should be plenty long enough.
Now that it is created, you can click on the "Structure" tab at the upper-right if you ever need to add more fields to your table, or if you need to modify existing ones.
Note: When choosing names for your tables & fields, make sure you don't name them the same as any of the reserved keywords in MySQL ("table","select",etc):
MySQL DataTypes [top]
MySQL has several data types, most notably:
Basic SQL Commands [top]
SQL stands for Structured Query Language and was developed by IBM. All relational databases understand statements made using SQL. You can do a lot with a little in SQL, and the four main commands we will focus on is INSERT, UPDATE, SELECT, and DELETE.
INSERT INTO table1 ( column1,... ) VALUES (expression,...)
So, for example, in our recording table:
INSERT INTO recording (id,title) VALUES (1,'Hello Nasty')
Actually, when you have a table that has an ID as a primary key, you don't have to specify it when you enter a new entry. It will automatically enter the proper value for you, so:
INSERT INTO recording (title) VALUES ('Hello Nasty')
Note that numeric values do not need quotes to delineate them, but string values do. Also, if your string has a single quote ' in it, you must escape it by adding another single quote:
INSERT INTO recording (title) VALUES ('Don''t Fear The Reaper')
MySQL also gives you the ability to enter multiple records at the same time. The syntax for this is:
INSERT INTO recording (artist, title) VALUES ('AC/DC','Powerage'), ('AC/DC','High Voltage'), ('AC/DC','Highway to Hell');or
INSERT INTO recording (artist, title) VALUES ('AC/DC','Powerage'); INSERT INTO recording (artist, title) VALUES ('AC/DC','High Voltage'); INSERT INTO recording (artist, title) VALUES ('AC/DC','Highway to Hell');
The UPDATE statement
The UPDATE statement is used to modify any existing records in your table. Here is the format of the UPDATE statement:
UPDATE table SET column1=expression1, column2=expression2, ... [WHERE "conditions"]
So, for example, in our recording table:
UPDATE recording SET title='The White Album' WHERE title='White Album'
UPDATE recording SET title='OK, Computer' WHERE id=3
The SELECT statement
The SELECT statement is used to query the database and retrieve selected data that match whatever criteria you specify. It has five main clauses, and FROM is the only required clause.
Here is the format of the SELECT statement:
SELECT [DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list" [ASC | DESC] ] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ] [LIMIT [offset,] number-rows]
Often, you only need a simpler version of this:
SELECT column1 FROM table1 WHERE "conditions"
SELECT id FROM recording WHERE title = "Hello, Nasty"
The above statement will select all of the values in the id column from the recording table for recording whose title is "Hello, Nasty". If you use an * for which columns you want, it acts as wildcard and gets all of the columns:
SELECT * FROM recording WHERE title = "Hello, Nasty"
If this is our last incarnation of the "recording" table above, this would return ID and title. You can also retrieve built-in MySQL functions like NOW() which returns the current system datetime:
Here is a list of comparison operators that can be used in the WHERE clause.
|>=||Greater than or equal to|
|<=||Less than or equal to|
|<> or !=||Not equal to|
|LIKE||String comparison test|
SELECT id, title FROM recording WHERE title LIKE 'Hello%';
The above statement selects the ID and title of every recording that beings with 'Hello'. In our case, it will return "Hello, Nasty" and its ID.
DISTINCT is a keywords used to obtain only the "distinct" or unique records in your query results. DISTINCT will discard the duplicate records for the columns you specified after the "SELECT" statement: For example, suppose one of our fields in recording was "release_year", which is the year of the recording's release date. We could say:
SELECT DISTINCT release_year FROM recording
which would return all of the unique release years in the recording table. Thus, if two recordings were released in the same year, that year would only show up once in our results.
The DELETE statement
The DELETE statement is remove specific records from your table. Here is the format of the INSERT statement:
DELETE FROM table1 [WHERE "conditions"]
So, for example, to delete just the "Hello, Nasty" entry in our recording table, we would say:
DELETE FROM recording WHERE title='Hello,Nasty'
If you wanted to delete everything from the recording table:
DELETE FROM recording
Related Resources [top]