Database Systems | Murat Can Ganiz

Course Overview

Database Systems is a core computer engineering course that teaches students how to analyze data requirements, design relational databases, implement schemas, write SQL queries, and build database-backed applications. The course follows the database design lifecycle from requirements and conceptual modeling to logical design, normalization, physical implementation, performance, application development, data warehousing, and database administration.

The course is project-oriented. Students gradually transform a realistic application domain into a working database system through requirements analysis, E-R/EER modeling, relational mapping, normalization, SQL implementation, stored procedures, triggers, indexes, views, and a small database application or demo. A recurring expectation is that design changes across project steps must be documented and justified.

Requirements analysis E-R and EER modeling Relational model Normalization SQL Stored procedures Triggers Views and indexes Database applications Data warehousing

Learning Outcomes

Analyze Data Requirements

Identify entities, attributes, relationships, constraints, business rules, and transaction needs from an application domain.

Model Data Conceptually

Create E-R and enhanced E-R models with correct cardinalities, participation constraints, weak entities, specialization/generalization, and relationship attributes.

Design Relational Schemas

Map conceptual models into relational tables, define keys and constraints, detect redundancy, and normalize schemas using functional dependencies.

Use SQL Effectively

Write DDL and DML statements, joins, nested queries, grouping, aggregation, views, stored procedures, triggers, and practical database scripts.

Implement and Tune Databases

Build physical databases, use indexes, reason about performance, and prepare scripts or backups that can be demonstrated and reproduced.

Connect Databases to Applications

Develop a simple database-backed application and explain how user actions map to database operations, stored procedures, triggers, and integrity constraints.

Textbooks, Slides, and Lab Resources

The course uses textbook slides and lab materials organized around database design, SQL, physical implementation, and database applications. Representative textbook-slide chapters include introduction, data modeling in organizations, enhanced E-R modeling, logical database design and the relational model, physical database design and performance, SQL, advanced SQL, database application development, data warehousing, and database administration.

Microsoft SQL Server sample databases AdventureWorks sample databases Azure SQL Database quickstart Data warehousing and OLAP review slides

Lab work has historically used Microsoft SQL Server / SQL Server Management Studio and sample databases such as AdventureWorks and AdventureWorksDW. The exact toolchain may change by semester.

Core Topic Map

Module	Representative Topics	Typical Skills
Introduction to Database Systems	Database systems, DBMS architecture, data independence, file processing versus DBMS, users and roles, database lifecycle.	Explain why database systems are used and distinguish conceptual, logical, and physical levels.
Data Requirements and Conceptual Modeling	Application domain analysis, business rules, entities, attributes, relationships, cardinality, participation, relationship attributes.	Convert informal requirements into clear data requirements and an E-R diagram.
Enhanced E-R Modeling	Weak entities, associative entities, unary/binary/ternary relationships, generalization, specialization, categories, inheritance constraints.	Choose the correct modeling construct and explain the semantics of each constraint.
Logical Database Design and Relational Model	Relations, tuples, attributes, domains, candidate keys, primary keys, foreign keys, referential integrity, relational mapping.	Map E-R/EER diagrams into relational schemas with correct keys and constraints.
Normalization	Functional dependencies, closure, minimal cover, insertion/update/deletion anomalies, 1NF, 2NF, 3NF, BCNF, decomposition.	Detect redundancy, normalize schemas, and evaluate lossless join and dependency preservation.
SQL Fundamentals	CREATE/ALTER/DROP, INSERT/UPDATE/DELETE, SELECT, WHERE, joins, grouping, HAVING, ORDER BY, subqueries, set operations.	Write correct queries and schema definitions for realistic relational databases.
Advanced SQL and Database Programming	Views, stored procedures, functions, triggers, cursors, transactions, constraints, generated reports, reusable SQL scripts.	Implement database-side logic and explain when logic belongs in SQL versus the application layer.
Physical Design and Performance	Indexes, clustered/nonclustered indexes, query performance, storage considerations, denormalization trade-offs, backup scripts.	Select indexes, justify physical design decisions, and prepare reproducible database deployments.
Database Application Development	Application/database connection, forms/pages, data access code, parameterized queries, CRUD operations, validation, reporting.	Build and demonstrate an application that uses the database correctly and safely.
Data Warehousing and Administration	OLTP versus OLAP, fact tables, dimension tables, star/snowflake schemas, ETL, roll-up/drill-down, security, backup/recovery, roles.	Explain analytical database design and basic database administration responsibilities.

