MySQL is a main open source database management system. It is a multi-user, multithreaded database management system. MySQL is particularly popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL and PHP. MySQL database is accessible on most important OS platforms. It runs on BSD Unix, Linux, Windows or Mac. Wikipedia, YouTube, Facebook use MySQL. These web sites manage millions of queries each day. MySQL comes in two versions: MySQL server system and MySQL embedded system.MySQL is developed in C/C++.Except for C/C++, APIs exist for PHP, Python, Java, C#, Eiffel, Ruby, Tcl or Perl. It is very necessary to understand the database before studying MySQL. A database is an application that stores the organized collection of records. It can be accessed and manage by the user very easily. It permits us to organize data into tables, rows, columns, and indexes to locate the applicable information very quickly. Each database consists of distinct API for performing database operations such as creating, managing, accessing, and searching the data it stores. Today, many databases available like MySQL, Sybase, Oracle, MongoDB, PostgreSQL, SQL Server, etc. In this section, we are going to focus on MySQL mainly.
TOPICS
ON SQL:
RDBMS
TERMINOLOGY
FEATURES
PRIMARY KEY
FOREIGN KEY
NORMALIZATION
RDBMS
A Relational DataBase Management System (RDBMS) is a software that Enables you to enforce a database with tables, columns and indexes.Guarantees the Referential Integrity between rows of various tables.Updates the indexes automatically.Interprets an SQL query and combines data from various tables.
RDBMS Terminology
- Database − A database is a series of tables, with related data.
- Table − A table is a matrix with data. A table in a database appears like a simple spreadsheet.
- Column − One column (data element) consists of information of one and the same kind, for example the column postcode.
- Row − A row is a group of related data, for instance the data of one subscription.
- Redundancy − Storing data twice, redundantly to make the device faster.
- Primary Key − A primary key is unique. A key value can not show up twice in onetable. With a key, you can only locate one row.
- Foreign Key − A foreign key is the linking pin between two tables.
- Compound Key − A compound key (composite key) is a key that consists of more than one columns, because one column is not sufficiently unique.
- Referential Integrity − Referential Integrity makes sure that a foreign key value constantly points to an existing row.
MYSQL FEATURES
MySQL is a relational database management system (RDBMS) primarily based on the SQL (Structured Query Language) queries. It is one of the most popular languages for accessing and managing the data in the table. MySQL is open-source and free software below the GNU license. Oracle Company supports it.
The following are the most necessary elements of MySQL:
It is secure
MySQL consists of a solid data security layer that protects sensitive data from intruders. Also, passwords are encrypted in MySQL.
It is scalable
MySQL supports multi-threading that makes it easily scalable. It can handle almost any amount of data, up to as much as 50 million rows or more. The default file size limit is about 4 GB. However, we can increase this number to a theoretical limit of 8 TB of data.
Speed
MySQL is considered one of the very fast database languages, backed by a large number of the benchmark test.
High Flexibility
MySQL supports a large number of embedded applications, which makes MySQL very flexible.
Compatible on many operating systems
MySQL is compatible to run on many operating systems, like Novell NetWare, Windows* Linux*, many varieties of UNIX* (such as Sun* Solaris*, AIX, and DEC* UNIX), OS/2, FreeBSD*, and others. MySQL also provides a facility that the clients can run on the same computer as the server or on another computer.
Allows roll-back
MySQL allows transactions to be rolled back, commit, and crash recovery.
Memory efficiency
Its efficiency is high because it has a very low memory leakage problem.
High Performance
MySQL is faster, more reliable, and cheaper because of its unique storage engine architecture. It provides very high-performance results in comparison to other databases without losing an essential functionality of the software. It has fast loading utilities because of the different cache memory.
High Productivity
MySQL uses Triggers, Stored procedures, and views that allow the developer to give higher productivity.
GUI Support
MySQL provides a unified visual database graphical user interface tool named “MySQL Workbench” to work with database architects, developers, and Database Administrators. MySQL Workbench provides SQL development, data modeling, data migration, and comprehensive administration tools for server configuration, user administration, backup, and many more. MySQL has a fully GUI supports from MySQL Server version 5.6 and higher.
PRIMARY KEY IN SQL
Primary Key Constraint is a type of key through which you can uniquely identify every tuple or a record in a table. Every table can have only one primary key but can have more than one candidate keys. Also, every primary key must be unique and should not include any NULL values.
Primary keys are used along with the foreign keys to refer to various tables and form referential integrities. For Table A, a primary key can consist of single or more than one columns.
FOREIGN KEY CONSTRAINT
A foreign key is a type of key used to link two tables in a database. So, a foreign key is an attribute or a series of attributes in one table that refers to the primary key in any other table.
For Example, if Table A and Table B are associated to each other, then if Table A consists of the primary key, this table would be referred to as the referenced table or parent table. Similarly, if Table B consists of a foreign key, then that table is known as the referencing table or child table.
NORMALIZATION
It is the techniques of reducing the redundancy of data in the table and additionally improving the data integrity. Normalization is a way of organizing the data in the database. Normalization entails organizing the columns and tables of a database to make sure that their dependencies are properly enforced through database integrity constraints.
1st
Normal Form (1NF)
In this Normal Form, we tackle the problem of atomicity. Here atomicity means values in the table must not be further divided. In simple terms, a single cell can’t keep more than one values. If a table consists of a composite or multi- valued attribute, it violates the First Normal Form.
2nd Normal Form (2NF)
The first condition in the 2nd NF is that the table has to be in 1st NF. The table also must not include partial dependency. Here partial dependency meansthe suitable subset of candidate key determines a non-prime attribute.
3rd Normal Form (3NF)
The same rule applies as before i.e, the table has to be in 2NF before proceeding to 3NF. The other condition is there should be no transitive dependency for non-prime attributes. That means non-prime attributes (which doesn’t form a candidate key) should no longer be based on other non-prime attributes in a given table. So a transitive dependency is a functional dependency in which X → Z (X determines Z) indirectly, through virtue of X → Y and Y → Z (where it is not the case that Y → X)
RELATED COURSES: