Database Users and the Role of the DBA
A database system serves naive users, application programmers, sophisticated users and specialised users, and is run by a database administrator whose responsibilities span schema, access, performance, backup and recovery.
-
DBMS Fundamentals
- Data, Information and Databases
- What a DBMS Is and Why It Exists
- File System versus DBMS
- Advantages and Limitations of a DBMS
- Database Users and the Role of the DBA
- Three Level Architecture and Data Abstraction
- Logical and Physical Data Independence
- Schema, Instance and Metadata
- Database Applications and the Database System Environment
- Database Architecture
- Data Models
-
ER Model
- Entities, Entity Sets and Entity Types
- Types of Attributes in the ER Model
- Keys in the ER Model
- Relationships, Relationship Sets and Degree
- Cardinality and Participation Constraints
- Strong and Weak Entities
- Drawing and Reading ER Diagrams
- Extended ER: Generalisation, Specialisation and Aggregation
- Converting an ER Diagram into Relational Tables
- ER Design Projects
- Relational Model
- Relational Algebra
- Functional Dependencies
-
Normalisation
- Why Normalisation Exists: Anomalies and Redundancy
- First Normal Form
- Second Normal Form and Partial Dependency
- Third Normal Form and Transitive Dependency
- BCNF and BCNF Decomposition
- 4NF, 5NF, Multivalued and Join Dependencies
- Lossless Decomposition and Dependency Preservation
- Complete Worked Normalisation: Unnormalised to BCNF
- Denormalisation and When to Use It
Concept
A database is not used by one kind of person. Exam questions ask for the categories by name, and interview questions ask what the DBA actually does all day. Both are below.
Categories of database user
| User | How they interact | Example |
|---|---|---|
| Naive or end users | Through a ready made application screen. They never see the schema and usually do not know a database exists. | A clerk issuing a book, a student checking results |
| Application programmers | Write the programs those end users click. They embed queries in a host language. | A developer building the results portal |
| Sophisticated users | Write their own ad hoc queries directly, without an application. | Analysts, researchers, engineers investigating an issue |
| Specialised users | Build applications that do not fit the ordinary data processing pattern. | Scientific, geographic, expert system and design applications |
| Database administrator | Owns and operates the database itself. | The DBA, discussed below |
Others in the environment
- Database designers — identify the data, design the schema and the constraints, before the system exists.
- System analysts — gather requirements and specify what the system must do.
- Tool developers — build the design, monitoring and migration tools everyone else uses.
- Operations staff — run the hardware and the environment the DBMS sits in.
The DBA
DBA RESPONSIBILITIES
SCHEMA define and evolve tables, columns, constraints
STORAGE decide physical layout, files, indexes, partitions
ACCESS create accounts, grant and revoke privileges, roles
INTEGRITY enforce rules the business depends on
PERFORMANCE monitor, find bottlenecks, tune queries and indexes
BACKUP design the backup plan and TEST the restore
RECOVERY bring the database back after a failure
AVAILABILITY replication, standby, planned maintenance
CAPACITY forecast growth in data, users and load
SECURITY auditing, encryption, least privilege, patching
LIAISON work with designers, developers and managementImportant terminology
| Term | Meaning |
|---|---|
| DBA | Database administrator: the person accountable for the database. |
| DDL | The part of the language that defines structure. |
| DML | The part that manipulates data. |
| DCL | The part that grants and revokes access. |
| Privilege | A specific permission on a specific object. |
| Role | A named bundle of privileges granted to users as a unit. |
| Schema owner | The account that owns a set of objects and can change them. |
Example
One request touches four kinds of user, which is why the categories matter.
"Results are loading slowly on the day marks are published."
end user reports the screen is slow
app programmer checks the code path, finds one query
running once per student instead of once
sophisticated runs the query by hand, reads the plan,
user sees a full scan on 400,000 rows
DBA adds the missing index, verifies the plan,
checks the effect on write performance,
and schedules the change with a rollbackCommon mistakes
- Describing the DBA as only a backup taker. Backup is one of eleven responsibilities, and the untested restore is the real failure.
- Merging sophisticated and specialised users. Sophisticated users write queries; specialised users write unusual applications. Different categories.
- Forgetting the naive user is the largest group. Nearly everyone using a database never sees it.
- Assuming the DBA writes the application. That is the application programmer. The DBA owns the database.
Exam and interview questions
- List the categories of database user with an example of each.
- State eight responsibilities of a DBA.
- Differentiate a sophisticated user from a specialised user.
- Who defines the schema, and who grants privileges?
- Why is testing the restore more important than taking the backup?
Practice
- For your college portal, name one person in each user category.
- Write the five steps a DBA should take before adding an index to a live table.
- List three DBA responsibilities that are really security responsibilities.
Conclusion
Naive users click, application programmers build, sophisticated users query, specialised users solve unusual problems, and the DBA keeps the database correct, fast, secure and recoverable while all four are working.