File and Database Design

1
File and Database Design

• Using what we learned

2
Data hierarchy

• bits
• bytes
• characters
• fields
• records
• files
• databases

3
File organizations and their utility ...

• Sequential storage
• Random (?) access storage
• direct
• indexed

4
Outline of file design

• Begin with data stores and associated logical
  data elements,
• Verify if logical data structures and records
  have been normalized to 3rd normal form
• If yes use them.
• If no normalize them.

5
Outline of file design (contd)

• Decide between traditional file access methods
• sequential,
• direct access,
• indexed sequential. Or,...
• Data base approach
• hierarchical,
• network,
• relational.

6
Sequential access files

• order of the file,
• how do we search sequential files?
• where do we use sequential files?
• update problem
• search problem

7
Direct access (DA) files

• order of the file,
• how do we search DA files?
• where do we use DA files?
• fast single search and retrieve (master files,
  on-line transactions)
• on the average, large OH when computing
  addresses.

8
Indexed sequential files

• order of the file,
• how do we search indexed files?
• where do we use indexed files?
• the best and worst of sequential and DA,
• uses an index which must be maintained,
• maintenance and update have to use overflow, i.e.
  lot of blank spaces.

9
Physical files

• Tapes
• Hard drives
• Flexible medium
• Optical storage

10
Tape storage

• Tape
• most popular form of storage in 1960s
• continued in form of cassette tape drive
• requires sequential access
• inconvenient and slow
• Recent revival in tape storage for
• Backing up data,
• Archiving data.

11
Optical disk storage

• CD-ROM
• data stamped on when manufactured or burned in
  when written
• holds up to 680 MB
• coated with clear plastic, durable
• estimated life exceeds 500 years
• inexpensive to manufacture
• ideal for distribution of large files
• R/W capability
• random access!

12
Disk storage

• Hard disks Winchester and disk packs
• provide random access to files
• faster than floppy disk access
• continually in motion
• Removable hard disk - hard disks that can be
  inserted and removed from drive
• increase storage capacity
• provides security for data
• random access!

13
Hard drives
14
Floppy drives
15
Floppy drives

• Floppy disk - round piece of flexible mylar
  plastic covered with thin layer of magnetic
  oxide.
• sealed in protective casing
• also called floppies or diskettes
• inside casing is floppy
• not as speedy as zips
• decreased use
• Zip disk - high-capacity floppy disk manufactured
  by Iomega Corporation (100-250 MB).

16
Data models

• Inverted lists
• Hierarchical
• Relational
• Network

17
Hierarchical data models

• The simplest database model arranges record types
  as a hierarchy, or a hierarchical database.
• A record type in a hierarchical database is
  called a node or a segment.
• top node root node
• parent node can have more than one child node
• child node can have only one parent node
• In a hierarchical database, physical links are
  created between the stored records.
• effective for data that has simple relationships
• less effective for complex relationships and that
  require flexible data access

18
Hierarchical data model
Department is the root node. It is also the
parent node for the Employee and Job entities.
Department
The child nodes--Employee, Timecard, and
Job--have only one parent node. Department is the
parent node for Employee and Job. Employee is the
parent node for Timecard. Notice that Employee is
both a child node and a parent node.
Employee
Job
Timecard
19
Relational data models

• A relational database is perceived by its users
  to be a collection of tables which are roughly
  equivalent to a collection of record types.
• rows of a table are called tuples (like records)
• columns of a table are called attributes (like
  fields)
• In a relational model, records are related to the
  data stored jointly in the fields of records in
  two files.
• Flexibility to define relationships

20
Relational data models
Data from the two tables can be combined by
matching the data in two fields. For example, the
data in the Employee and the Timecard tables can
be joined by matching the data in the SocNum
field.
The data for each record is stored in a table. A
relational database for a hospital would have
four tables Employee, Timecard, Job, and
Department. Note that Job and Department are not
yet related.
21
Network data model

• In a network database, related record types are
  referred to as a set.
• A set contains an owner which is similar to a
  parent record.
• also contains members (like child records)
• Only one-to-many relationships are allowed in
  the network database model.
• The network database model allows member records
  to have more than one owner.
• more flexibility than hierarchical

22
Network data model
In a network database, records are classified as
owners or members. Department and Job are owners
in relation to Employee. Employee is an owner in
relation to Timecard. Employee is a member under
Department or Job. Timecard is a member under
Employee.
A set
Another set
Job
Department
Job
Employee
Employee
The network database model allows a member to
have more than one owner. Here the member
Employee has two owners Job and Department.
An owner and its member or members are referred
to as a set. Here the owner, Job, and the
member, Employee, are a set. You could refer to
the this section of the model as the Employment
set.
Timecard
23
End file and database design
Let's build some files and data bases now!