Transcript Database Design

Κεφάλαιο 2
MONTEΛΟΠΟΙΗΣΗ ΚΑΙ
ΑΝΑΠΤΥΞΗ ΒΑΣΕΩΝ ΔΕΔΟΜΕΝΩΝ
YV - Database Design and E-R
44
Introduction -- Data Modeling

MODELS
- “FORMAL” MODEL
- “REAL WORLD” - Intuitive
YV - Database Design and E-R
45
Modeling Tools and Purposes
NATURAL LANGUAGE (English, Greek, Chinese, etc.)
MATHEMATICS (quantity, measure, comparison, etc.)
LOGIC (reasons, implies, follows from, etc.)
PHYSICAL CHEMISTRY (molecular structure, energy, etc.)
PURPOSES:
-
RECORD INFORMATION
EXPLAIN BEHAVIOR
PREDICT BEHAVIOR
SIMULATION
HIDE DETAILS
YV - Database Design and E-R
46
Modeling Tools - Purposes
- FROM MORE TO LESS ABSTRACT:


REAL WORLD 
DATA (FACTS) 
MODELING TOOLS
- APART FROM RECORDING DATA, we need to:
ORGANIZE --- INTERPRET --- INTERCONNECT the data
A data model is a tool for effecting this abstraction
(irrespective of the particular data items)
YV - Database Design and E-R
47
Data Modeling -- Questions
- how might we model this world?
-
what are the essential components?
what do we know about them?
how to organize them?
how to inter-relate?
what information is of interest?
what information is irrelevant?
to whom?
for what?
how often?
under what conditions? .........
YV - Database Design and E-R
48
Data Modeling -- Structure of a Data Model
- A DATA MODEL has three essential components:
STRUCTURES
OPERATIONS
CONSTRAINTS
Each component is examined in the sequel, with a
running example from Mathematics
YV - Database Design and E-R
49
Data Modeling -- Structure of a Data Model (a)
(A) STRUCTURES
- abstract objects
- abstract away particular data
- concentrate on general properties

Example: A structure in Mathematics -- SETS
Picasso
S1
Miro
the set of all artists
El Greco
S2
YV - Database Design and E-R
Gonzales
Picasso Thubithareta
the set of all Spaniards
50
Data Modeling -- Structure of a Data Model (b)
(B) OPERATIONS
- manipulate the structures
- form new ones from “old”
- change the model from “static” to more “dynamic”

Example: An operation on mathematical sets -- SET INTERSECTION

S1
YV - Database Design and E-R
S2
S3
51
Data Modeling -- Structure of a Data Model (c)
(C) CONSTRAINTS
- properties that the structures of the models MUST obey
Examples:
“all artists must have a death date that is AFTER their birthdate”
“no museum employee must earn more than the museum director”
“the branch director of the bank must also be a banker”
There are different kinds of constraints:
INHERENT, EXPLICIT, and IMPLICIT.
All constraints the model lets you express, make the model more or
less appropriate to the needs of the enterprise to be modeled!
YV - Database Design and E-R
52
The Process of Database Creation Using a Data Model

From the REAL WORLD
Real World

To the DATA MODEL
Data Model
Database

To the DATABASE
YV - Database Design and E-R
53
DATABASE SYSTEMS Database Development

DATABASE DEVELOPMENT
– A long and tedious process of transforming the knowledge of
a real (mini) world into a database to be used with a DBMS
– To semantically simplify database development, the
complete process is broken down into separate consecutive
processes which generate intermediate descriptions.
Requirements Collection and Analysis, Conceptual
Database Design, Logical Database Design, Physical
Database Design, Database Loading
YV - Database Design and E-R
54
Complete Process of Database Development
DBMS SYSTEM INDEPENDENT
DBMS SYSTEM DEPENDENT
.
Relational
Model
MINI WORLD
Requirements Collection
and Analysis
Database Requirements
Logical Data Model
Conceptual /External
Schemas
Logical
Database
Design
Conceptual Database
Design (e.g., E-R Model)
Conceptual Data Model
YV - Database Design and E-R
E-R diagram
Database
Physical
Database
Design
Physical Model
Internal Schema
Database
Loading
55
Database Development (1)

REQUIREMENTS COLLECTION and ANALYSIS
– The first process (phase) is called REQUIREMENTS
COLLECTION and ANALYSIS and is necessary to obtain the
database requirements from the real world description.
Real World Descriptions

