Database Systems, Models, Schemas and Architecture

1. Data, Information and Databases

Data consists of recorded facts such as customer identifiers, product prices, dates and measurements. Information is data interpreted in context. The value 1250 becomes meaningful when identified as an invoice total in rupees for a particular customer and date.

A database is an organized collection of related data designed for persistent storage, retrieval and controlled change. It includes the stored values and the structure that gives those values meaning. A library database, for example, distinguishes books, physical copies, members and loans so that the same member details do not have to be repeated for every loan.

A database is more than a set of unrelated files. Relationships, constraints, shared definitions and controlled access allow applications to treat the data as one coherent system.

2. Database Management Systems

A database management system or DBMS, is software that defines, stores, retrieves, updates, protects and recovers a database. Applications and users issue requests to the DBMS rather than manipulating storage files directly.

Core DBMS responsibilities include:

• defining schemas and constraints;
• processing queries and updates;
• managing persistent storage and indexes;
• coordinating simultaneous transactions;
• enforcing authorization;
• recording changes needed for recovery;
• maintaining metadata about database objects;
• providing backup, restore and administrative facilities.

The term database system often includes the database, DBMS software, applications, users, procedures and computing infrastructure together.

3. Limitations of Independent Files

Traditional file-based applications often give each program its own files and interpretation rules. This design can produce:

• redundancy, because the same customer or product is stored repeatedly;
• inconsistency, because one copy changes while another does not;
• isolation, because related facts use incompatible formats;
• program-data dependence, because file layout is embedded in application code;
• weak integrity, because every program must reimplement validation;
• difficult concurrent access;
• incomplete recovery after interruption;
• scattered security rules.

A DBMS does not remove every design problem, but it centralizes structure and services so that these concerns can be handled consistently.

4. Database Vocabulary

An entity is a distinguishable real-world concept about which data is stored, such as a Student, Course or Order. An attribute is a property, such as studentName or orderDate. A relationship associates entities, such as a Student enrolling in a Course.

In the relational model, a relation is represented as a table. A tuple is a row and an attribute is a column. A column draws values from a domain, which describes the permitted kind of values.

A key is an attribute or combination of attributes used to identify rows or connect related facts. A constraint is a rule that valid database states must satisfy.

5. Metadata and the Data Dictionary

Metadata is data that describes other data. Column names, data types, nullability, keys, constraints, indexes, privileges and object ownership are metadata.

The DBMS stores metadata in a catalog or data dictionary. Query processors and administration tools inspect it to understand available objects. Users can query catalog views to discover table definitions and privileges instead of relying on undocumented assumptions.

The catalog is itself managed data. Changing a table through a schema command updates the catalog as well as the structures used to store and interpret rows.

6. Schema and Instance

A database schema is the relatively stable definition of structure and rules. It specifies tables, columns, relationships, constraints, views and other objects.

A database instance, in the data-model sense, is the collection of values stored at a particular moment. Inserting a row changes the instance without necessarily changing the schema. Adding a column changes the schema.

The word instance is also used operationally for a running installation or server process. Context distinguishes a snapshot of database contents from a running database-server instance.

7. Data Models

A data model provides concepts for describing structure, relationships, constraints and operations. It determines how designers and users think about stored data.

Major model families include:

• relational models based on tables and declarative operations;
• object-relational models that extend relational systems with richer user-defined structures;
• key-value models that retrieve values by keys;
• document models that store nested, self-describing documents;
• wide-column models organized around column families;
• graph models centered on vertices, edges and traversal.

NoSQL is an umbrella for several nonrelational approaches rather than one single model.

8. Conceptual Data Models

A conceptual model describes the business domain independently of a particular DBMS. It identifies important entities, attributes, relationships, cardinalities and rules.

For a library, the conceptual model might state:

• a Member can create many Loans;
• each Loan belongs to one Member;
• a Book title can have several physical Copies;
• each Loan concerns one Copy;
• a copy cannot have two active loans at the same time.

