|
DBA Methodology for
Logical and Physical Database Design
ActiveCodeWizard.com
Generate full-featured and fully customizable
ASP database applications for SQL Server or MS Access online
with their ASP Generator. You can also administor all the
databases on your web server with the ASP Internet DBA. Free
membership required.
Free DBA Script Collections
SQL Server Script Library - Umachandar's SQL Server Scripts
Planet SourceCode - Free SQL Server Scripts -
Microsoft's SQL Server Code Libraries - SQL Server Technical Articles -
SQL Server – Downloads - The Oracle Resource Stop -
DBA Oracle Support Scripts - Oracle DBAToolz SQL Script Archive -
Adelante Oracle Scripts - Jeffrey
Hunter’s Oracle DBA Scripts -
Steve Rea’s Oracle Scripts - LazyDBA.com - SQL Code Samples -
PLNet.org
- Oracle Editor’s Picks - Database Source Code Library DevX
-
Trigeminal Software - Quest SQL Server Navigator
Database Application
Lifecycle:
1.
Database Planning
2.
System Definition
3.
Requirements Collection and Analysis
4.
Database Design
5.
DBMS selection (optional)
6.
Application Design
7.
Prototyping (optional)
8.
Implementation
9.
Data Conversion and Loading
10.
Test
11.
Operational Maintenance
2. System
Definition
System definition involves
identifying the scope and boundaries of the database
application, including its major user views. A user view
can represent a job role or business application
area.
Return
to Index |
2. System
Definition
System definition involves
identifying the scope and boundaries of the database
application, including its major user views. A user view
can represent a job role or business application
area.
Return
to Index |
3.
Requirements Collection and Analysis
Requirements collection and analysis is
the process of collecting and analyzing information
about the company (or part of the company) that is to be
supported by the database application, and using this
information to identify the requirements for the new
system.
Return
to Index |
4. Database
Design
Database design is the process of
creating a design for a relational database that will
support the company's operations and objectives. This
stage includes the logical and physical design of the
database.
* CONCEPTUAL DESIGN OF THE GIS
* J. M. Zaldívar and C. N. Murray
Develop the Logical Database
Design (LDD)
1.0 Model the organization's data needs
(Logical Data
Model)
Application Developers: Develop the
requirements for a
conceptual
data
definition & logical data
design.
The conceptual
data definition can be defined as a high level
entity
relationship
diagram that includes major data subject
areas.
The logical
data design will further breakdown
the
subject areas into
data elements, showing the logical grouping and
the
inter-relationships of the logical data
groups.
Data Administrator: Implement
the conceptual definition & logical
data
design. DAs
should be involved in early conversations to understand
the
background of the
information development. As data
elements are
defined,
they should be input to the Corporate Data
Dictionary.
Populate the Data Dictionary. Populating is the
responsibility
of the
Project Data Admin.
Since
most data elements should already be described, only
aliases need
to be input. In
either case, data elements or aliases should be put into
the
data dictionary at the
point they are defined. The term "data dictionary"
refers to a set of tools and products used to
house Agency-related data
elements & models. Its information is called
"meta-data" and
contains:
data element names, descriptions, characteristics,
values,
alias names for
product-specific references (i.e., SAS , DB2,
ORACLE)
The Data
Dictionary will be the source of record for all
relational
database
structures. This means that copybooks and the
Data
Definition Language
(DDL) will be generated
from
the Data Dictionary. A reference to a data
dictionary may
allude to one
or more of its products which are used to
accomplish
actions such as
the following:
o Developing
relationships among elements
o Constructing file layouts
o Generating COPYBOOK
descriptions
o Generating
Data Definition Language (DDL) for relational
databases
o Generating
logical project and enterprise data
models
o Inputting data
elements into the Corporate Dictionary at the
point
they
are defined
o Coordinating and
communicating metadata between
dictionaries
o Viewing all
references to listed
activities
* Generate logical data views from the completed Data
Dictionary
for
the App. Developer. Develop a project data
model to be
included
in the Corporate data model. Build the local
logical data model
for
each
view.
*
Determine data relationships and data integrity rules
with
project
team members. To ensure certainty in the
formulation of the
data
relationships, the Corporate DA will call
upon subject area
experts,
as needed, within the Agency for a complete review
of
data
relationships and integrity rules, as they pertain
to the Agency.
*
Document usage patterns and possible access to the
information.
The access information is important when defining
physical
table
requirements. This task will incorporate input from the
Application
Developer's design requirements. Note: At the
logical
level,
Oracle maintains tablespaces, schemas, and data
blocks
and extents/segments. Database schemas act as a
dictionary to
define
the names of all tables comprising the database. A
schema is a
named
collection of objects, usch as tables, views, clusters,
and
procedures,
associated with a particular user.
1.1 Identify
entities
Entity is a distinct object (person, place, thing,
concept or
event)
that is to be represented in the
database.
1.2 Identify
relationships
Relationship is the association between entities.
Relationships
are
of three main types: aggregation and
association,
* a building is 'part
of'
a school; spatial, e.g. a house is 'near to' a lake; and
topological,
(a
valve is 'connected to' a pipe. Of these types, only the
first may
be
explicitly represented in terms of traditional
relational joins.
The
others will be derived indirectly from spatial
attributes of
the
objects or else thruthe interaction between these
spatial attributes.
1.3 Identify and associate
attributes (columns) with
entities
Attribute is a property that describes some aspect of
the object
we
wish to record. Attribute data is often referred to as
tabular
data.
1.4 Determine attribute
domains
Each domain is a set
of values of the same "kind".
1.5 Determine candidate and primary key
attributes
Every entity has a set of attributes that
uniquely define an
instance
of that entity. This set of attributes is called the
primary key.
The
primary key may be composed of a single attribute--;an
employee
is uniquely identified by a social security number--;or
of
several
attributes--;a repair item is uniquely identified by the
Repair ID
&
the item number. Sometimes, the attributes that compose
the
primary
key are obvious; other times they are not. To test your
understanding
of the primary key you must look at existing data and
also
interview
people who understand the way in which the organization
operates.
Don't rely solely on existing data to validate your
understanding
of the primary key. No Part of the Primary Key Is
Null
Data Base
Administrator:
Develop the appropriate indexes to access
and support the
logical
view determined by the ADs. Development is the
responsibility
of
the Project DBA.
1.6
Specialize/Generalize entities (optional
step)
1.7 Remove
features not compatible with the relational
model
1.8 Check
model supports user transactions
2.0 Create and
check tables for each local logical data
model
2.1
Create tables for local logical data
model
2.2
Check table structures using
normalization
Identify dependencies in the data model
Check
consistency
within data model Normalize to Third Normal Form (3NF)
to
reflect business requirements
2.3 Check tables support user
transactions
2.4 Define integrity
constraints
Referential Integrity: Tables are related
to one another thru
foreign
keys. A foreign key is one table column for which
the set of
possible
values is found in the primary key of a second
table.
Referential
integrity is achieved when the set of values in a
foreign key
column
is restricted to the primary key that it
references or to the null
value.
Once the database designer declares primary &
foreign
keys,
enforcing data & referential integrity is
the responsibility of
the
DBMS.
Data Base
Administrator:
Ensure Referential Integrity (RI) where
applicable. The RI
will
usually reflect data integrity rules of the data
structures inherent
to
the design. Apply RI rules before data is loaded
to ensure the
rules
are tested during test loads. RI is the responsibility
of the
Corporate
DBA, although the Project DBA will be directly involved
with
the
formulation and testing of the RI rules.
2.5
Review local logical data model with users
3.0
Build and check global logical data model (optional
step)
3.1 Merge
local logical data models into global
model
3.2 Check
logical data model
3.3 Check for future
growth
3.4 Review
global logical data model with users
Develop the
Physical Database Design
Data Administrator:
o
Initiate transfer of the logical information from the
Project DA to the
Project DBA to create
a "logical model as implemented".
o Ensure adherence
to naming conventions and develop logical
groupings
of data through oversight by
the Corporate DA.
o Signing off on all information
handed to the DBA for implementation is the
responsibility of the Corporate DA.
Application Developers:
o
Determine table size, volumes and resource estimates
(transaction rates,
estimated
populations, required response time). This information
will be
required by Capacity
Planning.
Data Base
Administrator:
o Determine the database
platform based on access, transactions, number
of
users.
o The Project DBA will work
with the Corporate DBA and DAs to design
an
efficient and effective
relational database structure, with the
information
supplied by the DAs
and capacity numbers.
o Develop the best physical
database design to formulate table definitions
through the use of available processes and
tools.
o Generate the Data Definition Language (DDL)
to create the objects
necessary to
implement in the relational DBMS using the Data
Dictionary.
The Data Dictionary
will be the source of record for the DDL.
o Conduct
architecture walkthroughs throughout the life cycle of a
project.
A database walkthrough should
occur three times in the life cycle of the
project. The intent of the walkthrough is to
ensure that database
performance is maintained
and data redundancy is controlled.
4.0 Translate global logical data model
for target DBMS
Physical Data Model for the prototype
Administrative Area Dictionary
4.1
Design base tables for target
DBMS
4.2 Design
business rules for target DBMS
5.0 Design
physical
representation
5.1
Analyze
transactions
5.2
Choose file
organizations
5.3
Choose
indexes
6.0 Consider the
introduction of controlled
redundancy
6.1
Consider derived
data
6.2 Consider
duplicating columnns or joining tables
together
7.0 Design security
mechanisms
7.1
Design user
views
Views are
used to define queries, to state access permissions,
to
define integrity constraints, etc. Languages for
expressing
views
include relational algebra (procedureal
language),
relational calculus (declaritive language), SQL,
etc.
7.2
Design access
rules
Data
Administrator:
Document appropriate accesses, primary keys and
data order
to
support the logical view determined by the
ADs.
8.0 Monitor and tune the operational
system
Return
to Index |
5. DBMS
selection (optional)
The aim of DBMS selection is to select a system
that meets the current and future requirements of the
company, balanced against costs that include the
purchase of the DBMS product and any additional
software/hardware, and the costs associated with
changeover and training.
|
Fourteen Point DBMS Selection
Checklist:
1. Data
Dictionary/Catalog Approach
2.
Application Building Capabilities
3.
Ad-Hoc Retrieval Approach
4. Data Views
Support
5. Data Type Support
6.
Data Access: Proprietary vs SQL
7.
Integrity Checking Approach
8.
CASE Tool Connectability
9.
Security
10. Utilities
11. Data Mass
Load/Unload
12. Data Compression
13.
Recovery/Backup
14. Processing
Speed |
Return
to Index
|
6.
Application Design
Application design involves
designing the user interface and the application
programs that use and process the database. This stage
involveds two main activities: transaction design and
user interface design.
Return
to Index |
Responsibility Menu
| Application
Development | Data
Administration (DA) |
| Database
Administration (DBA) | Database
Systems Programming |
| Information
Analysis | Operations
Scheduling |
| Security
Administration | Systems
Operations |
DATA MANAGEMENT FUNCTIONAL RESPONSIBILITIES
|
FUNCTION
:
Application
Development
|
RESPONSIBILITIES:
* Determining
& documenting the application
requirements including a definition of the
data
relationships & usage
patterns
* Developing programs
* Integrating
computer code, business processes
and/or
rules and information access to achieve a
functional computer application |
|
ACITIVITES:
|
* Developing the requirements for the
conceptual
& logical data
models
* Defining application requirements
*
Defining data relationships & usage patterns
*
Writing program specifications
* Conducting design
reviews
* Coordinating program integration
testing
* Coding & testing programs
*
Developing requirements for data integrity
rules
(Referential Integrity)
*
Coding common routines, such as exit routines,
SQL-return-code-checking routines,
checkpoint/restart logic
* Creating &
loading test data into tables
* Participating in
performance stress tests
* Developing parameters for
application backup &
recovery
procedures
* Ensuring naming convention
compliance |
|
FUNCTION:
Data Administration
(DA)
|
RESPONSIBILITIES:
* Developing and
maintaining data model,
data
dictionaries
* Integrating data elements on a
project
& enterprise-wide
basis |
|
ACITIVITES:
|
* Developing logical & institutional
data models
* Establishing the standards for naming
conventions,
data relationships &
data attributes
* Populating the data dictionaries
with data elements
& corresponding
metadata
* Defining data integrity rules
*
Developing user views
* Supporting the database
administrator
& the application
developers
* Participating in conceptual database
design
sessions
* Participating in
selecting database management
support
tools |
Return
to Responsibility Menu
|
FUNCTION:
Database Administration
(DBA)
|
RESPONSIBILITIES:
* Designing &
creating relational
database
objects
such as tables, views &
indexes
* Supporting and maintaining the
environment
a relational DBMS requires
to properly
function (i.e., security,
recovery, backup
&
reorganizations)
* Ensuring that relational DBMS
code
performs efficiently
(i.e., SQL review, DB monitor) |
|
ACITIVITES:
|
* Providing assistance in the
development
of the conceptual &
logical database design
* Designing the physical data
model
* Developing & maintaining naming
standards for database objects such
as tablespaces,
tables, indexes
& views
* Defining & creating the database
objects
* Assisting application developers
in
functional prototyping
* Carrying
out performance prototyping
* Developing model JCL
for utilities
* Recommending BU/recovery
procedures
for application tables
*
Supporting application programmers in the
testing of database applications
*
Participating in the methodology
checkpoint reviews
* Providing technical
assistance
to other team members
*
Granting authority to project DBAs
for
appropriate databases
* Reviewing & approving
requested table
changes for
production
* Assisting in product
installation
& defining initial
installation options
* Providing functional guidance
to the systems
programmer & the
operator
* Providing input to the organization's
capacity
planning function
*
Monitoring & tuning the database
subsystem or instance
* Establishing guidelines
for the use
of the database subsystem
or
instance security & coordinating
their
use with the security admin.
function
* Defining requirements for
the Database Resource
Governor, if
applicable
* Developing & implementing
database
administration policies &
procedures
* Evaluating & testing
database-related
software
*
Developing operating procedures
* Supporting the
database subsystems
& instances
*
Participating in selecting database
management support tools |
Return
to Responsibility Menu
|
FUNCTION:
Database Systems
Programming |
RESPONSIBILITIES:
Traditional support
performed by the system programmer |
|
ACTIVITIES:
|
* Installing prerequisite products
*
Installing database software & other
related
software products
* Selecting
database management tools
* Implementing problem
determination
& correction
procedures
* Applying software maintenance
on an ongoing basis
* Running the
performance
& systems monitoring
tools
& providing input to the
database administrator
& the
capacity planning group
* Providing initial
authorization
* Resolving internal software
problems
* Defining the operating
environment
(hardware, software &
transaction manager)
* Performing error diagnosis for
system
& application failures
*
Resolving system-wide performance problems
*
Supporting attachment facility access
& other subsystem
interfaces |
Return
to Responsibility Menu
|
FUNCTION:
Information
Analysis
|
RESPONSIBILITIES:
Including ad hoc
reporting
& extraction requirements in
the
systems development life cycle |
|
ACTIVITIES:
|
* Assisting in identification
& definition of business entities,
attributes
& processes
* Defining
data integrity rules
* Defining security,
availability
& performance objectives
*
Assisting in the development
& testing
phase of the application,
specific to ad hoc
reporting & extracting access
* Assisting in
identifying and defining source data
* Assisting with
and maintaining data definitions
as they
change over time |
Return
to Responsibility Menu
|
FUNCTION:
Operations Scheduling |
RESPONSIBILITIES:
Scheduling database
utility jobs such as load & reorganization |
|
ACTIVITIES:
|
* Ensuring successful completion
of all database production job streams
*
Implementing & scheduling production database
jobs
* Implementing backup & recovery
procedures
* Migrating database apps from test to
production
* Maintaining naming standards
& procedures in the batch environment
*
Ensuring overall standards & procedures
are
complied with thru the Change
Management process |
Return
to Responsibility Menu
|
FUNCTION:
Security
Administration
|
RESPONSIBILITY:
* Establishing security
group IDs or security ID for
use in
database authorization
* Granting authorization to
users of the production
system |
|
ACTIVITIES:
|
* Controlling external access to the
databases
* Ensuring relational databases
internal
authorization is correct
*
Setting up secondary authorization
IDs |
Return
to Responsibility Menu
|
FUNCTION:
Systems Operations |
RESPONSIBILITY:
Operating databases in
a test
and/or production environment |
|
ACTIVITIES:
|
* Starting, stopping & controlling the
database
subsystem or instance including
components such as
the trace
functions
* Monitoring messages from the database
address
spaces & taking the
appropriate action
* Interfacing with other
operators, the help desk
& shift
managers on the status of database operations
*
Informing systems programmers
or
database administration of
problems |
Return
to Responsibility Menu
A typical Database Administrator working in
metro Washington -- Seattle is expected to earn a median base
salary of $76,933. Half of the people in this job are expected
to earn between $66,961 and $87,421 (i.e., between the
25th
and 75th percentiles). These numbers are based on national
averages adjusted by geographic salary differentials.
(This
data is as of December, 2000)
Database Administrator:
Administers,
maintains, develops and implements policies and procedures for
ensuring the security and integrity of the company database.
Implements data models and database designs, data access and
table maintenance codes; resolves database performance issues,
database capacity issues, replication, & other distributed
data issues. May require a bachelor's degree in a related area
and 2-4 years of experience in the field or in a related area.
Familiar with standard concepts, practices, and procedures
within a particular field. Relies on limited experience and
judgment to plan and accomplish goals. Performs a variety of
tasks. Works under general supervision; typically reports to a
manager. A certain degree of creativity and latitude is
required.
Database Analyst II:
Reviews, evaluates,
designs, implements and maintains company database[s}.
Identifies data sources, constructs data decomposition
diagrams, provides
data flow diagrams and documents the
process. Writes codes for database access, modifications, and
constructions including stored procedures.
http://www.microsoft.com/sql/default.htm
|
