Using Mysql To Explain Databases Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

A database is a collection of organized and related data. Databases store information in an organized manner in the form of a table. The table structure consists of rows and columns which holds data. A database management system can be used to handle the data in a database.

A database can contain duplicate records in tables if the database is not normalized and tables are not indexed. This can increase redundancy of the data in the database. You can normalize the database and index the tables to eliminate redundancy and enhance the speed of data search and retrieval operations.

In this session, you will learn how to create a database. You will identify the different types of data and how to create a table using different data types. In addition, you will learn how to normalize a database, and implement indexes and referential integrity in a database.

Basic Concepts in MySQL

Before you can use MySQL, there are certain rules that must be adhered to by the developer. This will help you to understand the naming conventions in MySQL. It will also help you to understand, how commands are used in MySQL.

Naming Conventions

While naming a database and its components, such as tables, columns, and so forth you will have to follow certain rules. For example, you will have to ensure that the name does not exceed the specified length or use illegal characters.

Note: MySQL allows reserved words to be used as names. However, these words have to be enclosed in quotes when used as object names.

The following naming conventions are followed by MySQL:

Legal Characters- You can use any alphabet (a-z or A-Z) or digits (0-9) in the name. You can also use characters, such as '_' or '$'. A name can start with a digit but cannot contain only digits because you cannot distinguish it from a number. You cannot use '.' within the name because it acts as the separator. For example, db_name.table_name where db_name is the database name and table_name is the name of the table in the database. You also cannot use the separator characters such as '/' or '\'.

Length of names- MySQL restricts the length of the name of the databases, indexes, or columns up to 64 characters. However, MySQL allows alias names up to 256 characters and compound statement labels are restricted to 16 characters. Examples of compound statement labels include join and conditional operators, such as INNER JOIN, OUTER JOIN, LEFT INNER JOIN, RIGHT OUTER JOIN, FROM, and WHERE.

Note: When you create an alias for a column name in a CREATE VIEW statement, the length must not exceed 64 characters.

MySQL Commands

You can enter SQL commands on a single line or split them across multiple lines. All MySQL commands should be terminated by a semicolon. MySQL waits for a semicolon (;) to execute a command. Alternatively, you can use the '\g' command.

Case Sensitive Conventions

In MySQL, case sensitivity varies with the object in question.

Note: Read more about using reserved words as identifiers at http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

SQL Statements and Keywords

The SQL statements and keywords are not case sensitive. A user can write SQL statements in upper or lower case. Consider the statements below:

SELECT VERSION ( );

select version( );

Both the statements are equivalent. The above commands will return the version of MySQL being used. The following combinations are also valid:

Select VERSION ( );

SeLecT version ( );

Database and Table names

Case sensitivity of databases, tables, and trigger names depend on the operating system. If the underlying operating system is case sensitive, then so are the names of databases, tables, and triggers. For example, Microsoft Windows is not case sensitive and so databases, tables, and triggers names in MySQL running on Microsoft Windows are not case-sensitive. However, Linux is case sensitive and so databases, tables, and triggers names under a Linux installation of MySQL are case-sensitive.

This is because these objects are stored as files on the file system.

Note: You cannot use different cases while referring to the same object in the same SQL statement. For example, if you refer to a database as MyDB, then you cannot refer to it as mydb or MYDB elsewhere in the same statement.

Columns, Column Aliases, Index, Stored Routines, and Events Names

Names of columns, column aliases, index, stored routines, and events are not case sensitive. You can use one of the following SQL statements to display the column 'FNAME' from a SAMPLE table:

SELECT FNAME from SAMPLE ( );

SELECT fname from SAMPLE ( );

Log Files

Log file names are case sensitive.

Creating a MySQL database

MySQL is a relational database management system. You can use MySQL to manage relational databases. A relational database consists of different tables in which data is stored such that there is minimal redundancy. A relational database also contains relationships or links to related tables.

You can create a database if you have the proper privileges.

The syntax for creating a database is:

CREATE DATABASE [IF NOT EXISTS] <dbname>

where,

CREATE DATABASE - adds a new database to the instance of MySQL

IF NOT EXISTS - checks for databases with the same name before adding the new database. MySQL does not allow you to create databases with same names

dbname - specifies a name for the new database

For example, to create a database named EMPLOYEE for storing employee information, such as personal details, qualifications, salary details, and so on, enter the following command at the command prompt:

CREATE DATABASE EMPLOYEE;

Figure 3.1 displays the output of the command.

Figure 3.1: Creating a Database

Figure 3.1 displays the number of rows affected, and the time taken to execute the command.

Working with MySQL Tables

As compared to a DBMS, RDBMS store data in different tables to ensure consistency and faster search and retrieval operations. A table stores data in rows and columns. A field in a table is called a column and row is called a record. A record is also defined as collection of fields. The columns in a table contain different types of data.

Describing Data Types

Data types define the type of the data that will be stored in a column. Data types also define the size and the type of data that can be stored in the column.