Typical Project Structure

The course project is designed as an incremental database design and implementation exercise. Each step builds on the previous one, and changes should be explicitly documented rather than silently introduced.

Step 1 — Project Proposal and Data Requirements

Define the application domain, scope, stakeholders, core business rules, important transactions, and initial data requirements. A good proposal is neither too trivial nor too broad.

Step 2 — Conceptual Database Design

Create an E-R or EER diagram using a proper modeling tool. Identify entities, attributes, keys, relationships, cardinalities, participation constraints, and special modeling cases.

Step 3 — Logical Design and Normalization

Map the conceptual design into relational schemas, define primary/foreign keys, identify functional dependencies, normalize the design, and explain the reasons for schema changes.

Step 4 — Physical Database Implementation

Implement the schema in the chosen DBMS. Prepare SQL scripts for tables, constraints, sample data, views, queries, stored procedures, triggers, indexes, and backup/restore workflow.

Step 5 — Database Application and Demo

Develop a small database application or interface that demonstrates realistic operations. Explain which page/module supports which function and how application behavior maps to database operations.

Project Deliverables and Documentation

Design Documents

Project proposal and scope statement
Data requirements and business rules
E-R/EER diagram prepared with a diagramming tool
Relational schema and mapping explanation
Normalization report with functional dependencies
Change request document for any design modifications

Implementation Artifacts

SQL schema creation scripts
Insert scripts or sample data-loading scripts
Representative SELECT queries
Views, stored procedures, triggers, and indexes
Backup/restore or deployment instructions
Application source code and final demonstration report

Publicly visible course summaries do not include old student identifiers, grades, or demo scheduling logistics. The enduring lesson is the same: a database project should be reproducible, documented, and internally consistent across requirements, design, implementation, and application behavior.

Assessment Pattern

The exact grading plan may change by semester. The course has typically combined homework, quizzes, project steps, lab work, midterm/final exams, and project demos. Exams and quizzes commonly emphasize E-R/EER modeling, relational mapping, normalization, SQL, advanced SQL, stored procedures, triggers, and physical implementation concepts.

Modeling and Design

Students are expected to draw correct diagrams, explain constraints, map diagrams to tables, and justify schema design decisions.

SQL and Implementation

Students write SQL queries, DDL scripts, constraints, joins, aggregations, views, procedures, triggers, and database application code.

Project Demonstration

Students demonstrate working databases and applications, explain changes between submissions, and answer questions about design and implementation details.

Practical Checklist for Students

Do not start with tables immediately; first understand requirements and business rules.
Use a proper tool for E-R/EER diagrams; avoid hand-drawn diagrams for formal submissions.
State cardinality and participation constraints explicitly.
Give every table a clear primary key and every foreign key a clear referenced table.
Check whether many-to-many relationships require associative tables.
Normalize before implementation, and document any intentional denormalization.
Keep SQL scripts organized so the database can be recreated from scratch.
Use constraints to protect data integrity instead of relying only on application code.
Test queries on realistic data, not only on one or two rows.
Document changes between project steps with a change request or change log.

Study Questions

The following study questions are designed for review and public course understanding. They summarize recurring skills and concepts rather than reproducing a specific exam.

Database Fundamentals and Requirements Analysis