database requirements
The process is hard conceptually (requires interviews,
experience and few tools are available)
YV - Database Design and E-R
56
Running Example (1):
A Company Database

Requirements of the COMPANY database
– A company is organized into DEPARTMENTS. Each
department has a name, number, and an employee that
MANAGES the department. We are interested on the start
date of the department manager.
– A department has several locations. Each department
controls a number of PROJECTS, where each project has a
name, number and is located at a single location.
YV - Database Design and E-R
57
Running example database (2)
– Regarding the EMPLOYEEs, we want to store their social
security number, address, salary, sex, and birth date.
– Each employee WORKS FOR one department, but may
WORK ON several projects. We also keep track of the
number of hours per week that an employee currently works
on each project, as well as his/her direct supervisor.
– Each employee may have a number of DEPENDENTS.
For each dependent, we keep their name, birth date, sex
and relationship with the employee.
YV - Database Design and E-R
58
Database Development (2) Database Design

The process continues with the DATABASE DESIGNS
– Design is the process of specifying the schema of a database
using a given model (at some level of database development)
– There are 3 separate database designs which generate
intermediate schemas.
Conceptual database design
Logical database design
Physical database design
YV - Database Design and E-R
(Conceptual Data Model)
(Conceptual and External
Schemas - Views)
(Internal Schema)
59
Conceptual Database Design:
Creating an Intuitive Model

CONCEPTUAL DATABASE DESIGN
– Effort to provide Semantic Clarity
– Identify the Semantic Objects, the Relationships among
them, as well as semantic rules (constraints) that must hold
– OBJECTIVE:
An abstract, yet complete description of the part of the world
that will be recorded in the database using a pseudo-formal
notation (e.g., the E-R model)
YV - Database Design and E-R
60
Logical Database Design: Creating a
Conceptual Schema and the External
Schemas

LOGICAL DATABASE DESIGN
– The process of transforming a conceptual design (model)
into formal schemas expressed in the chosen data model
(the one supported by the DBMS to be used, e.g., relational)
– The schemas (external, conceptual) together with the DML
determine the application programs
– Some Schemas, if well-designed, make queries simpler
– Some Schemas - appropriately designed - avoid
redundancy, avoid update anomalies, etc.
YV - Database Design and E-R
61
Physical Database Design:
Creating the Internal Schema

PHYSICAL DATABASE DESIGN
– The process of specifying the internal storage structures of
the objects implementing the conceptual schema -- that is,
the access methods that comprise the internal schema.
OBJECTIVES:
– Performance - Performance - Performance
– Support of the Query Optimizer
YV - Database Design and E-R
62
Logical and Physical Database Designs:
3-level Architecture
.
EXTERNAL
SCHEMA
EXTERNAL
SCHEMA
LOGICAL
DATABASE
DESIGN
CONCEPTUAL
SCHEMA
Using a Data Model
like the Relational
PHYSICAL
DATABASE
DESIGN
INTERNAL
SCHEMA
YV - Database Design and E-R
Access Methods, etc.
63
Database Development (3)
Data Loading

Finally, comes the process of DATABASE LOADING
Raw Data Files
Database
Internal Schema
– There are always Database BULK LOADING utilities
available together with a large DBMS
YV - Database Design and E-R
64
Automating Database Development

TOOLS FOR DATABASE DEVELOPMENT
– It is hard to automate the higher level database development
processes, but at lower levels there are computerized tools
available that assist the designer in the challenging task.
For instance, tools generating “draft” conceptual schemas
(mostly in E-R) from a conceptual model (mostly relational)
e.g., AD/VANCE, Blue/60, Colonel, ER-Designer, Lyddia,
IDEF/Leverage, MastER PLus, RIDL, Silverrun, etc.
– Some can also generate and evaluate the internal schemas
(e.g., for consistency) or create data dictionaries
– There are also commercial CASE tools for DB development
e.g., IEW, IEF, Excelerator, CASE Product, Foundation, etc.
YV - Database Design and E-R
65
Conceptual Model Design:
The Entity-Relationship Model

The E-R model is the “winner” conceptual model that was
accepted by the researchers and the market because of its
simplicity, clarity and graphical notation.

The E-R model is essentially an intuitive model that
attempts to abstract the kinds of real world information
which typical databases store

All other models can be judged in terms of how they
accommodate the features of the E-R Model.
YV - Database Design and E-R
66
E-R Model Components

