All you need to know about lessons Learned In My Basics of MySQL.

I will first go with what is a database, it is a holder where data can be easily collected in an orderly way, or it is an organized collection of data stored and accessed electronically thus managing and manipulating these data is easy.

Small databases can be stored on a file system while large databases are hosted on computer clusters or cloud storage.

What is a Database Management System?

Is software where you can store, retrieve, define and manage your data in a database.

These serve as an interface between an end user and a database allowing a user to create, read, update, and delete data in the database.

Importance of a DBMS.

1. Improved efficiency. They offer an efficient way of handling large amounts and multiple types of data, the ability to access this data efficiently allows one to make informed decisions quickly.

2. Versatility. They can easily be accessed on a computer, tablets, and even mobile phones.

3. Multi-access. It makes it possible for multiple users to access the same database from different places in different ways to fulfill different intents. E.g., For a teacher filling in grades for their students which are to be used for grading, the same information can be accessed by the deputy academics.

4. Creating an organized working environment. Offers a way to create a smooth more organized working environment, and provides an easy way to access, update, as well as process data stored in the database.

5. There is improved data sharing and data security.

Most used DBMS include.

• MySQL

• SQL Server

• MongoDB

• Oracle Database

• PostgreSQL

• Maria DB

• SQL lite

Types of Data Management Systems. (DBMS)

• Hierarchical

• Network

• Relational

• Object-Oriented

A. Hierarchical Database Model.

In this database model, data is organized in a tree-like structure, data is stored as records that are connected through links.

In this case, it is structured in a free-like manner where the data is stored in a top-down or bottom-down order represented by the parent-child relationship. A parent can have many children whereas the child, he/she has only one parent.

B. Network Database Model.

Is a database model that allows multiple records to be linked to the same owner file.

Here we see that all the children are allowed to have multiple parents and also it helps to solve the more complex models i.e., orders, many too many relationships.

Entities are organized in a graph manner which has an option to be accessed through several paths.

C.Relational Database Model.

The relational model means that the logical data structures, data tables, views, and indexes, are separate from the physical storage structures.

The separation means that the database administrators can manage physical data storage without affecting access to that data as a logical structure.

It is mostly used as a DBMS model because of its flexibility and easy-to-implement nature.

It is worked with on the normalization of data. It stores data in fixed structures and manipulates it using SQL. They are table-oriented.

D. Object-Oriented Database Model.

Is a database management system in which information is represented in the form of objects.

In this way, data is stored in the form of objects. It maintains structure which is known as classes and displays the data within that.

It describes the database as a collection of objects which also stores both the values and operations.

RELATIONAL DATABASE MANAGEMENT SYSTEMS.

It is a type of DBMS that stores data in a row-based table structure that connects related data elements. An RDBMS includes functions that maintain the security, accuracy, integrity, and consistency of the data.

RDBMS is used for a database management system. The concept is based on the relational model as introduced by E.F. Codd.

The relationship between data files is relational in RDBMS. They connect the data and the different files by using common data numbers or maybe using key concepts.

Properties of Relational Database Management System.

• Values are atomic.

• All of the values in a column have the same data type.

• Each row is unique.

• The sequence of columns is insignificant.

• The sequence of rows is insignificant.

• The column has a unique name.

• Integrity constraints maintain data consistency across multiple tables.

KEY CONCEPT IN RDBMS.

Is either a single column or attribute or a group of columns that can uniquely identify rows or tuples in a table.

In simple terms, a key refers to a set of attributes that help us identify the unique rows from the table.

It helps to establish relationships among the tables as well as ensure that there are no rows with duplicate information.

Types of keys in RDBMS.

• Primary key

• Super key

• Candidate key

• Alternate key

• Composite key

• Foreign key

- Primary key.

Is a key in a relational database that is unique for each record. A technique used to classify unique rows in a table.

• It is a unique identifier such as a phone number, id number

• Does not contain null values.

• A value used should be unique.

• Are not always to be a single attribute/column.

• It can be a set of more than one attribute/column.

- Super key.

It's a single key or a group of multiple keys that can uniquely identify tuples in a table. It is a set of one or more columns or attributes to uniquely classify rows in a table.

