YV - Database Design and E-R 44 Κεφάλαιο 2 MONTEΛΟΠΟΙΗΣΗ ΚΑΙ ΑΝΑΠΤΥΞΗ...

YV - Database Design and E-R 1

Κεφάλαιο 2Κεφάλαιο 2

MONTEΛΟΠΟΙΗΣΗ ΚΑΙ

ΑΝΑΠΤΥΞΗ ΒΑΣΕΩΝ ΔΕΔΟΜΕΝΩΝ


Introduction -- Data ModelingIntroduction -- Data Modeling

MODELS

- “FORMAL” MODEL

- “REAL WORLD” - Intuitive


Modeling Tools and Purposes 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


Modeling Tools - PurposesModeling 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)


Data Modeling -- QuestionsData 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? .........


Data Modeling -- Structure of a Data ModelData 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


Data Modeling -- Structure of a Data Model (a)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 -- SETSPicasso

S1 Miro the set of all artists

El Greco

Gonzales

S2 Picasso Thubithareta the set of all Spaniards


Data Modeling -- Structure of a Data Model (b)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 S2 S3


Data Modeling -- Structure of a Data Model (c)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!


The Process of Database Creation Using a Data ModelThe Process of Database Creation Using a Data Model

From the REAL WORLD

Real World

To the DATA MODEL Data Model

Database To the DATABASE


DATABASE SYSTEMS Database Development 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


Complete Process of Database DevelopmentComplete Process of Database Development

.

MINI WORLD

Requirements Collectionand Analysis

Conceptual DatabaseDesign (e.g., E-R Model)

Database Requirements

Conceptual Data ModelE-R diagram

LogicalDatabaseDesign

RelationalModel

DBMS SYSTEM INDEPENDENT DBMS SYSTEM DEPENDENT

Logical Data ModelConceptual /External Schemas

PhysicalDatabaseDesign

Physical Model Internal Schema

DatabaseLoading

Database


Database Development (1)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)


Running Example (1):A Company Database

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.


Running example database (2)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.


Database Development (2) Database DesignDatabase 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 (Conceptual Data Model)

Logical database design (Conceptual and External Schemas - Views)

Physical database design (Internal Schema)


Conceptual Database Design:Creating an Intuitive Model

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)


Logical Database Design: Creating a Conceptual Schema and the External

Schemas

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.


Physical Database Design: Creating the Internal Schema

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


Logical and Physical Database Designs: 3-level Architecture

Logical and Physical Database Designs: 3-level Architecture

.

EXTERNAL SCHEMA

EXTERNAL SCHEMA

CONCEPTUAL SCHEMA

INTERNAL SCHEMA

LOGICALDATABASEDESIGN

Using a Data Modellike the Relational

PHYSICALDATABASEDESIGN

Access Methods, etc.


Database Development (3)Data Loading

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


Automating Database DevelopmentAutomating 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.


Conceptual Model Design: The Entity-Relationship Model

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.


E-R Model ComponentsE-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


E-R Model: Structures (2)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.



DOMAINS are sets of values for attributese.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)



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.


Constraints: Relationships (1)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)



– 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 WORKS-FOR (they all work for a department) --- total

while, not all employees participate in the relationship MANAGES (not every employee is a manager) - partial



– 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 PERSON married-to PERSON (function)

1 : N MOTHER having CHILDREN (inverse function)

N : 1 STUDENT reports-to ADVISOR (function)

N : M EMPLOYEE works-on PROJECT



.

e1

e2

e3

e4

r1

r2

r3

r4

r5

d1

d2

d3

e1

e2

e3

e4

r1

r2

r3

r4

r5

d1

d2

d3

PERSON married-to PERSON MOTHER having CHILDREN

1 : 1 1 : N



.

e1

e2

e3

e4

r1

r2

r3

r4

r5

d1

d2

d3

e1

e2

e3

e4

r1

r2

r3

r4

r5

d1

d2

d3

STUDENT reports-to ADVISOR EMPLOYEE works-for PROJECT

N : 1 N : M


Constraints: Attributes (1)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.


Constraints: Attributes (2)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.


E-R Model: Other ConceptsE-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.


Aggregation

Used when we have to model a relationship involving (entitity sets and) a relationship set.– Aggregation allows

us to treat a relationship set as an entity set for purposes of participation in (other) relationships.

– Monitors mapped to table like any other relationship set.

Aggregation vs. ternary relationship: Monitors is a distinct relationship, with a descriptive attribute. Also, can say that each sponsorship is monitored by at most one employee.

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn


E-R Model: Graphical NotationE-R Model: Graphical Notation

. ENTITY SET

WEAK ENTITY SET

RELATIONSHIPSET

ATTRIBUTE

PRIMARY KEY

is_A

GENERALIZATION

E1R1E2

Total PARTICIPATION of E1 in R1

E1E2 R11 N

Cardinality ratio 1:N for E2:E1 in R1


E-R Model for the Company (1)E-R Model for the Company (1)

.

EMPLOYEE

SUPERVISION

1 N

Dependents-of

DEPENDENT

1

N

WORKS-FOR

WORKS-ON

MANAGES

N 1

1 1

MN

DEPARTMENT

PROJECT

CONTROLS

1

N

supervisor supervisee


E-R Model for the Company (2)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


Constraints Beyond the ER ModelConstraints 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.


E-R Model Limitations and Extensions (1)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


E-R Model Limitations and Extensions (2)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.


Database Development: Conceptual ModelDatabase Development: Conceptual Model

..

MINI WORLD

