Principles Today of Information and Database Management 198:336 Week 4 Feb 14 Matthew Stone Relational model
Big picture Schemas and tables ER diagrams to schemas Queries and SQL
Recap Week 1: Motivation for info management Week 2: Theory of representation Week 3: Practice of representation Week 4: A classic case study
Pictorial view
TELL INFORMATION MANAGER ASK
Pictorial view
Definition operation Describes the structure of specific information to IM
Corresponds to a conceptual model Helps avoid errors
DEFINE
TELL INFORMATION MANAGER ASK
1
Relations
Named after logical relation
sid 53666 53688 53650 name login Jones jones@cs Smith smith@eecs Smith smith@math age 18 18 19 gpa 3.4 3.2 3.8
sid 53666 53688 53650
name login Jones jones@cs Smith smith@eecs Smith smith@math
age 18 18 19
gpa 3.4 3.2 3.8
Example of students relation
Corresponds to Student(53666,Jones,jones@cs,18,3.4) Student(53688,Smith,smith@eecs,18,3.2) Student(53650,Smith,smith@math,19,3.8)
Also called Tables
In the relational model, IM stores set of relations or tables
DEFINE
sid 53666 53688 53650
name login Jones jones@cs Smith smith@eecs Smith smith@math
age 18 18 19
gpa 3.4 3.2 3.8
TELL INFORMATION MANAGER ASK
For obvious reasons
Relational model Define
Creates new tables
Relational Model: Example Definition statement
create table books ( isbn char(13) null not primary key, author char(80), title char(100), price float(4,2) );
Tell
Inserts new rows into tables
Query
SQL expressions describe data of interest Results are returned as tables
2
Relational model example Tell statement
insert into books values ('0-672-31697-8', 'Michael Morgan', 'Java 2 for Professional Developers', 34.99);
Relational model example Tell statement
Adds a row to the specified table in the information manager to include the specified entity or relationship.
Relational model example Query example
SELECT author,title FROM books WHERE price > 30;
Relational model example This returns a new table
author Michael Morgan ... title Java 2 for Professional Developers
Relational Model Most widely used model.
Relational Query Languages Queries can be written intuitively, and the...