Basics of DBMS

Learn database fundamentals, relational models, and basic SQL queries.

Search Centers Visit Site
Basics of DBMS showcase image
Track
Software Development
Level
Foundation
Language
English
Duration
60 hours
Learning Mode
Learn at ALC or at Home
Introduction
What you'll learn ?
Syllabus
Work-Centric Approach

Introduction

  • DBMS Basics: Learn the purpose, advantages, and applications of DBMS.
  • ER Modelling: Design and interpret ER diagrams with entities, attributes, and relationships.
  • Relational Databases: Understand relational models, integrity constraints, and schema design.
  • SQL Fundamentals: Master basic SQL commands, including DDL and DML.
  • Advanced SQL Queries: Work with joins, nested queries, aggregate functions, and constraints.
  • Database Design: Create normalized relational databases with key constraints.
  • Practical Applications: Solve real-world problems using SQL for various scenarios.
  • Client/Server Architecture: Understand DBMS architectures and database utilities.
  • This course provides a comprehensive foundation in database management systems, SQL, and relational database design, preparing learners for practical application and advanced learning.

What you'll learn ?

  • Comprehensive DBMS Knowledge
  • Understand the fundamental concepts of database management systems, their architecture, and applications.
  • Explain the advantages and limitations of DBMS over traditional data storage methods.
  • Proficiency in ER Modelling
  • Develop and interpret ER diagrams to design effective database schemas.
  • Apply key concepts like attributes, relationships, and structural constraints to create accurate data models.
  • Relational Database Design Expertise
  • Convert ER models into relational schemas while maintaining data integrity and consistency.
  • Implement integrity constraints such as primary keys, foreign keys, and referential integrity.
  • SQL Query Development Skills
  • Write basic and advanced SQL queries for data manipulation, retrieval, and management.
  • Perform complex operations using joins, nested queries, aggregate functions, and set operations.
  • Data Integrity and Optimization
  • Ensure data consistency and integrity through the application of relational integrity constraints.
  • Optimize database performance using relational algebra and efficient query design.
  • Practical Database Applications
  • Solve real-world problems by designing and managing databases for applications like library and ticketing systems.
  • Gain hands-on experience with creating, updating, and querying databases through projects and exercises.
  • Client/Server and DBMS Tools Expertise
  • Understand and apply centralized and client/server DBMS architectures.
  • Use database utilities and tools to maintain, optimize, and secure databases.
  • Industry-Ready Skills
  • Develop the ability to design, implement, and manage databases for business and enterprise applications.
  • Acquire the skills necessary for roles like Database Administrator, SQL Developer, and Data Analyst.
  • These outcomes equip learners with the knowledge and practical skills required to excel in database design, management, and application development.

Syllabus