It contains multiple attributes that may not be able to independently identify tuples in a table, but when grouped with certain keys, they can identify tuples uniquely.

• It is a superset of a candidate key.

• Student_Id

• Student_Id, Name,

In the student table, the name of the student can be the same but their Student_Id can't be the same, so this combination can be a key.

- Candidate key.

A single key or a group of multiple keys uniquely identifies rows in a table.

It's a set of one or more columns or attributes to uniquely classify rows in a table.

• All remaining attributes or columns except for the primary key are considered as the candidate key.

• The candidate key is as strong as the primary key.

- Alternate key.

Is a key that has not been selected as the primary key but is a candidate key. However, it is considered a candidate key for the primary key.

A candidate key not selected is called an alternate key or secondary key.

Out of all candidate keys, only one gets selected for the primary key, and the rest keys are known as alternate or secondary keys.

• Candidate key

• Student_Id

• Passport number

• License number

- Composite key.

Is a combination of two or more columns in a table that can be used uniquely identify each row in the table.

Consist of greater than one attribute to uniquely classify rows or records or tuples in a table.

• None of the columns can perform as a primary key.

• The combination of a key can be considered a composite key.

- Foreign key.

Set of attributes in a table that refers to the primary key of another table.

These are the columns of a table that refers to the primary key of another table and basically, they act as a cross-reference between tables.

NORMALIZATION TECHNIQUE.

Normalization is the process to eliminate data redundancy and enhance data integrity to the table. It is an act of organizing data to avoid duplication and redundancy.

Importance.

• Helps to minimize duplicate data.

• To minimize or avoid data modification.

• To simplify queries.

Types of Normalization.

1.First Normal Form (1NF)

2.Second Normal Form (2NF)

3.Third Normal Form (3NF)

