SQL - Class Notes
2.7.06
Michael Xue

Christopher McCulloh

Excel is not a database.

We will be learning Relational Database.


What is a Relational Database?
Can be looked at as a large storage container. Inside of this is a collection of tables.
Each table represents a collection of information. i.e. a "people" table would represent all of the
people in the database, or a "department" table representing the departments, etc.

Relational Database is a storage with a collection of tables in it. These tables have relationships
in them. Let's say you have "people" and "departments". A department could have alot of people
in it. If you take the relationship from the department to the people in the department, is one
department with lots of people (one to many). The relationship between one person in the department
is one person to one department (one to one). So along with the entities in the tables, the DB also
stores the relationships between the tables. That is why it is a relational database.

There is a component alongwith the database(s) called DBMS (Database Management System). This is a
piece of software that manages all of the data that you have (Oracle, MS-SQL server, Access, MySQL).
The software will extend a "listening post" (called a port). Anything you want to do dealing with
data, you use a "high-level language" called SQL. You write your SQL and you send it to that port,
then the DBMS will parse the SQL, and do what you told it to do (with "low-level language"). This
way you can write your queries in an almost english way.

SQL or DBMS Client => SQL => port => DBMS => Database => Table => Info

There is yet another layer. You use a piece of software to write the SQL to send. Then the DBMS sends
the info back to you when it retrieves is. The client is a program that you write as a software
developer. Your program can actually generate SQL without you having to keep writing it every time.
However, we will not use this approach, we will use a pre-made client to manipulate the database
since this is not a programming class. Otherwise, we would write the client ourselves.


The Textbook
Very general textbook, talking about a database in general terms. Good to not corner yourself with
a particular product because of how often companies switch software. We will use this as a refrence
book. This class is a practical hands on class. Pace is very fast, you have to work hard.

We will also be setting up our own open-source DBMS. We will be able to duplicate this at home. Must
do this stuff outside of class!

We are going to create a script file that you can mail to yourself at the end of class.


DBMS
We will be using MySQL for our DBMS. mysqld-max is the name of it. The client is called mysql. It is
all bundled together. We will download it all at once and install it so that we will have both. In
linux they will be downloaded and installed seperately.

Even if we use oracle or something else besides mysql, we will be writing standardized sql-92 compliance
so that the script should be portable across all clients.


Getting Started
First we will need to download and install MySQL.

Once the server is installed and started, we will start the sql environment and
get to know SQL a little bit.

Now lets restart our computer to make sure we know how to get back to this point. When it boots back
up, open a command prompt and restart the server (type mysqld-max). Then browse back to your folder
and type "mysql -u root" to get back into the MySQL environment. Later we can create more users, but
for now you will just be telling it "start mysql with the user root" (mysql -u root)...

Now lets learn how to create and delete databases a bit more.

Once you have created your first database, we can start creating things inside of them and running
querries.

A database contains tables. A simple table has rows and columns.

Columns will be things like (for the people table) id, first_name, last_name, age, sex, etc.

Rows will fill in the info (for the example above) 101, John, Smith, 30, M, etc.

So, when we are creating a table, we first need to specify our columns. We also have to specify what
type of data goes into those columns (integer, string, etc).

Now lets go ahead and create a simple table.

Next lets create a script file so that we don't have to keep typing all
of this stuff in and retyping it if we make mistakes, or if the computer crashes or something.

Ussually in a database table, you have a primary key. So that a column
like id (for example) will never be repeated (must be unique).

In summary, inside the DBMS you have databases, and inside databases you have tables. Each table has
rows and columns. The DBMS has a port that it uses to listen to a client, who will send it SQL. In our
case we are using MySQL Client. The user interacts with the database through the MySQL Client.