Databases I
What Is a Relational Database?
Most common and important type of database structure.
A relational database is a collection of tables,
which are called relations.
Table structure:
- Tables have rows, called
tuples or
records.
- Tables have columns, called
attributes or
fields.
Formally, a table is a set of rows of the same type,
so there are no duplicate rows, and the order of rows is
unspecified.
Each row is identified by a
primary key,
which consists of one or more attributes.
The rows represent individual entities,
such as persons, products,
or actions or events.
The columns represent their properties,
such as a person's name and address.
Each column has a particular
data type,
such as integer or text.
Example:
The PET table (petID is the primary key)
| petID |
name | breed | sex | weight | price |
| P1 | Noodle | poodle |
M | 10 | 50 |
| P2 | April | cat | F | 5 |
30 |
| P3 | Mickey | mouse | M | 0.5 |
5 |
| P4 | Minnie | mouse | F | 0.4 |
5 |
| P5 | Yvonne | poodle | F | 8 |
55 |
| P6 | Pierre | poodle | M | 12 |
60 |
| P7 | Tom | cat | M | 8 |
25 |
| P8 | Tux | penguin | M | 22 |
295 |
Simple Table Operations
- project attribute, …
from table
- — creates a table having all rows, and just the given
attributes,
from the given table
- select test
from table
- — creates a table having all attributes, and
just the rows where the test is true,
from the given table
Examples
- project name
from pet
- project name, breed, price
from pet
- select (breed = 'poodle')
from pet
- select (weight ≤ 10)
from pet
- select (price > 100)
from pet
Boolean operators:
we can use
and,
or,
not in the test:
- SELECT
(sex = 'M' and price < 40)
from pet
Combining Table Operations
we can use project and
select together:
- project name, breed
from
(select (price < 35)
from pet)
- project petID, sex, weight
from
(select (breed = 'poodle')
from pet)
Computer Databases
Database products: Microsoft Office Access,
Openoffice.org Base, Oracle, PostgreSQL, MySQL, etc.
Create a Pet Shop Database with One Table
- Create a new database
- Create a table named pet with these attributes and data types:
- petID (text — mark it as the primary key)
- name (text)
- breed (text)
- sex (text — M or F)
- weight (number — in MSO Access: "single")
- price (number — in MSO Access: "currency")
This is the schema for the table.
- "Open" the table and enter the rows of table data from above.
SQL: A Database Query Language
A query is a statement
that retrieves information from a database.
Query Design
Before typing the query, figure out:
- Which attributes do you want to be displayed?
Their names are not always the same as their
English descriptions.
- Which tables contain those attributes?
- If you don't want to display these attributes from every
row, what are the conditions for including a row?
Typing an SQL select Query
The select statement
is the workhorse of SQL queries.
Its name is misleading: it does far more than the
select operation
described above.
After designing the query:
- Type the
select clause:
- Type the word
select and a space
- Type the attribute names, separated by commas and spaces
- Begin a new line
- Type the
from clause:
- Type the word
from and a space
- Type the name of the table containing the data
- Begin a new line
- Type the
where clause:
- Type the word
where and a space
- Type the test.
(If the test uses boolean operators
and or or,
begin each one of them on a new line.)
Usage of the select Statement (Summary)
select attribute, …
from table
where test;
Exercises
Formulate the queries above in SQL;
enter the queries and evaluate them.