Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • Data Models (Conceptual, Logical, Physical)
  • Database Design, Entity-Relationship Diagram (ERD)
  • Codd’s 12 Rules for RDBMS
  • Introduction to SQL
  • Categories of SQL Commands: DDL, DML, DCL, DTL/TCL
  • DDL (CREATE/ALTER/DROP/TRUNCATE)
• Lab:
  • Performing basic CREATE, ALTER, DROP Commands

Lecture Notes (In-Depth)

1. Data Models

A data model defines how data is organized, stored, and accessed in a database. It provides a framework for representing data and its relationships at different levels of abstraction.

• Conceptual Data Model:
  • High-level, abstract representation of data requirements.
  • Focuses on entities (objects like Student, Course) and their relationships (e.g., Student enrolls in Course).
  • Independent of any DBMS or physical storage details.
  • Tools: Entity-Relationship Diagrams (ERDs).
  • Example: A university system with entities Student, Course, and Department, and relationships like Student enrolls in Course.
• Logical Data Model:
  • Defines the structure of the database in terms of tables, columns, and relationships.
  • Maps the conceptual model to a specific DBMS (e.g., relational tables in MySQL).
  • Includes primary keys, foreign keys, and data types but is independent of physical storage.
  • Example: A Student table with columns student_id (Primary Key), name, and dept_id (Foreign Key to Department table).
• Physical Data Model:
  • Specifies how data is physically stored on disk, including storage engines, indexes, and file structures.
  • DBMS-specific (e.g., MySQL’s InnoDB vs. MyISAM storage engine).
  • Considers performance optimization (e.g., indexing, partitioning).
  • Example: A Student table stored using InnoDB with a B-tree index on student_id.

2. Database Design, Entity-Relationship Diagram (ERD)

Database Design is the process of creating a structured, efficient, and scalable database schema based on user requirements. It involves multiple phases:

• Phases of Database Design:
  • Requirement Analysis: Gather and analyze data needs (e.g., what entities and relationships are required for a school management system?).
  • Conceptual Design: Create an ERD to represent entities, attributes, and relationships.
  • Logical Design: Convert ERD into tables with keys and constraints.
  • Physical Design: Optimize storage and performance (e.g., choosing storage engines, indexing).
• Entity-Relationship Diagram (ERD):
  • A visual representation of entities, their attributes, and relationships.
  • Components:
    • Entities: Objects represented as rectangles (e.g., Student, Course).
    • Attributes: Properties of entities, shown as ovals (e.g., student_id, name for Student).
    • Relationships: Connections between entities, shown as diamonds or lines (e.g., enrolls between Student and Course).
  • Notations:
    • Crow’s Foot Notation: Common in tools like MySQL Workbench, uses symbols to show cardinality (e.g., one-to-many, many-to-many).
    • Chen Notation: Uses diamonds for relationships and ovals for attributes.
  • Cardinality:
    • One-to-One (1:1): One student has one ID card.
    • One-to-Many (1:N): One department has many students.
    • Many-to-Many (M:N): Students enroll in multiple courses, and courses have multiple students (requires a junction table).
  • Example: An ERD for a school database:
    • Entities: Student (student_id, name, age), Course (course_id, course_name), Enrollment (student_id, course_id, enrollment_date).
    • Relationships: Student enrolls in Course via Enrollment (many-to-many).

3. Codd’s 12 Rules for RDBMS

E.F. Codd, the founder of the relational model, defined 12 rules (actually 13, numbered 0 to 12) to ensure a DBMS qualifies as a true RDBMS. These rules establish standards for data integrity, accessibility, and independence.

• Rule 0 (Foundationontol): The Information (Foundation) Rule: All data must be stored in tables and accessible via a single query language (e.g., SQL).
• Rule 1: The Information Rule: All data is represented in tables (rows and columns).
• Rule 2: Guaranteed Access Rule: Every data element is accessible via a combination of table name, primary key, and column name.