Database Systems

This course introduces the fundamental concepts and practices of database systems, with emphasis on database design, relational models, SQL, relational algebra, normalization, indexing, hashing, transaction management, and concurrency control. Students will develop the ability to design, query, optimize, and manage relational databases efficiently.

Instructor: Md. Shakil Ahmed

Term: Fall

Database Systems

CSE 207 · Core Course · 3.0 Credit Hours

This course introduces the fundamental concepts of database systems and relational database management. Students develop practical and theoretical knowledge of database design, SQL, relational algebra, normalization, indexing, hashing, transaction management, and concurrency control.
Database Systems Relational Model SQL Relational Algebra ER Modeling Normalization Indexing Hashing Transactions Concurrency Control

Course Overview

This course introduces the fundamental concepts and practices of database systems, with emphasis on designing, implementing, maintaining, and retrieving information efficiently from database management systems. Students will develop practical and theoretical knowledge of relational databases, SQL, database design, normalization, indexing, hashing, and transaction management.

The course emphasizes database modeling, query formulation, database optimization, integrity constraints, functional dependencies, and transaction processing. Students will learn to analyze database requirements and develop efficient relational database solutions through theoretical study, problem solving, and real-world case studies.

Course Learning Outcomes

CLO1 — Database Fundamentals

Understand the fundamental concepts, terminology, architecture, components, and applications of database management systems.

CLO2 — SQL and Relational Algebra

Apply SQL and relational algebra to define, query, manipulate, and retrieve information from relational databases.

CLO3 — Database Design

Design relational databases using entity-relationship modeling, functional dependencies, decomposition, and normalization techniques.

CLO4 — Database Performance and Management

Apply indexing, hashing, transaction management, and concurrency control techniques to improve database access and ensure reliable database operations.

Prerequisites

  • No formal prerequisite.
  • Basic programming knowledge.
  • Basic understanding of data structures and algorithms.
  • Basic mathematical and problem-solving skills.

Textbooks

  • Primary: Database System Concepts by Abraham Silberschatz, Henry F. Korth, and S. Sudarshan.
  • Primary: Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke.

Reference Books

  • Fundamentals of Database Systems by Ramez Elmasri and Shamkant B. Navathe.
  • Database Systems: The Complete Book by Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom.
  • Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke.

Tools and Platforms

Database Management Systems

MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database for database implementation, querying, administration, and management.

Development and Modeling Tools

MySQL Workbench, phpMyAdmin, Visual Studio Code, Git, GitHub, and ER diagram and database modeling tools.

Online Resources

Grading

Assessment Weight
Class Participation and Activity 5%
Class Tests 15%
Assignment / Report / Presentation / Viva 10%
Midterm Examination 30%
Final Examination 40%
Total 100%

Course Schedule

Week Topic Description
1 Introduction to Database Systems Overview of database systems, database applications, limitations of traditional file processing systems, levels of data abstraction, database instances, database languages, storage management, query processing, transaction management, database users, and database administrators.
2 Relational Model Introduction to the relational model, relations, attributes, relation schemas and instances, keys, schema diagrams, and relational query languages.
3 Introduction to SQL Introduction to Structured Query Language, SQL data definition, basic data types, schema definition, integrity constraints, basic SQL queries, queries on single and multiple relations, Cartesian products, natural joins, filtering, ordering, set operations, and aggregate functions.
4 Database Modification and Nested Queries Practical application of INSERT, UPDATE, and DELETE operations, nested subqueries, and complex SQL query construction.
5 Intermediate SQL Advanced SQL operations including inner joins, outer joins, views, materialized views, and view modification and update operations.
6 Integrity Constraints and Relational Algebra Application of NOT NULL, UNIQUE, PRIMARY KEY, CHECK, and referential integrity constraints. Introduction to authorization, user-defined domains, and relational algebra operations.
7 Relational Algebra Detailed study and problem solving using select, project, set operations, Cartesian product, rename, natural join, assignment, outer join, division, aggregate functions, and relational algebra-based queries.
8 Entity-Relationship Model Introduction to entity sets, relationship sets, cardinality constraints, participation constraints, ER diagrams, degrees of relationships, and attribute types.
9 Advanced ER Modeling and Database Design Mapping cardinalities, entity roles, weak entity sets, reduction of ER models to relational models, specialization, generalization, aggregation, and real-world database design.
10 Relational Database Design Study of atomic attributes, decomposition, functional dependencies, attribute closure, superkeys, candidate keys, and primary keys. Students will analyze functional dependencies for relational database design.
11 Functional Dependencies and Canonical Cover Determination of attribute closure, identification of keys, analysis of functional dependencies, and construction of canonical covers.
12 Normalization and Database Design Practical application of First Normal Form, Second Normal Form, Third Normal Form, and Boyce-Codd Normal Form. Students will determine normal forms, normalize relational schemas, and study denormalization.
13 Indexing and Hashing Introduction to search keys, index files, ordered indices, dense and sparse indices, hash indices, and evaluation of indexing strategies.
14 B+ Trees and Hashing Introduction to multilevel indexing, B+ tree construction, insertion, updating, deletion, hash functions, buckets, static hashing, bucket overflow, dynamic hashing, and extendible hashing.
15 Transactions and Concurrency Control Study of transaction states, ACID properties, transaction atomicity and durability, storage structures, concurrency control, and transaction management techniques.
16 Database Case Study and Problem Solving Application of database design, SQL, normalization, indexing, and transaction concepts to a real-world database case study through problem solving and discussion.