Database (SQL Server & Oracle)

Course Objective:

  • To introduce students to the concepts and applications of database systems.
  • To understand how databases are designed, implemented, and managed.
  • To gain proficiency in using SQL and other database technologies.
  • To develop an understanding of modern database management principles such as data integrity, concurrency, normalization, and transactions.

Prerequisites:

  • Basic understanding of computer programming (typically in languages like Python, Java, or C).
  • Familiarity with fundamental data structures and algorithms.

Course Outline

Week 1: Introduction to Databases

  • What is a Database?
    • Definition, components, and use cases
  • Types of Databases: Relational, NoSQL, Distributed, etc.
  • DBMS vs. File Systems
  • Advantages of using a DBMS
  • Applications of Databases in various industries

Week 2: Database Models

  • Relational Model: Tables, rows, columns, and keys
  • Entity-Relationship Model (ER Model):
    • Entities, attributes, relationships, and cardinality
    • ER Diagrams (ERD)
  • Object-Oriented and NoSQL Models
    • Overview of other database models (hierarchical, network, graph, etc.)

Week 3: Relational Algebra and SQL

  • Relational Algebra: Basic operations like select, project, join, union, difference
  • Introduction to SQL (Structured Query Language)
    • Data Definition Language (DDL): CREATE, ALTER, DROP
    • Data Manipulation Language (DML): INSERT, UPDATE, DELETE, SELECT
  • Basic queries, filtering data, sorting, and aggregation

Week 4: Database Design Principles

  • Normalization: Purpose, 1NF, 2NF, 3NF, Boyce-Codd Normal Form (BCNF)
  • Functional Dependencies and Determining Normal Forms
  • Denormalization: When and why it’s used
  • Primary, Foreign, and Composite Keys

Week 5: SQL Advanced Topics

  • Joins: Inner Join, Outer Join (Left, Right, Full)
  • Subqueries: In SELECT, INSERT, UPDATE, DELETE
  • Indexes: Purpose and types (single-column, composite, unique)
  • Views and Triggers: Creating and managing views, trigger mechanisms
  • Stored Procedures and Functions

Week 6: Transaction Management and Concurrency Control

  • Transactions: Properties (ACID: Atomicity, Consistency, Isolation, Durability)
  • Transaction States: Active, Partially Committed, Committed, Aborted
  • Concurrency Control: Locks, Deadlocks, and Isolation Levels
  • Two-Phase Locking and its role in preventing anomalies

Week 7: Database Security and Backup

  • Database Security: Authentication, Authorization, and Encryption
  • SQL Injection and Preventive Measures
  • Backup and Recovery: Types of backups, strategies for data recovery

Week 8: Indexing and Query Optimization

  • Indexes: Structure and Types (B-Tree, Hashing, etc.)
  • How indexing improves query performance
  • Query Optimization: Execution plans, cost-based optimization
  • Techniques for optimizing SQL queries

Week 9: NoSQL Databases

  • Overview of NoSQL Databases: Types (Document, Key-Value, Column-Family, Graph)
  • MongoDB: Basics, CRUD operations, and querying
  • Cassandra, Redis, Neo4j: Introduction to other NoSQL databases
  • Comparing NoSQL and SQL: Use cases, strengths, and limitations

Week 10: Distributed Databases and Big Data

  • Distributed Database Concepts: Fragmentation, Replication, and Consistency
  • CAP Theorem: Consistency, Availability, Partition Tolerance
  • Big Data: Overview, tools like Hadoop and Spark
  • Distributed Databases and their management systems

Week 11: Data Warehousing and Business Intelligence

  • Data Warehousing: ETL (Extract, Transform, Load), OLAP (Online Analytical Processing)
  • Data Modeling: Star Schema, Snowflake Schema
  • Business Intelligence (BI): Use of databases in decision support systems (DSS)

Week 12: Cloud Databases and Future Trends

  • Cloud Databases: Concepts, deployment models (SaaS, PaaS, IaaS)
  • Major cloud database providers: Amazon RDS, Google Cloud SQL, Microsoft Azure SQL
  • Blockchain and Databases: Use of decentralized databases
  • Emerging Trends: AI in databases, Autonomous Databases

Week 13: Database Project and Case Studies

  • Project Work: Students work on designing and implementing a small database system
  • Case Studies: Real-world examples of database management and optimization

Week 14: Final Review and Exam

Final Exam: Comprehensive assessment of all topics

Final Project Presentation

Review of all key concepts and tools covered in the course