CAP422J1 - COMPUTER APPLICATIONS: DBMS
DETAILED NOTES ON UNIT 1 & 2
---
UNIT I: INTRODUCTION TO DATABASES
1. Introduction to Databases
A Database is a structured collection of data stored in a computer system so that it can be easily accessed, managed,
and updated. Databases are essential for storing information in modern software systems.
Examples:
- Library Management System
- Hospital Records System
- Banking and ATM Transactions
- Social Media Platforms
2. Database Users
- Database Administrator (DBA): Manages the DBMS and ensures efficient operation.
- Database Designers: Define the database structure.
- Application Programmers: Develop application programs to interact with the database.
- End Users: Use database services to query or update data.
3. Characteristics of the Database Approach
- Data Abstraction: Hides internal complexity.
- Data Independence: Changes in data structure do not affect application programs.
- Multiple User Interfaces: Supports forms, queries, reports.
- Concurrent Access: Supports multiple users accessing data simultaneously.
- Backup and Recovery: Provides automatic backup and recovery functions.
4. Applications of DBMS
- Banking systems
- Airlines reservation systems
- Universities (student information systems)
- E-commerce
- Manufacturing and HR systems
5. Advantages of DBMS
- Reduces redundancy
- Maintains data consistency
- Provides data security
- Facilitates easy data retrieval
- Ensures data integrity
- Allows data sharing
6. Disadvantages of DBMS
- Cost of hardware and software
- Complexity
- Higher impact of failure
- Training requirements
7. Database System Concepts and Architecture
- Data: Raw facts stored in the database.
- Database: Organized collection of related data.
- DBMS: Software to manage databases.
8. Data Models
- Hierarchical Model: Tree-like structure.
- Network Model: Graph structure with records and sets.
- Relational Model: Tables with rows and columns.
- Object-oriented Model: Based on objects, classes, inheritance.
9. Schemas and Instances
- Schema: Logical design or blueprint.
- Instance: Actual content of the database at a particular time.
10. Three-Schema Architecture
1. Internal Level: Physical storage.
2. Conceptual Level: Logical structure of the database.
3. External Level: User views.
Diagram: Three-Schema Architecture
User Views (External Schema)
Conceptual Schema (Logical View)
Internal Schema (Physical Storage)
11. Data Independence
- Logical Data Independence: Ability to change conceptual schema without affecting external schema.
- Physical Data Independence: Ability to change internal schema without affecting conceptual schema.
12. Database System Architectures
- Centralized: Single database, single server.
- Client/Server: Clients request services, server provides them.
Diagram: Client/Server Architecture
Clients ---> Request ---> DBMS Server ---> Database
<--- Response <---
---
UNIT II: DATA MODELLING AND NORMALIZATION
1. Entity-Relationship (ER) Model
Used to model real-world objects.
Components:
- Entity: Object (e.g., Student, Course).
- Entity Set: Collection of similar entities.
- Attributes: Properties of entities.
- Key Attribute: Uniquely identifies an entity.
- Relationship: Association between entities.
2. Attribute Types
- Simple: Atomic (e.g., Age)
- Composite: Made of sub-parts (e.g., Name = First + Last)
- Derived: Computed (e.g., Age from DOB)
- Multivalued: Multiple values (e.g., Phone Numbers)
3. ER Diagrams Notation
- Rectangle: Entity
- Ellipse: Attribute
- Diamond: Relationship
- Lines: Connections
Example ER Diagram
[STUDENT]----(Enrolled)----[COURSE]
| |
[StudentID] [CourseID]
[Name] [CourseName]
4. Relational Data Model
- Relation: Table
- Tuple: Row
- Attribute: Column
- Schema: Description of a table
- Instance: Table content at a given time
5. Relational Model Constraints
- Domain Constraints: Data type restrictions
- Key Constraints: Unique identification
- Entity Integrity: Primary key can't be null
- Referential Integrity: Foreign key must reference a valid primary key
6. Functional Dependencies (FD)
Describes relationships between attributes.
Example:
If StudentID -> Name, then Name is functionally dependent on StudentID.
7. Normalization
Reduces redundancy and improves integrity.
First Normal Form (1NF)
- Atomic values only
- No repeating groups
Second Normal Form (2NF)
- Must be in 1NF
- No partial dependency (non-prime attributes fully dependent on whole primary key)
Third Normal Form (3NF)
- Must be in 2NF
- No transitive dependency (non-prime attributes must depend only on primary key)
Example of Normalization
Original Table:
StudentID | Name | Course | Instructor
---------------------------------------
101 | Raj | DBMS | Dr. Mehta
101 | Raj | OS | Dr. Kumar
Problems: Data redundancy, anomalies
Normalized Tables:
- Student(StudentID, Name)
- Course(Course, Instructor)
- Enrollment(StudentID, Course)
