![Page 1: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/1.jpg)
Chapter 2
The relational Model of data
1
Relational model introduction
![Page 2: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/2.jpg)
Contents
What is a data model?
Basics of the relational model
Next :
How to define?
How to query?
Constraints on relations
2
![Page 3: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/3.jpg)
What is a Data Model?
A data model is a notation for describing
data or information. It consists of three
parts:
Structure of the data: mathematical
representation of data
Operations on data.
Constraints.
3
![Page 4: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/4.jpg)
Two important data models
The relational model ( and object-relational
model):
relational model = tables
The semistructured-data model
semistructured model = trees/graphs
XML and its related standards.
4
![Page 5: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/5.jpg)
A relation is a Table
name manf
Winterbrew Pete’s
Bud Lite Anheuser-busch
Beers
5
Attributes (column headers)
Tuples (rows)
Relation name
Each attribute has a domain, an element type.
![Page 6: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/6.jpg)
Schemas (模式)
Relation schema = relation name and attribute list.
Optionally: types of attributes.
Example: Beers(name, manf) or Beers(name: string, manf: string)
Database = collection of relations.
Database schema = set of all relation schemas in the database.
6
![Page 7: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/7.jpg)
Relation Instances (关系实例)
is current set of rows for a relation schema.
Example: beer relation
Name Manf.
Winterblue Peters
Budlit A.B.
7
Dynamic
changing
![Page 8: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/8.jpg)
Key of Relations
There are many constraints on relations
Key constraints is one of them
For example:
Beer(name, manf)
If name is a key, do not allow two tuples to
have the same name.
Each object should be distinguished in the world
8
![Page 9: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/9.jpg)
Why Relations?
Very simple model.
Often matches how we think about data.
Abstract model that underlies SQL, the most
important database language today.
9
![Page 10: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/10.jpg)
a Running Example
Beers(name, manf)
Bars(name, addr, license)
Drinkers(name, addr, phone)
Likes(drinker, beer)
Sells(bar, beer, price)
Frequents(drinker, bar)
Underline = key (tuples cannot have the same value in all key attributes).
10
![Page 11: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/11.jpg)
Database Schemas in SQL
SQL is primarily a query language, for getting
information from a database.
SQL also includes a data-definition
component for describing database
schemas.
11
![Page 12: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/12.jpg)
Creating (Declaring) a Relation
Simplest form is:
CREATE TABLE <name> (
<list of elements>
);
To delete a relation:
DROP TABLE <name>;
12
![Page 13: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/13.jpg)
Creating (Declaring) a Relation (cont.)
To modify schemas
ALTER TABLE <name> ADD <new attribute>
ALTER TABLE <name> DROP <attribute>
![Page 14: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/14.jpg)
Three kinds of table
Stored relations: tables, a relation that exists
in the database, can be modified or queried.
real, stored.
Views: relations defined by a computation.
virutal, not really exists.
Temporary tables: constructed by the SQL
processor when it performs. thrown away,
not stored.
![Page 15: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/15.jpg)
Elements of Table Declarations
Most basic element: an attribute and its
type.
The most common types are:
INT or INTEGER (synonyms).
REAL or FLOAT (synonyms).
CHAR(n ) = fixed-length string of n characters.
VARCHAR(n ) = variable-length string of up to
n characters.
15
![Page 16: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/16.jpg)
Example: Create Table
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL
);
16
![Page 17: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/17.jpg)
SQL Values
Integers
reals
Strings requires single quotes.
Two single quotes = real quote, e.g., ’Joe’’s Bar’.
Bit strings of fixed or varying length, BIT(n) means bit string of length n
Any value can be NULL.
Boolean: true, false, unknown
17
![Page 18: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/18.jpg)
Dates and Times in SQL
The form of a date value is:
DATE ’yyyy-mm-dd’
Example: DATE ’2007-09-30’ for Sept. 30,
2007.
The form of a time value is:
TIME ’hh:mm:ss’
Example: TIME ’15:30:02.5’ = two and a
half seconds after 3:30PM.
18
![Page 19: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/19.jpg)
Declaring Keys
An attribute or list of attributes may be
declared PRIMARY KEY or UNIQUE.
Meaning: no two tuples of the relation may
agree in all the attribute(s) on the list.
PRIMARY KEY or UNIQUE attributes can be
declared when creating a table.
19
![Page 20: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/20.jpg)
Declaring Single-Attribute Keys
Place PRIMARY KEY or UNIQUE after the
type in the declaration of the attribute.
Example:
CREATE TABLE Beers (
name CHAR(20) UNIQUE,
manf CHAR(20)
);
20
![Page 21: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/21.jpg)
Declaring Multiattribute Keys
A key declaration can also be another
element in the list of elements of a CREATE
TABLE statement.
This form is essential if the key consists of
more than one attribute.
May be used even for one-attribute keys.
21
![Page 22: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/22.jpg)
Example: Multiattribute Key
The bar and beer together are the key for Sells:
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL,
PRIMARY KEY (bar, beer)
);
22
![Page 23: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/23.jpg)
PRIMARY KEY vs. UNIQUE
In a table declaration:
1. PRIMARY KEY : only one PRIMARY KEY , No
attribute of a PRIMARY KEY can ever be
NULL in any tuple.
2. UNIQUE: several UNIQUE attributes, may
have NULL’s values.
23
![Page 24: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/24.jpg)
24 24
Other Attributes Properties
NOT NULL = every tuple must have a real value for this attribute. i.e. the value for this attribute may never be NULL.
DEFAULT value = says that if there is no specific value known for this attribute’s component in some tuple, use the stated <value>.
![Page 25: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/25.jpg)
25
Example: Default Values
CREATE TABLE Drinkers (
name CHAR(30) PRIMARY KEY,
addr CHAR(50)
DEFAULT ‘123 Sesame St.’,
phone CHAR(16)
);
![Page 26: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/26.jpg)
26 26
Effect of Defaults
insert the fact that Sally is a drinker, but
we know neither her address nor her
phone.
INSERT INTO Drinkers(name)
VALUES(‘Sally’);
![Page 27: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/27.jpg)
27 27
Effect of Defaults (cont.)
What tuple appears in Drinkers?
name addr phone
‘Sally’ ‘123 Sesame St’ NULL
If we had declared phone NOT NULL,
this insertion would have been rejected.
![Page 28: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/28.jpg)
Semistructured Data
Based on trees.
Motivation:
flexible representation of data.
sharing of documents among systems
and databases.
28
![Page 29: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/29.jpg)
Graphs of Semistructured Data
Nodes = objects.
Labels on arcs (like attribute names).
Atomic values at leaf nodes (nodes with no
arcs out).
Flexibility: no restriction on
Labels out of a node.
Number of successors with a given label.
29
![Page 30: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/30.jpg)
Example: Data Graph
30
Bud
A.B.
Gold 1995
Maple Joe’s
M’lob
beer beer bar
manf manf
servedAt
name
name name
addr
prize
year award
root
The bar object for Joe’s Bar
The beer object for Bud
Notice a new kind of data.
![Page 31: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/31.jpg)
JavaScript Object Notation (JSON)
Standard for “serializing” data objects
Human-readable, useful for data
interchange
Useful for representing and storing
semistructured data
31
![Page 32: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/32.jpg)
JSON example
{“Beers”:
[ {“name”: “Bud”,
“manf”: “A.B.”,
“price”: 13},
{“name”: “Mobel”,
“manf”: “A.B.”,
“Prize”: {“year”: 1995,
“award”:”gold”}
] }
32
Basic constructs (recursive) • Base values number, string, boolean, … • Objects { } sets of label-value pairs • Arrays [ ] lists of values
![Page 33: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/33.jpg)
Relational Model versus JSON
Relational JSON
Structure Tables Nested sets, array
schema Fixed in advance Flexible, self descripting
Queries Simple expressive
language
Not widely used
Ordering none arrays
Implementation Native system NOSQL system
33
![Page 34: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/34.jpg)
XML versus JSON
XML JSON
Verbosity More Less
Complexity More Less
Validity DTD, XSD, widely
used
JSON scheme, not
widely used
Prog. Interface mismatch More direct
Querying Xpath,Xquery Json Path,
Json Query 34
![Page 35: chapter 2 relational model - SJTUli-fang/Chapter2-relationalModel.pdf · Chapter 2 The relational Model of data 1 Relational model introduction . Contents ... Relational algebra:](https://reader033.vdocuments.us/reader033/viewer/2022051602/5b1ea6337f8b9a901f8bdb5a/html5/thumbnails/35.jpg)
Summarization
Relational model, XML model, JSON notations
A data model consists of three parts:
Data structure√
Operations on the data ?
Constraints ?
Next:
Relational algebra: operations & constraints.
Relational algebra: the core of the SQL.
35