Database concepts, architecture and ER modelling notes — Unit 1

Before the advent of databases, computer systems stored data using traditional File Processing Systems. A university might have one set of flat files for the Accounts department, another set for the Library, and a third for the Examination department. This approach led to severe, compounding issues as organizations grew.

⇒1.1 The Problems with File Processing Systems

• Data Redundancy and Inconsistency: The exact same data (e.g., a student's address) is stored in multiple places (Accounts file, Library file). If the student moves, their address might be updated in Accounts but forgotten in Library, leading to inconsistent, contradictory data.
• Difficulty in Accessing Data: If a manager needs a new report combining data from Accounts and Examinations, a programmer must write a brand-new application program specifically for that report. There is no easy, standard way to query the data.
• Data Isolation: Data is scattered across various files, and the files might be in different formats (CSV, binary, text). Writing new applications to retrieve the appropriate data is incredibly difficult.
• Integrity Problems: Data values must satisfy specific constraints (e.g., Account Balance cannot fall below $0). In a file system, these constraints must be hard-coded into the application logic itself. Changing a constraint means finding and rewriting every single application that touches that file.
• Atomicity of Updates: If a system crashes in the middle of a complex operation (like transferring money from Account A to Account B), the file system might be left in an inconsistent state (Money deducted from A, but never added to B). It is difficult to ensure "all-or-nothing" execution.
• Concurrent Access Anomalies: If two bank clerks try to read and update the same account file at the exact same millisecond, the final balance will likely be incorrect. File systems lack robust concurrency control.
• Security Problems: Enforcing authorization (e.g., Clerk A can read salaries but not modify them, Clerk B cannot see salaries at all) is extremely difficult when data is just sitting in plain text files.

A Database Management System (DBMS) was designed specifically to solve every single one of these problems.

A Database Management System (DBMS) is a complex piece of software consisting of a collection of interrelated data and a set of programs to access, update, and manage that data securely and efficiently.

⇒2.1 Core Characteristics of a DBMS

A true DBMS differs fundamentally from a file system due to these defining characteristics:

• Self-Describing Nature (The Catalog/Dictionary): A DBMS doesn't just store data; it stores data about the data (Metadata). The Database Catalog contains a complete definition of the database structure, data types, and constraints. This means the DBMS software can work with any database, because it just reads the catalog to understand the structure.
• Program-Data Independence: In traditional programming, the structure of data files is hard-coded into the C or Java application. If you add a new column to a file, the application breaks. In a DBMS, the structure is stored in the catalog separately from the access programs. You can change the database structure without rewriting your application code.
• Data Abstraction: A DBMS provides multiple views of the data, hiding the complex physical storage details (like how bytes are laid out on the hard drive) from the end-users and developers.
• Support for Multiple Views: Different users need different data. A payroll clerk sees salary information, while a marketing manager sees customer names. The DBMS allows the creation of personalized virtual 'Views' of the database tailored to specific users, enhancing security and usability.
• Sharing of Data and Multiuser Transaction Processing: A modern DBMS allows thousands of users to access and modify the database simultaneously while strictly ensuring that the data remains consistent and uncorrupted, utilizing complex concurrency control software.

A large enterprise database involves many different types of people interacting with it in completely different ways. They are generally categorized based on their technical expertise and their role.

⇒3.1 Actors on the Scene (Direct Users)

• Database Administrator (DBA): The chief controller of the database. The DBA authorizes access, monitors performance, acquires hardware resources, performs backups, and tunes the system for speed. They have ultimate "superuser" privileges.
• Database Designers: Responsible for communicating with all end-users to understand their requirements, and then translating those requirements into the logical and physical database schemas (e.g., drawing the ER diagrams and deciding which tables to create).
• End Users: The people who use the data for their daily jobs.
  Casual/Parametric End Users: Use pre-programmed interfaces (like mobile apps or bank teller software) to query and update the database. They don't write SQL. Sophisticated End Users: Business analysts, data scientists, and engineers who write complex, ad-hoc SQL queries to extract intelligence from the database.
• Application Programmers / Software Engineers: Developers who write the backend code (in Node.js, Python, Java) that interacts with the database via APIs. They write the "canned transactions" that the parametric users execute.

⇒3.2 Workers Behind the Scene

These are the people who build and maintain the DBMS ecosystem itself, rather than using the data.

• DBMS System Designers and Implementers: The elite engineers at Oracle, Microsoft, or the open-source community who actually write the C/C++ code that constitutes the DBMS software itself.
• Tool Developers: Engineers who build graphical interfaces, monitoring dashboards, and performance tuning tools that sit on top of the DBMS.

Also known as the ANSI/SPARC architecture, this is the most critical conceptual framework in database theory. Its entire purpose is to separate the user applications from the physical database, achieving Data Independence.

The architecture divides the database into three distinct levels of abstraction:

⇒4.1 Internal Level (Physical Schema)

The lowest level of abstraction. It describes how the data is actually stored on the physical hardware. It deals with bytes, disk blocks, B-Trees, hashing algorithms, and access paths. Only the DBMS kernel and the DBA interact with this level.

⇒4.2 Conceptual Level (Logical Schema)

The middle level. It describes what data is stored in the entire database and the relationships among that data. It hides physical storage details and concentrates on defining entities, data types, relationships, constraints, and security policies. Database Designers work at this level (e.g., "We need a Table called STUDENTS with columns ID and Name").

⇒4.3 External Level (View Schema)

The highest level. It describes only a specific portion of the database that a particular user group is interested in, hiding the rest of the database for security and simplicity. There can be many different external schemas (Views) for the same conceptual schema.

For example, the HR department has an External View showing `Employee Name` and `Salary`. The IT department has an External View showing `Employee Name` and `Laptop Assigned`. Neither knows about the other's data, even though it all resides in the same Conceptual Schema.

Data Independence is the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. It is the primary benefit of the Three-Schema Architecture.

⇒5.1 Logical Data Independence

The ability to modify the Conceptual Schema without causing the External Schemas or Application Programs to be rewritten.

Changes at the conceptual level include adding a new table, adding a new column to an existing table, or splitting a table in two.
Example: If we add an `Age` column to the `STUDENTS` table at the Conceptual level, the existing External Views (which only pull `Name` and `ID`) are completely unaffected. The application code doesn't break. This is incredibly difficult to achieve and represents the true power of a relational database.

⇒5.2 Physical Data Independence

The ability to modify the Internal Schema without causing the Conceptual Schema to change.

Changes at the internal level are usually done to improve performance.
Example: The DBA decides to switch from storing data sequentially to using a complex B+ Tree hash index to speed up searches. They might move the database from a spinning hard drive to an SSD. The Conceptual Schema (the tables and columns) remains exactly the same. The application programmer writes the exact same SQL query; the DBMS just executes it much faster now. Physical Data Independence is easy to achieve.

To interact with the various levels of the DBMS architecture, specific programming languages (or sub-languages) are required. In modern systems like PostgreSQL or MySQL, these are all combined into one comprehensive language: SQL (Structured Query Language).

⇒6.1 Data Definition Language (DDL)

Used by the DBA and Database Designers to define both the Conceptual and Internal schemas. DDL commands define the structure, constraints, and relationships of the database.

Crucially, DDL does not touch the actual data inside the tables; it only creates or destroys the tables themselves.

Common DDL commands include:
`CREATE`: To make a new database, table, index, or view. `ALTER`: To modify the structure of an existing table (e.g., adding a column).
`DROP`: To completely obliterate a table and all its data from the system. `TRUNCATE`: To empty a table of all data, but leave the structure intact.

⇒6.2 Data Manipulation Language (DML)

Used by application programmers and end-users to retrieve, insert, delete, and modify the actual data residing in the tables.

DML is further divided into two types:
Procedural DML: The user must specify what data is needed and exactly how to get it (by writing loops and navigational code). Non-Procedural / Declarative DML: The user specifies only what data is needed, and the DBMS figures out the most efficient way to get it. SQL is declarative.

Common DML commands include:
`SELECT`: To retrieve data. `INSERT`: To add new rows.
`UPDATE`: To modify existing rows. `DELETE`: To remove specific rows.

A Data Model is a collection of concepts that can be used to describe the structure of a database—the data types, relationships, and constraints. Data models provide the necessary abstraction.

⇒7.1 Evolution of Data Models

• Hierarchical Model: Data is organized in a strict tree-like structure. A parent record can have multiple children, but a child can only have ONE parent. (Very inflexible. e.g., IBM's IMS).
• Network Model: Solves the hierarchical restriction by allowing data to be organized in a graph. A child can have multiple parents. (Highly complex, navigation required following physical pointers).
• Relational Model: Proposed by E.F. Codd in 1970. Represents data simply as two-dimensional tables (relations). All relationships are logical, not physical pointers. It relies on a mathematical foundation (Relational Algebra). This is the absolute industry standard today (MySQL, Oracle, SQL Server).
• Object-Oriented Data Model: Treats data as objects with methods and encapsulation, similar to OOP programming languages. Used in specialized CAD/CAM or multimedia databases.
• NoSQL (Not Only SQL) Models: Designed for massive horizontal scaling and unstructured data. Includes Document-based (MongoDB), Key-Value stores (Redis), Column-family stores (Cassandra), and Graph databases (Neo4j).

For the remainder of this course, we will focus exclusively on the Relational Model and its precursor, the ER Model.

Before you create tables in SQL, you must design the database conceptually. The Entity-Relationship (ER) model is a high-level conceptual data model that views the real world as a collection of basic objects (entities) and the connections among them (relationships).

The result of this modeling process is an ER Diagram, which serves as a universally understood blueprint that developers and business analysts can discuss before writing any code.

⇒8.1 Entities and Entity Sets

• Entity: A specific "thing" or "object" in the real world with an independent existence. It can be physical (a person, a car) or conceptual (a university course, a bank account).
• Entity Set: A collection of all entities of the same type that share the same properties. For example, `John Doe` is an entity. `STUDENT` is the entity set containing all students.

In an ER Diagram, an Entity Type is represented by a Rectangle.

An Attribute is a property or characteristic that describes an entity. For the `STUDENT` entity, attributes might include `RollNumber`, `Name`, and `Age`.

In an ER Diagram, an Attribute is represented by an Oval attached to its entity rectangle.

⇒9.1 Types of Attributes

• Simple (Atomic) Attributes: Cannot be divided into smaller subparts. (e.g., `Age`, `Gender`).
• Composite Attributes: Can be divided into smaller subparts, which represent more basic attributes with independent meaning. (e.g., `Address` can be broken down into `Street`, `City`, `State`, `Zip`).
• Single-Valued Attributes: Have a single value for a particular entity. (e.g., A person has only one `Date_of_Birth`).
• Multi-Valued Attributes: Can have a set of values for a single entity. (e.g., A person might have multiple `Phone_Numbers` or `College_Degrees`). Represented in diagrams by a Double Oval.
• Derived Attributes: The value of this attribute is not stored permanently; instead, it is calculated dynamically from other attributes. (e.g., `Age` is derived from `Date_of_Birth` and the current date). Represented by a Dashed Oval.

A database must have a way to uniquely identify every single entity within an entity set. You cannot rely on a `Name` attribute, because two students might both be named "John Doe".

⇒10.1 Key Attributes

An attribute (or a combination of attributes) whose values are distinct for each individual entity in the entity set is called a Key Attribute. For a `STUDENT` entity, the `RollNumber` or `Social_Security_Number` would be a key.

In an ER Diagram, the Key Attribute is represented by an oval with the attribute name underlined.

⇒10.2 Candidate, Primary, and Super Keys

• Super Key: A set of one or more attributes that, taken collectively, allow us to uniquely identify an entity in the entity set. (e.g., `{RollNumber, Name, Age}` is a super key, even though it's unnecessarily bloated).
• Candidate Key: A "minimal" Super Key. It is a super key for which no proper subset is a super key. An entity might have multiple candidate keys (e.g., both `RollNumber` and `Email` could uniquely identify a student).
• Primary Key: The one specific Candidate Key chosen by the database designer as the principal means of identifying entities. It must be unique and cannot be NULL.

Entities do not exist in isolation. A Relationship is an association among two or more entities. For example, a `STUDENT` entity is associated with a `COURSE` entity through an `ENROLLS_IN` relationship.

In an ER Diagram, a Relationship is represented by a Diamond connecting the participating entities.

⇒11.1 Degree of a Relationship

The degree is the number of participating entity types.

• Unary (Recursive): An entity is related to itself. (e.g., An `EMPLOYEE` is managed by another `EMPLOYEE`).
• Binary: Two entities participate. The most common type. (e.g., `STUDENT` and `COURSE`).
• Ternary: Three entities participate simultaneously. (e.g., `SUPPLIER`, `PART`, and `PROJECT`).

Cardinality expresses the maximum number of relationship instances that an entity can participate in. It defines the business rules of the database.

⇒12.1 The Four Cardinality Ratios (for Binary Relationships)

• One-to-One (1:1): An entity in A is associated with at most one entity in B, and vice versa.
  Example: A `MANAGER` manages exactly one `DEPARTMENT`, and a `DEPARTMENT` is managed by exactly one `MANAGER`.
• One-to-Many (1:N): An entity in A is associated with any number of entities in B. An entity in B is associated with at most one entity in A.
  Example: A `DEPARTMENT` employs many `EMPLOYEE`s, but an `EMPLOYEE` belongs to only one `DEPARTMENT`.
• Many-to-One (N:1): The reverse of 1:N.
• Many-to-Many (M:N): An entity in A is associated with any number of entities in B, and an entity in B is associated with any number of entities in A.
  Example: A `STUDENT` can enroll in many `COURSE`s, and a `COURSE` can have many `STUDENT`s enrolled in it.

In an ER Diagram, cardinality is typically shown by placing a `1` and an `M` (or `N`) on the lines connecting the diamond to the rectangles.

While Cardinality dictates the maximum number of relationships, Participation dictates the minimum. It answers the question: "Does the entity have to participate in this relationship to exist?"

⇒13.1 Total vs. Partial Participation

• Total Participation (Existence Dependency): Every entity in the set MUST participate in at least one relationship instance.
  Example: Every `EMPLOYEE` must belong to a `DEPARTMENT`. An employee cannot exist in a vacuum.
  Notation: Represented by a Double Line connecting the entity rectangle to the relationship diamond.
• Partial Participation: Some entities may participate in the relationship, but it is not required for their existence.
  Example: An `EMPLOYEE` may manage a `DEPARTMENT`, but most employees do not.
  Notation: Represented by a Single Line.

A Weak Entity is an entity type that does not have enough attributes to form a Primary Key of its own. It cannot exist without being tied to a stronger "Owner" entity.

For example, consider a company database that stores `EMPLOYEE`s and their `DEPENDENT`s (children/spouses) for health insurance purposes.
The `DEPENDENT` entity might only have a `Name` attribute (e.g., "Timmy"). "Timmy" is not a unique identifier across the whole company. Timmy's existence in the database is entirely dependent on his father, the `EMPLOYEE`, working there.

⇒14.1 Characteristics and Notation

• A weak entity must always have Total Participation with its identifying relationship.
• It is identified by combining the Primary Key of its Owner Entity with its own Partial Key (Discriminator), usually a dashed-underline.
• In an ER Diagram, a Weak Entity is drawn as a Double Rectangle, and its identifying relationship is drawn as a Double Diamond.

As databases became more complex, the basic ER model was extended to include Object-Oriented concepts like inheritance.

⇒15.1 Specialization and Generalization

These represent the "IS-A" relationship (Subclasses and Superclasses).

• Specialization (Top-Down): The process of defining a set of subclasses from a superclass.
  Example: An `EMPLOYEE` (Superclass) can be specialized into `ENGINEER`, `TECHNICIAN`, or `MANAGER` (Subclasses). The subclasses inherit all attributes of the superclass (Name, ID) but also have their own specific attributes (e.g., Engineer has `Programming_Language`, Manager has `Bonus_Amount`).
• Generalization (Bottom-Up): The reverse process. Recognizing that `CAR` and `TRUCK` share common attributes and generalizing them into a `VEHICLE` superclass.

In diagrams, an "IS-A" relationship is often depicted as a triangle pointing toward the superclass.

Unit 1 provides the theoretical foundation and the architectural blueprinting tools required before any SQL code is written.

⇒16.1 University Exam Checklist

• List five major disadvantages of traditional File Processing Systems compared to a DBMS.
• Draw and explain the Three-Schema Architecture (ANSI/SPARC).
• Define Logical Data Independence and Physical Data Independence. Give an example of each.
• Differentiate between DDL and DML.
• Explain the different types of attributes in an ER model (Simple, Composite, Single-valued, Multi-valued, Derived) with examples and their diagrammatic notations.
• Define Primary Key, Candidate Key, and Super Key.
• Explain the four types of Cardinality Ratios (1:1, 1:N, N:1, M:N).
• What is the difference between Total and Partial Participation?
• What is a Weak Entity? How is it represented in an ER diagram?
• Draw a complete ER diagram for a University Management System or a Hospital Management System, correctly applying all notations.