Using Relational Databases and SQL - California State University ...

musicincurableΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 4 μήνες)

201 εμφανίσεις

Using Relational Databases and SQL

John Hurley

Department of Computer Science

California State University, Los Angeles

Lecture 1

Introduction

Introduction

John Hurley

Call me John, especially outside class.

If that’s too informal for you, you can call me “Instructor”


(VI Dos ) 4 VIII Seven dash 8 Won Five Fore!
(text preferred)

hurley_j@sbcglobal.net

Office hours listed on course page. I will often be in
A
-
318 at other times, too.


Attendance


Administrative

Course page:

http://www.calstatela.edu/faculty/jhurley2/classes/cs122


Syllabus

Software download links

Assignment dates

Grading

Grading: A, B, C, (with + and
-
), NC.

If you are an undergraduate and don’t get a C or
better, you get an NC

If you are a graduate student and don’t get a B or
better, you get an NC

See the grading scale on the syllabus; no curve

In past terms, I have assigned all grades from A to C
as well as NC in this class. Median grade is usually
B or B+, lower than the median grade in my CS120
sections.



About 15% of the all the course grades I have given in
CS122 were NCs. Everyone to whom I have ever given an
NC missed significant portions of the coursework.

If you decide not to take the class, drop it yourself.
Don’t
rely on me to drop you!

I can’t drop anyone after the no
-
record drop deadline

You will have your midterm grades before the drop
-
with
-

W deadline



Labs

Administered on CSNS

Posted by Tuesday morning, usually due the following Tuesday
before class.

Although you will not usually finish the lab in class, lab
attendance is required. Let me know *in advance* if you won’t
be able to attend a lab for some good reason

Last lab will be a practice final exam

People who do well on labs but poorly on exams and quizzes
receive careful scrutiny!


Assignments

Labs and exams will contain the following types of questions:

short answers

1
-
paragraph answers

SQL to English

English to SQL


Quizzes

Quizzes will be administered either during lectures or labs

I may give one or two pre
-
announced quizzes which require you
to define a few very important terms

Usually unannounced

Usually closed
-
book

No makeups!



You may discuss assignments and techniques with other
students

You may *NOT* copy code

You may not copy any text from any source for exam or
quiz questions

It is
very obvious

to me when students do this! I
will be harsh about this.

You must use a lab computer for exams. If you use a
laptop for lab work, be sure to try out the lab computers
*in advance* of the midterm.


Cheating

Using Relational Databases and SQL

Part I

Databases

Database Definition

A
database

is a structured collection of persistent
data.

Many definitions are like this one:

A collection of data, typically modelling the activities of
one or more related organizations
(Ramkrishnan and
Gherke,
Database Management Systems
.)


It is possible to quibble with the part about modelling
real
-
world activities. Database designers don’t
always know what the data will be used for.


What is a Database?

Structured using a database model

No database model, no database!

Often, not always, used to model organizational
activities

Examples:

Companies

Stores

Universities

Database Skills

Database skills are foundational in CS

The great majority of modern applications use
databases to store information

You will put these skills together with your OOP
programming skills a little later if you are an
undergraduate, very soon if you are a grad student

As a working software engineer, you will
probably use the skills you learn in this class
every day



Database Skills

Some applications you are familiar with that rely
heavily on large databases:

Wikipedia

GET

Amazon.com

ITunes


Tables


User can add and remove tables, get information
from them, update or delete information in them,
change them

These are the skills we will study in this class

Database Background

Storage was bulky, expensive, and slow in the old
days!

Physical/Logical Separation

Previous to the inventions of DBMS, one had to
write a program that traversed pointers at the
physical level to extract data from a database

By abstracting the physical level and writing a
program at the logical level instead, extracting
data from a database became much easier

Database Models

The Two Levels of a Database Model

Physical Level (how data is stored)


The things we don’t have to worry about

Logical Level (how data is organized)


The things we do care about

The Basic Models

Hierarchical Model


(IBM’s IMS) represented data as
a tree

Network Model (CODASYL)


Relational Model

(
ALPHA, SEQUEL)


Hierarchical Database Model

Hierarchical Database Model

Example of a query to retrieve info:

