Everything about Databases using SQL and MongoDB

A 3 month course which literally gives you "Everything" you need to know about Databases

  • Domain : CSE
Enroll Now View demo

A Quick Overview

A database is a place to store data. Sounds too simple right? let’s expand it, a database often looks as simple as a spreadsheet. There are columns, rows and tables. A column is a vertical section, it is the name of a value. A row is a horizontal section, it contains values for the columns. A table is simply a separate spreadsheet that is contained within the same database, and can be related to the other tables. A table often has something called a Primary Key(or PKey). The Pkey is a unique identifier to tie sets of data together. Let’s assume you are logging into an Ecommerce website. You enter your email address, your password and then you are on your home page. The product suggestions in your homepage will be much different from the product suggestions in your friends home page. This is because the suggestions that you get are based on your interests. This is done by building a Database of all the actions that you do in the website, what products you search, which category does the searched product belong to, what price range are you comfortable with and much more. To handle all this data, we use a Database software.

The most common one is SQL or Structured Query Language. SQL is a specific language used to do things (query) with the data. This includes actions like selecting records created between given dates or updating a record that matches given criteria. Databases are the backbone of most of the world at this point, everything is driven by data, every website, every piece of software.


GET COURSE COUNSELLING TODAY

Get a 1-on-1 demo to understand what is included in the course and how it can benefit you from an experienced sales consultant. The demo session will help you enroll in this course with a clear vision and confidence.

Request a Demo Session

COURSE SYLLABUS

1Introduction to Databases

A Database is a collection of interrelated data which helps in efficient retrieval, insertion and deletion of data from a database. We can also organize the data in the form of tables, views, schemas, reports etc. In this week we will having a small introduction to the topic, we will be looking at

  • Types of databases
  • Databases vs File system
  • Architecture
  • Schema
  • Models
  • Data Independence
  • DBMS languages
  • Set theory
  • Database in a web application architecture
  • ACID properties
  • Create a relational database application of your ecommerce application
    • Requirement analysis
    • Specification
    • Design
    • Implementation

 

2Entities and Relationships

An Entity–relationship model describes the structure of a database with the help of a diagram, which is known as Entity Relationship Diagram (ER Diagram). An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. An Entity may be an object with a physical existence – a person, car, house, or employee. Or, it may be an object with a conceptual existence like– a company or job. These entities can have attributes that define its properties. By defining the entities, their attributes, and showing the relationships between them, an ER diagram illustrates the logical structure of databases. ER diagrams are used to sketch out the design of a database. In this week we will be looking at-

  • Entities, Relationships and Attributes
  • ER Model and Relational Model
    • Advantages and Disadvantages
  • Attributes of relationships
    • Simple vs Composite Attributes
    • Single valued vs Multi-valued Attributes
    • Stored vs Derived Attributes
    • Complex Attributes
  • Degree of a relationship and Cardinality Ratio
  • Total vs Partial Participation
  • Intention vs Extension
  • Recursive Relationship
  • How Relational Model is derived from Discrete Math

3Creating Database Tables

Once we have data, it is vital that it gets categorised into a database table. This allows for easy retrieval, editing, insertion and deletion of new or old data. This week, we will be looking at how to create a database table. In specific we will be looking at

  • Converting ER diagram to Tables in RDBMS
  • Super Key vs Key vs Candidate Key vs Primary Key
  • Integrity rules
  • Entity Integrity Constraints
  • Referential Integrity Constraints
  • Constraints violated by Insertion, Deletion, Updation and Selection
  • Steps taken by DBMS in case of constraint violation
  • Authorization in relational databases
  • Generalisation and Specialisation
  • Aggregation

4Database Design

