DBMS Lab : Assignment 1

ASSIGNMENT NO 1

 Title: Study of Open Source Relational Databases : MySQL

Objectives: To learn and understand open source relational databases

Outcomes: Students will be able to learn concepts of relational databases

Hardware requirements: Any CPU with Pentium Processor or similar, 256 MB RAM or more, 1 GB Hard Disk or more.

Software requirements:  Ubuntu 14 Operating System, MySQL

Theory:  A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database, contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient.

Database Applications:

  • Banking: transactions
  • Airlines: reservations, schedules
  • Universities: registration, grades
  • Sales: customers, products, purchases
  • Online retailers: order tracking, cialis pas cher customized recommendations
  • Manufacturing: production, inventory, orders, supply chain
  • Human resources: employee records, salaries, tax deductions

Data Models:

  • Relational model
  • Entity-Relationship data model (mainly for database design)
  • Object-based data models (Object-oriented and Object-relational)
  • Semistructured data model (XML)
  • Other older models:
    • Network model
    • Hierarchical model

 A database is a means of storing information in such a way that information can be retrieved from it. In simplest terms, a relational database is one that presents information in tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database. A Database Management System (DBMS) handles the way data is stored, maintained, and retrieved. In the case of a relational database, a Relational Database Management System (RDBMS) performs these tasks.

A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. Most commercial RDBMS’s use the Structured Query Language (SQL) to access the database, although SQL was invented after the development of the relational model and is not necessary for its use.

MySQL open source RDBMS overview: MySQL is a popular open source relational database management system (RDBMS) choice for web-based applications. Developers, database administrators and DevOps teams use MySQL to build and manage next-generation web- and cloud-based applications. With most open source RDBMS options, MySQL is available in several different editions and runs on Windows, OS X, Solaris, FreeBSD and other variants of Linux and Unix:

  • MySQL Classic Edition, available to only independent software vendors, OEMs and value-added resellers, is designed to be an embeddable database for read-intensive applications.
  • MySQL Community Editionis the free downloadable version of MySQL available under the GNU General Public License (GPL).
  • MySQL Standard Editionis the entry-level RDBMS offering for online transaction processing
  • MySQL Enterprise Editionadds advanced features, management tools (including OEM for MySQL) and technical support.
  • MySQL Cluster Carrier Grade Editionis designed for Web and cloud development.

Data types supported by MySQL open source RDBMS: MySQL data types include numeric types, date and time types, string types (including binary, character and Binary Large Object), and spatial types. Additionally, MySQL will map certain data types from other DBMSes to MySQL data types for easier portability.

Install MySQL on Ubuntu 14.04:

 MySQL is an open-source database management system, commonly installed as part of the popular LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. It uses a relational database and SQL (Structured Query Language) to manage its data. The short version of the installation is simple: update your package index, install the mysql-serverpackage, and then run the included security and database initialization scripts.

Step 1 — Installing MySQL: There are two ways to install MySQL. You can either use one of the versions included in the APT package repository by default (which are 5.5 and 5.6), or you can install the latest version (currently 5.7) by manually adding MySQL’s repository first. You can just use the mysql-server APT package, which just installs the latest version for your Linux distribution.  To install MySQL this way, update the package index on your server and install the package with apt-get.

  • sudo apt-get update
  • sudo apt-get install mysql-server

You’ll be prompted to create a root password during the installation. Choose a secure one and make sure you remember it, because you’ll need it later. Move on to step two from here.

Installing MySQL 5.5 or 5.6

If you want to install MySQL 5.5 or 5.6 specifically, the process is still very straightforward. First, update the package index on your server.

  • sudo apt-get update

Then, to install MySQL 5.5, install the mysql-server-5.5 package.

  • sudo apt-get install mysql-server-5.5

To install MySQL 5.6, install the mysql-server-5.6 package instead.

  • sudo apt-get install mysql-server-5.6

For both options, you’ll be prompted to create a root password during the installation. Choose a secure one and make sure you remember it, because you’ll need it later.

Step 2 — Configuring MySQL: First, you’ll want to run the included security script. This changes some of the less secure default options for things like remote root logins and sample users.

  • sudo mysql_secure_installation

This will prompt you for the root password you created in step one. You can press ENTER to accept the defaults for all the subsequent questions, with the exception of the one that asks if you’d like to change the root password. You just set it in step one, so you don’t have to change it now.

Next, we’ll initialize the MySQL data directory, which is where MySQL stores its data. How you do this depends on which version of MySQL you’re running. You can check your version of MySQL with the following command.

  • mysql –version

If you’re using a version of MySQL earlier than 5.7.6, you should initialize the data directory by running mysql_install_db.

  • sudo mysql_install_db

Step 3 — Testing MySQL: Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.

  • service mysql status

If MySQL isn’t running, you can start it with sudo service mysql start.

For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect to MySQL as root (-u root), prompt for a password (-p), and return the version.

  • mysqladmin -p -u root version

Step 4 — Log in MySQL: Use following command to log in to MySQL

In terminal, write

  • sudo mysql –u root –p

You will be prompted for password. After log in you can see mysql> prompt to execute queries

 Conclusion: In this assignment, we have studied concept of relational databases, MySQL DBMS and steps to install MySQL on Ubuntu O.S.

Roll No. Name of Student Date of Performance Date of Assessment Grade Sign of Student Sign of Faculty

References:

  1. Database System Concepts, 6th Edition by Abraham Silberschatz, Henry F. Korth, S. Sudarshan
  2. https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-14-04

Leave a Reply

Your email address will not be published. Required fields are marked *