for book in (get_children("Programming/J.Smith”))

print book.field("Title"), book.field("Publisher")


Mostly superseded by relational model

Has an afterlife with XML


XML Data

<CATALOG>


<CD>



<TITLE>When a Man Loves A Woman</TITLE>



<ARTIST>Percy Sledge</ARTIST>



<COUNTRY>USA</COUNTRY>



<COMPANY>Atlantic</COMPANY>



<PRICE>8.70</PRICE>



<YEAR>1987</YEAR>


</CD>


<CD>



<TITLE>Black Angel</TITLE>



<ARTIST>Savage Rose</ARTIST>



<COUNTRY>EU</COUNTRY>



<COMPANY>Mega</COMPANY>



<PRICE>10.90</PRICE>



<YEAR>1995</YEAR>


</CD>

</CATALOG>

Network Model

Built on hierarchical model but allows multiple parents
and multiple children

Relational Model

Proposed by Edgar F. Codd (circa
1969
)


Database is a collection of tables (relations)

Relational comes from ‘Relational
Algebra/Calculus’ and not from ‘Relationships’

Relational model is based on extensive
mathematical theory, which we will not cover in
this class

Dominant database model

Oracle was the first to aggressively market a
commercial relational database product

Dr. Edgar F(rank) Codd

MA Mathematics, MA Chemistry

MS and PhD in Communication Sciences

ACM Turing Award (1981)


Tables


Artists = Table (Relation)


ArtistID, City, Region, ... = Columns (Attributes)


Each row is called a Record (Tuple)


Using Relational Databases and SQL

Part II

Database Management Systems

Database Management Systems
(DBMS)


Software (programs) to manage databases

Database creation and deletion

Data maintenance (updating)


Data security (encryption, user access control)



Common DBMSs

MySQL
, PostgreSQL (free, open source)


Oracle, MS SQL Server (commercial)


Database Schemas

The definition of the database, where you define

Tables

Relationships

Constraints

Stored Functions and Procedures

Views

Indexes

Schemas are typically represented by a schema
diagram; see the Lyric diagram linked from the
course page

Database Management Systems
(DBMS)

You can have multiple databases, each with a
single schema

A separate database for each application

Toystore (First database)

Bookstore (Second database)

Furniture Store (Third database)

Etc.

You can also have a single database, with
multiple schemas

Database Management Systems
(DBMS)


Using Relational Databases and SQL

Part III

Query Languages

Query Languages

Query: question

Query Language = A computer language used to
extract data from a database

Data Sublanguage = A computer language used to
extract and manipulate database data

SEQUEL/SQL (
1974
)


developed at IBM

Query Languages

Data Sublanguage Alpha (Codd’s original query
language)





Data Sublanguage SEQUEL (SQL)


SQL

Stands for Structured Query Language

A non
-
procedural, domain
-
specific language (not like
C or C++)


An open ANSI and ISO standard

Supported by most major DBMS

Some variations in implementations

Used by programmers, managers, and database
administrators

SQL Functions

View information from relational databases

Single and multiple table selections

Calculation and analysis

Manipulate information in relational databases

Insert and delete records

Update records

Create relational databases

Create databases, tables, constraints, ...

Nonstandard Features


SQL is an open standard, but developers of DBMSs often
add additional features that are not part of the standard


Differentiate their products from competitors


Vendor lock
-
in


What happens when you want to switch to a different DBMS?


Is it a good idea to use features like this?


Using Relational Databases and SQL

Part IV

Lyric Database Discussion

Primary Keys

Primary key is used to uniquely identify every
record in a table

Must be a field with unique values

What would happen if we needed to identify
individuals in the university DB and tried to do this
using first name? Last name? Both? Height? DOB?

The Lyric Database

Database for a web
-
based company that provides
services to artists and the studios that they work
for

Before we start extracting data from a database,
we must understand the database completely first

Let’s go over all the tables and attributes

Primary Key Example

What is the primary key of the Studios table?

What is the primary key of the
XRefArtistsMembers table? (hint: primary keys
can come in pairs and tuples!)

Using Relational Databases and SQL

Part V

MySQL

MySQL

For coursework, we will use MySQL, which you must install on a
USB drive.

You may also install it on your own laptop, but note that you will
have to use the lab computers for the midterm and final exam, so
be sure you can run it from a USB drive before the midterm.


You may also use MySQL on the cs
1
server. If you would like to
try this,
1
) send me an email requesting a cs
1
account, then see this
tutorial:

http://cs.calstatela.edu/wiki/index.php/Courses/CS_
122
/Fall_
2008
/Tu
torial_
01


Downloading MySQL

Get MySQL Server Community Edition.

Go to CS
122
web page and follow the links to MySQL site

mysql
-
5.5
.x has the MySQL database client and server programs

Get the .zip files (not the MSIs) for your OS (Windows vs. OSX) and
processor (
32
vs
64
bit). The files are labelled in a way that may confuse
you into downloading the source code, which you don’t need. Be careful
to get the binaries instead. MySQL
5.5.8
-
win
32
, for example, is
132
MB.
The
27
MB file is the source code.

Extract the zip files; you will have two directories

You may also want to use the MySQL Workbench, which is a GUI tool for
working with MySQL. However, Workbench is buggy. Please don’t ask
me to help you with it until at least week
3
, after everyone is working
smoothly with the main MySQL software.

mysql
-
workbench
-
gpl
-
5.2
.x.... is the MySQL GUI Tools

Using MySQL In Windows

This process should only be slightly different in OSX

Open up a Windows command line console

Use the cd command to navigate to the

mysql
-
5
.x.xx
-
xx/bin directory


If you add this directory to your PATH, you won’t have
to navigate there every time.

Type in the following to start the database server:

start
mysqld


Then type in the following to start the database
client:

mysql


u root

Mac Users

An OSX version of MySQL is available, but some
students have had difficulties with it and I am not
good at supporting OSX. You may find the easiest
way to use a Mac in this course is to use MySQL on
CS
1
.

Some MySQL Commands

Once MySQL has started and you see the mysql
prompt:

At mysql> prompt type in:
show databases;

At mysql> prompt type in:
create database lyric;

At mysql> prompt type in:
use lyric;

At mysql> prompt type in:
show tables;

You shouldn’t see any yet

Adding Data to a Database

Now that the database is selected, let's load a
database script

Download lyric.sql from the course webpage

At mysql> prompt type in:
source [path] lyric.sql;

Where [path] stands for the path to the location where you
saved lyric.sql.

You should see a bunch of messages like:

Query OK,
1
rows affected (
0.01
sec).