Course
Title: Database Management System Program:
BICTE
Course
No. : ICT. Ed. 446 Nature of course:
Theoretical + Practical
Level:
Bachelor Credit Hour:
3 hours (2T+1P)
Semester:
Fourth
Teaching Hour: 80 hours (32+48)
1.
Course Description
The
purpose of this course is to introduce the fundamental concepts of database
management, including aspects of data models, database languages, and database
design. Student will be also able to understand the current trends of database
management such as big data, data analytics: data warehousing, online
analytical processing and data mining.
2.
General Objectives
Through
this course, students shall
·
become proficient at modeling
databases at conceptual and logical levels of design,
·
be able to develop
database schemas with design principles that enforce data integrity,
·
become knowledgeable in
the creation, altering, and manipulation of tables and views using SQL,
·
become proficient at
casting queries in SQL, and
·
be able to understand
concepts of transaction management, concurrency control, and recovery.
·
Be able to explain about
big data and data analytics such as data warehousing, online analytical
processing and data mining
3.
Course Outlines:
|
Specific
Objectives |
Contents |
|
·
Identify data management approaches and their values. ·
Define differences between file systems and database management systems. ·
Understand benefits of database management systems. ·
Describe different data models and their usefulness. ·
Understand the concept of data abstraction and data independence. |
Unit 1: Database System Introduction (4 hrs) 1.1. Database System Applications 1.2. Purpose of Database Systems 1.3. View of Data 1.4. Database and Application Architecture 1.5. Database Users and Administrators 1.6. History of Database Systems Practical Work (4 hrs) ·
Create program to demonstrate differences between
data access from file system and database management system |
|
·
Explain use and importance of ER model. ·
Use ER diagrams to design databases. ·
Learn to identify attributes and entity-relationship sets ·
Make use of generalization, specialization and aggregation concepts ·
Learn conversion of ER diagrams into
Relational model. |
Unit 2: Database Design using ER Model (4 hrs) 2.1. The
ER Model : entity sets and relationship sets 2.2. Attributes
and its types 2.3. Mapping
Cardinalities 2.4. Constrains
on ER Model 2.5. Extended
ER Features: Aggregation, Specialization and Generalization, Constraints on
Specialization/Generalization 2.6. Reducing
ER diagram to Relational Schemas Practical Works: (8 hrs) · Draw ER diagrams for real world scenarios · Learn to use appropriate symbols for constraints · Practice Conversion of ER model to Relational model |
|
· Explain
structure of SQL queries. · Use SELECT,
FROM and WHERE clauses efficiently. · Understand
concept behind join operations. · Discuss and
Use aggregate functions and subqueries. · Apply
database modification statements. · Explain and
use DDL statements. · Understand concept behind views and use them. · Make use of grant and revoke
statements |
Unit 3: Structured
Query Language (SQL) (6
hrs) 3.1 Introduction to Relational Database 3.2 Database Schema and Schema Diagram 3.3 Introduction: Basic Structure of SQL Query,
SELECT, FROM and WHERE clause, Using Multiple Relations 3.4 String/Pattern Matching, Ordering the Display
of Tuples, Cartesian product, Join Operations: Join Types and Join
Conditions. 3.5 Set Operations and Null Values 3.6 Nested Queries: Set membership Test, Set
Comparison and Test for Empty Relations. 3.7 Aggregate Functions, Group by Clause and
Having Clause 3.8 Database Modifications: Insert, Delete and
Update Operations 3.9 Data Definition Language: Domain Types in
SQL, Create, Alter and Drop statements 3.10
View 3.11
Authorization in SQL : grant and revoke privileges Practical Works: (18
hrs) · Create relational database by using create statements · Populate tables with data by using INSERT statement · Practice basic SQL queries by using Select..from.. where · Use Cartesian products, natural join and set operations to solve
queries · Use sub queries, aggregate functions and outer joins to solve queries · Practice DML statements DELETE and UPDATE · Practice DDL statements ALTER, and DROP · Demonstrate SQL authorization: grant and revoke operations |
|
·
Understand importance of integrity constraints. ·
List and discuss different types
of integrity constraints. ·
Use Integrity constraints for maintaining for achieving correctness of
data. ·
Compare and contrast between assertions and
triggers |
Unit 4:
Integrity Constraints (4
hrs) 4.1 Domain Constraints: Not
Null Constraints, Unique Constraints, Primary key Constraints, Check
Constraints. 4.2 Referential Integrity:
Using Referential Integrity, Cascading Actions 4.3 Assertions and Triggers:
Creating and Deleting Assertions, Creating and Deleting Triggers, Assertions
vs Triggers. Practical Works: (4
hrs) · Demonstrate use of Domain constrains and referential integrity · Create assertions and triggers |
|
·
Exemplify database modification anomalies. ·
Understand and exemplify functional dependencies. ·
Discuss and exemplify conversion of de-normalized
relations into normalized forms. |
Unit 5:
Relational Database Design (6
hrs) 5.1 Features of good relational designs 5.2 Keys: Super Key, Candidate Keys and Primary
Keys 5.3 Functional Dependencies 5.4 Anomalies 5.5 Decomposition using functional dependencies 5.6 Normal forms: 1NF, 2NF, 3NF and BCNF Practical Works: (8
hrs) · Demonstrate Database anomalies · Design good RDBMS (anomalies free database) |
|
·
Understand the concepts of transaction and its properties ·
Make use of serializability ·
Understand the problems behind concurrent execution of transactions ·
Describe and exemplify lock based concurrency control technique. ·
Discuss need of recovery in database management systems. |
Unit 6: Transaction Management (4
hrs) 6.1
Transaction Concept 6.2
ACID Properties 6.3
Serializability 6.4
Concurrency Control: Need of Concurrency Control, Lock-Based
Protocols 6.5
Recovery: Failure Classification, Shadow paging Practical Works:
(2
hrs) · Demonstrate commit and rollback |
|
·
Understand the concept of big data, NoSQL, data warehousing, data
mining and OLAP |
Unit 7: Big Data Analytics (4
hrs) 7.1
Concept of Big data 7.2
Concept of NoSQL 7.3
Concept of Data Warehouse and Data Mining 7.4
Concept of Online Analytical Processing Practical Works:
(4
hrs) · Research about different NoSQL Syntax |
9
Instructional Techniques
The instructional techniques for this course are
divided into two groups. First group
consists of general instructional techniques applicable to most of the units.
The second group consists of specific instructional techniques applicable to
particular units.
4.1 General Techniques
Reading materials will be provided to students
in each unit. Lecture, Discussion, use of multi-media projector, brain storming
are used in all units.
4.2 Specific Instructional Techniques
Demonstration
is an essential instructional technique for all units in this course during
teaching learning process. Specifically,
demonstration with practical works will be specific instructional technique in
this course. The details of suggested instructional techniques are presented
below:
Unit 1: Self
reading, and making study reports
Unit 2: Assignment
on Creating ER diagrams and converting ER model to Relational model
Unit 3: Homework
and Assignment on Laboratory works in SQL
Unit 4: Group
Discussion on Integrity Constraints
Unit 5: Mini Case
Study on Normalization
Unit 6: Self
reading and making study reports
Unit 7: Self reading,
creating and presenting study reports
5.
Evaluation :
|
Internal Assessment |
External Practical Exam/Viva |
Semester Examination |
Total Marks |
|
40 Points |
20 Points |
40 Points |
100 Points |
Note: Students must pass separately in internal assessment, external
practical exam and semester examination.
5.1 Internal Evaluation (40 Points):
Internal evaluation will be conducted
by subject teacher based on following criteria:
1)
Class Attendance 5
points
2)
Learning activities and class
performance
5 points
3)
First assignment ( written assignment) 10 points
4)
Second assignment (Case Study/project
work with presentation ) 10 points
5)
Terminal Examination 10 Points
|
|
|
Total
40 points |
5.2 Semester
Examination (40 Points)
|
Examination Division, Dean office will conduct final
examination at the end of semester. 1) Objective question (Multiple choice 10 questions x
1mark) 10 Points 2) Subjective answer questions (6 questions x 5 marks) 30 Points |
|
Total
40 points
|
5.3
External Practical Exam/Viva (20 Points):
Examination Division, Dean Office will
conduct final practical examination at the end of semester.
10 Recommended books and References materials
(including relevant published articles in national and international journals)
Recommended books:
· Silberschatz,
H.F. Korth, and S. Sudarshan, Database
System Concepts, 7th Edition, McGraw Hill, 2019
References materials:
· C.J. Date, SQL and Relational
Theory: How to Write Accurate SQL Code, 2nd Edition, O’Really
Media, 2011
- C.J. Date, An introduction to Database System, 8th
Edition, Addison Wesely, 2003
No comments:
Post a Comment