Transcript database models - School of Civil Engineering USM

EAK 362/2 MIS LECTURE 4
PART 2
Managing Databases
SUMMARY OF FIRST LECTURE
ENTITY:
Any concrete or abstract object or event in the user’s world
ATTRIBUTE:
A characteristic of interest about an entity
VALUE: A symbol or character string assigned to an entity instance to describe it
RELATIONSHIP: Some connection or association between entities
An organization collects information about Entities any person, object, or event, however concrete or
abstract. Attributes are the characteristics of interest about entities. Values of attributes represent the
actual data pertaining to specific entities in the organization or its environment. Relationships may exist
between entities and are usually represented by additional attributes. For example, recording the number of
the organizational unit for which an employee works establishes a relationship between these two entities.
ORGANIZATIONAL
UNIT
PERSON
Entity
UNIT NUMBER:
2100
2100
DEVELOPMENT
DEPT
P. I. Carr
o
NAME:
DEVELOPMENT
DEPT.
BUDGET:
$391,000
PARENT UNIT:
2000
HEAD:
P.I. Carr
SEX:
F
SECRETARY
BIRTHDATE:
JOB CODE:
Relationship
R.F.CALLAGAN
EMPL
POSITION TITLE:
Value
15324
NAME:
LEVEL:
Attribute
EMPLOYEE NUMBER:
5210
ORG UNIT:
2100
SALARY:
$31,400
550606
PRIMARY SKILL:
5210
SECONDARY SKILLS:
5520, 5220
DATABASE
•an organized collection of data

There are four major database models,
each of which has a different way of
representing the relationships between
entities.
1. HEIRARCHICAL DATABASE
2. NETWORK DATABASE
3. RELATIONAL DATABASE
4. OBJECT-ORIENTATED DATABASE
1.Hierarchical Database Model
What is the simplest database model?


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
Hierarchical Database Model
What is the simplest database model?
Hierarchical
database
model
Department
The child nodes--Employee, Timecard,
Employee
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.
Department is the
root node. It is also
the parent node for
the Employee and
Job entities.
Timecard
Job
Hierarchical Database Model
What is the simplest database model?

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

2. Network Database Model
Which database model allows record types to
have more than one parent?


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
Network Database Model
Which database model allows record types to
have more than one parent?
Network
database
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.
Job
Department
Job
Employee
The network database
model allows a member
to have more than one
owner. Here the member
Employee has two
owners: Job and
Department.
Employee
Timecard
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.”
3. Relational Database Model
What if I need more flexibility to define
relationships?

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.
Relational Database Model
What if I need more flexibility to define
relationships?
Relational
database
model
The data for each
record is stored in a
table. A relational
database for the
Midtown General
Hospital would have
four tables: Employee,
Timecard, Job, and
Department.
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.
4. Object-Oriented Database Model
Do other data models exist?

An object-oriented database
model treats an entity as an object.
object is defined by attributes
 object can be manipulated using
methods
 objects with similar attributes are
grouped in a class

Object-Oriented Database Model
Do other data models exist?
Objectoriented
database
model
Data Management Software

Data management software helps you:





create a collection of data
enter and manipulate data
format data into reports
interact with data on the Web
The software you select depends on:



data model
flexibility you require
resources devoted to maintaining data
Database Management Systems

A database management system
(DBMS) is application software that helps
you manage the data in more than one
file at a time.



provides a way to create custom programs for
data management tasks
many provide an option for client/server
operation
Most users purchase a commercial DBMS
package, such as Microsoft Access.
Typical Capabilities of DBMS

Create and maintain a database






add, delete, revise records
Extract and list records meeting certain
criteria
Make an inquiry (e.g. What were the total
sales for 2002?)
Sort records in ascending or descending
order
Provide on-screen forms for data input
Generate formatted reports and charts
Querying the Database

Query by example




specify conditions
e.g., list all students with marks > 80
The computer will quickly locate the record
you seek
Structured Query Language (SQL)


standard method for accessing databases
is a popular query language for
microcomputers, mainframes, and
supercomputers
Client/server Databases
Traditional databases are located
centrally
 Client/server technology permits
data to reside at many locations on
a network
 Users can query the database
without regard for its physical
location

Databases and the Internet




Databases can be accessed via the
Internet through a Web interface
Often an interface can be developed for
legacy databases
The Common Gateway Interface (CGI) is
used for this
CGI is a standard for passing information
between a Web server and other
programs on the server
Interfacing to Databases
Web
browser
CGI
interface
HTTP
server
Database
Server
Example: Internet Search
Engines
Keywords you enter are converted
to queries
 Queries are submitted to database
 Results are returned as HTML

Example:
Library Catalogue
Used to require dumb terminals for
access
 Now accessed via the Internet


Online Public Access Catalogue (OPAC)
• this uses telnet

WebPac
• Web-based
Data Management Tasks
A “data management environment”
refers to the software or program that
you use to design and manage data.”
Regardless of the data management
environment you use, many data
management tasks remain the same.
Designing the File Structure
Where do I begin?
The key to an effective file or
database is the initial design of its
structure.
 Determine what data needs to be
collected and stored.
 Two major inputs to this design

Current system analysis
 Proposed system design

Designing the File Structure
Where do I begin?


The second step is to organize the
information into fields so that it can be
used flexibly.
Third step - decide on the data type for
each field.



After you specify the data type, you should
decide on the format and valid range for each
field.
The field format provides a template for the
way data is displayed on the screen.
Many data management environments allow
you to use a range check to specify what
constitutes the range of valid entries and
decrease errors.
Designing the File Structure
Where do I begin?

Fourth - group the fields. Each group that
you create becomes a record type.



When you group fields into record types, you
essentially make the choice between using a
flat file and a database.
Repetition of data is referred to as data
redundancy.
The process of analyzing data to create most
efficient database structure is normalization.
Searching
How do I find a particular record once it is
entered?



A database is like a vast warehouse of
data.
Data warehousing means collecting
vast amounts of data, usually at least 10
GB.
Data mining means combing though
data to discover patterns and
relationships important to decision
making.
SUMMARY




Database management is one of the main
applications of computers
DBMS allow databases to be built and
manipulated without low-level
programming
Several types of database: relational and
object-oriented most important today
Many databases can be accessed using a
Web browser