Database Applications and the Database System Environment
Where databases are actually used, and what sits inside a running database system: the DBMS software, the stored database, the catalog, the applications and the people who keep it working.
-
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
The first phase closes by placing everything learned so far into one picture: where databases are used, and what a complete database system is made of.
Where databases are used
| Domain | What the database holds | What makes it demanding |
|---|---|---|
| Banking | Accounts, transactions, loans, customers | Atomicity and durability are non negotiable |
| Airlines and railways | Schedules, seats, bookings, fares | Extreme concurrency on a small set of rows |
| Universities | Students, courses, enrolments, results | Heavy read spikes on result day |
| Retail and e-commerce | Products, stock, orders, payments | Stock accuracy under concurrent orders |
| Hospitals | Patients, admissions, prescriptions, tests | Privacy, auditing and long retention |
| Telecom | Subscribers, call records, billing | Very high insert volume |
| Libraries | Titles, copies, members, loans | Simple, and the classic teaching example |
| Manufacturing | Parts, suppliers, production, inventory | Complex relationships between parts |
| Social platforms | Users, posts, relationships, messages | Graph shaped data and enormous scale |
The database system environment
PEOPLE
end users programmers designers DBA analysts
| | | | |
+------------+-----------+--------+-------+
|
APPLICATIONS
screens, reports, batch jobs, APIs
|
+-------v--------+
| DBMS SOFTWARE |
+----------------+
| DDL compiler | processes definitions
| DML compiler | processes data requests
| query optimiser| chooses a plan
| runtime engine | executes the plan
| buffer manager | memory and pages
| txn manager | atomicity, isolation
| recovery mgr | log, undo, redo
| authorisation | who may do what
+-------+--------+
|
+--------------v---------------+
| STORED DATABASE |
| + STORED DATA DICTIONARY |
+------------------------------+The components in words
| Component | Responsibility |
|---|---|
| DDL compiler | Reads definition statements and records the structure in the catalog. |
| DML compiler | Turns data requests into an internal form the engine can run. |
| Query optimiser | Chooses among many correct plans using catalog statistics. |
| Runtime engine | Executes the chosen plan and returns results. |
| Buffer manager | Decides what stays in memory and what is written to disk. |
| Storage manager | Manages files, pages, records and index structures. |
| Transaction manager | Provides atomicity and isolation across concurrent work. |
| Recovery manager | Uses the log to restore consistency after a failure. |
| Authorisation manager | Checks privileges before any access happens. |
| Data dictionary | Stores the metadata every other component reads. |
Example
One request through the whole system
a student presses "show my result"
-> application builds a request for the results view
-> authorisation manager checks the account may read it
-> DML compiler turns it into an internal form
-> optimiser reads catalog statistics, picks an index scan
-> runtime engine asks the buffer manager for pages
-> buffer manager finds two pages cached, reads one
from disk through the storage manager
-> rows are returned, the application renders the page
Nine components. The student saw a screen.Common mistakes
- Listing applications as part of the DBMS. They sit above it and use it.
- Leaving the data dictionary out of the diagram. It is stored inside the database and read by nearly every component.
- Confusing the buffer manager with the storage manager. One manages memory, the other manages files on disk.
- Forgetting people are part of the environment. The standard diagram includes them, and marks are given for it.
Exam and interview questions
- Draw the database system environment and label every component.
- State the responsibility of the DDL compiler, the optimiser and the recovery manager.
- Trace one query through the system, naming each component it touches.
- Give five application areas of a DBMS and say what makes each demanding.
- Why is the data dictionary drawn inside the stored database?
Practice
- Pick any application on your phone. List four entities its database probably stores and one reason it needs transactions.
- Trace an insert, rather than a read, through the components above.
- Name the component that fails when: a query is suddenly slow, a crash loses data, an unauthorised user reads a table.
Conclusion
A database system is the stored database and its catalog, the DBMS software that manages them, the applications built on top and the people who design, use and administer the whole thing. Phase 2 zooms in on how those pieces are deployed across tiers.