MySQL supports different data types to store values. Following are the different categories under which the data can be categorized:

Numeric data types

Date and Time data types

String data types

Complex data types

Numeric Data Type

A numeric data type stores data in number form. For example, a column called Product_Id containing information on the product number can be defined as numeric. There are several numeric data types available in MySQL.

Note: Numbers can be classified as signed and unsigned. A signed number is preceded with negative or positive sign. An unsigned number does not have any sign and is assumed to be positive.

Table 3.1 lists the numeric data types in MySQL.

Numeric Data Type

Storage In Bytes

Description

Synonyms

BIGINT(length)

8

Stores unsigned numbers in the range of 0 to 18,446,744,073,709,551,615, and signed numbers in the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

DECIMAL(length, decimal)

Precision +2

Stores floating point numbers and cannot be unsigned

DEC

NUMERIC

DOUBLE

8

Stores double precision floating point numbers. Range for a negative number is between -1.7976931348623157E+308 and -2.2250738585072014E-308,0. Range for a positive number is between 2.2250738585072014E-308 and 1.7976931348623157E+308

DOUBLE PRECISION

REAL

FLOAT(length, decimal)

4

Stores single precision floating point numbers. Range for a negative number is between -3.402823466E+38 and

-1.175494351E-38,0. Range for a positive number is between 1.175494351E-38 and 3.402823466E+38

INT(length)

4

Stores integer numbers. Range for a signed number is from -2,147,483,648 to 2,147,483,647. Range for an unsigned number is from 0 to 4,294,967,295

INTEGER

MEDIUMINT(length)

3

Stores integer numbers. Range for a signed number is from -8,388,608 to 8,388,607. Range for an unsigned number is from 0 to 16,777,215

SMALLINT(length)

2

Stores integer numbers. Range for a signed number is from -32,768 to 32,767. Range for an unsigned number is from 0 to 65,535

TINYINT

1

Stores integer numbers. Range for a signed number is from -128 to 127. Range for an unsigned number is from 0 to 255

BOOLEAN

Table 3.1: Numeric Data Types in MySQL

String Data Type

A string represents a sequence of characters. A string data type is enclosed in single quotes or double quotes. It enables you to enter alphabets from a-z, A-Z, and numbers from 0-9. For example to store employee names in a column called Ename, you can use the string data type. The number of characters defines the length of a character string.

Table 3.2 lists the string data types supported by MySQL:

Data Type

Storage In Bytes

Description

CHAR (Length)

Length

Stores character data set and can be up to 255 characters

BINARY (Length)

Length

Stores binary byte string up to 255 characters

VARCHAR ()

Length+1

Stores up to 255 characters. Uses one byte to store 255 characters. Uses additional two bytes to if values require more than 255 characters.

VARBINARY

Length+2

Stores values up to 255 bytes.

BLOB

Length+2

Stores large amount of binary data such as images and can be up to 64 kB. Uses an additional byte of storage if values exceed 65,536.

TINYBLOB

Length+1

Stores up to 255 bytes

MEDIUMBLOB

Length+3

Stores text in size up to 16 MB. Uses additional 3 bytes of storage if values exceed the specified length.

LONGBLOB

Length+4

Stores text values whose size exceeds

4 GB. Uses additional 4 bytes of storage if values exceed the specified length.

TEXT

Length+2

Stores up to 64 kB. Uses additional 2 bytes of storage if values exceed the specified length.

TINYTEXT

Length+1

Stores short text values up to 256 bytes

MEDIUMTEXT

Length+3

Stores medium-sized text whose size is up to 16MB. Uses additional 3 bytes of storage if values exceed the specified length.

LONGTEXT

Length+4

Stores large text values whose size exceeds

4 GB. Uses additional 4 bytes of storage if values exceed the specified length.

Table 3.2: String Data Types in MySQL

Date Data Type

A date data type stores date and time information. For instance, you can store the date of birth of employees in date type column. The Date data type column stores date as string value in the default format, 'YYYY-MM-DD' .

Table 3.3 lists the date data types in MySQL.

Data Type

Format

Storage In Bytes

Description

DATE

YYYY-MM-DD

3

Stores a date type of data in the range of January 1,1000 to December 31,9999

DATETIME

YYYY-MM-DD hh:mm:ss

8

Stores date and time. The range for it is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIME

hh:mm:ss

3

Stores time

TIMESTAMP

YYYY-MM-DD hh:mm:ss

4

Stores timestamp

YEAR

YYYY

1

Stores a year in the range of 1901 to 2155

Table 3.3: Date Data Types in MySQL

Complex Data Type

Complex data type is a special data type. It defines the list of possible values that can be inserted in a column. For example, you can assign the SET or ENUM data type to a column where a distinct string value will be chosen from a list of predefined values.

Table 3.4 lists the complex data types in MySQL.

Numeric Data Type

Storage In Bytes

Description

ENUM

1 ,2

