MySQL Database Management

Introduction

• Relational Database Management Systems (RDBMS) allow the creation of databases with relations
• RDBMS facilitates storing, retrieving, and manipulating data through queries
• This chapter covers creating, populating, and querying databases using MySQL

SQL

• Structured Query Language (SQL) is used to access and manipulate data in a database management system, offering a special kind of language
• SQL is a popular query language for relational database systems
• The statements are easy to learn, written with descriptive English words, and are case insensitive
• SQL simplifies database interaction
• Users specify what should be retrieved, and SQL handles the process
• SQL offers statements for defining data structure, constraints, manipulating data, and retrieving data in various ways

Installing MySQL

• MySQL is open source RDBMS software available for download
• After installing, the MySQL service should to be started
• The mysql> prompt indicates MySQL is ready for SQL statements

Notes for using SQL

• SQL is case-insensitive
• End all SQL statements with a semicolon (;)
• When entering multi-line SQL, do not end the first line with a semicolon, then enter continues on the next line
• The prompt changes to -> to indicate statement continuation until the final line with a semicolon is entered

Data Types and Constraints in MySQL

• Databases consist of relations, and relations consist of attributes (columns)
• Each attribute must have a data type
• Constraints can be set for each attribute of a relation

Data type of Attribute

• The data type indicates what kind of data can be stored in each attribute
• Specifies the operations that can be performed
• Date, time, numeric and string data types are most commonly used

Commonly used data types

• CHAR(n): Stores fixed-length character data for specified n, which can be between 0-255, padded with spaces if less than n
• VARCHAR(n): Stores variable-length character data can be between 0-65535, actual storage depends on string length
• INT: Stores integer values occupying 4 bytes, for larger values, use BIGINT
• FLOAT: Stores numbers with decimal points, occupying 4 bytes
• DATE: Stores dates in YYYY-MM-DD format, supported range is 1000-01-01 to 9999-12-31

Constraints

• Restrictions on data values within an attribute, used to ensure data correctness
• Defining constrains is not mandatory for every attribute

Commonly used Constraints

• NOT NULL: Ensures the column cannot have a NULL value (missing, unknown or not applicable)
• UNIQUE: All values in the column are distinct
• DEFAULT: Sets a default if no value is specified
• PRIMARY KEY: Uniquely identifies each row/record in a table
• FOREIGN KEY: References the primary key of another table

SQL for Data Definition

• Data requires a relation schema definition before storing
• Defining a schema involves creating a relation, naming a relation, identifying attributes and deciding on data types
• Specify constraints to meet any requirements
• SQL allows statements for defining, modifying, and deleting relation schemas
• These schema operations are part of Data Definition Language (DDL)

Database Creation

• Databases store data in relations or tables
• CREATE statement is used to create a database

CREATE Database

• Use the following syntax: CREATE DATABASE databasename;
• To create a StudentAttendance database, the command is as follows: CREATE DATABASE StudentAttendance;

Notes About Linux

• Database and table names are case-sensitive
• Not case-sensitive in Windows, it is good practice to write names as they were initially
• Databse creation enables management of multiple databases

Selecting a Database

• Use the statement SHOW DATABASES to determine existing databases
• Select with the USE command, so USE StudentAttendance is required to use the StudentAttendance database

Checking Empty Databses

• Initially, created databases are empty
• Confirmed with the SHOW TABLES statement

Creating Tables

• Use the CREATE TABLE statement to define the layout of the tables

Create Table Syntax

• Use the following syntax: CREATE TABLE tablename( attribute1 datatype constraint, attribute2 datatype constraint, ... attributenN datatype constraint);

Points To Keep In Mind

• Degree of the relation (N) is defined by the number of columns
• Give the attribute the name of the column
• Data type is the value type the attribute can hold
• Constraint indicates the restrictions to the values of an attribute
• By default all attributes take NULL except the primary key

Identifying data types and attributes

• The table STUDENT are identified along with their constraints (if applicable)
• Limit maximum students in a class to 100, and roll numbers in a sequence from 1 to 100 i.e. 3 digits which use INT data type
• The Student Name (SName) can vary in length, but assuming 20 digits will be enough, use data type VARCHAR(20)
• The data type for the attribute SDateofBirth is DATE
• If using guardian's 12 digit Aadhaar number as GUID, declare GUID as CHAR (12) since Aadhaar number is of fixed length

Creating tables

