Week 1: Introduction to Databases

• Objective: Understand basic database concepts and their importance in data management.
• Topics:
  • Data, Information, Metadata.
  • Definition and advantages of databases.
  • Database models: Hierarchical, Network, Relational.
• Activities:
  • Introduction to database environments using tools like MySQL Workbench.

Week 2: Entity-Relationship (ER) Modeling

• Objective: Learn database design and visualization through ER modeling.
• Topics:
  • ERD Elements: Entities, Attributes, Relationships.
  • Degree of Relationships (1:1, 1:M, M:M).
  • Attributes: Types, associations, and their role in database design.
• Activities:
  • Create an ER diagram for a sample business scenario.

Week 3: Database Normalization

• Objective: Understand and apply normalization techniques to avoid data redundancy and anomalies.
• Topics:
  • Anomalies and Functional Dependencies.
  • Normal Forms: 1NF, 2NF, 3NF.
• Activities:
  • Normalize a database schema based on a real-world dataset.

Week 4: SQL Basics – Data Definition Language (DDL)

• Objective: Learn to create and manage database structures using SQL.
• Topics:
  • DDL commands: CREATE, ALTER, DROP, RENAME, TRUNCATE.
  • Concept of Keys: Primary Key, Foreign Key, Composite Key.
• Activities:
  • Create and modify tables using MySQL or MS SQL Server.

Week 5: SQL Basics – Data Manipulation Language (DML)

• Objective: Manipulate data within tables using SQL.
• Topics:
  • DML commands: INSERT, UPDATE, DELETE.
  • Data retrieval basics: SELECT statement.
• Activities:
  • Perform CRUD operations on a sample database.

Week 6: Advanced SQL Queries

• Objective: Write complex queries for data retrieval and analysis.
• Topics:
  • WHERE, GROUP BY, ORDER BY, HAVING clauses.
  • Aggregate functions: MIN, MAX, AVG, SUM, COUNT.
• Activities:
  • Analyze and summarize data using advanced SQL queries.

Week 7: SQL Constraints and Functions

• Objective: Implement constraints and functions for data integrity and transformation.
• Topics:
  • Constraints: NOT NULL, UNIQUE, CHECK, FOREIGN KEY.
  • SQL Functions:
    • Character functions: UPPER, LOWER, CONCAT.
    • Numeric functions: ROUND, MOD, COALESCE.
    • Date and Conversion functions: TO_DATE, TO_CHAR.
• Activities:
  • Use constraints and functions to enforce data validation.

Week 8: Views and Data Dictionary

• Objective: Understand views and their role in a multi-user environment.
• Topics:
  • Creating and managing views: Simple and complex views.
  • Importance of views for data abstraction.
• Activities:
  • Create views to simplify complex queries.

Week 9: User Privileges and Roles

• Objective: Learn to manage database access in a multi-user environment.
• Topics:
  • User creation and role management: CREATE USER, CREATE ROLE.
  • Assigning and revoking privileges: GRANT, REVOKE.
• Activities:
  • Set up a multi-user database environment and assign roles.

Week 10: Database Security, Optimization, and Final Project

• Objective: Implement database security and optimize database performance.
• Topics:
  • Transactions and concurrency control.
  • Backup and recovery strategies.
  • Basic indexing and optimization techniques.
• Final Project:
  • Design and implement a fully functional database for a real-world scenario, including:
    • ERD creation.
    • Normalization.
    • SQL scripts for schema creation, data insertion, and advanced queries.