The conceptual model uses business language. It should not begin with indexes, storage pages or vendor data types. Its purpose is agreement about what the data means.

9. Logical Data Models

A logical model translates the conceptual design into structures of a selected model while remaining mostly independent of physical storage. In a relational design, it identifies relations, columns, primary keys, foreign keys, domains and normalization decisions.

The library model may become MEMBER, BOOK, BOOK_COPY and LOAN relations. The logical design specifies that LOAN.member_id references MEMBER.member_id and that LOAN.copy_id references BOOK_COPY.copy_id.

The logical model resolves many-to-many relationships through associative relations and separates facts according to dependencies. It states what structures exist without deciding exactly which indexes or disk organization will implement them.

10. Physical Data Models

A physical model describes how a particular database product will implement and access the logical design. It includes vendor data types, indexes, partitioning, storage engines, tablespaces, clustering, compression and placement choices.

Physical design depends on actual workload. An index that speeds frequent lookup consumes storage and adds work to inserts and updates. Partitioning that helps large historical queries may complicate other operations.

Conceptual, logical and physical designs are connected but should remain distinguishable. A physical optimization should not silently change business meaning.

11. The Relational Model

The relational model represents information as relations. In formal relational theory, a relation is a set of tuples, so duplicate tuples do not exist and row order has no meaning.

Practical SQL tables differ in useful ways. SQL normally permits duplicate rows unless a key, unique constraint or DISTINCT operation prevents them. SQL also supports NULL and uses bag-like query results in many operations. Therefore, relational theory provides the foundation while SQL defines the actual behavior of a database product.

A well-designed relational table represents one type of fact. Each row has the same set of columns and constraints define valid values and relationships.

12. Rows, Columns and Domains

A row represents one occurrence of the fact modeled by a table. In CUSTOMER, one row represents one customer. In ORDER_ITEM, one row may represent one product line within one order.

A column represents one attribute of that fact. Its declared type limits representation, while additional constraints refine meaning. A DATE column prevents arbitrary text but does not by itself ensure that an end date follows a start date.

Column order does not define conceptual meaning and row order is not guaranteed without ORDER BY. Applications should refer to columns by intended names and request ordering explicitly.

13. Keys and Identity

A superkey is any set of attributes that uniquely identifies a row. A candidate key is a minimal superkey: removing any attribute destroys uniqueness. One candidate key is selected as the primary key and other candidate keys remain alternate keys.

A natural key uses meaningful domain data, such as a government-issued identifier. A surrogate key is an artificial identifier such as an auto-generated number.

Key design must reflect stable identity. A person's name is rarely a sound key because it is neither unique nor immutable. A surrogate key simplifies references, but natural uniqueness still needs a UNIQUE constraint when the business rule requires it.

14. Integrity Constraints

Constraints keep invalid states out of the database:

• domain integrity restricts column values through types and checks;
• entity integrity requires primary-key values to identify rows and not be NULL;
• referential integrity requires a foreign key to match a referenced candidate key or be NULL when permitted;
• business constraints express additional rules such as positive quantity or unique email.

Application validation improves user messages, but database constraints remain essential because several applications, scripts and users may modify the same data.

15. Database Views and External Perspectives

A view is a named query that presents a selected perspective on stored data. It can hide sensitive columns, simplify repeated joins, expose stable names and restrict users to relevant rows.

CREATE VIEW active_members AS
SELECT member_id, full_name, email
FROM member
WHERE status = 'ACTIVE';

An ordinary view stores the query definition rather than a separate permanent copy of every result row. The DBMS evaluates it from underlying tables when used, subject to optimization and product behavior.

Views support external schemas: different users can see different representations of one logical database.

16. Three-Schema Architecture

The three-schema architecture separates:

1. external level — views and representations for particular users or applications;
2. conceptual level — the overall logical organization and constraints;
3. internal level — physical storage structures and access paths.