4.Boyce & Codd Normal Form (BCN`)

1. First Normal form. (1NF)

Is a property of a relation. Each set of columns should have a unique value which helps to prevent using multiple columns to fetch the same row.

Each should contain a primary key that indicates that all rows as unique data. The primary key is usually a single column, but if needed, then more than one column can be combined to create a single primary key.

2. Second Normal Form. (2NF)

In the 2NF, relational must be in 1NF, where it cannot be able to reduce the redundancy.2NF follows that there will not be any partial redundancy of any column on the primary key, it follows the concept of fully functional dependency.

3. Third Normal Form (3NF)

A relation of a table will be in 3NF if it is already in 2NF and does not contain any transitive partial dependency. It helps to reduce data duplication and supports achieving data integrity.

If a table has no transitive dependency for non-prime attributes, then the relationship should be in 3NF.

4. Boyce & Codd Normal Form (BCNF)

It is the advanced version of 3NF, stricter than 3NF.To use the law of BCNF, we need to make sure our data is already in 3NF.

WHAT IS MYSQL?

Structured Query Language. Is the language to communicate with the databases. Commands help you to store, process, analyze and manipulate databases. Helps to access and manipulate your data.

Relational databases store data in the form of tables that can be easily retrieved, managed, or updated.

Table features of SQL.

• SQL lets you access any data within the relational database.

• It is very fast in retrieving a large amount of data very efficiently.

• SQL is versatile as it works with database systems i.e., Oracle, MySQL, etc.

• Helps you manage databases without knowing a lot of coding.

Application of SQL.

1. Used to create databases, define their structures, implement them, and lets you perform many functions.

2. Used for maintaining an already existing database. It is a powerful language for entering data, modifying data, and extracting data from a database.

3. It is extensively used as a client-server language to connect the front end with the backend thus supporting the client-server architecture.

4. When deployed as a Data Control Language (DCL), it helps protect your database from unauthorized access.

DATA TYPES.

Is an attribute that specifies the type of data that the object can hold.

COMMANDS IN MYSQL.

These are instructions coded into SQL statements which are used to communicate with the database to perform specific tasks, functions, and queries with data.

Types of commands.

1. Data Definition Language (DDL)

2. Data Query Language. (DQL)

3. Data Manipulation Language (DML)

4. Data Control Language. (DCL)

5. Transaction Control Language (TCL)

A. Data Definition Language (DDL).

It is a standardized language with commands to define the storage groups (serogroups), different structures, and objects in a database.

It is used in a generic sense to refer to any language that describes data. DDL structures create, modify, and remove database objects such as tables, indexes, and serogroups.

Helps to define the database schema.

Deals with the description of the database and have the power to deal with creating and modifying the structure of the object.

How it works.

• Create. It is used to create; it has objects like

• Table

• Index

• Function

• Views

• Store procedures

• Triggers

• Drop, which is used to delete objects in the database.

• Alter, is used to alter the structure of the database.

• Truncate,the command which is used to remove all the records from the table which includes all the memory allocated for the records is removed.

• Comment used to add comments in the data dictionary.

• Rename, is used to rename an object existing in the database.

B. Data Manipulation Language. (DML)

The programming language is used for adding, deleting, and modifying data in the database.

Handles all the data manipulation parts. Includes most important parts of the SQL,

Represents a collection of programming languages used to make changes in the database i.e. CRUD operations to:

• Create

• Read

• Update

• Delete

• Insert

• Select

• Insert,used to insert data in the table.

The update helps to update existing data within a table.

• Delete, to delete records from a database table.

C. Data Query Language. (DQL)

Used to perform queries on the data and information and is composed of COMMAND statements only.

Used to make queries on the data within schema objects. The main focus of the DQL command is to get some schema relation based on the query passed into it.

Select, the command used to retrieve all the data from the table.

D. Data Control Language. (DCL)

Consists of statements that control security and concurrent access to table data.

Deals with the rights and permission of the database. Works with the controlling part of the data.

Grant, and provide users access privileges to the database.

Revoke helps to withdraw the user's access privileges given by using the grant command.

E. Transaction Control Language. (TCL)

Are commands used for managing and controlling the transactions in a database to maintain consistency?

• Commit, commits a transaction.

• Rollback rollbacks a transaction for any error occurrences.

The set transaction specifies the characteristics of the transaction.

These commands are used for maintaining the consistency of the database and for the management of transactions made by the DML command.

A transaction is a set of SQL statements that are executed on the data stored in the DBMS.

FILTER RECORDS IN MYSQL.

SQL filters are text strings that you use to specify a subject of the data items in an internal or SQL database data type.

GROUP BY statement groups records into summary rows and returns one record for each group.

Select column_names

From table_name

Where(condition)

Group by column_names

Order by column_names

WHERE clause (for SQL database and internal data types) provides a set of comparisons that must be true for a data item to be returned. It is used to filter the record from the data. it is used to extract only those records that fulfill a specified condition.

HAVING clause operates on ground records and returns rows where aggregate function results matched with given conditions only. Enables you to specify conditions that filter which group results appear in the results.

Select column_names

From table_name

Where(condition)

Group by column_names

Having (condition)

Order by column_names

OPERATORS IN MYSQL.

Are combined with the where clause to filter the data.

• And

• Or

• Not

• In

• Between

• And Returns a record if all the conditions are TRUE which are separated by AND.

• Or Returns a record if any conditions are TRUE which are separated by or.

• In Can contain multiple values n a where clause. It is used as a shortcut for multiple OR condition

• Between. Helps to select values within a given range and those values can be numbers, texts, or dates.

PATTERN MATCHING IN MYSQL.

Enables you to use _ to match any single character and %.to match an arbitrary number of characters including zero characters.

In MYSQL, SQL patterns are case insensitive by default.

Like operators are used with WHERE CLAUSE to find the pattern from the database.

NULL VALUES IN MYSQL

Is a field with no values.

ENTITY RELATIONSHIP DIAGRAMS (ERD)

Describes the relationship of entities that need to be stored in a database.

It is mainly a structural design for the database, a framework using specialized symbols to define the relationship between entities.

ERD is created based on three main components i.e.

a) Entities

b) Attributes

c) Relationship

Why we use Entity Relationship Diagrams.

Helps to conceptualize the database and helps us to know which fields need to be embedded for a particular entity.

Gives a better understanding of the information to be stored in a database. Reduces complexity and saves time which allows you to build a database quickly (blue point)

Helps to describe the elements using Entity-Relationship Model.

Components of Entity-Relationship Model.

a) Entity

• Weak entity

• Attribute

b) Key

• Composite

• Multivalued

• Derived

c) Relationship

• One to one

• One to many

• Many to one

• Many to many

A. ENTITY

Can either be a living or non-living component which is distinguishable from other objects in the real world.

It is shown cased as a rectangle in an ERD.

EG student study course, here both student and course are entities.

• Weak Entity.

Is an entity that cannot be uniquely identified by its attributes alone. Therefore, it must use a foreign key in conjunction with its attributes to create a primary key.

An entity that relies upon another entity is called a weak entity. It is showcased as a double rectangle in the ERD.

• Attribute.

Describes the properties of the entities. Is represented as oval in the ERD.

B. KEY.

Is used to denote the property that uniquely identifies an entity and which is mapped to the primary key field in a database.

Uniquely identifies an entity from an entity set. The text of the key attribute is underlined.

• Composite attributes.

Is an attribute that is composed of other attributes where it can be split into two components. Eg an address can be further split into house number, street number, city, state, country, and pin code.

It is represented with an oval and that attribute is rather connected with other attributes.

• Multivalued attributes.

An attribute that can possess more than one value. It can have more than one value associated with the key of the entity. E.g., a large country can have many divisions, possibly in different cities.

The double oval is used to represent a multivalued attribute.

-Derived attributes.

An attribute that can be extracted from other attributes of the entity. I.e., the values are calculated from other attributes e.g. date_of_birth and age, we derive the value of age with the help of the date_of_birth attribute.

In ERD, a derived attribute is represented by a dashed oval.

C. RELATIONSHIP.

Showcased by a diamond shape in ERD. Shows the relationship among entities.

-One-to-One Relationship.

When a single element of an entity is associated with a single element of another entity, that is called one to one relationship. E.g., a student has one identification card and an identification card is given to one student.

-One to Many Relationships.

When a single element of an entity is associated with more than one element of another entity, that is called many relationships. E.g., a customer can place many orders but an order cannot be placed by many orders.

*-Many to One Relationship.

When more than one element of an entity is related to a single element of another, then it is called many to one. E.g., a student enrolls for only one course, but a course can have many students.

-*Many to Many Relationships.

When more than one element of an entity is associated with more than one element of another entity, that is called many to many relationships. E.g., an employee can be assigned to many projects and a project can have many employees.

How to draw an ERD.

1. First, identify all the entities, embed all the entities in a rectangle, and label them properly.

2. Identify the relationship between the entities and connect them using a diamond in the middle illustrating the relationship. Don't connect relationships.

3. Connect attributes for entities and label them properly.

4. Eradicate redundant entities or relationships.

5. Make sure your ERD supports all the data provided to design the database.

6. Make effective use of colors to highlight key areas in your diagrams.

SUBQUERY IN SQL

A subquery is a select query that is enclosed inside another query. The inner select query is used to determine the results of the outer select query.

Types of subqueries.

1.Subqueries with the SELECT statement.

2.Subqueries with the UPDATE statement.

3.Subqueries with the DELETE statement.

4.Subqueries with the INSERT statement.

Subqueries are majorly used with the SELECT statement.

The INSERT uses data from the subquery to insert into another table.

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

The subquery can be used in conjunction with the DELETE statement.

HOW TO BECOME A DATABASE DEVELOPER.

A database developer is a person who is accountable for creating and managing huge data assets of a company.

Responsibilities of an SQL Developer.

1. All databases have a strategic structure and logic behind how data is stored and retrieved.

2. An SQL developer designs the databases accordingly for the businesses i.e., data model.

3. It is the SQL developer who is responsible for fixing the general issues of the database after it is created and deployed.

4. Creates optimized SQL queries and refines the existing ones to extract information from the database.

5. Run several diagnostic tests to keep a check on the server and the database.

6. Gather client requirements and identify the features that the database owners want.

7. Backup and restore data for clients.

8. They perform tasks like data management and data migration.

Skills required.

• Good knowledge of SQL commands and functions.

• Programming

• Excellent understanding of various databases.

• Know the Integration of databases with data visualization software.

• Critical thinking and problem-solving skills.

How to become one.

• Get hands-on experience with SQL

• Get certified.

• Work on projects.

• Build a portfolio.

• Apply for jobs.

---