Roles, queries, and transactions

In the previous lesson we had:

Now the questions are:

• Who decides what data we should have?
• Who manages the database?
• How does an application ask the DBMS for something?
• What happens when several operations must run together?

This lesson answers exactly those questions.

Imagine a university system.

Students, teachers, the registrar, and finance all use it — but they do not share one role.

End user uses the system but does not need to know SQL or database design.

A registrar clicks “enroll student” and may never see what runs behind that button:

So the end user works with the system, not necessarily with the database directly.

Examples: a student, a bank teller, a registrar, a shop clerk.

DA = Data Administrator

This role focuses on data policy and management at the organization level.

A university must decide:

• What student information do we keep?
• Do we also need medical data?
• Who may see grades?
• How many years should we retain records?

These are organizational decisions more than technical ones.

The DA says what data we have and under what policy it is managed.

In real organizations the DA/DBA boundary is not always the same, and a separate DA job title may not exist.

For the course model, remember:

DA = data policy maker

DBA = Database Administrator — the technical manager of the database.

Suppose the organization decided: “A student must not see another student’s grade.” That is an organizational need. Someone must implement it technically.

A DBA may own:

• User management
• Permissions
• Backup
• Recovery
• Performance
• Security
• Monitoring
• Database maintenance

The DA decides data policy; the DBA manages the database environment technically.

The notes say DBP = Database Programmer. In today’s market you more often see Database Developer or SQL Developer.

They write queries, build procedures, work with tables, and develop the database side of the application.

Or they design a stored procedure to place an order.

Mental model:

In a real project the jobs often overlap.

This part matters a lot.

A query is a request to retrieve or inspect information from the database.

Example: show every purchase made by Ali.

That means: from the purchases table, return rows whose customer_name is Ali.

In everyday programming we often call every SQL statement a query. More precisely:

is clearly a query — it asks a question of the data. But:

is more an SQL statement than a query in the narrow sense.

The broader term is SQL statement.

SQL is mainly a declarative language.

You tell the DBMS what you want, not exactly how to find it.

You did not say “start at the first record, then read memory…”. You only said you want students in Tehran.

The DBMS chooses how to run it. That choice is usually made by a query optimizer.

Suppose the app is written in C#. That program may run SQL inside it.

C# is the main application language; SQL is used to talk to the database.

Older sources call C# or Java the host language.

“SQL is the guest” is a teaching metaphor, not a term you must memorize.

This part is often exam material. A useful metaphor:

Treat the database as a building.

• DDL builds the building.
• DML changes what is inside.
• DCL decides who may enter and act.

DDL = Data Definition Language. It works with database structure.

That creates the table.

ALTER changes an existing structure:

The table is still students, but it gained a column.

DROP removes a database object:

The table itself is gone.

For now, remember CREATE, ALTER, DROP as DDL.

DML = Data Manipulation Language. We are not building table structure; we work with the rows inside.

Insert:

Update:

Delete data:

And reading data:

Many courses teach SELECT as DML. Some taxonomies split it out as DQL = Data Query Language.

If your instructor said SELECT → DML, answer that way in that class — and know the DQL split also exists.

A classic exam question. Suppose students is a table.

If you say:

the rows are removed, but the table remains. But:

the table itself is removed.

DCL = Data Control Language — control of access.

Give Ali SELECT permission:

To take it back:

A memory hook:

• DDL → Design
• DML → Modify data
• DCL → Control

Not etymologically exact, but useful for recall.

The DBMS must know which tables exist, which columns they have, each column’s type, which users exist, and which permissions they hold.

That information about the database is metadata.

students.name → VARCHAR(100) is about structure. “Sara” is data; the fact that name is VARCHAR(100) is metadata.

The DBMS keeps this metadata in a data dictionary or system catalog.

The data dictionary is the set of metadata about database structure and objects.

For example: table name, column name, data type, constraints, indexes, users, privileges — and more, depending on the DBMS.

This is the most important part of the lesson.

