Understanding Database Schemas: A Comprehensive Guide
What is a Database?
A database is a collection of related data, which refers to known facts that can be recorded and have implicit meaning.
The Three-Schema Architecture
The three-schema architecture aims to separate user applications from the physical database.
Internal Level (Internal Schema)
- Describes the physical storage structure of the database.
- Uses a physical data model, detailing data storage and access paths.
Conceptual Level (Conceptual Schema)
- Describes the entire database structure for all users.
- Hides physical storage details, focusing on entities, data types, relationships, operations, and constraints.
- Often based on a conceptual schema design in a high-level data model.
External Level (External Schemas or User Views)
- Presents a tailored view of the database for specific user groups, hiding irrelevant information.
- Typically implemented using a representational data model, potentially based on a high-level data model design.
Advantages of Using a DBMS Approach
Controlling Redundancy
Data redundancy, common in file processing, wastes storage, duplicates effort during updates, and increases inconsistency risks. However, controlled redundancy, like indexes, can enhance query performance.
Restricting Unauthorized Access
A DBMS provides security and authorization mechanisms for the Database Administrator (DBA) to manage accounts and enforce access restrictions.
Providing Persistent Storage for Program Objects
Object-oriented DBMS simplifies the storage and retrieval of complex runtime objects (e.g., lists, trees) beyond program termination.
Providing Storage Structures and Search Techniques for Efficient Query Processing
The DBMS optimizes query processing by selecting efficient execution plans based on storage structures.
Providing Backup and Recovery
The DBMS ensures data recovery from hardware or software failures through its backup and recovery subsystem.
Providing Multiple User Interfaces
A DBMS caters to diverse users with varying technical expertise by offering multiple interfaces, such as query languages and programming interfaces.
Representing Complex Relationships Among Data
A DBMS effectively represents, defines, retrieves, and updates complex data relationships.
Enforcing Integrity Constraints
A DBMS enforces data integrity constraints, starting with data type specifications for each data item.
Permitting Inferencing and Actions Via Rules
Deductive DBMS allows declarative rules for inferring new data, while active DBMS enables “active rules” to trigger automatic actions.
ER Diagram
Types of Database End Users
Casual End Users
Access the database occasionally for varied information, often using sophisticated query languages. Examples include middle or high-level managers.
Naive or Parametric End Users
Frequently query and update the database using predefined, tested queries (canned transactions). Examples include bank tellers, reservation agents, and shipping clerks.
Sophisticated End Users
Utilize advanced DBMS features to develop custom applications for complex requirements. Examples include engineers, scientists, and business analysts.
Categories of Data Models
High-Level or Conceptual Data Models
- Align with how users perceive data, using concepts like entities, attributes, and relationships.
- Entities represent real-world objects (e.g., employee), attributes describe entity properties (e.g., salary), and relationships depict associations between entities (e.g., works on).
Low-Level or Physical Data Models
- Describe data storage specifics on physical media (e.g., magnetic disks), intended for IT specialists.
Representational (or Implementation) Data Models
- Bridge the gap between high-level and low-level models, offering user-friendly concepts while staying close to data storage implementation.
- Hide storage complexities but allow direct implementation on computer systems.
Types of Attributes in ER Models
Simple vs. Composite Attributes
- Composite attributes are divisible into subparts (e.g., Address with Street_address, City, State, Zip). Simple or atomic attributes are indivisible (e.g., Age).
Single-Valued vs. Multi-Valued Attributes
- Single-valued attributes hold one value per entity (e.g., Age). Multi-valued attributes can have multiple values (not shown in the provided HTML).
Stored vs. Derived Attributes
- Stored attributes are directly stored in the database (e.g., Date of Birth). Derived attributes are calculated from other attributes (not shown in the provided HTML).
Note: The provided HTML lacks information on multi-valued and derived attributes. These concepts are essential for a comprehensive understanding of ER models.