Database Systems

DIT 2nd Semester Revised Course 2022 Outline

Database Systems  

Total Th Hrs   80                                                         Total Pr Hrs      40

Total Th Mks   75                                                        Total Pr. Mks     25

GENERAL OBJECTIVES: After completion of this course, students are expected to be able: 

• To ascertain the importance of databases in knowledge-based societies. 
• To understand the basic concepts of Database and Database Management Systems. 
• To transform an Entity Relationship Model into a Relational Schema. 
• To use an Open Source software. 
• To apply SQL commands for data manipulation in a database. 
• To use MySQL Community Server 8.0 for Creating and Maintaining a database.
• To Create Front-end in MS ACCESS for MySQL Databases

Module 1

Allotted Weeks 01                                                 Contact Hours 06

Introduction to Database Concepts

• Definition of Database
• Importance/Advantages of Databases 
• Database Models 
• Hierarchical 
• Network 
• Relational 
• Definition of Database Management System (DBMS) 
• Relational DBMS (SQL Based) (MS Access, MS SQL, MySQL, ORACLE) 
• Non-Relational DBMS (NoSQL) (MongoDB, HBase, Cassandra) 
• Functions of DBMS 
• Data Dictionary Management 
• Data Storage Management 
• Data Integrity Management 
• Backup & Recovery Management 
• Security Management 
• Multi-User Access Control

Module 2

Allotted Weeks 01                                                 Contact Hours 06

Relational Database Concepts

• Definition of Relational Database & RDBMS 
• Entity, Table, Record, Field 
• Primary Key, Composite Key, Foreign Key
• Referential Integrity 
• Types of Relationships 
• One-to-One 
• One-to-Many 
• Many-to-Many

Module 3

Allotted Weeks 03                                               Contact Hours 18

Data Modelling

• Introduction to Data Modelling & its importance 
• Definition of Entity Relationship Diagram (ERD) 
• Elements of ERD 
• Transforming ERD to Relational Schema 
• Normalization of Relational Database 
• First Normal Form (1NF) 
• Second Normal Form (2NF) 
• Third Normal Form (3NF)

Module 4

Allotted Weeks 15                                               Contact Hours 90

Data Modelling

• Introduction to MYSQL 
• Definition and advantages of MySQL 
• Installation of MySQL Community Server 8.0 
• Introduction to MySQL Workbench 8.0 
• Introduction to MySQL Shell 8.0 
• Introduction to MySQL 8.0 Command Line Client 
• Introduction to Structured Query Language (SQL) 
• Data Definition Language (DDL) 
• Data Manipulation Language (DML) 
• Data Control Language (DCL)
• MySQL Statements for Database Development 
• Database Manipulation 
• CREATE DATABASE statement 
• DROP DATABASE statement 
• USE Statement 
• Basic Data Types 
• VARCHAR 
• TEXT 
• LONGTEXT 
• INT 
• BIGINT 
• FLOAT 
• DOUBLE 
• BOOL 
• DATE 
• TIME 
• YEAR 
• Table Manipulation 
• SHOW TABLE Statement 
• CREATE TABLE statement
• DROP TABLE statement 
• TRUNCATE TABLE statement 
• ALTER TABLE statement 
• ALTER TABLE – ADD 
• ALTER TABLE – DROP COLUMN 
• ALTER TABLE – MODIFY COLUMN 
• CREATE INDEX statement 
• INSERT INTO statement 
• UPDATE statement 
• DELETE statement 
• LIMIT clause 
• INSERT INTO SELECT statement 
• DESC and EXPLAIN statements 
• Data Retrieval 
• SELECT statement 
• SELECT DISTINCT statement 
• WHERE clause 
• Operators used in WHERE clause 
• Wildcards ( % , _ ) used in WHERE clause 
• AND, OR, NOT Operators 
• ORDER BY clause 
• ORDER BY DESC 
• GROUP BY statement 
• HAVING clause 
• Constraints 
• NOT NULL 
• UNIQUE 
• PRIMARY KEY 
• FOREIGN KEY 
• DEFAULT 
• CHECK 
• Functions 
• AVG()
• COUNT() 
• CONCAT() 
• LOWER() 
• LENGTH() 
• LTRIM() 
• MIN() 
• MAX() 
• RTRIM() 
• REPLACE() 
• REPEAT() 
• STRCMP() 
• SUBSTR() 
• SUM() 
• UPPER() 
• MySQL Operators 
• Arithmetic Operators 
• Comparison Operators 
• Logical Operators 
• Views 
• CREATE VIEW statement 
• DROP VIEW statement 
• MySQL Joins 
• INNER JOIN 
• LEFT JOIN 
• RIGHT JOIN 
• CROSS JOIN 
• Front-end-Development 
• Connection MS Access to MySQL Database. 
• Creating Forms for MySQL Databases 
• Creating Reports for MySQL Databases