|
Database Designers & Database Administrators
|
|
|
The Database Professional
|
|
|
Typically, database designers and database administrators share the same role in
smaller organizations. From a distance, they certainly appear the same. In reality,
however, database designers work in more of a linear pattern structuring new database
components towards objectives, while database administrators work in more of a circular
pattern performing and rechecking batch jobs and ETL processes.
|
|
|
|
|
|
The Database Designer |
|
|
Database design is a linear process. The database designer works with the development
team to construct a data-store for the application to persist user and system information.
At some point the database design and the associated application is considered “done”
and the combined solution moves into the operations support phase of its lifecycle.
This transition represents the end of the database designer role, and the solution
is handed off to the database administrator to maintain the daily operational performance
of the database.
Database designers need to be familiar with the following concepts, in this order:
- Table construction, data-types
- Keys, complex keys, indexes, column constraints
- Simple SQL statements: SELECT, INSERT, UPDATE, DELETE
- Database normalization
- Referential integrity, primary/foreign keys
- Basic database role security
- Stored procedures, triggers
- Transaction processing, trans-isolation
- Entity Relationship Diagram (ERD) tool generators like TOAD, etc.
- Advanced database role security
- Limit use of CURSORS, limit complex joins
- Replication, log-shipping (one-way replication), merge (two-way replication)
- Physical configuration performance enhancements
- Advanced data-warehousing techniques (snowflake/star schema, Ralph Kimball/Bill
Inmon)
- Large-scale database shard techniques
|
|
|
|
|
|
The Database Administrator |
|
|
Database administration is a circular process. The database administrator is tasked
with maintaining and making minor adjustments to existing systems. In addition to
the above list, database administrators should be skilled in the following concepts:
- Recovery mode settings, re-indexing
- Extract Translate Load (ETL) tools, bulk inserts
- Transaction-blocking resolution
- Performance tuning, performance monitoring
- Re-indexing, reading index fragmentation levels
- Job creation and monitoring
- Database clustering
|
|
|
|
|
|
|
|