Information Systems Design

Goals of this course
To provide f thorough and systematic treatment of conceptual and logical design
To base this treatment on the Entity-Relation model
To advocate that conceptual design and function anslysis be conducted together
To address completely the translation of conceptual design in ER model in the three popular data models- relational, network, hierarhical, and vice versa
To illustrate the concepts via realistic large case study
To provide a survey of state of art of design tools
To provide enough support for students in terms of exercises and bibliographic notes

Conceptual DB Design

Functional Analysis for DB Design

Logical Design and Data Tools
11 High-Level Logical Design Using ER model
12 Logical Design for Relational Model
13. Logical Design for Network Model
14. Logical Design for hierarchical Model
15. DB Design Tools

DB design in the Information Systems Life Cycle

Phases of DB Design

Function driven approach to information system design

Dependence of DB design phases on the class of DBMS

Joint data- and function- driven approach to information systems design

Bibliography
1. W.Davis System Analysis and Design : A structured Approach . Addison-Wesley 1983
2. R.Farley Software engineering Concepts. VcGraw Hills 1985
3. A.Cardenas Data Base Management System. 2 ed. Allyn and Bacon 1985

Data Modeling Concepts

Structure of the lecture
Section 1- Abstractions
Section 2- Properties of mapping
Section 3- Data models, Schemas, Instances of DB
Section 4- ER Model
Section 5- How to read an ER-schema

Abstractions in Conceptual Data Design

Classification Abstraction

Table
The Classification Abstraction is used for defining as class of real world objects characterized by common properties
One level tree having as its root class
A Node leaf is a member of root class
{black chair, Black table, White chair, White table}
Table
Chair
Black Furniture
White Furniture
Black Table
White Table
Black Chair
White Chair

Aggregation Abstraction
Aggregation abstraction defines a new class from set of (other) classes that representits component parts
Leaf IS PART OF root class (is A)

Generalization Abstraction
A generalization abstraction defines a subset relationship between the elements of two or more classes In generalization all the abstractions defined for the generic class are inherited by all the subset classes

Properties of Mapping
A Binary aggregation is a mapping established between two classes.

Binary aggregation USES

Binary aggregation OWNS

Binary aggregation OWNS

Minimal cardinality (min-card)
Let us consider aggregation A between classes C1 and C2
The minimal cardinality or min-card of C1 in A denoted min-card(C1,A), is the minimum number of mappings in which every element of C1 can participate.
Similarly, the min-card of C2 in A, denoted min-card(C2,A), is the minimum number of mappings in which each element of C2 can participate

Maximal Cardinality
Let us consider aggregation A between classes C1 and C2
The maximal cardinality or max-card of C1 in A denoted max-card(C1,A), is the maximum number of mappings in which every element of C1 can participate.
Similarly, the max-card of C2 in A, denoted max-card(C2,A), is the maximum number of mappings in which each element of C2 can participate

One –to-one mapping
If max-card(C1,A)=1 and max-card(C2,A)=1 then we say that the aggregation is ONE-TO-ONE

One to many mapping
If max-card(C1,A)=n and max-card(C2,A)=1 then we say that the aggregation is ONE-TO-MANY

Many –to –one mapping
If max-card(C1,A)=1 and max-card(C2,A)=n then we say that the aggregation is MANY-TO-ONE

Many –to-many mapping
If max-card(C1,A)=m and max-card(C2,A)=n ( m, n>1), then we say that the aggregation is MANY-TO-MANY

N-ary aggregation
An n-ary aggregation is a mapping established among three or more classes
Minimal Cardinality ( min-card) Let us consider the aggregation A between classes C1,C2,…,Cn The min-card of Ci in A is minimal number of mappings in which each element of Ci can participate
Maximal Cardinality ( max-card) Let us consider the aggregation A between classes C1,C2,…,Cn. The max-card of Ci in A is maximum number of mappings, in which each element of Ci can participate
The two values of minimal and maximal cardinality completly characterize each participation of one class in aggregation

Representation of ternary aggregation Meets
CS47 .
EE01 .
.
.
. ter
. skil
.
.
.
.MON
. TUE
.WED
.THU
.FRI

Generalization
A Generalization Abstraction establishes the mapping from generic class to the subset class
Person
Male
Female
Total, Exclusive

Partial, overlapping generalization

Partial, Exclusive Generalization

Total, overlapping generalization

Data models
A Data model is a collection of concepts that can be used to describe a set of data and operations to manipulate the data.
When a data model describes a set of concepts from a given reality,
It is called a conceptual data model
The Concepts in data model are typically by using abstraction mechanisms and are described through linguistic and graphic representations . Syntax can be defined fnd a graphical notation can be developed as parts of data model.
Conceptual model is tool for representing reality at high level of abstraction
Logical models support data descriptions that can be processed by computer. They include hierarchical, network, relational models
These models to phisical structure of database

Schema
Schema is a representation of a specific portion of reality, built using a particular data model
Schema is static, time – invariant collection of linguistic or graphic representations that describe the structure of data of interest such as what within one organization

