Understanding the Relational Model and SQL for Database Management, Slides of Database Management Systems (DBMS)

Download Understanding the Relational Model and SQL for Database Management and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

1

Lecture 3:

The relational model

www.cl.cam.ac.uk/Teaching/current/Databases/

E.F. Codd

Today’s lecture

• What’s the relational model?
• What’s SQL?
• How do we create databases in SQL?
• How do we convert E/R models to a relational model?
• How do we enforce real-life constraints in a relational database?

The relational model: Early

history

• Proposed by E.F. Codd (IBM San José) ~
  • Prior to this the dominant model was the network model (CODASYL)
• Mid 70’s: prototypes
  • Sequel at IBM San José
  • INGRES at UC Berkeley (M. Stonebraker)
  • PRTV at IBM UK
• 1976-: System R at IBM San José
  • Transactions (J. Gray et al.)
  • Query optimiser (P. Selinger et al.)
  • Extended -testing (Boeing et al.)
• 1978/9-: CODD at Cambridge Computer Lab
  • Extended relational algebra query language (K. Moody)

5

The relational model: Basics

• A relational database is a collection of relations
• A relation consists of two parts:
  • Relation instance : a table, with columns and rows
  • Relation schema : Specifies the name of the relation, plus the name and type of each column
• Can think of a relation instance as a set of rows or tuples

Examples

• Relation instance

sid name login^ age 1001 Myleene MK 23 1002 Danny DF 22 1003 Noel NS 21 1004 Suzanne SS 20 1005 Johnny JS 23

Field names

Fields

Tuples

Relational terminology

• A domain is a set of values. All domains in a relation must be atomic (indivisible)
• Given a relation R=R(A 1 : 1 , …, An: (^) n), R is said to have arity (degree) n
• Given a relation instance, its cardinality is the number of rows - For example, in Students, cardinality= (arity=4)

Keys

• Given a relation R=R(A 1 : 1 , …, An: (^) n) a (candidate) key is a subset of fields K {A 1 , …, An} that acts as a unique identifier for each tuple in the relation instance
• We annotate the schema accordingly, e.g.

R=R(A 1 : 1 , …, An: (^) n)

SQL

• SQL is the ubiquitous language for relational databases
• Standardised by ANSI/ISO in 1992: SQL/
• Part of SQL is a Data Definition Language (DDL) that supports the creation, deletion and modification of tables

Removing and altering tables

• We can delete both the schema information and all the tuples, e.g. DROP TABLE Students;
• We can alter existing schemas, e.g. adding an extra field ALTER TABLE Students ADD COLUMN matric INTEGER;

Adding and deleting tuples

• Can insert tuples into a table, e.g. INSERT INTO Students(sid,name,login,age) VALUES (“1006”, “Julia”, “jfg”, 21);
• Can remove tuples satisfying certain conditions, e.g. DELETE FROM Students WHERE name=“Myleene”;

From E/R diagrams to relations

• The E/R model is convenient for representing the high-level database design
• Given an E/R diagram there is a reasonably straightforward method to generate a relation schema that corresponds to the E/R design

Entity types to relations

• A (strong) entity type maps to a relation schema in the obvious way, e.g.

is mapped to the relation schema

Employees(NI: 1 , Name: 2 , dob: 3 )

NI Name dob

Employees

Example

is mapped to the relation schema:

Works_in(NI: 1 , DID: 2 , since: 3 )

Works_in

NI Name dob

Employees

DID dname budget

Departments

since

M (^) N

Recursive relationship sets

• Just pick appropriate field names! E.g.

is mapped to

Reports_to(sup_NI: 1 , sub_NI: 1 )

Employees

Reports-to

NI

name (^) dob

supervisor subordinate