File and data base concepts

1

• File and data base concepts

DB design
2

• File Concepts

• DATA REPRESENTATION
• bits and bytes
• EBCDIC and ASCII
• CONCEPTS
• field or data element
• record
• file or table
• data base
• FILE ORGANIZATION
• sequential
• indexed sequential
• direct
• FILE PROCESSING
• batch vs immediate
• online vs offline

3
(No Transcript)
4

• DB a set of entities and relations

In city
In state
Street
Cities
States
located
Registered
on
in
Cars
In country
Houses
Occupied
Owned
by
Has
by
color
Citizen of
Colors
Persons
Countries
Country
of origin
5

• DB subsystems

DBMS
DDL
User
OS
DBM
Data
DML
Base
Application
program
DB logical structures hierachical network
relational
6

• Views, Schema and Subschemas

1
2
5
4
7
9
10
8
15
12
17
13
7

• DB Design concepts

how to represent entities and relations
Flat files
...
COLOR
WEIGHT
PROJ
PROJNAME
P
PNAME
S
QTY
PROJ
STATUS
S
SNAME
CITY
DB tables
graphical
STATUS
S
SNAME
CITY
S
FIELDS
P
P
COLOR
WEIGHT
PNAME
keys and atributes
primary and foreign keys
J
PROJ
CITY
PROJNAME
QTY
PROJ
S
O
P
relational notation
S (
S,
SNAME,STATUS,CITY)
P (
P
,PNAME,COLOR,WEIGHT)
J (
PROJ
,PROJNAME,CITY)
O (
S,P,PROJ
,QTY)
8

• Entities and relations

RELATIONS one-to-one
one-to-many many-to-many
Product
a supplier
can provide
project
product can
a product
can use
be part of
can have
Project
Supplier
a supplier
can supply
a project
can have
9

• DB Design principles

NORMALIZATION 1. represent each ENTITY as a
TABLE 2. select the PRIMARY KEY 3. assign
entity ATTRIBUTES to FIELDS 4. represent an
ONE-TO-MANY relation by a FOREIGN KEY in the MANY
table. 5. represent MANY-TO MANY relations as a
NEW TABLE. Use FOREIGN KEYS to identify entities
involved. The combination of foreign keys is the
PRIMARY KEY of the new table. Assign the common
attributes to fields.
10

• DB design example

many
many
Product
a supplier
can provide
project
product can
a product
can use
be part of
can have
many
many
Project
Supplier
a supplier
can supply
many
a project
many
can have
S (S,SNAME,STATUS,CITY) P (P,PNAME,COLOR,WEIGHT)
J (PROJ,PROJNAME,CITY) O (S,P,PROJ,QTY)