RDBMS | MySQL

Duration | 50 hours

MySQL is an open source relational database management system. MySQL is popular among all databases, and is ranked as the 2nd most popular database, it is one of the most reliable and performative database and is known for organizing data into one or more data tables in which data types are related to each other. These relations help structure data, as SQL is a language programmers use for creation, modification and extraction of data from a relational database.

Prerequisites

Basic knowledge of DBMS
Prior Programming Experience

Course Objectives

The primary goal of this MySQL Course is to make one ample knowledge about MySQL ,so that they can begin working on this database technology right after finishing the course. This MySQL training is also focused on having one learn how MySQL database can be used alone or in conjunction with the web application that needs to store the data in the database.

Things you will learn

Module 1: RDBMS Concepts and Data Modeling

We help our students at every stage, from the start of a course to the actual skill-building technique. Our curriculum covers the following.

Introduction to Data

  • Requirements of RDBMS
  • Relational Model
  • What is a Data Model?
  • Steps involved in the development of
  • Data Model
  • Goals of Conceptual Data Modeling
  • Conceptual Database Design
  • Database design process

Entity relationship model

  • Main Elements of an Entity-Relationship Diagram
  • Entities
  • Sub type, super type
  • Regular or Strong & Weak entities (Independent and dependent entities)
  • Entity Instances
  • Identifying and Modelling Entities
  • Relationships
  • Modelling Relationships
  • Defining a Relationship
  • Types of relationships
  • Minimum and maximum relationships

Attributes

  • Finding Attributes
  • Meaningful Components for Attributes
  • Different diagrammatic Conventions

Visualize data both ways, top-down and bottom-up

  • Transformation of entity relationship model into relational schema
  • Logical Database Design
  • Top-Down approach – E/R Modeling technique
  • Bottom-Up approach –
  • Normalization
  • Differentiate between the above two approach
  • Relational database design and update anomalies

Test your relationship with entities

  • Discussion of case study on E/R modelling
  • Execution of case study

Normalize datum relationships

  • Normalization
  • Appreciate the need for normalization
  • Functional Dependencies
  • Loss-less decomposition
  • Various forms of Normalization
  • I Normal form
  • II Normal form
  • III Normal form

Codify normalization

  • Boyce Codd Normal form (BCNF)
  • Discussion of case study on
  • Normalization
  • Execution of case study

Michael Widenius’s daughter’s Structured Query Language

  • Introduction to MySQL
  • MySQL Tools to Access Data

Things you will learn

Module 2: SQL

We help our students at every stage, from the start of a course to the actual skill-building technique. Our curriculum covers the following.

Classification of SQL – DDL| DML| DCL |TCL

  • Schema Objects – List of schema, usages and Conventions
  • RDBMS Data types in General

Table is the go-to guy for queries

  • MySQL Data types
  • Identify the major structural components of the MySQL Database
  • Table manipulation – Creating,
  • Inserting Data into tables
  • Write SELECT statements that include queries

Hands on practice session

  • Lab exercises
  • Using metadata
  • Retrieve row and column data from tables with the SELECT statement
  • Run data manipulation statements (DML) to update data in the tables
  • Viewing the data – Restricting, Organizing, Grouping

Integrity reveals beauty

  • Returning top rows
  • Auto Increment
  • Integrity constraints
  • Domain integrity constraints
  • Entity integrity constraints
  • Referential integrity constraints

Witness how the world changes, table by table

  • Self-referential integrity constraints
  • Querying Data dictionary – Querying system tables for Tables, Constraints
  • ‘Group by’ clause and ‘Having’ clause
  • SQL Functions
  • Single row functions

One call, that’s all it takes

  • Number functions
  • Character functions
  • Date functions
  • Miscellaneous functions
  • Conversion Functions
  • Group functions
  • Creating and altering the structure and constraints of the table

When one is willing and eager, the God joins in

  • Temporary tables
  • Joins – Equi-join, non-equi join
  • Outer joins, Left Outer join, Right Outer join
  • Self join
  • Cross Join
  • Natural Join
  • Using
  • On

Set goals, operate on them, and express success

  • Set operators
  • CASE expressions
  • Lab exercises
  • Hands on practice session

I have a small query

  • Queries – Complex
  • Sub Queries
  • Writing Single-Row and Multi-Row Sub-queries
  • Describing and Explaining the Behaviour of Sub-queries when NULL values are retrieved
  • Using Regular Expressions
  • Write multiple-column sub-queries

Believe you can and you’re halfway there

  • Correlated Sub-Queries
  • Introduction to other Schema Objects
  • Lab exercises
  • Hands on practice session

Make a transaction and make a transformation

  • Views – Concepts, Features,
  • Definition, Creation, Join views updateable rule
  • Manipulating and Altering Views
  • Transactions and Transactional
  • Statements
  • TCL

Never stop looking up

  • Indexes
  • Purpose of indexes
  • Classification of indexes
  • Examples of indexes
  • Creating and dropping indexes
  • Querying the data dictionary

Know the rules well

  • SQL Tuning – overview
  • Order of execution of SELECT statements
  • Rules for writing SELECT statements
  • Rules for writing join statements – to improve performance
  • Concepts of Privileges and Role
  • DCL – Data Control Language – Grant and Revoke

If you can’t find the key to success, pick the lock

  • Concurrency and Locking Mechanism
  • Types of Locks – Implicit and Explicit locking
  • Levels of Lock
  • Lab exercises
  • Hands on practice session

Things you will learn

Module 3: Stored Routines

We help our students at every stage, from the start of a course to the actual skill-building technique. Our curriculum covers the following.

Advantages of Stored Routines

  • Managing Stored Routines
  • Procedural extensions to standard SQL
  • Delimiter
  • Flow control
  • Loops
  • While
  • Loop
  • Repeat

Cursor is a wonderful workplace

  • Cursors
  • Error handling
  • Stored Procedures
  • Calling or executing a Procedure
  • Drop procedure
  • Modification
  • Metadata
  • Lab exercises
  • Hands on practice session

Information is the new currency

  • Show Function / Procedure Status
  • INFORMATION_SCHEMA.ROUTINES
  • INFORMATION_SCHEMA.PARAMETERS

You’ll never pull the trigger

  • Managing Triggers
  • Creating Triggers
  • Drop Triggers
  • Metadata
  • Show Create Trigger
  • Show Triggers
  • INFORMATION_SCHEMA.TRIGGERS
  • Exercises
  • Hands on practice session

Become an event planner

  • Events
  • Managing Events
  • Create Event
  • Alter Event
  • Drop Event
  • Metadata
  • SHOW CREATE EVENT
  • SHOW EVENTS
  • INFORMATION_SCHEMA.EVENTS

Ab Job Pakki!!
Kickstart your career