We have heard a lot about Student Management Systems, Hospital Management Systems, Hotel Management Systems, and so many other management systems, the term is used for managing the data related to each and every organization. In the past, ledgers and paper works were used to manage the data of these organizations, now it has been changed and typically Database Management Systems(DBMS) are used for management purposes.
A database is a collection of several tables related to a particular system or domain, for example, the student management system will have all the data related to students only and will not include the staff. Likewise, it is applicable for other databases only, in a database typically all the tables are related to one another. Tables are made of rows and columns, the first contains the column headers and the following rows are the data field.
In this tutorial, we’ll manage to learn how to create a full-fledged database using MySQL with all the tables and constraints.
Creating a database in MySQL
Before we get started with the hands-on, let’s see the diagram for the database that we are going to create.
The above is an ER diagram for a student management system, it’s a sample diagram and has four entities, namely, Student, Admin, Teacher, and Course. We’ll be created all four tables within the database.
Now to create a database on a MySQL server, you can use MySQL workbench or command line, We’ll be using MySQL command line for this lecture. Below is the command for creating a new database:
CREATE DATABASE student_management;Code language: SQL (Structured Query Language) (sql)Creating tables in a database
Now that we have created the database let’s create the tables, but before creating we need to access the database where we want to create tables, for that use the below command:
USE student_managment;Code language: SQL (Structured Query Language) (sql)Let’s create our first table of this database, the admin table, with three fields, AID, name, and password. We use CREATE TABLE command to create a table.
CREATE TABLE admin(
AID int(10),
name varchar(30),
password varchar(25),
PRIMARY KEY(AID)
);Code language: SQL (Structured Query Language) (sql)Likewise, let’s create all other tables as well. The next table is the student table, we have five fields, namely, SID, name, course, phone number, and total semesters. The primary key for the student table will be SID as it is unique to each student.
CREATE TABLE student(
SID int(10),
name varchar(20),
course varchar(5),
phone int(10),
semester int(2),
PRIMARY KEY(SID)
);Code language: SQL (Structured Query Language) (sql)The third table is the course table with fields such as CID, cname, fees, and duration of the course, the primary key here is CID.
CREATE TABLE course(
CID varchar(4),
cname varchar(30),
fees int(7),
duration int(3),
PRIMARY KEY(CID)
);Code language: SQL (Structured Query Language) (sql)The last table which we will be creating is of teachers, there are four fields in this table namely TID, tname, course, and education of the teacher. The primary key for this table is TID.
CREATE TABLE teacher(
TID int(10),
tname varchar(30),
course varchar(5),
education varchar(5);
PRIMARY KEY(TID)
);This is how we create a database, remember we can perform any changes on these tables for now as we have created schemas only. Schema is nothing but a structure of a table, and there are no records in any of the tables. We can perform the changes like adding a new column or changing the primary key as well as adding a foreign key to the tables for connecting each and every table together.
Conclusion
In this tutorial, we learned what a database is, how are they useful in day-to-day activities, where are these databases used, and so on. We saw a sample database and created a sample database by ourselves with the help of CREATE command.