STRUCTURES
– There are two semantic primitives:
» Entities
Specific objects or “things” that exist or are thought to exist
and are to be represented in the database
e.g., the EMPLOYEE “mary”, the “research” DEPARTMENT,
the PRODUCT “bolts”, the TEAM “olympiakos”, ...
» Relationships
These are also (special) objects that associate two or more
distinct entities with a specific meaning
(formally,
a relationship is an ordered set of entities)
e.g., mary “is fan” of olympiakos, mary “works for” research
YV - Database Design and E-R
67
E-R Model: Structures (2)

Entities and Relationships may have ATTRIBUTES, which
are properties used to describe them
e.g., a PERSON entity may have name, age, address, ...
(for instance, “mary”, “21”, “Mela Street no. 10”, ...)
also, date is an attribute of the relationship BORROWS (for
instance, mary borrowed a book on “October 20”)
– Usually the attributes of a relationship define such properties:
- who established the relationship
- when it was established
- when it may expire, etc.
YV - Database Design and E-R
68
E-R Model: Structures (3)

DOMAINS are sets of values for attributes
e.g., DOMAIN OF NAMES = the set of all names
DOMAIN of WEIGHT = the set of all weights
INTEGERS FROM 0 to 220
CHAR STRINGS from 1 to 10 in length, etc.

Types of Attributes
– SIMPLE: an entity has a single atomic value (Sex, SSN)
– COMPOSITE: the attribute is composed of several
components (e.g., address = { Street, No, Town, Country} )
– MULTI-VALUED: an entity may have multiple values for that
attribute (e.g., color of a CAR, degrees of a PERSON)
YV - Database Design and E-R
69
E-R Model: Structures (4)

Entities with the same attributes are grouped or typed into
ENTITY SETS (also called, ENTITY TYPES)
– For instance, all PERSONS, all DEPARTMENTS, etc.

Relationships of the same type are also grouped/typed into
RELATIONSHIP SETS (TYPES)
– For instance, the WORKS-ON relationship set in which the
entity sets EMPLOYEE and PROJECT participate.
or, the MANAGES relationship set in which the entity sets
EMPLOYEE and DEPARTMENT participate.
or. the SALE-ACT relationship set in which PRODUCTS,
CUSTOMERS, and SALESPERSONS participate.
YV - Database Design and E-R
70
Constraints: Relationships (1)

CONSTRAINTS in the E-R Model
1. Properties of RELATIONSHIP SETS
– More than one relationship set can exist with the same
participating entity types (multiple relationships)
e.g., WORKS-FOR and MANAGES between EMPLOYEE
and DEPARTMENT.
– A relationship can relate two entities of the same entity set
(this is called, a recursive relationship type)
e.g., a SUPERVISION relationship type relates one
EMPLOYEE (in the role of supervisee) with another
EMPLOYEE (in the role of supervisor)
YV - Database Design and E-R
71
Constraints: Relationships (2)
– A relationship set may involve (relationship degree):
2 entity sets ---> binary relationship
3 entity sets ---> ternary relationship
n entity sets ---> N-ary relationship
– The existence dependency specifies whether an entity
participates in a relationship and is either total or partial
e.g., all employees participate in the relationship WORKSFOR (they all work for a department) --- total
while, not all employees participate in the relationship
MANAGES (not every employee is a manager) - partial
YV - Database Design and E-R
72
Constraints: Relationships (3)
– The cardinality of a (binary) relationship set defines how
many entities of the first entity set in the relationship can be
related to how many entities of the second.
It can be:
1:1
1:N
N:1
N:M
YV - Database Design and E-R
PERSON married-to PERSON (function)
MOTHER having CHILDREN (inverse function)
STUDENT reports-to ADVISOR (function)
EMPLOYEE works-on PROJECT
73
Constraints: Relationships (4)
1:N
1:1
.
r1
e1
e2
e3
e4
e1
r2
d1
e2
r3
d2
e3
r4
d3
e4
married-to
YV - Database Design and E-R
r2
d1
r3
d2
r4
d3
r5
r5
PERSON
r1
PERSON
MOTHER
having
CHILDREN
74
Constraints: Relationships (5)
.
N:M
N:1
e1
e2
e3
e4
r1
e1
r2
d1
e2
r3
d2
e3
r4
d3
e4
YV - Database Design and E-R
r2
d1
r3
d2
r4
d3
r5
r5
STUDENT reports-to
r1
ADVISOR
EMPLOYEE works-for
PROJECT
75
Constraints: Attributes (1)
2.- Properties of ATTRIBUTES in SETS
– An attribute (or set of attributes) of an entity/relationship set
for which each entity/relationship in the set must have
unique value(s) is a key (sometimes called a superkey).
For instance, SSN of EMPLOYEE, NAME and ADDRESS
of EMPLOYEE, SSN and NAME of EMPLOYEE, etc.
– A candidate key is a minimal superkey (that is, no subset of
its attributes is a key)
For instance, SSN is a candidate key for EMPLOYEE, but
the combination {SSN, NAME} is not.
YV - Database Design and E-R
76
Constraints: Attributes (2)
– A primary key is one of the candidate keys that is agreed to
serve as an identifier for the entity/relationship set.
For instance, SSN is a good choice to be the primary key for
the entity set EMPLOYEE.
– A foreign key is a set of one or more attributes of an entity
(or relationship) set that forms a primary key for another
entity/relationship set.
For instance, for the relationship set of employees
WORKING-ON projects the attribute SSN is a foreign key.
YV - Database Design and E-R
77
E-R Model: Other Concepts

