BARANI INSTITUTE OF MANAGEMENT
SCIENCES
Database Systems
Class: BS(CS) Semester: Fall-24
Course Code: CSC-103 Credit Hours: 4(3-3)
Course Objectives: This course aims at teaching the students to write programs that not
only are correct but also computation and space efficient and optimized
for the intended use through appropriate structuring/organization of the
related data. Students will learn the standard data structures such as
linked lists, stacks, queues, trees, graphs and hash tables and the
algorithms that manipulate them. Students will also be introduced to the
concept of Algorithm complexity analysis to make them realize the cost
of the operations they perform on their data structures. Various
algorithm design techniques such as greedy, divide and conquer; back
tracking etc. will also be discussed.
Course Description: Any software design problem ultimately boils down to a question of
appropriate organization of the associated data, so that it can be
accessed and manipulated easily, thus making data structure a
fundamental factor in the overall correctness and efficiency of an
application. It is imperative that the data for any application be
organized in such a way that it can be retrieved, modified, and grow
efficiently. (Data Structures and Algorithms) teaches necessary skills to
achieve the said target.
Course Learning 1. On completion of this course, students should be able to:
Outcomes: 2. (GA-2) Explain fundamental database concepts.
3. (GA-3) Design conceptual, logical, and physical database schemas
using different data models.
4. (GA-4) Identify functional dependencies and resolve database
anomalies by normalizing database tables.
5. (GA-5) Use Structured Query Language (SQL) for database
definition and manipulation in any DBMS.
1
6. (GA-4) Choose an appropriate data structure and associated
algorithms to match a given set of requirements, targeting
maximum computation and space efficiency.
7. (GA-3) Have a sound understanding of various sorting and
searching algorithms and their distinguishing properties.
Books &Reference
Materials: 1. Data Structures Using C and C++ by A. Tenenbaum
https://www.academia.edu/8205017/Data_Structures_Using_C_
And_C_Y._Langsam_M._Augenstein_And_A._M._Tenenbaum
2. Data Structures and Algorithms in C++ by Adam Drozdek
3. Data Structures and Algorithm Analysis in Java by Mark A. Weiss
4. Data Structures and Abstractions with Java by Frank M. Carrano
& Timothy M. Henry
5. Data Structures and Algorithm Analysis in C++ by Mark Allen
Weiss Java Software Structures: Designing and Using Data
Structures by John Lewis and Joseph Chase
Tentative Grading As per course requirements, students must achieve passing grades
Policy: independently in both theoretical and practical evaluations to successfully
complete the course.
● 18 Mid Term Exam
● 30 Final Exam (Theory)
● 06 Assignments
● 06 Quizzes
● 20 Final Exam (Practical)
Plagiarism Policy: Collaboration and group work is encouraged but each student is required
to submit his/her own contribution(s). Your writings must be your own
thoughts. Cheating and plagiarism will not be tolerated and will be
referred to the Dean for appropriate action(s).
Course Assessment: Exams, Assignments, Quizzes, Course will be assessed using a
combination of written examinations.
Quiz/Assignments Approximately, 4 assignments and 4 quizzes shall be given during the
Policy: semester. The quizzes will be unannounced and normally last for 5-10
minutes. The question framed is to test the concepts involved in the last
few lectures and/or reading assignments. It will be the instructor’s will to
choose the number of quizzes for evaluations purposes. In order to give
practice and a comprehensive understanding of subject, home
2
assignments will be given. The students are advised to do the assignment
themselves. Copying of assignment is highly discouraged and taken as
cheating case and will be forwarded for disciplinary action. The questions
in assignments are more challenging to give students the confidence and
extensive knowledge about the subject and enable them to prepare for the
exams.
Mid Term Exam: A midterm exam of minimum 1 hour and 15 minutes duration will be
held that will cover all material till the midterm.
Final Term Exam: A final term exam of minimum 2 hour and 30 minutes duration will be
held that will cover all material.
Attendance Policy: 75% attendance is mandatory; deficient students will not be allowed to
appear in final examination.
Consultation Hours Thursday (08:30 am- 11:30 am)
for Students: Saturday (08:30 am- 11:30 am)
Course Outline:
Basic database concepts, Database Architecture, DB Design Life Cycle, Schema Architecture, Conceptual,
Logical and physical database Modelling and design, Entity Relationship diagram (ERD), Enhanced ERD,
Relational data model, mapping ERD to relational model, Functional dependencies and Normalization,
Relational Algebra, Structured Query language (SQL), Transaction processing, concurrency control and
recovery techniques, Query optimization concepts.
Basic database concepts, Database approach vs. file based system, database architecture, three level
schema architecture, data independence, relational data model, attributes, schemas, tuples, domains,
relation instances, keys of relations, integrity constraints, relational algebra, selection, projection,
Cartesian product, types of joins, normalization, functional dependencies, normal forms, entity
relationship model, entity sets, attributes, relationship, entity-relationship diagrams, Structured Query
Language (SQL), Joins and subqueries in SQL, Grouping and aggregation in SQL, concurrency control,
database backup and recovery, indexes, NoSQL systems.
LECTURE BREAKDOWN
Week Lec # Topic
File Based System, Database Approach
I
Role in Data Base Environment
1
History of data base Management System, Advantages and Disadvantages of
II
DBMSs
2 I How to build and manage database?
Database Systems, File based system, DBMS
approach
What is File based approach?
3
Limitations of File based approach
DBMS approach
II Advantages of DBMS, Cost and Risks Factors.
Three Level ANSI-SPARC Architecture
I External level, Conceptual Level, Internal Level
Disadvantages of Three Level Architecture
Roles in database environment
3
Information System Architecture (ISA)
II Database Development life cycle (planning,
design and implementation)
Fact Finding techniques
Entity Relationship modeling
● Entity
● Attributes and its types
● Identifier
● Primary Key
I ● Candidate Key
4
Relationship
● Degree of Relationship
o Unary
o Binary
o Ternary
o Quaternary
II Scenario for ERD
5 I Cardinality of Relationship
● One-to-one relationship
● One-to-many relationship
● Many-to-many relationship
Minimum/Maximum Cardinality
4
● Optional one
● Optional Many
● Mandatory one
● Mandatory Many
II Case Study (One in class and two as assignment)
● Enhanced ERD
o Super Type
o Sub Type
I o Generalization
o Specialization
o Attribute Inheritance
o Relationship Inheritance
o Constraints
6
▪ Completeness Constraint
● Total Specialization
● Partial Specialization
II
▪ Disjoint Constraint
● Disjoint Rule
● Overlap Rule
7 I ENHANCED ERD CASE STUDY ASSIGNMENT
II
● Transformation of (E)ER Model into Relational Model
o Attributes
▪ Simple
▪ Composite
5
▪ Multivalued
o Entities
▪ Regular
▪ Weak
▪ Associative
o Relationship
▪ Unary
● One-to-one
● One-to-Many
● Many-to-Many
▪ Binary
● One-to-one
o Minimum/Maximum same on both
o Optional-Mandatory
I ● One-to-Many
8 ● Many-to-Many
▪ Ternary
▪ Quaternary
● Super Type
II
● Sub Type
CASE STUDY of Transformation
Mid Term Examination
9 I Data Normalization
o Characteristics of Suitable Relation
6
o Advantages of Suitable Relations
o Goal of Normalization
o Data Anomalies
▪ Insertion Anomaly
II
▪ Deletion Anomaly
▪ Updating Anomaly
o Definitions of Some Terms
▪ Functional Dependency
I ▪ Partial Dependency
▪ Transitive Dependency
10
o First Normal Form
o Second Normal Form
II o Third Normal Form
o BCNF
CASE STUDY
Physical Design
I ● Logical Vs Physical Database Design
● Overview of Physical Database Design Methodology
● Translating Logical Data Model for Target DBMS
11
● Design File Organizations and Indexes
II
● Design User Views
● Design Security Measures
12 I File Organization
● Factors
● Sequential File Organization
7
● Indexed File Organization
II ● Hashed File Organization
Transaction Management
● Transaction
● Transaction Support
Properties of Transaction
I
Concurrency Control
● Loss Update Problem
13
● Uncommitted Dependency Problem
● Inconsistent Analysis Problem
Serializability
II ● Conflict Serializability
● Precedence Graph
Concurrency Control Techniques
Locking
● Shared Lock
I ● Exclusive Lock
● Two-Phase Locking (2PL)
14
● Cascading Rollback
Deadlock
● Techniques for Handling Deadlock
II
● Timeout
● Deadlock Prevention
15
● Deadlock Detection and Recovery
I ● Wait-For-Graph (WFG)
● Recovery from deadlock detection
II Concurrency Control Techniques
Timestamping
● Read Timestamp
● Write Timestamp
8
● MultiVersion Timestamp Ordering
● Optimistic Techniques
● Read Phase
● Write Phase
● Validation Phase
Database Recovery
● Types of Failures
Transaction and Recovery
Recovery Facilities
I ● Backup Mechanism
● Logging Facility
16
● Checkpoint Facility
Recovery Manager
Recovery Techniques
● Deferred Update
II
● Immediate Update
● Shadow Paging
I Project Demos
17
II Project Demos
Final Term Examination
LABS BREAKDOWN
Labs Table of Contents
1 Creating and Managing Tables
● Create Table
● Create Table using Subquery
● Alter Table
● Drop Table
● Truncate Table
2 Including Constraints
Not Null
9
Unique
Primary Key
Foreign Key
Check
Adding constraint
Dropping constraints
Enabling and disabling constraints
3 Manipulating Data DML
Insert
Manipulating Data DML
Update
Manipulating Data DML
Delete
4 The Relational Model
● SQL
● Displaying Table Structure
● Selecting Specific Column
● Restricting and Sorting Data
● Limiting Rows Selected
● Operator Precedence
5 The Relational Model
● SQL Statements
● Defining a Column Alias
● Concatenation Operator
● Eliminating Duplicate Rows
● Defining Null Values
6 Arithmetic Operator
● Logical Operators
● Comparisons Operators
10
7 Special Operators
● IN
● IS NULL
● LIKE
● BETWEEN
● Using Any and ALL operator
8 Single-Row Functions
Character Functions
Number Functions
Date Functions
Conversion Function
9 Group Functions
● SUM
● AVG
● COUNT
● MAX
● MIN
10 Displaying Data from Multiple Tables
● Equi-Join
● Non-Equi Join
● Outer- Join
● Self-Join
11 ● Using GroupBy Clause
● Using Having Clause
● Using ORDERBY Clause
12 Subquery
11
● How to use subquery
● Syntax of Subquery
● Types Of Subquery
● Single Row
● Multiple Row
13 ● Describe a view
● Create a view
● Retrieve data through a view
● Alter the definition of a view
● Insert, update, and delete data through a view
● Drop a view
● Create complex view
● Using with check option clause
● Denying DML operations
14 Create
● Sequence
● Index
● Synonym
15 Study & Implementation of Database Backup & Recovery commands.
Study & Implementation of Rollback, Commit, Save point.
16 Controlling User Access
● System privileges
● Granting privileges
● Revoking privileges
● Triggers
COURSE PROJECT
Selecting an organization (in a group of three students) and performing the following major activities in
the course project.
Requirements Description
12
ER Modeling
EER Modeling
Transformation
Physical Implementation
13
