NATIONAL UNIVERSITY

of Computer & Emerging Sciences, Lahore

FAST School of Computing

CS2005 – Database Systems
Spring 2024
Instructor Name Sana Fatima TA Name Section (BDS-4A): TBD
Email address: [email protected] Email address:
Office Location/Number: 59 TA Name Section (BDS-4B): TBD
Office Hours: Mon, Wed 10:00 - 11:30 PM Email address:

Course Information
Program: BS Credit Hours: 3 Type: Core
Pre-requisites (if any): CS2001 - Data Structures
Course Website (if any):
Class Meeting Time

Course Description
This course is an introduction to relational databases management Systems. The course will cover fundamental concepts of
databases with an emphasis on modeling, designing and implementation of database systems. The theory will be
augmented with hands-on exercises on database system. A project will be conducted in the database system lab that runs in
parallel with the course. In project, the students will develop a data-centric application with complete set of business
transactions and appropriate user interface using a popular programming language and a popular database management
system.

Learning Outcomes
At the end of the course, you will be able to:
 Describe how databases store and retrieve information using the basic concepts and terminology of relational
databases.
 Create an ER diagram (semantic model) about an enterprise (e.g., retail industry, airport, school, library) that correctly
describes the entities, attributes, and relationships among the entities, for some of its major business functions.
 Create a logical data model from an ER diagram to design a set of DB relations.
 Normalize a set of attributes to eliminate update anomalies or redundancies from a set of relations.
 Implement a logical data model using a DBMS.
 Write queries using formal query languages such as relational algebra.
 Write SQL statements to query a set of tables in a DBMS involving multiple conditions, ordering, aggregate functions,
grouping, group selection, set operations, joins, and nested queries.
 Write SQL statements to insert, delete and update a set of tables in a DBMS.
 Write SQL statements to create, alter, drop, rename a set of tables in a DBMS.
 Write SQL statements to add and drop constraints on a set of tables in a DBMS.
 Comprehend the ACID properties of Transactions and recoverability schedules.

Textbook: Ramez Elmasri, Fundamentals of Database Systems (7th Edition)

Reference Books
 Raghu Ramakrishnan, Database Management Systems (3rd Edition)
 C. J. Date, An Introduction to Database Systems (8th Edition)

Grading Scheme
Midterms (30%), Quizzes/Class Participation (10%), Assignments (10%), Final (50%)

Grading
 Minimum eligibility to pass this course is to get 50% marks.
 Academic integrity is expected of all the students. Plagiarism or cheating in any assessment will result in at least an F
grade in the course, and possibly more severe penalties.

Project
Students will design, implement, demonstrate, and document a database system. The project is to be done in groups of 3/4
students. Pick your partner as soon as possible. The groups are self-policing (e.g., each group is responsible for its own
division of labor, scheduling, etc.). A separate handout will be provided describing the project requirements in the 2nd week
of the classes.

Tentative Course Outline and Lecture Plan

Readings No of
Week Topics to be covered Topics Detail Asst.
(Textbook) Lec.

1 Introduction to • Databases and Database Users

Databases • Characteristics of the Database Approach
• Advantages of Using the DBMS Approach Ch 1,2 2
• Data Models, Schemas, Instances
• Architecture and Components of a DBMS

2-3 Relational Data RA SQL

Model
• Relational Model Concepts • Data Definition Statements (DDL)
o Domain, Attributes, Tuples, o Create, Alter, Drop, Rename
Relations • Specifying Constraints
Ch 5, 6 4 A1
o Characteristics of Relations o Attribute, Key, Referential Integrity,
• Relational Model Constraints Tuple-Based Using CHECK
o Domain, Keys, Integrity • Data Modification Statements (DML)
• Update Operations and Dealing with o Insert, Update, Delete
Constraint Violation

4-6 Formal Query RA SQL

Language: Relational
Algebra and The o Unary Relational Operations o Retrieval Queries
Database Language: o SELECT, PROJECT, RENAME o Basic Queries: SELECT-FROM-WHERE
SQL o Ordering, Arithmetic Operations,
Substring Comparison

o Binary Operations o Set Operations

o Union, Intersection, Difference,
Division A2,
Ch 6, 7, 8 6
A3
o Cartesian Product, JOIN o Joining, Full, outer, inner, Cross
o Outer Join, Outer Union, Full

o Aggregate Functions and Grouping o Aggregate Functions and Grouping

Query Tree o Nested Queries

o Correlated Nested Queries

-- o Views (Virtual Tables), Stores

Procedures, Assertions and Triggers

7-9 Database Design • Design Anomalies

Theory and • Informal Design Guidelines for Relational Databases
Normalization • Functional Dependencies (FDs)
o Convert Business statements into Dependencies
o Armstrong's Inference Rules for FDs
o Algorithm for computing Attribute Closure
Ch 14, 15 6 A4
o Minimal Cover of FDs
o Equivalence of Sets of FDs
• Normalization for Relational databases
o Normalization and De-Normalization
o Normal Forms: 1NF, 2Nf, 3NF, BCNF, 4NF, 5NF
• Overview of Relational Database Design Algorithms
10-12 Data Modeling Using • Entity Types, Entity Sets, Attributes, Keys
Entity-Relationship • Relationship Types, Relationship Sets, Roles
(ER) Model • Constraints on Relationship Types
• Relationship Types of Degree Higher than Two
• Enhanced Entity-Relationship (EER) Model Concepts
Ch 3, 4 5 A5
• Subclasses, Superclasses, Inheritance
• Specialization and Generalization
• Constraints and Characteristics of Specialization and Generalization
• Shared and UNION Type subclasses

12-13 Relational Database • Mapping ER Model Constructs to Relations

Design by • Mapping EER Model Constructs to Relations
Ch 9 2
ER- and EER-to-
Relational Mapping

13-14 Transaction • Issues in Transaction Processing

Processing Concepts • Why Concurrency Control is Needed
• Why Recovery is Needed
• Transaction States and Operations, System Log, Commit Point of a Transaction
• ACID Properties of Transactions Ch 20 3
• Characterizing Schedules based on Recoverability
• Characterizing Schedules based on Serializability
• Transactions Isolation Levels and Possible Violations
• Basic Two-Phase Locking Technique for Concurrency Control