Database design is the procedure of organizing data according to a database model. The designer will determine what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model. Database management system manages the data accordingly.

  • Functional dependencies
  • Relational Decomposition
  • Multivalued Dependency
  • Join Dependency
  • Inclusion Dependence
  • Normal forms-
    • 1st Normal Form
    • 2nd Normal Form
    • 3rd Normal Form
    • Boyce Codd Normal Form
  • Codd’s rules
  • Data-modeling with Unified Modeling Language (UML)

5SQL

Structured Query Language or SQL is a standard Database language used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre. As the name suggests, it is used when we have structured data. All databases that are not relational and therefore do not use SQL, are called NoSQL databases. we’ll be looking into NoSQL later in the course. In this week, we will be looking into-

  • Declarative programming
  • Studying SQL using MySQL and PhpMyAdmin.
  • Characteristics of SQL
  • Advantages of SQL
  • SQl Data Types
  • SQL Commands
  • SQL Operators
  • SQL Table queriesSQL SELECT Statement
  • SQL INSERT Statement
  • SQL UPDATE Statement
  • SQL DELETE Statement
  • SQL Clauses
  • SQL Aggregate Functions
  • SQL Set Operations
  • Dealing with NULL values

6 SQL

This week is a continuation of the previous one. Here, we continue to look into more topics related to the Structured Query Language.

  • SQL JOINs
    • Cartesian product
    • Natural Joins
    • Inner joins
    • Outer Joins
  • SQL Views
  • SQL Sub Queries
  • SQL Compound Queries
  • Query Processing
    • Materialization, Pipelining
    • Estimating Query Cost
    • Query optimizations

7Designing your ecommerce Database

  • Design your ecommerce database till BCNF
  • Identify keys
  • Document your design using UML Diagrams
  • Generate Complex queries for the functionalities of your ecommerce website

8Scaling the Database

  • Storage system
    • File structure
    • B Trees and B+ trees
    • Ordered vs Unordered File Organization
  • Database Hashing
  • Database indexing
  • Database Keys and Indexes
    • SQL Index
  • Database Partitioning
  • Database Sharding
  • Map Reduce
  • RAID
  • Data warehouse processing

9PL/SQL

  • When to use Procedural Language?
  • Functions
  • Stored Procedures
  • Triggers
    • Notify customers as soon as the product is back in stock
  • Common table expressions
  • Recursion
  • Cursors

10NOSQL Databases

  • Introduction to NoSQL
  • Advantages and Disadvantages
  • MongoDB
    • Comparison with SQL
    • Relational vs document oriented
    • Your first MongoDB database
    • Querying in MongoDB- CRUD
    • Creating and Retrieving products for ecommerce website
    • Sorting and Paginating in ecommerce website
    • Embedding vs Referencing in documents
    • Data modeling in NoSQL
    • Aggregation Pipelines
    • Map Reduce
    • Scalability and Indexing in key value database
  • Graph databases

11Transactions for concurrency control and failure recovery

  • Transactions
  • Concurrency Control
    • Need for Concurrent Execution of Transactions
    • Inconsistency Problem due to Concurrent Execution
    • Lost Update Problem
    • Dirty Read Problem
    • Unrepeatable Read Problem
    • Phantom Read Problem
  • Handling Deadlocks
  • Data Backup
  • Data Recovery
  • Data Replication
  • Security

12In Memory Database

  • Why use "In Memory" Database
  • Redis vs MongoDB
  • The Pub-Sub model
  • Install and use Redis
  • Redis commands
  • Backup
  • Pipelining
  • Partitioning
  • Security


Projects Overview

Project 1

Highlights

1. For the given functionality, submit the PL/SQL code
2. Using MongoDB, design your entire ecommerce Database. Mention why you used embedded vs referenced documents. Generate Complex aggregate queries for the given functionalities of your ecommerce website
3. Submit a 2-page report on why and how you would use Redis for fast caching in your large-scale ecommerce website.
4. Submit a report on how you would optimise given queries.