Stores one value out of a possible 65535 number of options in a predefined list of possible values. This data type can contain only one distinct string value from the predefined list of values. The values are represented internally as integers. Eg: ENUM('abc','def','ghi')

SET

1,2, 3, 4 0r 8

Stores a list of values from a predefined set of values. Maximum of 64 values can be stored in a SET data type column. The SET data type can contain zero or more values that can be specified in the list.

Table 3.4: Complex Data Types in MySQL

The SET data type is also a string data type. It is termed as complex data type because of its complexity. It is similar to ENUM data type as both data types have predefined sets of strings. The difference in the SET and the ENUM data type is that in the ENUM data type, a string is selected only from a predefined set.

You can use the SET data type to select any one optional value for a field containing a predefined list of values. For example, you can use the SET data type to choose the name of a country from a list of country names, such as Australia, New York, or California.

SET('AUSTRALIA','NEW YORK',CALIFORNIA')

The SET data type uses storage space in the form of bytes to store the number of elements defined in the data type. Table 3.5 lists the storage structure of SET data type depending upon the number of elements:

Number Of Elements

Storage In Bytes

1-8

1

9-16

2

17-24

3

25-32

4

33-64

8

Table 3.5: Storage Structure of SET Data Type

Creating Tables

A relational database contains several related tables. The table name can be up to 64 characters in length. The maximum length of a field name is 64 characters. Also, you can specify the table type or the storage engine while creating the table.

MySQL supports two types of tables:

Transaction-safe tables: Transaction-safe tables enable you to execute transactions or data manipulation commands without losing data. You can undo the changes made to transaction-safe tables.

Non-transaction safe tables: Non-transaction safe tables also allow you to execute transaction or data manipulation commands. However, you cannot undo the changes made to non-transaction safe tables because the changes are permanent.

Following are the features of transaction-safe tables:

Provides safety from data loss. MySQL can recover data, from a backup and the transaction log, in case of a crash or hardware failure.

Enables to combine and execute statements using the COMMIT command

Enables to undo the changes made to the data using the ROLLBACK command

Enables concurrency while simultaneously reading data from tables

Examples of transaction-safe tables are InnoDB and BDB

Following are the features of non-transaction safe tables:

Faster than the transaction-safe tables as no transaction overhead is required

Require less disk space

Require less memory for updates

Storage Engines

MySQL supports storage engines for different table types. Storage engines handle both transaction-safe and non-transaction safe tables. Table 3.6 lists the storage engines supported in MySQL:

Storage Engine

Feature

MyISAM

Enables high-speed storage, retrieval, and search characteristics. It is the default storage engine to handle tables.

InnoDB

Enables commit, rollback, and crash recovery characteristics to secure data

MERGE

Enables to create tables with identical column and index information

MEMORY (HEAP)

Enables to create tables that store contents in the memory. MEMORY tables can have up to 64 indexes per table, 16 columns per index, and a maximum key length of 3072 bytes.

EXAMPLE

Enables to customize storage engines

FEDERATED

Provides access to data from tables in remote databases without copying data to the local server. This engine does not create a replica or a copy of the database or tables to the local server while executing a query on the remote server.

ARCHIVE

Enables to store large amount of data without indexing

CSV

Enables to store data in text files using comma-separated values

BLACKHOLE

Enables to accept data without storing. Tables that have this storage engine will always return an empty result. This storage engine can be used in distributed database design where data is automatically replicated, but not stored locally.

Table 3.6: Storage Engines in MySQL

You will use the CREATE command to add a new table to a database.

The syntax for CREATE command is:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name create_clause,...) table_options] [IGNORE [REPLACE] select]

where,

CREATE TABLE - adds a new table to the database

tbl_name - specifies a name for the table

Table 3.7 lists the description for the options in the CREATE TABLE syntax:

Options

Description

TEMPORARY

Table exists as long as the current user is connected

IF NOT EXISTS

Creates table only if it does not exists in the database

create_clause

Defines column

table_options

Creates different types of tables such as ISAM, INNODB, and so on

IGNORE

If table already exists system ignores and does not replace the previous table

REPLACE

Replaces the table if a table with the same name exists in the database

Select

Enables to copy records from an existing table

Table 3.7: CREATE TABLE Options

The syntax for create_clause in the CREATE TABLE command is:

column type [NOT NULL | NULL][DEFAULT value][AUTO_INCREMENT][PRIMARY KEY][REFERENCE]

where,

column type - defines the data type for the column

Table 3.8 lists the options in the create_clause.

Options

Description

AUTO_INCREMENT

Specifies that the column value must be auto incremented. This option works only if the columns contain positive values.

DEFAULT value

Inserts the specified value when no value for that column is entered. The default value has to be a constant. The values cannot be a function or expression.

NOT NULL

Specifies that a column cannot contain a null value. When a null value is inserted, the system prompts with an error message.

NULL

Specifies that the column can contain null values

PRIMARY KEY

Defines a column as the primary key while creating a table.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.