• Create tables without specifying constraints along with attribute names for simplification
• Constraints are incorporated in Section 9.4.4

Example 9.1: Create table STUDENT

• Example command is CREATE TABLE STUDENT( -> RollNumber INT, -> SName VARCHAR(20), -> SDateofBirth DATE, -> GUID CHAR (12), -> PRIMARY KEY (RollNumber));

Notes about commands

• Use "," is used to separate two attributes
• Each statement terminates with a semi-colon (;)
• "->" Is an interactive continuation prompt if one enters an unfinished statement, the SQL shell will wait to enter the rest of the statement

Describe Table

• View the structure of an existing table using the DESCRIBE or DESC statement
• Example command is DESCRIBE STUDENT;

Show table

• See available tables in the database using SHOW TABLES

Alter table

• The table structure can be changed after initial creation
• Using the ALTER statment enables the addition, removal and modifications of attribute datatypes and add constraints

A. Add primary key to a relation

• Alter the tables created by adding a primary key to the GUARDIAN relation
• Command example: ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
• Records and Duplicates warnings indicate the values must be checked

Add Primary Key to the ATTENDANCE relation

• The primary key includes the composite key made up of the attributes, AttendanceDate and RollNumber
• Example Command: ALTER TABLE ATTENDANCE -> ADD PRIMARY KEY (AttendanceDate, RollNumber);

B. Add Foreign keys

• Observation points while adding to a foreign key to a relation
• The reference relation must be created and the referenced attribute(s) musy be apart of the primary key of the referenced relation
• The datatypes and size must match the referencing attributes

Alter table syntax

• Example syntax: ALTER TABLE table_name ADD FOREIGN KEY(attribute name) REFERENCES referenced_table_name (attribute name);

Example Adding Key To a Student table

• Add a key to the table "Student"
• Command syntax: ALTER TABLE STUDENT -> ADD FOREIGN KEY(GUID) REFERENCES -> GUARDIAN(GUID);

C. Adding Constraint Unique

• In the GUARDIAN table, the GPhone attribute adds a contraint Unique meaning there must be no two of the same values
• Command syntax: ALTER TABLE table_name ADD UNIQUE (attribute name);

D. Add an attribute to an existing statement

• We use the ADD attribute statement
• Command Synax: ALTER TABLE table_name ADD attribute name DATATYPE;

E. Modifying Datatype of An Attribute

• We can change data types with the ALTER statement.
• Command Syntax: ALTER TABLE table_name MODIFY attribute DATATYPE;
• We may want to change the GAddress from VARCHAR(30) to VARCHAR(40) by using the command: ALTER TABLE GUARDIAN -> MODIFY GAddress VARCHAR(40);

F. Modify an existing constraint

• An attribute with not null values can be changed with an alter statement .
• Command syntax: ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL;
• We must specify the datatype of the alter table while using MODIFY
• Example: We want a 'Student name' (SName) of type STUDENT is not null; so the following code is written: ALTER TABLE STUDENT -> MODIFY SName VARCHAR(20) NOT NULL;

G. Add default value to an attribute

• If the desire of want to define a default value it is done the following way
• Synax: ALTER TABLE table_name MODIFY attibute DATATYPE DEFAULT default_value;
• Specify the datatype of the attribute along with DEFAULT to add and configure a default value.

H. Remove an attribute

• Attributes can be removed with ALTER table
• We give an example, ALTER TABLE table_name DROP attribute; this will remove the respective attribute. Ex: ALTER TABLE GUARDIAN DROP income;

I. Remove primary key from the table

• Alter Table allows to add different keys by writing the following: ALTER TABLE table_name DROP PRIMARY KEY;

Drop Statement

• Removes a table from the system
• Can also remove an entire database and all of its tables
• Should be carried out cautiously

Drop Table Syntax

• DROP TABLE tablename;

Drop Database Syntax

• DROP DATABASE databasename;

SQL for Data Manipulation

• When a table is created, only its structure and not its data exists
• INSERT is used to populate
• DELETE and UPDATE statements are used to update/remove table records which belong to DML

Insertion of Records

• Use INSERT INTO to insert data
• Syntax: INSERT INTO tablename VALUES (value 1, value 2,….);`. Value 1 belongs to attribute 1 and value 2 belongs to attribute 2 so on

Additional notes when inserting

• Attributes don't need to be specified for the insert if statement has the same number of specified values
• Insert, record into a table with the foreign key to make sure other records in reference tables are populated