Questions
Syllabus
Qns. By Syllabus

Semester

Subject

Bachelors Level/Second Year/Third Semester/Science bit/third semester/database management system/syllabus

Bachelors In Information Technology

Institute of Science and Technology, TU

Nature of the course: (Theory+Lab)

F.M: 60+20+20 P.M: 24+8+8

Credit Hrs: 3Hrs

Database Management System [BIT202]
Course Objective
i.
The main objective of this course is to introduce the basic concepts of database, data modeling techniques using entity relationship diagram, relational algebra and calculus, basic and advanced features SQL, normalization, transaction processing, concurrency control, and recovery techniques.
Course Description

The course covers the basic concepts of databases, database system concepts and architecture, data modeling using ER diagram, relational model, SQL, relational algebra and calculus, normalization, transaction processing, concurrency control, and database recovery.

S1:Database Concepts and Architecture[4]
1
Database (Introduction, Examples)
2
Database Management System (Introduction, Advantages, Examples)
3
Database Users(Actors on the scene: Database Administrators, Database Designers, End Users(Causal, Naïve, Sophisticated, Standlone), System Analyst and Standalone Programmers, Workers behind the scene: DBMS System Designers and Implementers, Tool Developers, Operators and maintenance personnel) and Benefits of Databases
4
Data Models (Hierarchical, Network, ER, Relational, Object): , Schemas, and Instances; Three-Schema Architecture (Internal, Conceptual and External View) and Data Independence (Logical and Physical)
5
Database Languages (DDL, DML, SDL, VDL) and Interfaces (Menu Based, Mobile Apps, Form Based, GUI, Natural Language, Keyword Based, Search Input Output, Interfaces for Parametric users and DBA);
6
The Database System Environment(DBMS Component Modules, Database System Utilities, Tools, Application Environments, and Communications Facilities)
7
Centralized and Client/Server Architectures for DBMSs (One-tier, Two-tier, Three-tier, N-tier)
8
Classification of Database Management Systems (Single and Mutli user, Centralized and Distributed, Homogeneous and Heterogeneous, General Purpose and Special Purpose)
S2:Data Modeling Using the Entity-Relational Model[5]
1
Using High-Level Conceptual Data Models for Database Design (Requirement Analysis, Conceptual Design, Logical Design, Physical Design)
2
Entity Types, Entity Sets, Attributes (Composite Vs. Simple, Single Valued Vs. Multivalued, Stored Vs. Derived) and Keys
3
Relationship Types, Relationship Sets, Relationship Instances, Relationship Degree, Roles, and Structural Constraints(Cardinality Ratio, Participation Constraint)
4
Weak Entity Types (Weak Entity and Partial Keys)
5
ER Diagrams, Naming Conventions(Attribute, Entity, Relationship), and Design Issues
6
Relationship Types of Degree Higher Than Two
7
ER-to-Relational Mapping
8
Enhanced Entity–Relationship (EER): Subclasses, Superclasses, and Inheritance; Specialization and Generalization; Constraints (Overlapping, Disjoint) and Characteristics of Specialization and Generalization
S3:The Relational Data Model and Relational Database Constraints[5]
1
Relational Model Concepts (Domains, Attributes, Tuples, and Relations; Characteristics of Relation)
2
Relational Model Constraints (Domain Constraints, Key Constraints, Constraints on Null Values) and Relational Database Schemas (Relational Database state)
3
(Entity Integrity, Referential Integrity, and Foreign Keys)
4
Update Operations, Transactions, and Dealing with Constraint Violations (Inset, Delete Update Operations) (Restrict/Cascade/set null/ set default)
5
Basic Relational Algebra Operations (Unary: Select, Project, Rename; Binary: Binary: Set Theory(Union, Intersection, Set Difference), Cartesian Product, Join (Natural/Theta), Outer Join (Left/Right/Full)
S4:SQL[10]
1
Data Definition and Data Types (Attribute Data Types and Domains Create Database, Create Table, Drop Table, Drop Constraint, Drop Database)
2
Specifying Constraints (Attribute Constraints, Attribute Defaults, Key and Referential Integrity Constraints, Naming Constraints, Constraint using CHECK)
3
Basic Retrieval Queries (SELECT-FROM-WHERE Structure, Ambiguous Attribute Names, Aliasing, Renaming, Tuple Variables; Unspecified WHERE clause, Use of * in Select, Substring Pattern Matching using LIKE, Arithmetic Operators, Order by Clause)
4
Complex Retrieval Queries; (Where Clause using IS NULL, Logical Connectives, Nested Query, Correlated Nested Query, Using BETWEEN, IN and EXISTS Clauses in Where, Renaming Attributes, JOIN, Natural JOIN, OUTER JOIN (Left/Right), Aggregate Functions, GROUP BY and HAVING Clause)
5
INSERT, DELETE, and UPDATE Statements
6
Views (CREATE, DROP)
S5:Relational Database Design[7]
1
Relational Database Design Using ER-to-Relational Mapping (Mapping of Regular Entity, Weak Entity, Relationship Types, Multivalued Attributes, N-ary Relationship)
2
Informal Design Guidelines for Relational Schemas (Semantics of Attributes in Relations, Redundant Information in Tuples and Update Anomalies, NULL Values in Tuples, Generation of Spurious Tuples)
3
Functional Dependencies (Definition, Inference Rules for Functional Dependencies, Armstong’s Axioms, Attribute Closure, Equivalence of Functional Dependencies, Minimal Sets of Functional Dependencies)
4
Normal Forms Based on Primary Keys (First Normal Form, Second Normal Form, Third Normal Form)
5
General Definitions of Second and Third Normal Forms
6
Boyce-Codd Normal Form
7
Multivalued Dependency and Fourth Normal Form
8
Properties of Relational Decomposition (Attribute Preservation, Dependency Preservation, Loss-Less/ Non-Additive Join)
S6:Transaction Processing and Concurrency Control, and Recovery[8]
1
Introduction to Transaction Processing; (Single User vs. Multi User, Read/ Write Operations, Need for Concurrency Control: Lost Update Problem, Temporary Update (or Dirty Read) Problem, Incorrect Summary Problem, Unrepeatable Read Problem, Need for Recovery)
2
Transaction and System Concepts (Transaction States, System Log, Commit Point of Transaction)
3
Desirable Properties of Transactions (Atomicity, Consistency, Isolation, Durability)
4
Schedules, Conflicting Operations in Schedule, Characterizing Schedules based on Recoverability, Characterizing Schedules based on Seriablizility, Serial, Nonserial, Conflict Serializable Schedules, Testing for serializability of Schedule, Using Serializability for Concurrency Control
5
Concurrency Control Techniques
6
Two-Phase Locking (Types of Lock, Basic, Conservative, Strict, and Rigorous Two-Phase Locking, Deadlock and Starvation, Deadlock Prevention, Deadlock Detection) and Timestamp Ordering (Timestamp, Read Timestamp, Write Timestamp, Basic Timestamp Ordering, Strict Timestamp Ordering)
S7:Database Recovery Techniques[3]
1
Recovery Concepts (Recovery outline and categorization of recovery algorithms; Caching (Buffering) of disk blocks; Write-ahead logging, Steal/no steal, and Force/no-force; Checkpoints and Fuzzy Checkpointing; Transaction rollback and cascading rollback)
2
NO-UNDO/REDO Recovery Based on Deferred Update
3
Recovery Technique Based on Immediate Update
4
Shadow Paging
5
Database Backup and Recovery from Catastrophic Failures
S8:NoSQL[3]
1
Structured and Unstructured Data
2
Introduction to NoSQL Databases (NoSQL Database, Types of NoSQL Database, Advantage of NoSQL)
3
Discussion of basic architecture of Hbase, Cassandra and MongoDb;
References
1.
Fundamentals of Database Systems; Seventh Edition; Ramez Elmasri, Shamkant B. Navathe; Pearson Education
2.
Database System Concepts; Sixth Edition; AviSilberschatz, Henry F Korth, S Sudarshan; McGraw-Hill
3.
NoSQL for Dummies; Adam Fowler; John Wiley & Sons, Inc.
4.
Database Management Systems; Third Edition; Raghu Ramakrishnan, Johannes Gehrke; McGraw-Hill
5.
A First Course in Database Systems; Jaffrey D. Ullman, Jennifer Widom; Third Edition; Pearson Education Limited
Labrotary Work
The laboratory work includes design and implementation of database. Students should practice logical design of databases (ER Models) using case tools like draw.io, MS-Visio etc. The so designed database should be implemented by writing database programs using MYSQL, ORACLE etc. The database programs should include the DDL and DML statements like;
1.
Create, Drop, Alter, Rename of database/table/constraints
2.
INSERT, DELETE, and UPDATE Statements
3.
Specifying constraints (primary key, foreign key, referential integrity etc.)
4.
Basic and complex DML queries using select from where clause including the use of Order By, LIKE, IN, Exists, Between, Logical Connectives, LIMIT etc.
5.
Join Operations
6.
Set Theory Operations Union, Intersect, Set Difference
7.
Nested Queries
8.
Aggregate functions
9.
Group By Having Clause
10.
Create Views / Drop Views
11.
Concepts of transaction, Backup and recovery
12.
In addition, the students should practice and get familiar with the basics of NoSQL Database.