Week 2 Notes

Physical Models

Relational Model - Topics

• How is data represented in the relational model?
• What integrity constraints can be expressed?
• How can data be created and modified?
• How can data be manipulated and queried?
• How can we create, modify, and query tables using SQL?

Relational Model

Most widely used database model: Edgar Codd (1969)

• IBM DB2, Oracle, MS SQL Server, MySQL, PostgreSQL

Recent alternatives and variations:

• Dimensional Data Model - Datawarehousing
• Object Oriented - ObjectStore
• Object-Relational - Informix, Oracle
• In memory - Cache, MemCache, MUMPS
• BigTable/NoSQL - Google, Amazon, many more...
• Column Oriented - Vertica
• Spatial - MySQL, Oracle, & PostgreSQL extensions

Relational Model Definitions

Relational Database

A set of relations Relation Made up of 2 parts

• Instance: a table, with rows and columns

Rows = cardinality, #fields=degree/arity

• Schema: specifies name of relation, and the name and type of each column.

E.g. Students (sid: str, name: str, login: str, age: int, gpa: real)

• Think of a relation as a set of rows or tuples

• A relational database

A collection of relations with distinct relation names.

• The relational database schema

A collection of schemas for the relations in the database.

• An instance of a relational database

A collection of relation instances, one per relation schema in the database schema

• Each relation must satisfy the domain constraints in its schema.

Cardinality = Number of rows

Degree = Number of columns

The SQL Query Language

• Developed by IBM (System R) in the 1970's

Need for standard since it is used by many vendor's standards:

SQL-86
SQL-89
SQL-92
SQL-99
SQL-2011

Consists of:

• DDL - data definition language (define/create schema, i.e., create, delete, updata, insert)
• DML - data manipulation language (select queries)

Creating Relations in SQL

Creating a Relation (Table in SQL):

Create the admitsource relation:

• Note: the type (domain) of each field is specified. The type is enforced by the DBMS whenever tuples are added or modified.

CREATE TABLE admitsource(
ID int,
Code char(1),
Name varchar(50));

Modifying Relations in SQL

Tuples are inserted using the INSERT statement

Insert one tuple (row) into the relation (table) admitsource:

INSERT INTO admitsource(ID, Code, Name) values (9, 'B', 'Transfer from student rehab');

Delete one or more rows from table admitsource with restriction:

DELETE FROM admitsource WHERE Code='B';

Delete all rows from table admitsource (with no restriction):

DELETE FROM admisource;

Loading relations in MySQL

Bulk insert from file:

load Data local Infile 'D:/path' into table
INFORMS.admitsource fields terminated by '\t' lines terminated by '\r\n'
IGNORE 1 LINES;

Bulk insert from another table (fields need to match):

insert into poly_point_map
select poplyid, p1 p
from indices;

MySQL Commands | Lab 2

update admitsource set Code='9' where ID=9;

• could append an 'AND' or 'OR' for further conditions

Integrity Constraints Over Relations

• A database (DB) is only as good as the information stored in it.
• DB should prevent the entry of incorrect information.

Key Constraints

• Constraint on a relation that no two records have the same key attributes.
• A key constraint is a minimal subset of the fields of a relation that is a unique identifier for a tuple within a relation.
• A set of fields that uniquely identifies a tuple according to a key constraint is called a candidate key.

Creating a relation with primary key constraint:

CREATE TABLE admitsource(
	ID int,
	Code char(1),
	Name varchar(50),
	PRIMARY KEY(ID)); // add primary key constraint

Constraints

Foreign Key Constraints

• Sometimes information in a relation is linked to information in another table (relationship).
• If one of the relations is modified, the other must be checked, and perhaps modified to keep the data consistent.
• Foreign Key constraint is the most common constraint across two relations

...
FOREIGN KEY (AdmitSource) REFERENCES admitsource(ID));

P_Id int NOT NULL,
...
UNIQUE(Code),
NOT NULL(Name),
CHECK(P_Id>0) // check/domain constraints

Adding a null check along with a custom check

...
P_Id int NOT NULL CHECK (P_Id>0),

Adding a named constraint

CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

Altering the table to add an anonymous check

ALTER TABLE Persons ADD CHECK (P_Id>0)

Altering the table to add a named check

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

Inserting values into the table "admitsource"

insert into admitsource(ID, Code, Name) values (1, 'X', 'xx');

Relational Query Languages

Major Strength of the relational model:

• Support for simple, powerful query languages
  • Queries can be written intuitively
  • Operate on sets of data.
  • DBMS responsible for efficient evaluation.
  • Precise semantics for relational queries with strong mathematical (theoretical) foundation.
  • Allows optimizer to extensively re-order operations and still ensure the answer does not change.

Example single relation (entity, table) query:

Find all admit sources with a code less than or equal to 5:

select * from admitsource where Code <=5 order by ID

To find just codes and names

select Code, Name from admitsource where Code <= 5 order by ID

WHERE defines the constraint for the search

Find all patients transferred from a skilled nursing facility

select chf.PatientID, a.ID, a.Code, a.Name from chf_train chf, admitsource a where chf.AdmitSource = a.ID and a.ID = 5;

Add a primary key with multiple column values

...
PRIMARY KEY(ID, Code)

Find all patients transferred from any: ("inner join")

select chf.PatientID, a.ID, a.Code, a.Name from chf_train chf, admitsource a where chf.AdmitSource = a.ID and a.Name like '%Transfer%';

Query Multiple Relations

Find all patients transferred from any facility ("inner join") with standard syntax

select chf.PatientID, a.ID, a.Code, a.Name from chf_train chf inner join admitsource a on (chf.AdmitSource = a.ID) 
where a.Name like '%Transfer%' limit 5;

DML

• Primary functionality:

  • Data manipulation language (DML) - query
  • Data definition language (DDL)

• Additional functionality

  • triggers
  • advanced integrity contraints
  • embedded and dynamic SQL
  • Remote access
  • transaction management
  • security
  • advanced features: recursive queries

Basic SQL Query

SELECT [DISTINCT] select-list
FROM from-list
WHERE qualification

List official language by country:

select CountryCode, Language from CountryLanguage where isOfficial='T';

Find all languages spoken by all countries

select * from Country where Contine``` select * from city c;

### Single string compare
```nt like '%Asia%';

More complex compare with multiple tables

select c.Name Country, cl.Language from CountryLanguage cl, Country c
where cl.CountryCode=c.Code and isOfficial='T';

select c.Name Country, cl.Language from CountryLanguage cl, Country c
where cl.CountryCode=c.Code
and isOfficial='T'
and Region='Caribbean';

Expressions and Strings in SELECT statement

SELECT Name Country, 
IF(INSTR(Region, 'Central Americal'), Region, Continent) AS Area,
TRUNCATE( SurfaceArea*(0.62137*0.62137), 2) 
AS 'Area in Miles Squared'
FROM Country 
WHERE Continent LIKE '%America%';