Database Concepts Overview

Study Notes

A database is a structured collection of organized data, information, and records, typically stored electronically in a computer system.
A database is a shared collection of logically related data, enabling various users to access and manage data effectively in an organization.
Database Management Systems (DBMS) control databases.
Data, DBMS, and associated applications form a database system.

Databases store information crucial to business, social, personal, and religious life.
The core value of information lies not just in collection and retrieval, but in its application.
Databases surpass simpler storage systems in access, sharing, integrity, and security.

Everyday examples include supermarket purchases, credit card transactions, booking holidays, library use, insurance, video rentals, internet use, and university studies.

These systems store data in separate files, each managed by a specific program.
Example - Library data kept separately from Examination data
Limitations include program-data dependence (file structures are programmed into application code), redundant data storage (duplicate data in various files), limited data sharing among applications (lack of centralized control), and vulnerability to data inconsistencies.
File Processing Systems are prone to:
- Lengthy development times due to programmers designing their own file formats.
- Excessive program maintenance requiring significant resources.
- Prone to data inconsistencies whereby a change in one table requires modification in many connected tables or the data is changed unintentionally.

Databases are centralized repositories of shared data managed by a controlling agent. Data is standardized, providing a convenient, integrated approach.
A Database Management System, or DBMS, is needed.
Advantages include data sharing, controlled redundancy, data independence, and better integrity.
Using a DBMS improves efficiency, reliability, and scalability compared to file-based systems.

A software system for creating, maintaining, and controlling user access to databases.
Database application programs interact with a DBMS by issuing requests (SQL statements).
Examples include Access, MySQL, PostgreSQL, MongoDB, MariaDB, SQL Server, and Oracle. Oracle is generally considered more powerful and expensive than the others but has greater storage capacity and enhanced security.

Database design follows a systematic approach including data collection (gathering database information), data analysis (creating entity diagrams—visual representations of database entities and relationships), and design (defining the database schemas using either a visual tool or SQL). SQL (Structured Query Language) is used to define the database schema. A Database Wizard is a structured, step-by-step process to create, use, and control a database

Example Components include Client, Product, Order, Employee, Department, and Delivery.
Tables defining attributes like:
- Client: ID, Name, Phone, Address, Birthdate
- Product: Product No, Name, Price, Details, Production Date, Expiry Date, Stock
- Order: Order No, Date, Total
- Employee: Employee ID, Name, Job
- Department: Department No, Department name
- Delivery: Phone No, Name, National ID

Entity (something that holds data): (e.g., Customer, Product, Order). Attributes describe characteristics of an entity.
Relationship (how entities relate): (e.g., One customer can make many orders; An order contains many products.) Normal forms for attributes make sure you have correct storage structures.
Relationships include one-to-one, one-to-many, and many-to-many relationships.