Databases
Limitations of a File-Based Approach:
Data Redundancy & Inconsistency – Data may be duplicated and stored inconsistently,
leading to integrity issues.
No Validation Mechanism – Input errors can't be caught automatically.
Difficult Data Management – Updating or deleting specific pieces of data is complex and
error-prone in flat files.
Relational Database Design:
Relational database – organizes data into tables (relations) with rows (tuples) and columns
(attributes)
Primary Key – Uniquely identifies each record
Foreign Key – References a primary key in another table, creating relationships between
tables
Entity–Relationship (E–R) Modelling:
A top-down design approach
Entities – Represent real-world objects (e.g., Student, Booking)
Relationships – Describe associations between entities
Attributes are associated with entities
Normalization:
A bottom-up technique to eliminate redundancy:
1NF (First Normal Form) – No repeating groups
2NF (Second Normal Form) – No partial dependencies
3NF (Third Normal Form) – No transitive dependencies
Improves consistency, reduces duplication, and organizes data logically
Database Architecture (ANSI 3-level):
Internal Level – How data is stored physically (known only by DBMS).
Conceptual Level – Logical structure seen by the DBA.
External Level – Views for users/programs—controls what parts of data they access.
Facilities Provided by a DBMS:
Data Dictionary – Stores metadata (data about data).
Indexing – Improves search performance.
Backup & Recovery – Ensures data integrity and durability.
Query Processor – Handles SQL queries.
User Access Control – Manages who can see or edit data.
Structured query language (SQL):
Industry standard methods for building/modifying databases:
DBMS uses DDL to create, modify and remove data structures that form a relational
database
DDL statements written as scripts with syntax similar to a computer program
DBMS uses DML to add, modify, delete and retrieve data STORED in a relational
database
DML statements also written as script with syntax similar to a computer program
SQL (DDL) commands:
SQL (DDL) data types for attributes:
SQL (DDL) query commands:
SQL (DDL) maintenance commands:
Summary Points:
Databases enhance data integrity, reduce redundancy, and are easier to
manage than file-based systems
Relational databases use keys and relationships
Normalization and E-R modelling are foundational to good design
SQL (DDL + DML) is used for database creation and interaction
A DBMS offers powerful tools for secure, reliable, and efficient database
management
