Database Concepts and Architectures Explained

Posted on Aug 12, 2025 in Computers

Oracle Database Fundamentals

Oracle Memory Structures: SGA and PGA

The basic memory structures associated with Oracle are the System Global Area (SGA) and the Program Global Area (PGA). This statement is True.

Oracle Background Processes: LGWR and PMON

  • LGWR: Log Writer
  • PMON: Process Monitor

Single-User vs. Multi-User Databases

  • Single-User Database

    A database that only one user can access at a time. It is mainly used for personal or small-scale applications where simultaneous data access by multiple users is not needed.

  • Multi-User Database

    A database that allows multiple users to access and manipulate the database at the same time. It is used in large applications like banking, online shopping, etc., where concurrent access is required.

Strong and Weak Entities in ER Models

  • A strong entity has its own primary key which uniquely identifies each record, whereas a weak entity does not have a primary key and depends on a strong entity for its identification.
  • A strong entity can exist independently without depending on another entity, while a weak entity cannot exist on its own and must be associated with a strong entity.

Four Types of Database Attributes

  1. Simple Attribute – Cannot be divided further (e.g., Age, Salary).
  2. Composite Attribute – Can be divided into smaller sub-parts (e.g., Full Name into First Name and Last Name).
  3. Derived Attribute – Can be derived from other attributes (e.g., Age from Date of Birth).
  4. Multivalued Attribute – Can have multiple values for a single entity (e.g., Phone numbers).

Database Concepts and Views

Logical and Physical Database Views

Logical view refers to the way a user views the data, and Physical view refers to the way data is physically stored and processed. This statement is True.

Understanding Partial Dependency

The value of a non-key attribute can be determined by a subset of the primary key but not the entire key; this is known as Partial Dependency. This statement is True.

System Global Area (SGA) Components

The SGA is a group of memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. This statement is True.

Oracle Database Server Components

An Oracle database server has Processes and Memory Structures.

Advanced Database Concepts

Database Generalization Explained

  • Generalization is a process in database design where two or more lower-level entities are combined to form a higher-level entity.
  • It is a bottom-up approach.
  • Example: Suppose we have two entities: Car and Truck. Both share common attributes like vehicle number, engine capacity, etc. So, we generalize them into a higher entity called Vehicle.

Oracle Call Interface (OCI) Overview

  • Oracle Call Interface (OCI) is a low-level API (Application Programming Interface) provided by Oracle to allow programs written in languages like C or C++ to interact with the Oracle database.
  • It allows applications to perform operations like SQL execution, data retrieval, and transaction control.
  • OCI provides fine-grained control over database operations, offering high performance and flexibility.

Partial Dependency with Example

  • A Partial Dependency occurs when a non-prime attribute is dependent on only a part of a composite primary key and not the whole key.
  • Example: In a table Course_Registration (StudentID, CourseID, StudentName, CourseName),
    • (StudentID, CourseID) is the composite primary key.
    • StudentName depends only on StudentID (part of the key) — this is a partial dependency.

Key Advantages of DBMS

  • Data Redundancy Control – Avoids duplication of data.
  • Data Integrity – Ensures accuracy and consistency of data.
  • Data Security – Only authorized users can access the database.
  • Backup and Recovery – Provides backup and restore facilities in case of failures.
  • Efficient Query Processing – Enables fast and optimized retrieval of information.

ERD for Logical and Physical Structures

(Since drawing is not possible directly in text, a simple ERD can be described.)

  • Entities: Student, Course
  • Attributes:
    • Student: StudentID (PK), StudentName, Email
    • Course: CourseID (PK), CourseName
  • Relationship:
    • A Student enrolls in a Course.

Simple ERD Description:

[Student] ---------<enrolls>--------- [Course]
(StudentID, StudentName, Email)        (CourseID, CourseName)

Here, StudentID and CourseID are primary keys. The “enrolls” relationship shows that students can enroll in multiple courses.

Database Management System (DBMS) Deep Dive

Detailed Explanation of Oracle SGA

  • SGA (System Global Area) is a shared memory region that contains data and control information for one Oracle database instance.
  • It is allocated when the database instance starts and deallocated when the instance shuts down.
  • Key Components of SGA:
    • Database Buffer Cache: Stores copies of data blocks that are read from data files. It improves database performance by reducing physical reads.
    • Shared Pool: Stores parsed SQL statements, PL/SQL code, and data dictionary information.
    • Redo Log Buffer: Stores redo entries (changes made to the database) that can be used for recovery.
    • Large Pool: An optional area for large memory allocations like RMAN backup operations and session memory.
    • Java Pool: Used for Java code execution within the database.
  • Importance: SGA helps in speeding up data access and ensuring efficient database management by reducing physical I/O.

Steps to Establish JDBC Connection