Credit 1: Session 01
  • Introduction
  • History of DBMS
  • Purpose of Database Systems
  • Advantages of using the DBMS approach
    • DBMS Advantage_1
    • DBMS Advantage_2
    • DBMS Advantage_3
    • DBMS Advantage_4
  • Disadvantages of using the DBMS approach
  • DBMS and its applications
    • Enterprise Information
    • Banking and Finance
  • Example of a Database
    • Simple example of real-world entities and their relationships
    • Example of a Database with a Conceptual Data Model
  • Architecture of DBMS
  • Data Models, Schemas and Instances
  • Categories of Data Models
    • Relational Model
    • Entity-Relationship Model
    • Object-Based Data Model
    • Semi-structured Data Model
  • Database Schema vs Database State
  • Data-Manipulation Language (DML)
    • Types of DML
  • Data-Definition Language (DDL)
    • Database Administrators and Database Users
  • Database Users and User Interfaces
    • User-Friendly DBMS Interfaces
    • Other DBMS Interfaces
  • The Database System Environment
  • Database System Utilities
  • Centralized and Client/Server Architecture for DBMS
    • Physical Centralized Architecture
    • 2-tier Client-Server Architecture
    • 3-tier Client-Server Architecture
    • Benefits of the Oracle Client/Server Architecture
  • Conceptual Design using ERD
    • High-Level Conceptual Data Models
  • Introduction to ER Model
  • Symbols in ER Diagram
  • Entity types
  • Entity Sets
  • Attributes
    • Simple and Composite Attributes
    • Domain of Attributes and Key Attributes
    • Single Valued and Multi Valued Attributes
    • Stored and Derived Attributes
  • Entity-Set and Keys
  • Relationship
    • Relationship Types
  • Degree of a Relationship
  • Relational Model Integrity
  • Constraints
    • Key Constraints
    • Domain Constraints
    • Referential Integrity Constraints
    • Need for Foreign Key
    • Foreign Key Constraint
    • Participation Constraints
  • Keys in DBMS
    • Need for Composite Primary Key
    • ER Diagram
  • Naming Conventions
  • ER Design Issues
  • Structural Constraints
    • Mapping Cardinality - Example
    • Strong Entity Set 1
    • Strong Entity Set 2
    • Weak Entity Set 1
    • Weak Entity Set 2
  • Constraints Summary
  • Generalization_1
    • Generalization_2
    • Specialization_1
  • Specialization_2
  • Relational Model
    • Relational Model Concepts
    • Informal Definitions
    • Formal Definitions
    • Structure of Relational Databases_1
    • Structure of Relational Databases_2
    • Structure of Relational Databases_3
    • Database Schema_1
    • Database Schema_2
    • Characteristics of Relations_1
    • Relational Integrity Constraints
  • Entity Integrity
  • Referential Integrity
  • Other Types of Data Constraints_1
    • Other Types of Data Constraints_2
    • Semantic Integrity Constraints
  • Domains, Attributes, Tuples and Relations
    • Represent all the Entities and Relationships in Tabular Fashion_1
  • Represent all the Entities and Relationships in Tabular Fashion_2
  • Represent all the Entities and Relationships in Tabular Fashion_3
  • Represent all the Entities and Relationships in Tabular Fashion_4
  • Relational Algebra
    • Introduction_1
    • Introduction_2
    • PRELIMINARIES_1
    • PRELIMINARIES_2
    • Selection and Projection_1
    • Selection and Projection_2
    • Unary Operations_1
    • Unary Operations_2
    • The SELECT Operation
  • PROJECT Operation_1
  • PROJECT Operation_2
  • Sequences of Operations and the RENAME Operation
  • Sequences of Operations and the RENAME Operation with an Example
  • Set Theory Operations
    • The UNION, INTERSECTION and MINUS Operations_1
    • The UNION, INTERSECTION and MINUS Operations_2
    • The UNION, INTERSECTION and MINUS Operations - Examples
  • The CARTESIAN PRODUCT (CROSS PRODUCT) Operation
    • The CARTESIAN PRODUCT (CROSS PRODUCT) Operation- Examples 1
    • The CARTESIAN PRODUCT (CROSS PRODUCT) Operation- Examples 2
    • The CARTESIAN PRODUCT (CROSS PRODUCT) Operation- Examples 3
  • Binary Relational and Additional Relational operations
  • The JOIN Operation
    • The JOIN Operation- Example
    • The EQUIJOIN and NATURAL JOIN_1
    • The EQUIJOIN and NATURAL JOIN_2
    • The EQUIJOIN and NATURAL JOIN_3
    • The EQUIJOIN and NATURAL JOIN_4
    • The EQUIJOIN and NATURAL JOIN_5
  • Complete Set of Relational Algebra Operations_1
    • DIVISION Operation_1
  • Complete Set of Relational Algebra Operations_2
    • DIVISION Operation_2
  • List of Operators with Purpose
  • Additional Relational Operations and Generalized Projection
    • Aggregate Functions and Grouping_1
    • Aggregate Functions and Grouping_2
    • Recursive Closure Operations_1
    • Recursive Closure Operations_2
    • OUTER JOIN Operations_1
    • OUTER JOIN Operations_2
    • The OUTER UNION Operation_1
    • The OUTER UNION Operation_2
  • Examples of Queries in Relational Algebra_1
    • Examples of Queries in Relational Algebra_2
  • Examples of Queries in Relational Algebra
  • Relational Database Design using ER-to-Relational Mapping
  • Procedure to Create a Relational Schema from an Entity-Relationship (ER)
    • Mapping of Regular Entity Types
    • Mapping of Weak Entity Types
    • Mapping of Binary 1:1 Relationship Types
    • The Foreign Key Approach_1
    • The Foreign Key Approach_2
    • Merged Relation Approach
    • Cross-reference or Relationship Relation Approach_1
    • Cross-reference or Relationship Relation Approach_2
  • Mapping of Binary 1: N Relationship Types 1
    • Mapping of Binary 1: N Relationship Types 2
    • Mapping of Binary M: N Relationship Types 1
    • Mapping of Binary M: N Relationship Types 2
    • Mapping of Multivalued Attributes 1
    • Mapping of Multivalued Attributes 2
    • Mapping of N-ary Relationship Types
  • SQL
    • Introduction
    • SQL Data Definition and commands
    • SQL Data Types 1
    • SQL Data Types 2
    • Operators and Expressions
  • Credit 1 End Test
  • MYSQL Installation - Demo
  • SQL Server Management Studio - Demo 1
  • SQL Server Management Studio - Demo 2
  • SQL Server with Visual Studio - Demo 1
  • SQL Server with Visual Studio - Demo 2
  • Schema and Catalog Concepts in SQL_1
  • Schema and Catalog Concepts in SQL_2
  • The CREATE TABLE Command in SQL
    • The CREATE TABLE Command in SQL Examples
  • Attribute Data Types and Domains in SQL
    • Basic Data Types
    • Additional Data Types
    • Specifying Constraints in SQL
  • Specifying Attribute Constraints and Attribute Defaults
    • Specifying Key and Referential Integrity Constraints_1
    • Specifying Key and Referential Integrity Constraints_2
    • Giving Names to Constraints
  • Specifying Constraints on Tuples Using CHECK_1
  • Specifying Constraints on Tuples Using CHECK_2
  • Basic Retrieval Queries in SQL
    • The SELECT-FROM-WHERE Structure of Basic SQL Queries_1
    • The SELECT-FROM-WHERE Structure of Basic SQL Queries_2
  • Ambiguous Attribute Names, Aliasing, Renaming and Tuple Variables
  • Tables as Sets in SQL
    • Order of Query Execution
  • Ordering of Query Results_1
  • Ordering of Query Results_2
  • The UPDATE Command_1
  • The UPDATE Command_2
  • The DELETE Command_1
  • The DELETE Command_2
  • The INSERT Command_1
  • The INSERT Command_2
  • Additional Features of SQL_1
  • Additional Features of SQL_2
  • Create a Table Called Employee with the 2-Column Table Structure
    • Insert Any Five Records into the Table
    • Update the Column Details of Job
    • Rename the Column of Employ Table Using Alter Command
    • Delete the Employee whose Emp no is 105
  • Create Department Table with the 2-Column Table Structure
    • Add Column Designation to the Department Table
    • List the Records of Dept Table Grouped by Dept No
    • Insert Values into the Table
    • Update the Record where Dept No is 9
    • Delete any Column Data from the Table
  • Queries Using DDL and DML
    • Create a User and Grant all Permissions to the User
    • Insert any Three Records and Use Rollback
    • Add Primary Key Constraint and Not Null Constraint
    • Insert Null Values to Employee Table
    • Create User and Grant All Permissions to User
    • Insert Values in Department Table and Use Commit
    • Add Constraints like Unique and Not Null
    • Insert Repeated Values and Null Values
    • Design SQL Queries for Suitable Database Application Using SQL DML Statements
  • Design SQL Queries for Suitable Database Application Using SQL DML Statements
  • Comparisons Involving NULL and Three-Valued Logic
  • Nested Queries, Tuples and Set or Multi-Set Comparisons
    • Nested Queries, Tuples and Set or Multi-Set Comparisons - Examples
    • Nested Queries - Comparison Operator
    • Correlated Nested Queries
  • Functions
    • Miscellaneous Functions
  • The EXISTS and UNIQUE Functions in SQL
    • EXISTS Functions - Examples
    • NOT EXISTS Functions
  • Explicit Sets and Renaming of Attributes in SQL
  • Join - Introduction
    • Cross Join
  • INNER Join
    • Inner Join with Condition Syntax
  • SELF Join
  • LEFT OUTER Join
    • Left Outer Join with Condition
  • RIGHT OUTER Join and FULL OUTER Join
  • Join - Examples_1
  • Join - Examples_2
  • Join - Alternate Syntax
    • Join - Syntax Error
  • Multi Way Join
    • Design and Develop SQL DDL statements_1
    • Design and Develop SQL DDL statements_3
    • Library Database
  • Consider the schema for a Library Database: Write SQL queries to Retrieve details of all books in the library
  • Consider the schema for a Library Database: Write SQL queries to Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2022 to Jun 2022
  • Consider the schema for a Library Database: Write SQL queries to- Delete a book in BOOK table.
  • Consider the schema for a Library Database: Partition the BOOK table based on year of publication. Demonstrate its working with a simple query
  • Consider the schema for Order Database
    • Create Insertion of Values to Tables
    • Write SQL queries to: Find the name and numbers of all salesmen who had more than one customer.
    • Write SQL queries to: List all salesmen and indicate those who have customers in their cities (Use UNION operation.)
    • Write SQL queries to: List all salesmen and indicate those who do not have customers in their cities (Use UNION operation.)
  • Write SQL queries to: Create a view that finds the salesman who has the customer with the highest order of a day.
  • Write SQL queries to: Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.
  • Querying (using ANY, ALL, IN, Exists, NOT EXISTS, UNION, INTERSECT, Constraints etc.) 1- for railway ticketing
  • Querying (using ANY, ALL, IN, Exists, NOT EXISTS, UNION, INTERSECT, Constraints etc.) 2
  • Display unique PNR_NO of all passengers
  • Display all the names of male passengers
  • Display the ticket numbers and names of all the passengers.
  • Find the ticket numbers of the passengers whose name start with ‘r’ and ends with ‘h’.
  • Find the names of Passengers whose age is between 30 and 45.
  • Display all the passenger’s names beginning with ‘A’.
  • Nested Queries_1
  • Nested Queries_2
  • Nested Queries_3
    • Queries Using Null Functions
  • Design SQL Queries Using SQL DML Statements_4
  • Design SQL Queries Using SQL DML Statements_5
  • Design SQL Queries Using SQL DML Statements_6
  • Aggregate Functions in SQL
  • Order By Clause
    • Order By Clause- Example 1
    • Order By Clause- Example 2
  • Grouping
    • Grouping- Examples 1
    • Grouping- Examples 2
  • Grouping- Examples 3
  • Group By Errors
  • Specifying General Constraints as Assertions in SQL
    • Specifying General Constraints as Assertions in SQL - Example 1
    • Specifying General Constraints as Assertions in SQL - Example 2
  • Perform Query Using Aggregate Function_1
    • Perform Query Using Aggregate Function_2
  • Credit 2 End Test

