Databases

Dr. Brian Murphy

Lecture 1

Database Systems

Made up of 3 main elements:

A database is needed if:

Technical Definition

Software and hardware system to manage storage and retrieval of large sets of structured data

7 Commandments of Databases
1. Thy data must be correct and consistent
2. Though shalt avoid redundancy and repeition
3. Though shalt not waste space
4. Each table shalt correspond to a thing in the world
5. Thy information must be uniquely identifiable
6. Thy database searches shall be fast and reliable
7. Thou shall not change thy database design after it goes live.

Every row must have a unique identifier within a table. A foreign key is a primary key in another table. A schema is an abstract structure of the tables in a database.

Lecture 1.3 - Accessing and searching a database

Web Pages

Static web page

  1. Browser sends request to the web server
  2. The server fetches the requested page from the hard disk
  3. That page gets sent back to the client browser
  4. Browser renders and displays the web page

Dynamic web page

  1. Browser sends a custom request to the server
  2. Web server generates that page using templates from its hard disk, and relevent data from the database
  3. Page gets sent back to the client browser
  4. Browser renders and displays the web page

Lecture 2.1

Structured Query Language

Creating a new database structure (schema)
create, alter, drop
Adding information, changing it, or deleting it
insert, update, delete
Finding and extracting subsets of the information in the database
select

Extracting columns and/or rows/records from a tableProduces a new 'table' based upon an existing set of tables

select <fields> from <table>

<fields> - seperated by comma. * wildcard for everything

Duplicates are not selected (set theory)

Row extraction in SQL

select * from <table> where <field>='<data>' (AND...)

Structural information is carried across to output.

Lecture 2.2

WHERE conditions on SELECT

Test on numbers

=  <  <=  >  >=  !=

Tests on text

=  <>  LIKE

Wildcards can be used ->  % - Any sequence of characters _Any single character

 

[Extra Notes] 2.5 - Querying the Database

SELECT

SELECT syntax

All rows from specified columns

SELECT column1Name, column2Name,... FROM tableName

All rows from all columns

SELECT * FROM tableName

List rows that meet specified criteria

SELECT [...] FROM tableName WHERE criteria

SELECT without a table

SELECT 1+1;
> (2)
SELECT NOW();
> (YYYY-MM-DD HH:MM:SS)

Comparison Operators

Do not compare floats for equalities as they are not precise, use DECIMAL instead

For numbers (INT, DECIMAL, FLOAT)

=  equal to
<>  not equal to
!=  not equal to
>  greater than
<  less than
>=  greater than or equal to
<=  less than or equal to

String pattern Matching

LIKE and NOT LIKE 

Wilcards: 
_  Matches any single character
%  Matches any number of characters

Examples
abc%  Strings starting 'abc'
%xyz   Strings ending 'xyz'
%aaa%  Strings containing 'aaa'
_ _ _  [no spaces]  Strings containing any 3 characters

Arithmetic Operators

+  -  *  /
DIV  Integer Division
%  Modulus (Remainder)

Logical Operators

WHERE
NOT
AND
OR
XOR 
IN
NOT IN (AND-OR equivalent)

BETWEEN  NOT BETWEEN  Checking if a value is within a specified range

... WHERE value BETWEEN value1 AND value2

IS NULL  IS NOT NULL  Represents no value, missing value or unknown value.

Common mistake - NULL cannot be compared:

WHERE value = NULL;  INCORRECT
WHERE value IS NULL; CORRECT

ORDER BY Clause

SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC

ASC default. If several values in columnA have the same value, they will be ordered by columnB and so on.

For strings, ordering can be either case sensitive or case insensitive. Returned values can be randomised using RAND()

LIMIT Clause

Limits the number of rows displayed, useful for large databases

Lecture 11/10/16 - Selecting from multiple tables

SELECT * FROM table1, table2 WHERE table1.value = table2.value 

 

Lecture 10/10/16 - Keys

A key is a value that uniquely indentifies every entry in a particular table. A candidate key is any field that will have a unique value for every record.

How a key is chosen

What is a key used for?

Primary keys are used for indexing and searching purposes within its own table.

Lecture 18-10-16 Many to Many Links

Solution to this: Junction Table

A junction table is purely a link between 2 other tables to allow a many to many relationship. Often a juntion table consists just of foreign keys to other tables.

 

Other database operations

Updating an existing row

UPDATE tableName
SET field = newValue, field2 = newValue2
[WHERE field3 = value]

Changes can't be made that would break a table constraint. eg. Uniqueness, non-null etc.

Deleting an existing row

DELETE FROM tableName
WHERE field = value

A row can't be deleted that breaks a constraint. Eg, Foreign keys to other tables.

Lecture 25/10/16 - Database datatypes

Numeric

INT  Whole numbers
FLOAT  Approx real numbers
DECIMAL  Exact storage of real numbers

Date

DATE  TIME  DATETIME

Strings

VARCHAR  CHAR  TEXT  ENUM  BLOB

The length can be set for CHAR/VARCHAR types. ENUM for fixed selection. TEXT for longer chunks of text.

Lecture 26/10/16 - Planning a database

Things to consider