MYSQL: SQL REVISION TOUR (CLASS - XII) PART - 1

 MYSQL: SQL REVISION TOUR

CLASS – XII (PART - 1)

CHAPTER – 5

INTRODUCTION

A database system is basically a computer-based record keeping system. The collection of data, usually referred to as the database, contains information about one particular enterprise. In a typical file-processing system, permanent records are stored in various files. A number of different application programs are written to extract records from files and add records to the appropriate files. But this scheme has a number of major limitations and disadvantages, such as data redundancy (duplications of data), data inconsistency, unsharable data, unstandardized data, insecure data, incorrect data etc. A database management system is answer to all these problems as it provides a centralized control of the data.

Various advantages of database systems are:

Database systems reduce data redundancy (data duplication) to a large extent.

Database systems control data inconsistency to a large extent.

Databases facilitate sharing of data.

Centralized database can ensure data security.

Integrity can be maintained through database.

Relational Data Model:

A data model refers to a set of concepts to describe the structure of a database, and certain constraints (restrictions) that the database should obey.

In relational data model, the data is organized into tables (i.e. rows and columns). These tables are called relations, A row in a table represents a relationship among a set of values. Since a table is a collection of such relationships, It is generally referred to using the mathematical term relation, from which the relational data model derives its name.

Different terms used in relational model:

A non-key attribute, whose values are derived from the primary key of some other table, is known as foreign key in its current table.

MYSQL -  A Popular Relational Database Management System

MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It is downloadable from the www.mysql.org. In a MySQL database, information is stored in Tables. A single MySQL database can contain many tables at once and store thousands of individual records. MySQL provides you with a rich set of features that support a secure environment for storing, maintaining, and accessing data. MySQL is a fast, reliable, scalable alternative to many of the commercial RDBMS available today.

MySQL Database System

MySQL database system refers to the combination of a MySQL server instance and a MySQL database. MySQL operates using client/server architecture in which the server runs on the machine containing the database and clients connect to the server over a network.

The Server (MySQL Server) listens for client request coming in over the network and accesses database contents according to those requests and provides that to the clients.

Clients are programs that connect to the database server and issue queries in a pre-specified format. MySQL is compatible with the standards-based SQL.

Classification of SQL Statements

SQL commands can be divided into following categories.

Data Definition Language (DDL) Commands: Commands that allow you to perform tasks related to data definition e.g. (i) Creating, altering and dropping,   (ii) granting and revoking privileges and roles,  (iii) maintenance commands

Data Manipulation Language (DML) Commands: Commands that allow you to perform data manipulation e.g. retrieval, insertion, deletion and modification of data stored in a database.

Transaction Control Language (TCL) Commands: Commands that allow you to manage and control the transactions (a transaction is one complete unit of work involving many steps), e.g.

1. Making changes to database, permanent

2. Undoing changes to database, permanent

3. Creating savepoints

4. Setting properties for current transactions.

Common MYSQL Data Types

Datatype associates a fixed set of properties with the value. Some common data types are: char, varchar, tinytextm, text, blob, mediumtext, mediumblob, longtext, longblob, tinyint, smallint, mediumint.

Difference between Char and Varchar

The difference between Char and Varchar is that of fixed length and variable length. The Char datatypes specifies a fixed length character string. When a column is given datatypes as Char(n), then MYSQL ensures that all values stored in that column have this length i.e., n bytes. If a value is shorter than this length n then blanks are added, but the size of value remains n bytes.

Varchar on the other hand, specifies a variable length string. When a column is given datatypes as Varchar(n), then the maximum size a value is the column can have is n bytes. Each value that is stored in this column stores exactly as you specify it i.e., no blanks are added if the length is shorter than maximum length n. However, if you exceed the maximum length n then an error message is displayed.

NOTE: This is the notes of MYSQL REVISION TOUR. It is important for every students, who are going to appear in Class - XII CBSE BOARD Exam as well as competitive level exam.

!!! THANK YOU!!!