DBSF12courseoutline

=CSE341: Database Systems=

=Program:= BS(CS)

Semester:
Fall 2012

Instructor:
Imran Khan

**Course Lead:**
Imran Khan

**Credit Hours:**
4 (3 credit hours for theory + 1 credit hour for lab) Tuesday & Friday (8:30 am – 9:45 am & 10:00 am – 11:15 am)

Prerequisite(s):
CSE246

Course Description
This course covers the theoretical and applied aspects of database systems. It explains concept, modeling and implementation details of relational database management system. It includes extensive coverage of the relational model, relational algebra, and SQL. The course includes relational design principles based on dependencies and normal forms. Many additional key database topics from the design and application-building perspective are also covered: indexes, views, transactions management, authorization, integrity constraints, triggers. It also covers XML data including DTDs and XML Schema for validation, and the query and transformation languages XPath, XQuery, and XSLT.

Course Objective
At the end of this course student will be able to:
 * 1) understand the terminology of the different database models.
 * 2) model conceptual, logical and physical database model.
 * 3) create, manipulate, delete and query database object as well as make it secure.
 * 4) make connection between mathematical relations and relations in the relational model.
 * 5) Identify different types of keys in database and their use.
 * 6) Identify various types of update anomalies (insertion, modification, deletion)
 * 7) do the normalization of database model
 * 8) understand the concept of Transaction management and concurrency control in database.
 * 9) Work on semi-structure database such as XML.

Syllabus
Data/ Database/ DBMS, History of Database Systems, File-Based Systems **Database Systems Concepts & Architecture** Data Models, Three-schema Architecture (ANSI-SPARC), Data Independence,DBMS classification & Architecture **Data Modeling & ERD** Type of Data Models, Relational Model Concepts, Entities, Attributes, Keys, Relationship, Recursive relationship, Degree & Cardinality, Integrity Constraints, Participation Constraints **Mapping from conceptual to logical model** Composite & Multi-valued attributes, 1-1, 1-m, m-m relationships, Identifier Dependency, Existence Dependency, Weak Entities, Artificial Keys, Specialization and Generalization **Relational Algebra** Concept of Set-based Operations, Relational Operations, Type of Joins (Theta join, Equi join, Natural join, Outer join), Universal & Existential Quantifiers **Normalization**Functional Dependencies, Normalization (1NF – 5NF), Denormalization **SQL** Select/Insert/Update/Delete, Wildcard, IN/Not IN, EXISTS/NOT EXISTS, Aggregates, Group By/Having, Sub-queries, Correlated Queries, Derived tables, Stored Procedures, Views, Triggers, DDL **Database Connectivity** Front-end to back-end database connectivity and query execution **Database Indexing** Indexing, Clustered and Non-clustered index, Dense Vs. sparse indexing, Fill Factor **Transaction Handling** Transaction, ACID Properties, Transaction Schedules, Serializability, Precedence Graph **Concurrency Control** Concurrency, Locks & Type of Locks, 2-Phase locking, Isolation levels **Database Security** Authentication vs Authorization, Role based Security, SQL Injection, **Advance Topics** Disk Storage, Basic File Structures, and Hashing **Performance Tuning** Horizontal/Vertical Partitioning, Indexing XML Data - Well-formed XML, DTDs, IDs & IDREFs, XML Schema Querying XML – Xpath, XQuery, XSLT Introduction to Data warehousing, OLTP vs OLAP, Introduction to Distributed Databases Thomas Connolly, Carolyn Begg, Anne Strachan 4th Edition Ramakrishnan, Johannes Gehrke 3rd edition // 2. **Database Systems : The Complete Book** // Jeffery D. Ullman, Jennifer Widom 2nd edition 3. **Database Systems: Concepts, Designs and Application** Shio Kumar Singh 2nd edition
 * Introduction & History of Database Systems **
 * Semstructured Data **
 * Advanced Database Systems Concepts **
 * Textbook **
 * // Database Systems: a //****// practical approach to Design, //****// Implementation and Management //**
 * Reference books **
 * // 1. Database Management Systems //**


 * Marks Distribution (Tentative) **
 * Midterm || 30  ||
 * Final Examination || 30  ||
 * Quiz (3 to 5) || 10  ||
 * Assignment (3 to 5) || 10  ||
 * Project || 20  ||
 * Total || 100  ||
 * Total || 100  ||

Labs
Labs will cover database modeling in ERWin and Database development, Querying, and transactions using //Oracle 11g//.

Project
Students are required to do semester project in this course. Project will typically be an OLTP system implemented for any business domain.