Work-Centric Approach

The academic approach of the course focuses on ‘work-centric’ education. With this hands-on approach, derive knowledge from and while working to make it more wholesome, delightful and useful. The ultimate objective is to empower learners to also engage in socially useful and productive work. It aims at bringing learners closer to their rewarding careers as well as to the development of the community.

  • Step 1: Learners are given an overview of the course and its connection to life and work
  • Step 2: Learners are exposed to the specific tool(s) used in the course through the various real-life applications of the tool(s).
  • Step 3: Learners are acquainted with the careers and the hierarchy of roles they can perform at workplaces after attaining increasing levels of mastery over the tool(s).
  • Step 4: Learners are acquainted with the architecture of the tool or tool map so as to appreciate various parts of the tool, their functions, utility and inter-relations.
  • Step 5: Learners are exposed to simple application development methodology by using the tool at the beginner’s level.
  • Step 6: Learners perform the differential skills related to the use of the tool to improve the given ready-made industry-standard outputs.
  • Step 7: Learners are engaged in appreciation of real-life case studies developed by the experts.
  • Step 8: Learners are encouraged to proceed from appreciation to imitation of the experts.
  • Step 9: After the imitation experience, they are required to improve the expert’s outputs so that they proceed from mere imitation to emulation.
  • Step 10: Emulation is taken a level further from working with differential skills towards the visualization and creation of a complete output according to the requirements provided. (Long Assignments)
  • Step 11: Understanding the requirements, communicating one’s own thoughts and presenting are important skills required in facing an interview for securing a work order/job. For instilling these skills, learners are presented with various subject-specific technical as well as HR-oriented questions and encouraged to answer them.
  • Step 12: Finally, they develop the integral skills involving optimal methods and best practices to produce useful outputs right from scratch, publish them in their ePortfolio and thereby proceed from emulation to self-expression, from self-expression to self-confidence and from self-confidence to self-reliance and self-esteem!