Instances
An instance of schema is a dynamic, time variant collection of data that conforms to the structure of data defined by the schema
Sample instance
PERSON
JOHN SMITHM 11WEST12.,FT.LAUDERDALE 387-6713-362
MARY SMITHF 11WEST12.,FT.LAUDERDALE 389-4816-381
JOHN DOLEM 11RAMONA ST. PAOLO ALTO 391-3873-132
CAR
CA13718 MASERATI WHITE
FL18MIAI PORSCHE BLUE
CA CATA17 DATSUN WHITE
FL 171899 FORD RED
OWNS
387-6713-362 FL 18MIAI
387-6713-362 FL171899
391-3873-132 CA13718
391-3873-132 CA CATA17
Sample instance after insertion
CAR
CA13718 MASERATI WHITE
FL18MIAI PORSCHE BLUE
CA CATA17 DATSUN WHITE
FL171899 FORD RED
NY BABYBLUE FERRARI RED
OWNS
387-6713-362 FL18MIAI
387-6713-362 FL171899
391-23873-132 CA13718
391-32873-132 CA CATA17
389-4816-381 NY BABYBLUE

Relationships between model, schema, instance

Qualities of Conceptual Models
1. Expressiveness
2. Simplicity
3. Minimality
4. Formality
PROPERTIES OF GRAPHIC REPRESENTATIONS
1.Graphic Completeness
2. Ease of Reading

The Entity –Relationship Model
Basic elements of the ER Model
Entities. Entities represent classes of real world objects
Relationships. Relationships aggregation of two or more entities
Binary and n-ary relationships
Rings – are binary relationships connecting an entity to itself
( recursive relationships)

Portion of ER-schema representing entities PERSON, CITY and relationships IS BORN IN and LIVES IN

Instance for previous schema
PERSON={p1,p2,p3}
CITY= {c1,c2,c3}
LIVES IN= {
,
,
}
IS BORN IN= {
,
}

N-ary relationship MEETS

Relationship MEETS

Relationship MANAGES

min-card( PERSON,LIVES IN)=1
max-card( PERSON,LIVES IN)=1
min-card( CITY,LIVES IN)=0
max-card( CITY,LIVES IN)=n

Relationship LIVES IN

Ring relationship MANAGES

Attributes
Attributes represent elementary properties of entities or relations

An ER schema with entities,relationships,attributs

An example of instance of DB schema
PERSON={p1:
,
p2:
P3:
}
CITY={c1:
,
c2:
,
c3:
}
LIVES IN={
>,
>
>}
IS BORN IN={
>
>}

Schema PERSONNEL
Schema : PERSONNEL
Entity: PERSON
Attributes: NAME: text (50)
SOCIAL SECURITY NUMBER: text (12)
PROFESSION: text (20)
(0,n) DEGREE: text (20)
Entity: CITY
Attributes: NAME: text (30)
ELEVATION: integer
NUMBER OF INHABITANTS: integer
Relationship:LIVES IN
Connected entities: (0,n) CITY
(1,1) PERSON
Attributes: MOVING DATE: date

Relationship: IS BORN IN
Connected entities: (0,n) CITY
(0,1) PERSON
Attributes: BIRTH DATE: date

Generalization Hierarchies
In the ER model it is possible to establish generalization hierarchies between entities
An entity E is generalization of a group of entities E1,E2,…,En if each object of classes E1,E2,…,En is also object of class
E
E1
E2
En

COVERAGE:
Total generalization (t)
Partial generalization (p)
Exclusive (e)
Overlapping (o)
Pair: (t,e) the most frequently used

Generalization hierarchy for entity PERSON

Inheritance
All the properties of the generic entity are inherited by the subset elements
PERSON
NAME
ADDRESS
NAME
ADDRESS
DRAFT STATUS
MALE
FEMALE
NAME
ADDRESS
MAIDEN
NAME
Incorrect representation

PERSON
PERSON
ADDRESS
NAME
DRAFT
STATUS
MALE
FEMALE
Correct representation
MAIDEN NAME

Formal definition of Inheritance
Let E be an entity. Let A1, A2,…,An be single valued, mandatory attributes of E. Let E1, E 2,…,En be other entities connected to E by mandatory, one –to-one or many-to-one binary relationships R1,R2,…,Rn ( i.e.
min-card(E,Ri)=1)) Consider as a possible identifier the set I= {A1,…,An,E1,…,Em}, 0 ≤n, 0 ≤m, 1 ≤n+m
The value of the identifier for a particular entity instance is defined as the collection of all values of attributes A1,..,An and all instances of entities Ej,
j=1,…,m connected to e with i ≤n, j
Because of the assumption of considering mandatory single- valued attributes or mandatory relationships with max-card set to each instance of E is mapped either to one value of attributes Ai or to one instance of entity Ej, i ≤n, j ≤m

Exammpe of schema transformation

Each schema TRANSFORMATION has a starting schema and a resulting schema
Each SCHEMA TRANSFORMATION maps names of concepts in starting schema to names of concepts in resulting schema.
Concepts in the resulting schema must inherit all logical connections in the starting schema

Properties of top –down primitives
They have a simple structure: the starting schema is a single concept, the resulting schema consists of small set of concepts.
All names are refined into new names describing the original concept in the lower abstraction level
Logical connections should be inherited by the single concept of the resulting schema

Top –Down Primitives

Application of top-down primitives

Applying of complex top –down schema transformation

Bottom-up primitives

Strategies for Schema Design

Top-down strategy

In the top-down strategy schema is obtained applying pure top-down refinement primitives

Bottom-Up strategy
In the bottom –up strategy we apply pure bottom –up primitives

Inside-out strategy
This strategy is a special type of bottom –up strategy
Here we fix the most important or evident concepts and then proceed by moving as oil stain does finding first the concepts that are conceptually close to starting concepts and then navigate to more distant ones

Mixed strategy

Criteria for Choosing among Concepts

Entity vs. Simple attribute

Generalization vs. attribute
Generalization will be used when we expect that some property will be associated to the lower level

Composite attribute vs set of simple attributes

Inputs, outputs and activities of conceptual design

Outputs