10/10/2011

Relational Database

Relational Database • Relational database: a set of relations • Relation: made up of 2 parts: – Schema : specifies the name of the relation, plus name and type of each column, and constraints • E.G. Students (sid: string, name: string, login: string, age: integer, gpa: real). • *use DOB is a better design

Instance : a table, with rows and columns. #Rows = cardinality, #fields = degree / arity. • Can think of a relation as a set of rows or tuples (i.e., all rows are distinct). –

1

10/10/2011

Example Instance of Students Relation Fields, attributes, columns

sid 53666 53688 53650

name login Jones jones@cs Smith smith@eecs Smith smith@math

Rows, tuples, records • Cardinality = 3, degree = 5, all rows are distinct.

age 18 18 19

gpa 3.4 3.2 3.8

Integrity Constraints (ICs) • IC: condition that must be true for any instance of the database. –

–

ICs are specified when schemas are defined based upon semantics of realworld enterprises ICs are checked by DBMS when relations are modified. – Reduce data entry errors

2

10/10/2011

Most CommonTypes of Integrity Constraints (ICs) •Primary Key Constraint •Referential Integrity Constraint

Primary Key Constraint • A key is a minimal set of attributes that can uniquely identify a tuple • A superkey is a set of attributes that can uniquely identify a tuple • E.g., • • • •

{SSN,Name,Age} is a superkey of Students relation {SSN,Age} is a superkey of Students relation {SSN} is a key and also a superkey of Students relation

If there’s >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key. The other keys are called candidate keys.

3

10/10/2011

Foreign Keys Foreign key (FK) : Set of fields in one relation that is used to `refer’ to a tuple in another relation. (Must correspond to primary key of the second relation.) FK is Like a `logical pointer’. Employees SSN FNAME MNAME LNAME BDATE SALARY SUPERSSN DNO

Departments DNAME DNUMBER MGRSSN MGRSTARTDATE

•DNO is a foreign key of Employees where Employees is a referencing relation and Departments is a referenced relation

For each tuple, the value of FK can either be null or refer to the value of PK in the referenced relation and The domain of FK in the referencing relation is same as the domain of PK in the referenced relation

Students

Enrolled

4

10/10/2011

Relational Query Languages • A major strength of the relational model: supports simple, powerful querying of data. • Queries can be written in query languages, and the DBMS is responsible for efficient evaluation.

• Query languages (QLs) allow manipulation and retrieval of data from a database.

• Query Languages != programming languages! – –

QLs are not intended to be used for complex calculations. QLs support easy, efficient access to large data sets.

• Relational model supports simple, powerful QLs: – –

Strong formal foundation based on logic. Allows for much optimization.

5

10/10/2011

The SQL Query Language • Developed by IBM (system R) in the 1970s • Need for a standard since it is used by many vendors • Standards: – – – –

SQL-86 SQL-89 (minor revision) SQL-92 (major revision) SQL-99 (major extensions, current standard)

The STRUCTURED Query Language (SQL) •Data Manipulation Language (DML)

allows users to query, to insert, to delete, and to update rows.

•Data Definition Language (DDL) enables

creation, deletion, and modification of definitions for tables and views and integrity constraint specification.

6

10/10/2011

name ssn

lot Employees

Employees (ssn:CHAR(11), name: CHAR(20), lot:INTEGER) CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn))

name ssn

lot Employees

cost

pname

Policy

age

Dependents

Schema Dep_Policy(ssn:CHAR(11), pname:CHAR(20), age:INTEGER, cost:REAL)

Create a relation using SQL CREATE TABLE Dep_Policy ( ssn CHAR(11) NOT NULL, pname CHAR(20), age INTEGER, cost REAL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE)

7

10/10/2011

• SQL supports all 4 options on deletes and updates. – Default is NO ACTION (delete/update is rejected) – CASCADE (also delete all tuples that refer to the deleted tuple) – SET NULL / SET DEFAULT (sets foreign key value of referencing tuples)

8