Mappings connect these levels. The separation explains data independence: changes at a lower level should require as few changes as possible at higher levels.

17. Physical and Logical Data Independence

Physical data independence is the ability to change internal storage without changing the conceptual schema or applications. Adding an index, moving a table to different storage or changing file organization should not require rewriting every query.

Logical data independence is the ability to change the conceptual organization while preserving external interfaces. It is harder to achieve because adding or reorganizing business structures can affect what applications mean. Views and compatibility layers can insulate clients from some logical changes.

Data independence reduces coupling; it does not mean changes have no performance effect or that every schema change is invisible.

18. Client-Server Architecture

MySQL commonly runs as a database server process. A client connects using a network or local protocol, authenticates, sends SQL statements, receives results and manages transaction state.

The server parses SQL, checks names and privileges, chooses an execution plan, accesses data through a storage engine, applies constraints and returns results. This separation allows command-line tools, graphical tools, applications and administrative utilities to work with the same server.

In a three-tier application, a presentation client calls an application server, which contains business logic and connects to the database. This architecture avoids distributing database credentials and rules to every user interface.

19. MySQL Clients

The MySQL command-line client, often called the monitor, accepts SQL interactively and is valuable for direct administration and reproducible scripts.

MySQL Shell provides interactive modes and development or administration facilities beyond the traditional client. MySQL Workbench is a graphical client for query editing, schema browsing, modeling, administration and result inspection.

These programs are clients, not the database itself. Closing Workbench does not delete the database and several clients can connect to the same server subject to accounts and concurrency controls.

20. Query Processing

When the server receives a query, it generally performs several conceptual stages:

1. parsing checks syntax and forms an internal representation;
2. semantic analysis resolves tables, columns, types and privileges;
3. optimization considers equivalent plans and estimates cost;
4. execution obtains rows through storage and access methods;
5. result processing returns rows or an update status.

Declarative SQL describes the required result rather than a fixed procedural access path. The optimizer may use an index, scan a table, reorder joins or choose another equivalent strategy.

21. Transaction, Concurrency and Recovery Services

A transaction groups operations into one logical unit. Concurrency control coordinates transactions so simultaneous users do not corrupt shared facts or observe disallowed intermediate states.

Recovery uses logs and persistent structures to restore a valid state after a statement error, process crash or system failure. Committed changes must survive according to the system's durability contract, while incomplete changes must not become a partially applied business operation.

These services distinguish database storage from ad hoc file editing. Their precise behavior depends on transaction boundaries, isolation and the selected storage engine.

22. Database Users and Roles

Database environments involve several roles. Data architects and designers model information. Database administrators manage installation, security, backup, availability and performance. Developers write schemas, queries and applications. Analysts retrieve and interpret data. End users interact through applications or reporting tools.

Accounts should receive only the privileges required for their role. Shared administrator credentials prevent accountability and expose more capability than ordinary applications need.

23. Relational, Object-Relational and NoSQL Systems

Relational systems are strong when structured facts, constraints, joins and transactional consistency are central. Object-relational systems retain relational foundations while adding facilities such as richer types or nested structures.

NoSQL systems choose varied data models and distribution tradeoffs. A document store may fit aggregate-shaped data that is usually read together. A graph database may fit relationship traversal. A key-value store may fit direct lookup by a known key.

The categories overlap in modern products. Selection should follow data relationships, access patterns, consistency requirements, scaling needs and operational experience rather than a label.

24. Beginning a Database Design

Start with business facts and rules. Identify entities, events, relationships, lifetimes, cardinalities and identifiers. Ask which facts change independently and which rules must always hold.

For the library example, member details belong with members, copy details belong with copies and loan dates belong with the relationship between a member and a copy. Combining all three into one repeated table would make member and book data harder to maintain.

After the conceptual design is understood, create a logical schema, examine dependencies and normalization, define constraints and only then tune physical access from actual workloads. A good database structure records each fact at an appropriate place and lets the DBMS protect its meaning.