Requirements Collectionand Analysis

Conceptual DatabaseDesign (e.g., E-R Model)

Database Requirements


DBMS SYSTEM INDEPENDENT

- After using a Conceptual Data Model, like the E-R, tocreate the Conceptual Designwe reach a point where we haveto become DATABASE - DEPENDENT and transformthis intuitive description intoa FORMAL one which can be executed on the DBMS.


Database Development: Logical DesignDatabase Development: Logical Design

..


LogicalDatabaseDesign

RelationalModel

DBMS SYSTEM DEPENDENT

Logical Data ModelConceptual /External Schemas

At this stage, we have to selecta Logical Data Model, which issupported by a DBMS, and proceed with the Logical Database DesignThe common candidates are:RELATIONAL, NETWORK,and HIERARCHICAL


Logical Database DesignLogical Database Design

Virtually ALL logical data models represent Entity Sets as FILES (called Record Types or Relations)

Entity Set File

Entity Record

Attribute 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)


Database Design: Example (1)Database Design: Example (1)

.

EMPLOYEE WORKS-ON PROJECTN M

SSN

Name

Address

Salary

Number

Name

LocationHoursPerWeek

(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


Database Design: Example (2)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


Hierarchical Model - Intro (1)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 <-> Record Type

Entity <-> Record (Segment)

Attribute <-> Field

Relationship <-> Parent-to-Child

Parent-to-child relationships can only be 1:N



HIERARCHICAL SCHEMA

EMPLOYEE

PROJECT

SSN Name Address Salary

Number Name Location HoursPW

-- A hierarchical schema is a TREE, where each node is a RECORD TYPE

- 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



Example Database: A FOREST of database trees

e1 john athens 300000

p5 ytu crete 30

e3 mary patras 450000

p2 rty athens 15

p4 hju patras 20

p1 xyz crete 10

p2 rty athens 25

- records are interpreted as trees (thousands)- the M:N cardinality is dealt with DUPLICATION in the hierarchical model!

...

...

...



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)



.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.Nameend

get next EMPLOYEE where SSN = “E1”

do while there are still more project children of employee get next PROJECT child print PROJECT.Nameend

P1 E ? P ? E1



UpdatesINS: 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!)


- find the database tree for E3 and delete child P4

- PROBLEM: what if this is the only employee on P4 ?


- locate every P2 record and change the city value


DBTG-Network Data Model (1)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 <-> Record Type

Entity <-> Record

Attribute <-> Data Item

Relationship <-> DBTG-Set

owner-member relationships (1:N)– records and links in a single file– CONNECTOR records are used for N:M relationships


DBTG-Network Model (2)DBTG-Network Model (2)

DBTG-Network (CODASYL) Schema

EMPLOYEE

PROJECT

SSN Name Address Salary

Number Name Location

-- 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)

- Special RECORD Types are introduced to represent the N:M relationships (Connectors)

WORKSON

ESSN PNumber HoursPW

Emp_Works-On

Proj_Works-On



Example Database: Intertwined chains of records (network)

...

...

...

e1 john athens 300000 e3 mary patras 450000 e4 jack athens 145000

p1 xyz crete p2 rty athens p4 hju patras p5 ytu crete

e1 p2 15e1 p1 10 e3 p4 20e1 p5 30 e3 p2 25 e4 p4 40



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)




find next PROJECT record where Number = “P1” 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.Nameend

find next EMPLOYEE record where SSN = “E1”

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.Nameend



Updates


- 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)


- delete the connector record (Works-On) for this and adjust the two chains that this record is on.


- find the (single) record P2 and change the city value


Relational Model: Intro (1)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 <-> Relation

Entity <-> Record (Tuple)

Attribute <-> Attribute

Relationship <-> Relation

A Simple Model with Formal Foundations



Relational Schema

PROJECT

NUMBER NAME LOCATION

WORKS-ON

ESSN PNUMB HRSPW

EMPLOYEE

SSN NAME ADDRESS SALARY

-- A RELATIONAL schema is a set of RELATIONS, or TABLES each having a name

- The column names of the tables are the ATTRIBUTE names



Example Database: A set of populated TABLES

SSN NAME ADDRESS SALARY

ESSN PNUMB HRSPW

WORKS-ON

PROJECT NUMBER NAME LOCATION

EMPLOYEE

e1 john athens 300000

e3 mary patras 450000

e4 jack athens 145000

p1 xyz crete

p2 rty athens

p4 hju patras

p5 ytu crete

e1 p1 10

e1 p2 15

e1 p5 30

e3 p4 20

e4 p4 40

e3 p2 25

- the ROWS in the tables are called TUPLES and represent records



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)




select from EMPLOYEE, WORKS-ON relations where (WORKS-ON.Pnumb = “P1”) and (WORKS-ON.ESSN = EMPLOYEE.SSN) print EMPLOYEE.Name

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 CLOSEDi.e., they take tables and they produce again tables



Updates


- insert a tuple into the PROJECT table


- delete the tuple (e2, p4, 20) from WORKS-ON


- select the tuple (p2, rty, athens) from the PROJECT table, and change the value “athens” to “patras”


Logical Model ComparisonsLogical 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 44 Κεφάλαιο 2 MONTEΛΟΠΟΙΗΣΗ ΚΑΙ ΑΝΑΠΤΥΞΗ...

Documents

Transcript of YV - Database Design and E-R 44 Κεφάλαιο 2 MONTEΛΟΠΟΙΗΣΗ ΚΑΙ ΑΝΑΠΤΥΞΗ...