IELM 511: Information System design

1
IELM 511 Information System design
Introduction
Part I. ISD for well structured data relational
and other DBMS
Info storage (modeling, normalization) Info
retrieval (Relational algebra, Calculus, SQL) DB
integrated APIs
Part II. ISD for systems with non-uniformly
structured data
Basics of web-based IS (www, web2.0, ) Markups,
HTML, XML Design tools for Info Sys UML
Part III (one out of)
APIs for mobile apps Security, Cryptography IS
product lifecycles Algorithm analysis, P, NP, NPC
2
Example Banking system
Bank is organized in branches. Each branch is
located in a particular city and identified by a
unique name. The bank monitors the assets of each
branch.
Customers are identified by their SSN (equiv to
HKID). The bank stores each customers name and
address. Customers may have accounts, and can
take out loans. A customer may be associated
with a particular banker, who may act as a loan
officer of personal banker for that customer.
Bank employees are also identified by SSN. The
bank stores the Name, address, phone , start day
of employment of each employee, the name of all
dependents of the employee, and the manager of
the employee.
The bank offers two types of accounts savings
and checking. Accounts can be held by more than
one customer, and a customer may have many
accounts. Each account has a unique account
number. We store each accounts balance, and the
most recent date when the account was accessed by
each customer holding the account. Each savings
account has an interest rate, and overdrafts are
recorded for each checking account.
A loan originates at a particular branch, and is
held by one or more customers. Each loan has a
unique number. For each loan, the bank stores the
loan amount and the payments (date and amount) .
Payment numbers are not unique, but a payment
number uniquely identifies a payment for a
specific loan.
3
Information Storing ER models
Entity a well defined real/abstract object in
the domain of the IS. e.g. A particular customer
of the bank A specific loan
Attributes properties whose values describe the
entity. e.g. Customer is described by attributes
SSN, Name, Address
name
address
ssn
customer
4
ER models entity types
Entity set a set of entities of the same
type. e.g. Jones, 321-12-3123, Main, Harrison,
..., Adams, 335-57-7991, Spring, Pittsfield
Types of entities - Regular entity one or a
combination of attribute values uniquely
identifies the entity in a set. - Weak entity
no combination of attribute values can uniquely
identify the entity in a set.
name
address
ssn
payment_no
date
amount
customer
loan-payment
5
ER models attribute types
- Simple attribute The attribute has values
that are atomic
ssn
street
- Composite attribute Attribute value is
composed of 2 or more pieces
address
town
- Single valued A given entity will only have
one value for that attribute
name
- Multi-valued A unique entity may have
multiple values for this attribute
dependent-name
- Derived attribute If the value of the
attribute can be derived/computed from some
other values
employment-length
6
ER models relationships
A relationship is an association between two or
more entities. e.g. a customer Hayes borrows the
loan L-15
Relationship set borrows
Entity set customer
Entity set loans
Jones 321-12-3123 Main Harrison
Smith 019-28-3746 North Rye
Hayes 677-89-9011 Main Harrison
Williams 963-96-3963 Nassau Princeton
Adams 335-57-7991 Spring Pittsfield
L-17 1000
L-23 2000

L-11 900
L-16 1300
amount
name
address
loan_no
ssn
borrows
customer
loan
7
Relationship sets participation (aka existence
dependency)
borrows
Entity set customer
Entity set loans
Jones 321-12-3123 Main Harrison
Smith 019-28-3746 North Rye
Hayes 677-89-9011 Main Harrison
Williams 963-96-3963 Nassau Princeton
Adams 335-57-7991 Spring Pittsfield
L-17 1000
L-23 2000

L-11 900
L-16 1300
Entity Jones of type customer participates in
relationship borrows
Total participation if each entity of some set
has at least one relationship of type borrows,
then it has total participation in that
relationship type. e.g. loans has total
participation in borrows
Partial participation if some entities of a set
do not participate in a relation. e.g. customer
has partial participation in borrows
8
Relationship sets cardinality
Cardinality refers to how many of entities of a
set can be related to to another entity in a
relationship set.
mn cardinality Each customer may borrow more
than one, say m, loans Each loan may be held by
more than one, say n, customers.
name
address
amount
ssn
loan_no
m
n
borrows
customer
loan
1n cardinality e.g. Each employee can have at
most one manager (manager is also an employee)
1
manages
employee
n
11 cardinality e.g. Each branch can have at
most one manager, and each employee can manage at
most 1 branch
9
ER models Superkeys, Candidate Keys
A set of attributes whose values can uniquely
identify an entity of a given type is called a
Superkey of that entity. e.g. ssn is a
Superkey of entity customer. e.g. name, address
is not a superkey of entity customer Why?
Notice If K is a superkey, any superset of K is
also a superkey.
Any minimal superkey, K, is called a candidate
key. minimal gt removing any element from K will
give subset that are not superkeys
name
address
ssn
customer
10
ER models Specializations
Suppose all entities of a given set can be
categorized further into a few subsets. e.g.
entity account may be of type savings or
checking. The subsets form categories, or
specializations.
account_no
balance
account
isa
overdraft
interest-rate
checking
savings
isa
standard
gold
interest-payment
min-balance
interest-rate
11
ER diagram notations
Regular entity
Weak entity
simple attribute
multi-valued attribute
derived attribute
relationship
participation
total participation
specialization
isa
12
Bank ER
13
ER Diagrams use
Why bother to create a graphical image of the
same data as text ?
1. Construction of ER model assists focusing on
complete information 2. Easier to map ER model
into relational model This relational model
is a good starting DB design.
14
References and Further Reading
Silberschatz, Korth, Sudarshan, Database Systems
Concepts, McGraw Hill
Next Relational model, Normalization