Suggestions used-
1. Logical ending for each session- PPT script
2. DBMS divide into two – first 9 modules basic and last 3 modules advanced
3. Examples and assignments to be evolved from scratch to final project
4. Mongodb and redis only as per consumer perspective.


Flexible Course Fees

Choose the plan that’s right for you

Basic

2 Months Access

$92.89

Per month for 3 months

  • Access Duration : 2 months
  • Mode of Delivery : Online
  • Project Portfolio : Available
  • Certification : Available
  • Email Support : Available
  • Forum Support : Available
Premium

Lifetime Access

$199.05

Per month for 3 months

  • Access Duration : Lifetime
  • Mode of Delivery : Online
  • Project Portfolio : Available
  • Certification : Available
  • Individual Video Support : 12/month
  • Group Video Support : 12/month
  • Email Support : 12/month
  • Forum Support : Available
  • Telephone Support : Available
  • Dedicated Support Engineer : Available

Testimonials

Companies hire from us

See all

Certification

  • Top 5% of the class will get a merit certificate
  • Course completion certificates will be provided to all students
  • Build a professional portfolio
  • Automatically link your technical projects
  • E-verified profile that can be shared on LinkedIn

SKILL LYNC WORKS TO GET YOU A JOB

See all

Frequently Asked Questions

1Who can take your course?

Any Computer Science or Non-Computer Science Student or Professional willing to create and work with Databases or develop Web Applications which would involve using Databases. Anyone wanting to become a Back-End developer/Full Stack Developer. There are no prerequisites for this course.

2What is included in your course?

This course is a description of all the different databases which one could use in a web application and when to choose which one. It goes into details of designing, querying and optimising data storage and retrieval for fast web applications. It has 12 small exercises and 1 minor and 1 major project to mark course completion.

3What will the student gain from your course?

A complete and thorough understanding of Database systems and concepts. Students will gain confidence in each of the topics with debugging and optimisation skills, and understanding real world applications database implementation. How to design reliable, scalable and maintainable applications’ databases. The student will identify which database or repository is the best option according to response time, amount of data, type of data and analysis. On completion of this course, students will feel comfortable understanding any existing application’s database.

4What software skills are you teaching and how well are these tools used in the industry?

The Database component of any web application is a very important part of business requirements implementation. This is supposed to be fast, with optimal storage and retrieval, giving data correctly as expected, following the industry best design practices. It can get very complex for large scale applications. Hence a strong foundation of Database Systems and understanding is essential to get a job and be a competent Back-End/Full stack developer.

5What real world application for the tools and techniques will you teach in this course?

An example of an ecommerce website database will be taught and developed by the students. Proficiency in querying using SQL and in a NoSQL database is what we are targeting in this course. PhpMyAdmin tool will be used for MySQL relational database, MongoDB atlas will be used for NoSQL database and Redis will be used as an example of in memory Database.

6Which companies use these techniques and for what?

All companies creating software use a Database to store their applications data. Those working with microservices also have a single database for each microservice.

7How is your course going to help me in my path to MS or PhD?

Any Masters/PhD program requires experimenting and working with huge amounts of data. At the very least, it is required to know how to organise data in an optimised manner and retrieve data fast to work with it based on the research topic. Knowing Database Management also opens the door to understand organising data for Big Data technologies and research. Hence, the skill of knowing how to store and handle data becomes extremely crucial for further studies in any Engineering domain.

8How is this course going to help me get a job?

This course is extremely essential to gain a job as a Back-End or a Full Stack Developer which is a very hot career option for many years to come as most businesses require web applications for data storage and customer engagement with their high-speed services which work with real time data. This is the skill which is going to be a very important part of project/skill based technical interviews as Web Application Engineers.

9What is the ratio of theory to hands-on practical content?

40% of the course teaches theoretical concepts and 60% of it is hands on coding and testing. Practising is focused on, without which no skill is learnt best.


You Might Also Be Interested In

Related Courses

See all

The Skill-Lync Advantage

See all