Database Management Systems and ERD Fundamentals

Introduction to Database Management Systems

A database is a structured collection of data stored on a computer system. It is managed using a Database Management System (DBMS), which provides the necessary tools for data storage, retrieval, and modification.

Core Benefits of Using a DBMS

• Reduced Data Redundancy: A DBMS helps minimize repeated data through organizational rules such as normalization.
• Enhanced Data Accuracy: The system uses specific rules, such as referential integrity, to maintain data consistency.
• Support for Multiple Users: Many people can access and use the database simultaneously without encountering operational problems.

Centralized vs. Distributed Databases

• Centralized: All data is stored in a single, primary location. This setup is easier to manage but carries a higher risk if that specific location fails.
• Distributed: Data is stored across several different locations. This method is safer and often faster, though it is more complex to manage.

Data vs. Information

Raw data is transformed into information once it has been properly organized, processed, and analyzed.

Database Structure and Integrity Rules

The fundamental component of a relational database is the table, which is composed of rows and columns.

Referential Integrity and Foreign Keys

Referential integrity ensures that foreign keys always match a valid primary key. For example, this rule ensures that every order in a system is linked to an existing customer record.

The Data Dictionary

A data dictionary (or catalog) is a specialized file that stores metadata about the database structure, including table names, column definitions, data types, and integrity rules.

Understanding Composite Keys

A composite key is a primary key that consists of two or more columns. For example, in an OrderDetails table, the combination of OrderID and ProductID together identifies each unique row.

Entity-Relationship Diagrams (ERD)

An Entity-Relationship Diagram (ERD) visually represents the main components of a database: entities (such as tables), their attributes (fields), and the relationships between them.

Cardinality and Relationship Types

Cardinality indicates how many records in one table correspond to records in another table. Relationships are categorized as follows:

• Unary: A table is related to itself.
• Binary: Two different tables are related.
• Ternary: Three tables are related within a single relationship.

Practical Database Schema Example

Tables and Attributes

• Books: ISBN (PK), Title, PublishDate, Price, Status, StockQuantity
• Authors: AuthorID (PK), Name, Nationality
• BookAuthors: ISBN (FK), AuthorID (FK) (Used to link many-to-many relationships)
• Customers: CustomerID (PK), FirstName, LastName, Email, Phone, RegistrationDate
• Orders: OrderID (PK), CustomerID (FK), OrderDate, TotalAmount, PaymentStatus
• OrderDetails: OrderDetailID (PK), OrderID (FK), ISBN (FK), Quantity, PriceAtPurchase
• BorrowedBooks: BorrowID (PK), CustomerID (FK), ISBN (FK), BorrowDate, DueDate, ReturnDate

Relationships and Cardinalities

• Books ↔ Authors: Many-to-Many (facilitated via the BookAuthors table)
• Customers ↔ Orders: One-to-Many
• Orders ↔ OrderDetails: One-to-Many
• Customers ↔ BorrowedBooks: One-to-Many
• Books ↔ BorrowedBooks: One-to-Many

Normalized Data Model

Entity and Relationship Summary

Entities:

• Books (ISBN, Title, PublishDate, Price, Status, StockQuantity)
• Authors (AuthorID, Name, Nationality)
• Customers (CustomerID, FirstName, LastName, Email)
• Orders (OrderID, OrderDate, TotalAmount, PaymentStatus)
• OrderDetails (OrderDetailID, OrderID, ISBN, Quantity, PriceAtPurchase)
• BorrowedBooks (BorrowID, BorrowDate, DueDate, ReturnDate)

Relationships:

• Books ↔ Authors: Many-to-Many via BookAuthors.
• Customers ↔ Orders: One-to-Many.
• Orders ↔ OrderDetails: One-to-Many.
• Customers ↔ BorrowedBooks: One-to-Many.
• Books ↔ BorrowedBooks: One-to-Many.