A transaction is a set of operations the system treats as one logical unit of work.

Classic example: transfer 1,000,000. Two main operations:

These two operations are not independent. They must happen together.

Suppose the first step ran:

Then power failed, and this never ran:

Money left A but never reached B. The system is now wrong.

So these two operations are one transaction: both succeed, or neither does.

If every operation in the transaction succeeded:

Conceptually: make the changes final.

The transaction has finished successfully.

If something goes wrong:

Undo the changes made by this transaction.

A returns to its previous state.

Some notes say ABORT. Abort means the transaction failed or was cancelled. In SQL the command you usually use is ROLLBACK.

The four main transaction properties are remembered as ACID:

• A → Atomicity
• C → Consistency
• I → Isolation
• D → Durability

Do not only memorize them — understand them.

Atomicity = all or nothing

A transaction cannot be left half-done.

In a transfer, debit ✔ and credit ✘ is not acceptable.

Either both succeed, or neither does.

All or nothing

Consistency means a transaction must take the database from one valid state to another valid state.

Example rule: balance >= 0

If an account has 500,000, a 1,000,000 transfer must not leave balance = -500,000 when the rules forbid it.

A transaction must not break the database’s valid rules.

Isolation keeps concurrent transactions from interfering incorrectly.

Two people withdraw from the same account at once. Balance is 1,000,000. A wants 700,000 and B wants 700,000 at the same time.

If the DBMS does not control concurrency, both may see 1,000,000 and both withdraw. That is a concurrency problem.

Isolation stops concurrent transactions from leaving a wrong combined result.

No. This is a place many notes oversimplify.

Locking is one technique. DBMS products can also use MVCC — Multi-Version Concurrency Control.

Isolation is the result we want; locking is one technique for getting there.

Durability means if the DBMS said the commit succeeded, the change must not vanish a few seconds later — even after a crash.

Does durability mean mirroring? No. Notes that say that are imprecise.

Mirrors or replication can help, but they are not the definition. A DBMS can guarantee durability with a transaction log, write-ahead logging, recovery, and persistent storage.

Durability: a committed change must survive a crash.

Suppose:

We transfer 1,000,000. Afterward:

• Atomicity — A must not be debited alone.
• Consistency — database rules must still hold.
• Isolation — other transactions must not see a half-finished state.
• Durability — after COMMIT, a restart must not erase the transfer.

If this example is clear, you essentially understand ACID.

They are not identical, though they are closely related.

Data integrity is the broader idea that data stays correct and valid.

Consistency in ACID says a transaction must preserve the database’s constraints and rules.

So consistency is one of the things that helps keep the database valid.

You may see “internal vs external integrity” in a university handout. It is not a standard split used the same way in all database texts.

For a solid foundation, focus on the well-known integrity kinds:

• Entity integrity
• Referential integrity
• Domain integrity
• User-defined constraints

Grade between 0 and 20: domain constraint. A registration needs a real student: referential integrity. A unique primary key: entity integrity.

These become much more important later.

The note that “every task goes to the OS but a transaction goes to the DBMS” is oversimplified. Do not memorize it.

The application usually talks to the DBMS. The DBMS itself talks to the operating system for disk, memory, file I/O, and more.

A better model:

A database is mainly for storing and managing structured data. For example:

Those are facts.

A knowledge base may also keep rules, semantic relations, and inference structure. For example:

The system can infer from what it already knows.

The claim that a knowledge base “must use AI and NLP” is false. A knowledge base may be used in AI, but it does not require AI or NLP. Expert systems used knowledge bases long before language models.

Simply:

• Database: What data do we have?
• Knowledge Base: What do we know, and what relationships or rules exist?

A database might store:

A knowledge base may add rules and semantic relations on top of those facts.

Roles and the data path:

The end user uses an application:

And to talk to the DBMS:

And when several operations are one unit of work:

Next session is the relational model, tables, and keys: entity, attribute, primary key, foreign key, and relationship types. After that we move to ER diagrams.