What problems do database management systems solve compared with file-based data storage?
Explain the difference between data, information, metadata, schema, instance, and catalog.
What is data independence? Distinguish logical data independence from physical data independence.
Who are the typical users of a database system: end users, application programmers, database designers, and database administrators?
What is a transaction? Why are transactions important in multi-user database systems?
Given a short application description, identify candidate entities, relationships, attributes, constraints, and important transactions.
Why should a project proposal define scope carefully before the database design begins?
What makes a requirement ambiguous? Rewrite an ambiguous requirement as a precise data requirement.

E-R and Enhanced E-R Modeling

Define entity type, entity instance, attribute, relationship type, and relationship instance.
Explain the difference between simple, composite, single-valued, multivalued, stored, and derived attributes.
What is a key attribute? What is the difference between a candidate key and a primary key?
Distinguish one-to-one, one-to-many, and many-to-many relationships with examples.
What are minimum and maximum cardinality constraints? How do they affect database design?
What is a weak entity? How is it identified and how is it mapped to relations?
What is an associative entity? When should a relationship be converted into an associative entity?
Give an example of a ternary relationship. Why can replacing it with binary relationships be incorrect?
Explain specialization and generalization in the EER model.
What are disjointness and completeness constraints in specialization hierarchies?
Draw an E-R diagram for a university course registration system and include students, courses, sections, instructors, classrooms, and enrollment records.
Draw an EER model for an organization where employees may be engineers, managers, or both. State the disjointness/completeness assumptions.

Relational Model and E-R/EER Mapping

Define relation, tuple, attribute, domain, relation schema, and relational database schema.
What is the difference between a superkey, candidate key, primary key, alternate key, and foreign key?
Explain entity integrity and referential integrity.
Map a regular entity type with simple attributes to a relational table.
How are composite attributes represented in the relational model?
How are multivalued attributes mapped to relational tables?
How is a one-to-many relationship mapped? Which side receives the foreign key?
How is a many-to-many relationship mapped? What becomes the primary key of the associative relation?
How should relationship attributes be represented after mapping?
Compare the main strategies for mapping specialization/generalization hierarchies.
Given an E-R diagram, produce a relational schema with primary keys and foreign keys explicitly marked.
Given a relational schema, infer what E-R modeling decisions may have produced it.

Normalization and Functional Dependencies

What are insertion, deletion, and update anomalies? Give an example of each.
Define functional dependency. What does X → Y mean?
What is attribute closure and how is it used to find candidate keys?
Given a set of functional dependencies, compute the closure of a given attribute set.
Given a relation and functional dependencies, identify all candidate keys.
Define 1NF, 2NF, 3NF, and BCNF.
What is a partial dependency? How does it relate to 2NF?
What is a transitive dependency? How does it relate to 3NF?
What is the difference between 3NF and BCNF?
Normalize a relation from 1NF to 3NF and explain every decomposition step.
What is a lossless-join decomposition? Why is it important?
What does dependency preservation mean? Why can there be a trade-off between BCNF and dependency preservation?
What is a minimal cover? Compute a minimal cover for a small set of functional dependencies.
Give an example where denormalization may be acceptable for performance and explain the risk.

SQL Fundamentals

Write SQL statements to create a table with primary key, foreign key, uniqueness, and check constraints.
Explain the difference between WHERE and HAVING.
Explain the difference between inner join, left outer join, right outer join, and full outer join.
Write a query that joins three tables and filters rows based on a condition from each table.
Write a query using GROUP BY and aggregation to find totals or averages per category.
Write a query using a nested subquery with IN, EXISTS, or a comparison operator.
What is the difference between COUNT(*), COUNT(attribute), and COUNT(DISTINCT attribute)?
How do null values affect comparison, grouping, and aggregate functions?
Write SQL statements to insert, update, and delete rows while preserving referential integrity.
What are SQL set operations such as UNION, INTERSECT, and EXCEPT used for?
Given a business question, translate it into a correct SQL query.
Given an SQL query, explain its result in plain language.

Advanced SQL, Views, Stored Procedures, Triggers, and Transactions