Weak Entity Types (or, Dependent Entity Types)
– An entity type that does not have a key attribute
For instance, the entity type DEPENDENT
(it only makes sense in connection with his/her relative
EMPLOYEE who works for the company)

Generalization (is-A) relationship
– view a number of entity sets as being of one generic type
PAINTING is-A ART-OBJECT, DRAWING is-A ART-OBJECT
– Inheritance of properties (attributes) is the important benefit.
For instance, we keep attributes that are common to
PAINTINGS and DRAWINGS in ART-OBJECT and do not
have to repeat them - they are inherited.
YV - Database Design and E-R
78
name
ssn
Aggregation

Used when we have to
model a relationship
involving (entitity sets and)
a relationship set.
– Aggregation allows
lot
Employees
Monitors
until
started_on
us to treat a
dname
relationship set
pid
pbudget
did
budget
as an entity set
for purposes of
Sponsors
Departments
Projects
participation in
(other) relationships.
– Monitors mapped to  Aggregation vs. ternary relationship:
table like any other  Monitors is a distinct relationship,
relationship set.
with a descriptive attribute.
Also, can say that each sponsorship
is monitored by at most one employee.

YV - Database Design and E-R
79
E-R Model: Graphical Notation
.
ENTITY SET
is_A
WEAK
ENTITY SET
RELATIONSHIP
SET
GENERALIZATION
E2
R1
E1
Total PARTICIPATION of E1 in R1
ATTRIBUTE
1
E2
PRIMARY KEY
YV - Database Design and E-R
N
R1
E1
Cardinality ratio 1:N for E2:E1 in R1
80
E-R Model for the Company (1)
1
N
.
WORKS-FOR
DEPARTMENT
EMPLOYEE
1
1
1
MANAGES
1
N
1
M
SUPERVISION
Dependents-of
supervisor
supervisee
CONTROLS
N
N
WORKS-ON
PROJECT
N
DEPENDENT
YV - Database Design and E-R
81
E-R Model for the Company (2)

Attributes in Entities and Relationships (Note: the notation
here is different from the traditional, i.e. graphical with
eclipses, for saving space.)
–
–
–
–
–
–
EMPLOYEE -- SSN, Name, BirthDate, Sex, Address, Salary
DEPARTMENT -- Number, Name,Locations, NoOfEmployees
PROJECT -- Number, Name, Location
DEPENDENT -- Name, Sex, BirthDate, Relationship
WORKS-ON -- HoursPerWeek
MANAGES -- StartDate
YV - Database Design and E-R
82
Constraints Beyond the ER Model

Functional dependencies:
– e.g., A dept can’t order two distinct parts from the same supplier.
» Can’t express this wrt ternary Contracts relationship.
– Normalization refines ER design by considering FDs.

Inclusion dependencies:
– Special case: Foreign keys (ER model can express these).
– e.g., At least 1 person must report to each manager. (Set of ssn
values in Manages must be subset of supervisor_ssn values in
Reports_To.) Foreign key? Expressible in ER model?

General constraints:
– e.g., Manager’s discretionary budget less than 10% of the
combined budget of all departments he or she manages.
YV - Database Design and E-R
83
E-R Model Limitations and Extensions (1)

