Transcript Relational Model

Relational Model
Reading: C&B, Chap 2, 3 & 4
In this lecture you will learn
• The concept of Model
• Simple definitions of terminology in Relational
Model
• Why relational model is important?
• Initial idea of Relational Querying
Dept. of Computing Science, University of Aberdeen
2
Introduction
• DBMS models data with respect to
– Machine
• physical storage of data
• involves low level details that are not important for user
– User
• logical view of data (tables)
• We focus on User’s ‘DATA MODEL’
Dept. of Computing Science, University of Aberdeen
3
The Concept of ‘Model’
• A model represents ‘something’.
– At a level of detail suitable for its purpose
• For example, an architects ‘model’ of a bridge
– Architects model brings the bridge to life even before its
construction
• Models can be:
– Blueprints for building the real ‘something’s (such as bridges)
– Useful for testing (model a bridge and test it before
construction)
– Handy for learning (you start driving lessons on a simulator)
• All our thinking is based on ‘models’ of things
– We build models of everything
• Thinking with models is considered ‘SMART’
Dept. of Computing Science, University of Aberdeen
4
Learning with Relational Model
• Theory of databases involves many models
– Relational model, ER model
• Relational model is the user’s data model and we
learn it first
• DBMSs that use relational model are called Relational
Database Management Systems (RDBMS)
• Apply our learning on a few real RDBMS (MSAccess &
MYSQL)
– Mostly in practical classes
– Also in lectures
• Hopefully this training will help us with other RDBMS
as well (such as SQL Server and Oracle)
Dept. of Computing Science, University of Aberdeen
5
Why Relational Model?
• Most modern database software is based on relational
model
– Relational model is what the user sees in a RDBMS
• Relational Model has a simple logical structure (as we
will see later)
• It is intuitive (as we will see later)
• Relational model has its origins in sound mathematics
– Set theory and theory of relations (hence the name
relational)
– We study the mathematics later in the course
• Relational Model describes the pure concepts behind
real database systems
– Real systems at times may have unfaithful implementations
of these pure concepts
Dept. of Computing Science, University of Aberdeen
6
What is relational model?
• A logical representation of a database
• Proposed by IBM’s E. F. Codd in 1970
– Won a Turing Award for the model!!
• Earlier data models were not relational. They
were
– Hierarchical (tree) or
– Network (graph)
• We focus on relational models in this course
Dept. of Computing Science, University of Aberdeen
7
Relational Model is simple
• Relational Database: a collection of connected
tables (relations)
– Named Tables (relations) have
• Named Columns (attributes)
• Unnamed Rows (tuples)
– Domain: a set of allowable values for one or more
columns
– Relationships – links or connections
– Keys (primary, foreign)
– Integrity Constraints
• It’s that simple!
Dept. of Computing Science, University of Aberdeen
8
Example: DreamHome
• DreamHome models data in a typical real estate business
– you will use this database in the practical
• DreamHome has data related to
– Branch offices
– Staff in each branch office
– …
• Branch office data
– Branch number
– Address
• Staff data
– Staff number
– Staff details
• Since staff belong to a branch office, there is a relationship
between staff data and branch office data.
– Staff and Branch tables are linked (connected)
Dept. of Computing Science, University of Aberdeen
9
Example: DreamHome 2
Branch
Relation
branchNo
Street
City
Postcode
B005
22 Deer Rd
London
SW1 4EH
B007
16 Argyll St
Aberdeen
AB2 3SU
B003
163 Main St
Glasgow
G11 9QX
B004
32 Clover Dr
Bristol
BS99 1NZ
B002
56 Clover Dr
London
NW10 6EU
Staff
Relation
Primary Key
Attributes
Foreign Key
staffNo
fName
LName
Position
Sex
DOB
Salary
branchNo
SL21
John
White
Manager
M
1-Oct-45
30000
B005
SG37
Ann
Beech
Assistant
F
10-Nov-60
12000
B003
SG14
David
Ford
Supervisor
M
24-Mar-58
18000
B003
SA9
Mary
Howe
Assistant
F
19-Feb-70
9000
B007
SG5
Susan
Brand
Manager
F
13-Jun-40
24000
B003
SL41
Julie
Lee
Assistant
F
13-Jun-65
9000
B005
Dept. of Computing Science, University of Aberdeen
10
Relation (Table)
• Relation has two parts
– Schema
• Name of the relation and the set of attributes & their
respective domains
• For example,
Branch (branchNo:domain1, street: domain2, city:domain3, postcode:domain4)
– Instance
• Set of attributes and their values in a tuple
• For example,
(branchNo:B005, street: 22 Dee Street, city:Aberdeen,postcode:AB25 1AB)
Dept. of Computing Science, University of Aberdeen
11
Key
• A key is an attribute or set of attributes that
uniquely identify a row
• A table may have many candidate keys
• Primary Key: Key selected for unique identification of
rows
– For example, branchNo in the Branch table
– Cannot be NULL
• Foreign Key: an attribute or a set of attributes in one
table that refer to a row in another table
– For example, branchNo in Staff table
Dept. of Computing Science, University of Aberdeen
12
Properties of relations
• Rows & columns can be in any order
• no two rows are identical
• all values belonging to a particular attribute
are from the same domain
• attributes are atomic
• attributes may have a null value (but not the
primary key)
Dept. of Computing Science, University of Aberdeen
13
Relational model is intuitive
• Many common datasets naturally fit relational
model
• Telephone directory
– Directory(Name:domain1, Address:domain2,
TelNo:domain3)
• Class Attendance Sheets
– Attendance(Name:domain1, IDNo:domain2,
Signature: domain3)
• Try with other datasets you know
Dept. of Computing Science, University of Aberdeen
14
Data Retrieval: Relational Querying
• The main strength of relational model: supports
simple yet powerful data retrieval (querying in
general)
– The whole purpose of modelling data is to be able to retrieve
data items efficiently
• Output of a query is modelled as a relation (table)
• In RDBMS users view data always in terms of tables
– if you learn relational model well you can find it easy to use
databases
• SQL (see-quel): a popular query language for
relational databases.
Dept. of Computing Science, University of Aberdeen
15
Example: Relational Querying
Branch
• Consider the branch
relation from the
DreamHome database
• Query: we want a list of
branches in London
• Output is a sub-relation
(sub-table) of the
Branch relation (table)
branchNo
Street
City
Postcode
B005
22 Deer Rd
London
SW1 4EH
B007
16 Argyll St
Aberdeen
AB2 3SU
B003
163 Main St
Glasgow
G11 9QX
B004
32 Clover Dr
Bristol
BS99 1NZ
B002
56 Clover Dr
London
NW10 6EU
London Branches
branchNo
Street
City
Postcode
B005
22 Deer Rd
London
SW1 4EH
B002
56 Clover Dr
London
NW10 6EU
Dept. of Computing Science, University of Aberdeen
16
Conclusion
• Relational model of database: a collection of
connected tables
– Conceptually relational model is simple
– Querying the data is the main goal of relational
modelling
• User interactions with RDBMS are through
SQL (Query language) only
• We now focus on learning SQL
– Later we will learn how to organize data into a
relational model (under database design)
Dept. of Computing Science, University of Aberdeen
17