What is a view? How is it different from a base table?
Give two reasons to use views in a database application.
What is a stored procedure? When is it preferable to repeated application-side SQL strings?
Write a simple stored procedure that inserts a new record after validating an input condition.
What is a trigger? Distinguish BEFORE/AFTER and INSERT/UPDATE/DELETE trigger use cases.
Give an example of a trigger that enforces a business rule not easily represented by a simple constraint.
What are the risks of using too many triggers or hiding complex business logic in triggers?
What is a cursor? Why should cursors be used carefully?
Define transaction, commit, rollback, and savepoint.
Explain the ACID properties of transactions.
Give an example where a multi-step database operation must be executed inside a transaction.
How can parameterized queries reduce SQL injection risk?

Physical Design, Indexes, and Performance

What is physical database design and how does it differ from logical design?
What is an index? Why can an index improve search performance?
What is the difference between clustered and nonclustered indexes?
Which columns are good candidates for indexing? Which columns are poor candidates?
How can indexes slow down insert, update, or delete operations?
Explain why foreign-key columns are often indexed.
What is the trade-off between query performance and storage overhead?
What is a query execution plan and why is it useful?
How can denormalization improve read performance? What problems can it introduce?
What should be included in a database backup/restore or deployment script for a project demo?

Database Application Development

What are the common layers of a database-backed application?
Why should application code avoid hard-coded SQL scattered across many files?
How should a web or desktop application validate user input before sending data to the database?
What is CRUD? Give database operations corresponding to create, read, update, and delete.
How should an application handle database errors such as duplicate keys, missing foreign keys, and connection failures?
What is the difference between application-level validation and database-level constraints?
Why is sample data important in a database project demonstration?
What should a project report explain about the application part of a database project?
How can stored procedures and views simplify application code?
What security risks arise when database credentials or connection strings are handled carelessly?

Data Warehousing, OLAP, and Database Administration

Compare OLTP and OLAP systems in terms of workload, schema design, and query patterns.
What is a data warehouse? Why is it usually separated from operational databases?
Define fact table, dimension table, measure, star schema, and snowflake schema.
Design a simple star schema for sales analysis with product, customer, date, and store dimensions.
What are roll-up, drill-down, slice, dice, and pivot operations in OLAP?
What is ETL? What are typical data cleaning and transformation tasks in ETL?
What are the responsibilities of a database administrator?
Explain authentication, authorization, roles, privileges, and principle of least privilege.
What is backup and recovery planning? Why is it essential even for small systems?
How do cloud database services change deployment and administration tasks?

Project-Oriented Preparation Questions

Given a project topic, write a one-paragraph scope statement and list five core business rules.
For the same project, identify at least six entity types and their candidate key attributes.
Draw a conceptual model and state every relationship cardinality explicitly.
Map your conceptual model to relations and mark all primary and foreign keys.
Identify at least three functional dependencies and discuss whether your schema is in 3NF.
Write five representative SQL queries that demonstrate joins, aggregation, filtering, and nested queries.
Define one view, one stored procedure, one trigger, and one useful index for your project.
Prepare a short change request explaining why a relation, attribute, or constraint was added, removed, or modified.
Explain how your application pages or screens map to database tables and operations.
Design a demo script that recreates the database, loads sample data, runs important queries, and demonstrates application behavior.

Representative Tools

Tooling can vary by semester, but students should be comfortable with a relational DBMS, an SQL editor, a diagramming tool, and a way to package scripts and reports reproducibly.

SQL Server / SQL Server Management Studio
Azure SQL Database or another managed relational DBMS
E-R/EER diagramming tools such as Lucidchart, Visio, draw.io, or ER modeling tools
SQL scripts for DDL, DML, views, stored procedures, triggers, and indexes
Backup/restore or schema recreation scripts
Simple database-backed application framework of the semester

Public Page Note

This page is an evergreen public summary. Official semester deadlines, classroom links, grade weights, demo schedules, and submission rules are announced through the active course platform.