There are a lot of “E-R like” models available today that
attempt to solve some of the inherent limitations of the E-R
Model, like:
– The Model is weak expressively in that it does not support
directly a relationship to hold between relationships
Rather, it forces such relationship to be represented by a
relationship between their component entity types,
DATE is a relationship between MONTH, DAY and YEAR
EMPLOYMENT is a relationship between employee and employer
HIRING is a relationship between DATE and EMPLOYMENT
YV - Database Design and E-R
84
E-R Model Limitations and Extensions (2)

E-R Model Extensions
– deal with more “exact” constraints (e.g., “exactly one”, “at most
two”, “any number of”, etc. in participation)

Generally, several E-R extensions attempt to bring the E-R
Model closer to a SEMANTIC MODEL (in the sense of
Artificial Intelligence)

Another important limitation of the E-R Model is the lack of a
standard DML (No OPERATIONS in the Model).
Intuitively, someone may think of “navigation” in the network
graphically depicted with an E-R Diagram.
YV - Database Design and E-R
85
Database Development: Conceptual Model
DBMS
. SYSTEM INDEPENDENT
.
MINI WORLD
Requirements Collection
and Analysis
Database Requirements
Conceptual Database
Design (e.g., E-R Model)
Conceptual Data Model
YV - Database Design and E-R
- After using a Conceptual
Data Model, like the E-R, to
create the Conceptual Design
we reach a point where we have
to become DATABASE DEPENDENT and transform
this intuitive description into
a FORMAL one which can be
executed on the DBMS.
E-R diagram
86
Database Development: Logical Design
DBMS SYSTEM DEPENDENT
.
.
At this stage, we have to select
a Logical Data Model, which is
supported by a DBMS, and
proceed with the
Logical Database Design
The common candidates are:
RELATIONAL, NETWORK,
and HIERARCHICAL
Conceptual Data Model
YV - Database Design and E-R
Relational
Model
Logical Data Model
Conceptual /External
Schemas
Logical
Database
Design
E-R diagram
87
Logical Database Design

Virtually ALL logical data models represent Entity Sets as
FILES (called Record Types or Relations)
Entity Set
Entity
Attribute

File
Record
Field
The differences between the logical data models boil down
to how they represent RELATIONSHIP SETS,
– Some models allow only FUNCTIONAL relationships which
makes N:M relationships hard to represent (only indirectly)

BASIC TECHNIQUES:
- Represent relationship sets as FILES (e.g., relational model)
- Represent them as LINKS between files (network, hierarchical)
YV - Database Design and E-R
88
Database Design: Example (1)
.
N
EMPLOYEE
SSN
PROJECT
WORKS-ON
Address
Name
M
Salary
Number
Location
HoursPerWeek
Name
(Part of) the Company Database in the E-R Model: An N:M total relationship
set (WORKS-ON) between the entity types EMPLOYEE and PROJECT
YV - Database Design and E-R
89
Database Design: Example (2)

Consider 5 operations on this Conceptual Model
Q1:
Who works-on P1?
(which EMPLOYEEs work on the P1 PROJECT)
Q2: Where does E1 work-on?
(in which PROJECTS does EMPLOYEE E1 work on)
INS: Insert information about a new PROJECT
DEL: Delete the fact that employee E3 works on project P4
MOD: Project P2 has moved from Athens to Patras
YV - Database Design and E-R
90
Hierarchical Model - Intro (1)




Data Structures: TREES (Nodes and Links)
Operations:TREE-TRAVERSAL (parent to children)
Constraints: few, mostly inherent in the tree structure
Correspondence with the E-R Model
Entity Set
Entity
Attribute
Relationship
<->
<->
<->
<->
Record Type
Record (Segment)
Field
Parent-to-Child
Parent-to-child relationships can only be 1:N
YV - Database Design and E-R
91
Hierarchical Model - Intro (2)

HIERARCHICAL SCHEMA
-- A hierarchical schema
is a TREE, where each
node is a RECORD TYPE
EMPLOYEE
SSN
Name Address Salary
PROJECT
Number
Name Location HoursPW
- A LINK represents a
RELATIONSHIP (only 1:N)
- The relative position of the
nodes specifies the direction
and cardinality of the relationship
PARENT to CHILD (1:N)
- Note that the attribute HoursPW
of the relationship WORKS-ON
has been moved to the PROJECT
YV - Database Design and E-R
92
Hierarchical Model - Intro (3)