JDBC (Java Database Connectivity) is used to connect Java applications to a database.

  1. Import JDBC Packages

    Import necessary classes like java.sql.* to use JDBC.

  2. Load and Register the Driver

    Load the JDBC driver class. Example:

    Class.forName("oracle.jdbc.driver.OracleDriver");

  3. Establish the Connection

    Use DriverManager.getConnection() to connect to the database. Example:

    Connection con = DriverManager.getConnection("jdbc:oracle:thin@localhost:1521:xe", "username", "password");

  4. Create a Statement

    Create a Statement or PreparedStatement object to send SQL queries. Example:

    Statement stmt = con.createStatement();

  5. Execute SQL Queries

    Execute SQL commands like SELECT, INSERT, UPDATE, DELETE using the statement. Example:

    ResultSet rs = stmt.executeQuery("SELECT * FROM Students");

  6. Process the Results

    Process the ResultSet obtained from SELECT queries.

  7. Close the Connection

    Close the ResultSet, Statement, and Connection to free resources. Example:

    rs.close();
stmt.close();
con.close();

Core Functionalities of a DBMS

  • A DBMS (Database Management System) is software that manages databases and provides an interface between users and data.
  • Major functionalities:
    • Data Storage Management: Manages how data is stored, accessed, and updated.
    • Data Manipulation: Provides query languages like SQL for inserting, updating, deleting, and retrieving data.
    • Data Security and Authorization: Ensures only authorized users access the database.
    • Backup and Recovery: Automatically backs up data and restores it in case of failure.
    • Transaction Management: Ensures that transactions are processed reliably (ACID properties: Atomicity, Consistency, Isolation, Durability).
    • Data Integrity Management: Maintains accuracy and consistency of data over its lifecycle.
    • Concurrency Control: Manages simultaneous data access by multiple users without conflicts.

Database Storage and Modeling

Database Storage ERD and Views

  • Logical Storage Structure ERD Example

    Entities: Tablespaces, Segments, Extents, Blocks.

    [Tablespace] --> [Segment] --> [Extent] --> [Block]

  • Physical Storage Structure ERD Example

    Entities: Datafiles, Redo Log Files, Control Files.

    [Datafile] --> [Physical Blocks]
[Redo Log File]
[Control File]

  • Different Database Views

    • External View: How users interact with the database (user-specific).
    • Conceptual View: Logical structure of the entire database.
    • Internal View: Physical storage of data.

MongoDB createCollection() Method

  • In MongoDB, the createCollection() method is used to explicitly create a new collection.
  • Syntax:
    db.createCollection(name, options)
  • Parameters:
    • name: Name of the collection (required).
    • options: (optional) Configuration like capped collection, size limit, etc.
  • Example:
    db.createCollection("students", { capped: false })
  • Collections are usually created automatically when you insert a document, but createCollection() gives you more control over its configuration.

Transitive Dependency Explained

  • Transitive Dependency occurs when a non-prime attribute depends on another non-prime attribute rather than directly depending on the primary key.
  • Example: In a table Employee (EmpID, EmpName, DeptID, DeptName),
    • EmpID → DeptID → DeptName.
    • DeptName depends transitively on EmpID through DeptID.

Aggregation in ER Modeling

  • Aggregation is a concept in ER modeling where the relationship itself is treated as an entity.
  • It is used when relationships have their own attributes.
  • Example: If Employee works on a Project and Works_On relationship has an attribute like Hours_Worked, then Works_On can be treated as an entity using aggregation.
  • Aggregation helps model complex relationships more accurately.

Essential DBMS Functionalities

  • Data Storage Management: Manages the storage of data efficiently using physical and logical structures.
  • Data Security and Authorization: Ensures that only authorized users have access to certain parts of the database.
  • Transaction Management: Maintains data integrity and consistency by supporting ACID properties during concurrent access.

JDBC Drivers and Database Types

Different Types of JDBC Drivers

In JDBC (Java Database Connectivity), there are four types of drivers:

  • Type 1: JDBC-ODBC Bridge Driver

    • Uses ODBC driver to connect to the database.
    • Requires ODBC installation; slower and less portable.

  • Type 2: Native-API Driver

    • Converts JDBC calls into database-specific native calls (C/C++ libraries).
    • Faster than Type 1 but needs native library installation.

  • Type 3: Network Protocol Driver (Middleware Driver)

    • JDBC calls are sent to a middle-tier server, which communicates with the database.
    • Good for internet applications; platform-independent.

  • Type 4: Thin Driver (Pure Java Driver)

    • Directly converts JDBC calls to database-specific protocols without any native code.
    • Most efficient and commonly used driver in modern applications.

Oracle System Global Area (SGA)

  • System Global Area (SGA) is a shared memory area that holds data and control information for one Oracle database instance.
  • It includes:
    • Database Buffer Cache (stores copies of data blocks),
    • Shared Pool (stores SQL statements, PL/SQL code),
    • Redo Log Buffer (stores changes made to the database).
  • SGA improves the database’s performance by reducing the need for disk I/O operations.

