Database Systems

Design and Implementation with MySQL

January 30, 2019
Salahaddin University-Erbil
Software Engineering Dept.
BSc
2009-2012
5 mins read

General Information

  • University: Salahaddin University-Erbil
  • Department: Software Engineering Dept.
  • My Status: Assistant Lecturer
  • Level: BSc
  • Year: 2009-2012

Course Description

This course provides a comprehensive introduction to database management systems, focusing on relational databases and MySQL as the primary implementation platform. Students will learn fundamental database concepts, design principles, and practical implementation techniques essential for modern software development.

The curriculum covers database design methodologies, including Entity-Relationship modeling and normalization, as well as practical skills in SQL programming and database administration. Students will gain hands-on experience with MySQL, learning to create, manage, and query databases effectively.

Through projects and practical exercises, students will develop the skills necessary to design and implement database solutions for real-world applications, preparing them for careers in software development, data analysis, and database administration.

Prerequisites

  • Programming Fundamentals
  • Basic mathematics and logic
  • Understanding of data structures
  • Familiarity with computer systems
  • No prior database experience required

Course Objectives

Upon completion of this course, students will be able to:

  • Understand fundamental database concepts and relational database theory.
  • Gather and analyze database requirements effectively.
  • Design databases using Entity-Relationship modeling techniques.
  • Apply normalization principles to create efficient database schemas.
  • Implement databases using SQL and MySQL.
  • Perform data manipulation operations (insert, update, delete).
  • Write complex queries to retrieve and analyze data.
  • Understand transaction management and data integrity concepts.

Course Outline

Week 1: Introduction to Database Systems

  • Database fundamentals and concepts
  • Types of database systems
  • Database management system (DBMS) architecture
  • Advantages and disadvantages of database systems
  • Database applications and use cases
  • Lab: Setting up MySQL environment and basic operations

Week 2: Entity-Relationship Modeling

  • Entity-Relationship (ER) model fundamentals
  • Entities, attributes, and relationships
  • ER diagram notation and conventions
  • Cardinality and participation constraints
  • Lab: Creating ER diagrams using modeling tools

Week 3: The Relational Model

  • Relational model concepts and terminology
  • Tables, tuples, and attributes
  • Primary keys and foreign keys
  • Referential integrity and constraints
  • Lab: Converting ER diagrams to relational schemas

Week 4: Relational Algebra

  • Relational algebra operations
  • Selection, projection, and join operations
  • Set operations (union, intersection, difference)
  • Division and aggregation operations
  • Lab: Relational algebra query implementation

Week 5: SQL Fundamentals

  • SQL language overview and standards
  • Data Definition Language (DDL)
  • Creating tables and defining constraints
  • Data types and domain constraints
  • Lab: Creating and modifying database schemas

Week 6: SQL Querying and Data Manipulation

  • SELECT statement fundamentals
  • WHERE clause and filtering conditions
  • JOIN operations and table relationships
  • INSERT, UPDATE, DELETE operations
  • Lab: SQL query writing and data manipulation

Week 7: Midterm Exam and Review

  • Midterm Exam: Covers weeks 1-6 material
  • Review of database fundamentals and SQL
  • Query optimization practice
  • Lab: Exam review and SQL practice

Week 8: Advanced SQL Features

  • Aggregation functions and GROUP BY
  • Subqueries and nested queries
  • Views and virtual tables
  • Stored procedures and functions
  • Lab: Advanced SQL programming

Week 9: Database Normalization

  • Normalization theory and objectives
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Lab: Database normalization exercises

Week 10: Functional Dependencies and Schema Refinement

  • Functional dependency concepts
  • Armstrong’s axioms and inference rules
  • Closure of attribute sets
  • Schema refinement principles
  • Lab: Functional dependency analysis

Week 11: Transaction Management

  • Transaction concepts and ACID properties
  • Concurrency control basics
  • Locking mechanisms and deadlock prevention
  • Transaction isolation levels
  • Lab: Transaction management and concurrency

Week 12: Database Security and Administration

  • User management and access control
  • Database security and authentication
  • Backup and recovery strategies
  • Performance monitoring and tuning
  • Lab: Database administration tasks

Week 13: Advanced Database Topics

  • Indexing strategies and optimization
  • Query optimization techniques
  • Database design best practices
  • Emerging database technologies
  • Lab: Performance optimization and tuning

Week 14: Final Exam Preparation and Review

  • Comprehensive review of all course material
  • Practice problems and sample questions
  • Final Exam: Theoretical component
  • Lab: Final exam practice and preparation

Week 15: Final Project and Course Wrap-up

  • Final Exam: Practical database project
  • Course evaluation and feedback
  • Future learning paths and advanced topics
  • Lab: Final project presentation and evaluation

Textbooks

  • [Recommended] “Database Systems: The Complete Book” by Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom
  • [Optional] “MySQL Cookbook” by Paul DuBois

Assessment

  • Database Design Projects (30%)
  • SQL Programming Assignments (25%)
  • ER Modeling Exercises (20%)
  • Midterm Exam (15%)
  • Final Exam (10%)