Example Database: A FOREST of database trees
e3
e1
john
mary
patras
p2
athens 300000
...
p4
hju
...
450000
rty
athens 25
patras 20
...
p5
p1
p2
rty
xyz
crete 10
YV - Database Design and E-R
ytu
athens 15
crete 30
- records are interpreted
as trees (thousands)
- the M:N cardinality is
dealt with DUPLICATION
in the hierarchical model!
93
Hierarchical Model - Intro (4)

Operations: Tree-traversal
–
–
–
–


get next tree (of a given type)
get next child (of a given record)
pre-order tree-traversal
RECORD-AT-A-TIME ACCESS
The example queries Q1 and Q2 which are completely
SYMMETRICAL are treated by the hierarchical model DML in
a very ASYMMETRIC way (as will be seen next)
This is because EMPLOYEE was chosen to be the parent and
PROJECT to be the child (rather than the opposite)
YV - Database Design and E-R
94
Hierarchical Model - Intro (5)
. Q1:
Employees of P1
Q2: Projects for E1
do while there are still
more employees
get next EMPLOYEE
get next PROJECT child
where Number = “P1”
if found then
print EMPLOYEE.Name
end
P1
YV - Database Design and E-R
E?
get next EMPLOYEE
where SSN = “E1”
do while there are still more
project children of employee
get next PROJECT child
print PROJECT.Name
end
P?
E1
95
Hierarchical Model - Intro (6)

Updates
INS:
Insert information about a new PROJECT
- it is not possible to insert information about a new
project, unless there is an employee that works-on it.
(no child can exist without its parent!)
DEL:
Delete the fact that employee E3 works on project P4
- find the database tree for E3 and delete child P4
- PROBLEM: what if this is the only employee on P4 ?
MOD: Project P2 has moved from Athens to Patras
- locate every P2 record and change the city value
YV - Database Design and E-R
96
DBTG-Network Data Model (1)




Data Structures: constrained GRAPHS of records and links
Operations: arbitrary GRAPH-TRAVERSAL (navigation)
Constraints: several inherent and many explicit
Correspondence with the E-R Model
Entity Set
Entity
Attribute
Relationship
<-> Record Type
<-> Record
<-> Data Item
<-> DBTG-Set
owner-member relationships (1:N)
– records and links in a single file
– CONNECTOR records are used for N:M relationships
YV - Database Design and E-R
97
DBTG-Network Model (2)

DBTG-Network (CODASYL) Schema
EMPLOYEE
SSN
Name Address Salary
Emp_Works-On
WORKS
ON
ESSN PNumber
HoursPW
-- A NETWORK schema
is a GRAPH, where each
node is a RECORD TYPE
- A LINK represents a
RELATIONSHIP (only 1:N)
and has name and direction
(OWNER to MEMBER)
Proj_Works-On
PROJECT
Number
YV - Database Design and E-R
Name
Location
- Special RECORD Types are
introduced to represent the N:M
relationships (Connectors)
98
DBTG-Network Model (3)

Example Database: Intertwined chains of records (network)
e1
john
e1
p1
p1
athens 300000
10
xyz
e1
crete
YV - Database Design and E-R
p2
15
p2
e1
rty
e3
mary
p5
30
athens
patras 450000
e3
p4
p4
...
20
hju
e3 p2
patras
e4
jack
25
athens 145000
e4 p4
p5
ytu
40
crete
...
...
99
DBTG-Network Model (4)

Operations: GRAPH Traversal (Navigation)
–
–
–
–
–

find next record where ...
find next record within set type ...
standard order traversal (first, next, previous, last, etc.)
use of many pointers and currency indicators
RECORD-AT-A-TIME access
The example queries Q1 and Q2 which are completely
SYMMETRICAL are treated by the network model DML
in a completely SYMMETRIC way (as will be seen next)
YV - Database Design and E-R
100
DBTG-Network Model (5)
. Q1:
Employees of P1
Q2: Projects for E1
find next PROJECT record
where Number = “P1”
find next EMPLOYEE record
where SSN = “E1”
do while there are still more
connector records under project
find next WORKS-ON record
in Proj_Works-On set
find (owner) EMPLOYEE record
in Emp_Works-On set
print EMPLOYEE.Name
end
do while there are still more
connector records under employee
find next WORKS-ON record
in Emp_Works-On set
find (owner) PROJECT record
in Proj_Works-On set
print PROJECT.Name
end
YV - Database Design and E-R
101
DBTG-Network Model (6)