SQL vs. NoSQL Databases: Key Differences

  • SQL databases are relational, meaning they store data in structured tables with rows and columns, while NoSQL databases are non-relational and store data in flexible formats like documents, key-value pairs, graphs, or wide-columns.
  • SQL databases have a fixed schema, which means the structure of the data must be defined before inserting data. NoSQL databases have a dynamic or flexible schema, allowing changes to the data structure without much effort.
  • SQL databases use Structured Query Language (SQL) for defining and manipulating data. NoSQL databases use different query languages depending on the database type.
  • SQL databases are best suited for complex queries, transactions, and operations that require high data consistency. NoSQL databases are preferred for large-scale, high-speed applications where scalability and quick access to unstructured or semi-structured data are important.
  • Examples of SQL databases include MySQL, PostgreSQL, and Oracle. Examples of NoSQL databases include MongoDB, Cassandra, and Redis.

JDBC Connection Steps

  1. Import JDBC packages

    import java.sql.*;

  2. Load and register JDBC driver

    Class.forName("oracle.jdbc.driver.OracleDriver");

  3. Establish the connection

    Connection con = DriverManager.getConnection(DB_URL, username, password);

  4. Create a Statement object

    Statement stmt = con.createStatement();

  5. Execute SQL query

    ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

  6. Process the results and close the connection

    rs.close();
stmt.close();
con.close();

Database Normalization and Architecture

Database Normalization: 1NF, 2NF, 3NF

Normalization is the process of organizing data in a database to avoid redundancy and ensure data integrity. It involves breaking down large tables into smaller, manageable ones while establishing relationships between them. The goal is to reduce the complexity of data models and ensure data is stored logically.

First Normal Form (1NF)

A table is in 1NF if:

  • All columns contain atomic (indivisible) values.
  • Each record in the table is unique.

Example: Consider a student table:

StudentID | Name    | Courses
1         | John    | Math, Science
2         | Alice   | English, History

This is not in 1NF because the “Courses” column contains multiple values. To make it 1NF:

StudentID | Name    | Course
1         | John    | Math
1         | John    | Science
2         | Alice   | English
2         | Alice   | History

Second Normal Form (2NF)

A table is in 2NF if:

  • It is in 1NF.
  • All non-key attributes are fully dependent on the primary key.

Example: Let’s consider the following table:

StudentID | CourseID | Instructor | CourseName
1         | 101      | Dr. Smith  | Math
1         | 102      | Dr. Brown  | Science
2         | 101      | Dr. Smith  | Math

This table violates 2NF because Instructor depends on CourseID, not just the StudentID. To make it 2NF:

  • Split the table into two:
    1. Student_Courses(StudentID, CourseID)
    2. Courses(CourseID, Instructor, CourseName)

Third Normal Form (3NF)

A table is in 3NF if:

  • It is in 2NF.
  • There are no transitive dependencies, meaning non-key attributes should not depend on other non-key attributes.

Example: Consider the following table:

StudentID | Name    | DeptID | DeptName
1         | John    | 101    | Computer Science
2         | Alice   | 102    | History

This violates 3NF because DeptName depends on DeptID, which is a non-key attribute. To make it 3NF:

  • Split the table into:
    1. Students(StudentID, Name, DeptID)
    2. Departments(DeptID, DeptName)

University Management System ERD

For a University Management System, here’s a basic ERD with four entities:

  1. Student

    • Attributes: StudentID, Name, DOB

  2. Course

    • Attributes: CourseID, CourseName, Credits

  3. Professor

    • Attributes: ProfessorID, Name, Department

  4. Enrollment

    • Attributes: EnrollmentID, StudentID, CourseID

Relationships:

  • A Student can enroll in multiple Courses (many-to-many).
  • A Professor can teach multiple Courses (one-to-many).

Two-Tier vs. Three-Tier Architecture

Two-Tier Architecture Explained

  • Definition: In Two-Tier Architecture, the system is divided into two layers: the Client and the Server.

    • Client Layer: The front-end application that interacts directly with the user.
    • Server Layer: The back-end, which typically consists of the database and business logic.

  • How It Works:

    • The client sends requests to the server (usually a database server).
    • The server processes the request and sends back the data.

  • Advantages:

    • Simple to set up.
    • Faster performance in small applications because of direct communication between client and server.

  • Disadvantages:

    • Less scalable and difficult to maintain as the system grows.
    • Security risks due to direct access from clients to the server.

Three-Tier Architecture Explained

  • Definition: In Three-Tier Architecture, the system is divided into three layers: the Presentation Layer, the Logic Layer, and the Data Layer.

    • Presentation Layer (Client): The front-end that interacts with the user.
    • Logic Layer (Business Layer): The middle-tier server where business logic and processing take place.
    • Data Layer (Database Layer): The back-end that stores and manages the database.

  • How It Works:

    • The client interacts with the Presentation Layer.
    • The Presentation Layer sends the request to the Logic Layer, which processes it and communicates with the Data Layer (database).
    • The processed data is then sent back through the layers to the client.

  • Advantages:

    • More scalable and secure, as logic and data layers are separate from the presentation.
    • Easier to maintain and modify since each tier is independent.

  • Disadvantages:

    • More complex setup and maintenance.
    • Slightly slower due to multiple layers involved.