week 9

Week notes summary...............

Databases are Ubiquitous

• ATMs,

• web search engines,

• email address book,

• network logon,

• online stores, auctions (eg. amazon, ebay)

• blogs

Definition

A structured collection of similar data about

things or processes we care about

employee_id	first_name	last_name
2	Alex	Ulrich
3	Sarah	Tran

Relational Databases

The most common type of database

• Data is presented as a series of tables

• Relationships can be created between data.

For example:

• Students and subjects at university

• Recipes and ingredients

• Departments and employees

Databases

• Database terms

• Fields (columns)

• Data types

• Records (rows)

• Tables

• Keys (primary and foreign)

• Relationships

• Relational database

• Relational Database Management System

Field

• An elemental unit of data. Often referred to as a column.

employee_id	first_name	last_name
2	Alex	Ulrich
3	Sarah	Tran

Data type

• Each column is defined to accept a certain type of data:

• text,

• numbers,

• dates, etc

• The data type restricts the type of data that can be stored in a column.

Record

• A collection of fields. Often referred to as a row.

employee_id	first_name	last_name
2	Alex	Ulrich
3	Sarah	Tran

Table

• A collection of records.

employee_id	first_name	last_name
2	Alex	Ulrich
3	Sarah	Tran

Primary Key

• uniquely identifies each record in a table

employee_id	first_name	last_name
2	Alex	Ulrich
3	Sarah	Tran

• identifies relationship between data in different tables Foreign key

employee_id	first_name	last_name	dept_id
2	Alex	Ulrich	1
3	Sarah	Tran	1

dept_id	dept_name
1		Sales
2		Marketing

Relationships

• Link data in tables via keys

• One-to-one, one-to-many, many-to-many* *more on relationships next week

Relational Database

• A collection of related tables

• Relationships defined by keys

Functions of a DBMS

• define and manage a database structure

• query and manipulate the data

• define and manage relationships

• manage concurrent access

• maintain consistency and integrity

• performance (indexes)

• stored procedures, triggers and views

• transactions

• utilities: eg.backup and recovery

• security (who can access what)

Data Modeling

• How do you work out what tables and fields your database should have?

• the process used to determine the structure of a database

• usually carried out by someone with a title of Systems Analyst or Business Analyst

• a programmer or Database Administrator can create a database based on the model

• will be covered in week 10

Structured Query Language

• Structured Query Language (SQL) • often pronounced 'sequel'

• Language for communication with databases

• Standard (SQL89, SQL92, SQL99)

• Embedded in programming languages

SQL Data Manipulation

• Four basic operations:

• adding (inserting) data

• finding (selecting), data

• updating (modifying) data

• deleting data

• SQL statements consist of

• Keywords (clauses, functions, etc) and operators (=,+,-,*,/,>,<)

• Database column and table names

• User-supplied data

• Filtering and sorting data

• A WHERE clause can be used to restrict which records are returned by the search

• An ORDER BY clause can be used to sort the data

SELECT *

FROM blogs

WHERE AuthorId = 2

ORDER BY creation_date

Adding data

• The INSERT INTO and VALUES clauses are used to add data to a table

• The order of column names must be the same as the values

• Text values must be enclosed in quotes

INSERT INTO blogs (title, content, AuthorId)

VALUES ('New Flash', 'Adobe announces new Flash’,

• Updating data

• Use the UPDATE clause to choose a table

• Use the SET clause to say which values go in which fields

• Use the WHERE clause to say which record(s) to update

UPDATE blogs

SET category = 'Flash'

WHERE id = 2