Updates
INS:
Insert information about a new PROJECT
- create a new PROJECT occurrence
- in the beginning, there are no connectors, so it chains
to itself. (owners can exist without members, unless
otherwise specified --- via a membership constraint)
DEL: Delete the fact that employee E3 works on project P4
- delete the connector record (Works-On) for this
and adjust the two chains that this record is on.
MOD: Project P2 has moved from Athens to Patras
- find the (single) record P2 and change the city value
YV - Database Design and E-R
102
Relational Model: Intro (1)




Data Structures: RELATIONS or TABLES (one structure)
Operations: reduce tables, combine tables into larger ones
Constraints: few inherent (e.g., math sets), many explicit
Correspondence with the E-R Model
Entity Set
Entity
Attribute
Relationship
<->
<->
<->
<->
Relation
Record (Tuple)
Attribute
Relation
A Simple Model with Formal Foundations
YV - Database Design and E-R
103
Relational Model: Intro (2)

Relational Schema
EMPLOYEE
SSN
NAME ADDRESS SALARY
-- A RELATIONAL schema
is a set of RELATIONS, or
TABLES each having a name
WORKS-ON
ESSN PNUMB
HRSPW
- The column names of the tables
are the ATTRIBUTE names
PROJECT
NUMBER NAME
YV - Database Design and E-R
LOCATION
104
Relational Model: Intro (3)

Example Database: A set of populated TABLES
EMPLOYEE
SSN
NAME ADDRESS SALARY
WORKS-ON
PROJECT
e1
john
athens
300000
e3
mary
patras
450000
e4
jack
athens
145000
NUMBER NAME
LOCATION
ESSN PNUMB
HRSPW
e1
p1
10
e1
p2
15
e1
p5
30
e3
p4
20
p1
xyz
crete
e4
p4
40
p2
rty
athens
e3
p2
25
p4
hju
patras
p5
ytu
crete
YV - Database Design and E-R
- the ROWS in the tables are called
TUPLES and represent records
105
Relational Model: Intro (4)

Operations: Set-Theoretic Simple Operations
–
–
–
–

select records from RELATION(s) where ...
the where-clause may be complex, involving many operators
no order in retrievals is seen by the user
SET-AT-A-TIME record access seen by the user (even though, at
the physical level the system uses a RECORD-AT-A-TIME access)
The example queries Q1 and Q2 which are completely
SYMMETRICAL are treated by the relational model DML in a
completely SYMMETRIC way (as will be seen next)
YV - Database Design and E-R
106
Relational Model: Intro (5)
. Q1:
Employees of P1
select from EMPLOYEE,
WORKS-ON relations
where (WORKS-ON.Pnumb = “P1”)
and (WORKS-ON.ESSN =
EMPLOYEE.SSN)
print EMPLOYEE.Name
Q2: Projects for E1
select from PROJECT,
WORKS-ON relations
where (WORKS-ON.ESSN= “E1”)
and (WORKS-ON.Pnumb =
PROJECT.Number)
print PROJECT.Name
OPERATIONS in the relational model DML are CLOSED
i.e., they take tables and they produce again tables
YV - Database Design and E-R
107
Relational Model: Intro (6)

Updates
INS:
Insert information about a new PROJECT
- insert a tuple into the PROJECT table
DEL:
Delete the fact that employee E3 works on project P4
- delete the tuple (e2, p4, 20) from WORKS-ON
MOD: Project P2 has moved from Athens to Patras
- select the tuple (p2, rty, athens) from the PROJECT
table, and change the value “athens” to “patras”
YV - Database Design and E-R
108
Logical Model Comparisons

The main difference between the 3 classical logical models
is HOW RELATIONSHIPS ARE REPRESENTED
– RELATIONAL: explicitly by RELATIONS (in case of N:M
relationships) or/and with implicit connections between
relations represented by values of foreign keys.
– NETWORK: explicit connections via links and connector
records. COMMENT: powerful in representation but complex
– HIERARCHICAL (special case of network): explicit
connections via links. The case of N:M relationships is dealt
with by duplication of records. COMMENT: serious
limitations in expressiveness of non-hierarchic structures
YV - Database Design and E-R
109