Database basics (PK/FK/Constraints/CRUD) in DB Browser and ER diagram in MySQL Workbench

1. Introduction to DBMS

Definition and Purpose of DBMS: A Database Management System (DBMS) is software that interacts with end-users, applications, and the database itself to capture and analyze data. Its primary purpose is to provide a way to store and retrieve database information that is both convenient and efficient.
Types of DBMS: There are mainly four types of DBMS:
1. Hierarchical DBMS: This type of DBMS employs a “parent-child” relationship of storing data. This type of DBMS is rarely used nowadays. It’s structure is like a tree with nodes representing records and branches representing fields.
2. Network DBMS: This type supports many-to-many relations. It allows a child to have multiple parents and makes it possible to link records in a network or mesh structure.
3. Relational DBMS (RDBMS): This type of DBMS defines database relationships in terms of tables, also known as relations. Unlike a network DBMS, an RDBMS does not support many-to-many relationships. It’s currently the most widely used DBMS type.
4. Object-Oriented DBMS (OODBMS): This type supports storage of new data types. The data can be presented as a collection of objects, as in programming languages like Java, C++, etc.
Brief overview of SQL and its importance: SQL (Structured Query Language) is a standard language for managing and manipulating databases. It is used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, etc. Every DBMS platform, to some extent, is compatible with SQL, making it a universal language for database operations.

2. Understanding Databases

What is a Database?: A database is an organized collection of data stored and accessed electronically. Databases can be vast and complex, and they can be diversified in their application, serving functions from flight booking systems to online retail to managing city power grids.
Elements of a Database: A Database is made up of several key components:
- Table: This is where data is stored within the database. A table consists of rows (records) and columns (fields).Records: These are also known as rows. Each record in a table holds an individual piece of data in a database.Fields: These are also known as columns. Each field in a table represents some attribute of the data stored in records. Here’s a simple representation of a database table:

In this example, “Name”, “Age”, and “Email” are the fields. The data below them are the records.

Getting Started with DB Browser and MySQL Workbench

Installation and Setup
- DB Browser: DB Browser for SQLite is a high-quality visual open-source tool to create, design, and edit database files compatible with SQLite. To install DB Browser, you need to download the appropriate version from the official website according to your operating system. After downloading, you can install it like any other software. The installation is pretty straightforward:
  - Windows: Run the installer (.exe file) and follow the instructions
  - MacOS: Open the downloaded .dmg file and move the DB Browser for SQLite to the Applications folder
  - Linux: You can install it via terminal using package managers like apt, yum, or dnf. For example, on Ubuntu or Debian, you can use sudo apt-get install sqlitebrowser
- MySQL Workbench: MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and more. To install MySQL Workbench, download the latest version from the official MySQL website. Again, the installation process varies depending on your OS:
  - Windows: Run the installer (.msi file) and follow the instructions
  - MacOS: Open the downloaded .dmg file and follow the instructions
  - Linux: Use the package manager of your distribution for installation. For example, on Ubuntu or Debian, use sudo apt-get install mysql-workbench
Overview of the Interfaces
- DB Browser When you open DB Browser, you will see a simple and intuitive interface. Here are some key areas:
  - Toolbar: The toolbar provides shortcuts to commonly used options like “New Database”, “Open Database”, “Write Changes”, etc.
  - Database Structure: When you open a database, this tab will show the tables in the database and the fields in each table.
  - Browse Data: In this tab, you can view and modify the data in the tables.
  - Execute SQL: Here you can write and run SQL queries directly.
Creating a New Database and Tables in DB Browser
- New Database: To create a new database, click on “New Database” in the toolbar or in the File menu. You will be asked to choose a name and location for the database file.
- New Table: Once you’ve created a database, you can create a new table. Go to “Database Structure” tab, and click on “New Table”. A dialog will open where you can enter the table name and define the fields (columns). For each field, you can specify the name, type (like INTEGER, TEXT, DATE, etc.), and constraints (like PRIMARY KEY, NOT NULL, UNIQUE, etc.). After entering all the details, click on “OK” to create the table.

Tables in DBMS

What is a Table? In a relational database, a table (also called a relation) is a set of data elements (values) that is organized using a model of vertical columns (identifiable by name) and horizontal rows. A table has a specified number of columns, but can have any number of rows. Each row is identified by the values appearing in a particular column subset which has been identified as a unique key index.
Creating Tables in DB Browser Let’s consider an example of a table that represents ‘Students’. This table will include student details such as StudentID, FirstName, LastName, DateOfBirth, and Grade. Here’s the SQL statement to create this table:

  CREATE TABLE Students (
      StudentID INTEGER PRIMARY KEY,
      FirstName TEXT NOT NULL,
      LastName TEXT NOT NULL,
      DateOfBirth DATE NOT NULL,
      Grade TEXT
  );

In DB Browser, you can execute this SQL statement in the “Execute SQL” tab to create the table.

Elements of a Table: Rows (Records) and Columns (Fields) A table is made up of rows and columns. Each column in a table is designed to store a certain type of information, like numbers, texts, or dates. Columns are also known as fields. Each row in a table represents a set of related data, and every row in the table has the same structure. For example, in the Students table, each row represents a student, and the columns represent details about the student.

Data Types in SQL

Importance of Data Types Data types are essential because they determine what type of data can be stored in a field. For instance, if you want to store dates in a field, you choose the DATE data type for that field.
Common Data Types: INT, VARCHAR, DATE, BOOLEAN, etc.
INT: For integer numbers.
VARCHAR(n): For strings of text. ‘n’ is the maximum number of characters.
DATE: For dates.
BOOLEAN: For Boolean values true or false.
Practical examples of using different data types Here are examples of these data types being used in our Students table:
StudentID uses the INT data type because it will be storing integer values.
FirstName and LastName use the VARCHAR data type to store strings of text.
DateOfBirth uses the DATE data type to store date values.
And, suppose we want to add a field PassedExam that shows whether the student passed an exam or not, we can use the BOOLEAN data type.

Here’s an example of inserting some data into the table using SQL:

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, Grade) VALUES
(1, 'John', 'Doe', '2005-04-18', '7th'),
(2, 'Jane', 'Doe', '2006-05-20', '6th'),
(3, 'Alice', 'Smith', '2005-11-12', '7th'),
(4, 'Bob', 'Johnson', '2005-01-08', '7th'),
(5, 'Charlie', 'Brown', '2006-08-22', '6th'),
(6, 'Eve', 'Davis', '2005-07-02', '7th');

This SQL statement inserts 6 rows of data into the Students table. You can run this in DB Browser in the “Execute SQL” tab. After executing, you can go to the “Browse Data” tab to view the data in the table.

In order to delete a row from the table we can use, like:

DELETE FROM Students WHERE StudentID=7;

For dropping the table:

DROP TABLE Students;

Primary Keys

Definition and Importance of Primary Keys A primary key is a field or combination of fields in a table that uniquely identifies each row/record in that table. It is a fundamental aspect of relational databases because it provides a means of uniquely identifying each record, which in turn allows for relationships to be set between tables. The primary key must contain unique values, and it cannot contain NULL values.
How to Set a Primary Key in DB Browser When creating a table in DB Browser, you can specify a primary key in the ‘Create Table’ dialog. Or, if you’re writing the SQL statement, you can use the PRIMARY KEY keyword to set a primary key. Here’s an example:

  CREATE TABLE Students (
      StudentID INTEGER PRIMARY KEY,
      FirstName TEXT NOT NULL,
      LastName TEXT NOT NULL,
      DateOfBirth DATE NOT NULL,
      Grade TEXT
  );

In this table, StudentID is the primary key.

Foreign Keys and Relationships

Definition and Importance of Foreign Keys A foreign key is a field or collection of fields in a table that is used to establish a link between the data in two tables. The foreign key in one table points to the primary key in another table. Foreign keys play an essential role in database relationships, especially in maintaining the integrity of the data.
Types of Relationships: One-to-One, One-to-Many, Many-to-Many

One-to-One: Each row in one database table is linked to 1 and only 1 other row in another table.
One-to-Many: Each row in the table can be related to many rows in the relating table. This allows frequently used information to be saved only once in a table and referenced many times in all other tables.
Many-to-Many: One or more rows in a table can be related to 0, 1 or many rows in another table and vice versa.

How to Set Foreign Keys and Define Relationships in DB Browser To create a foreign key, you can use the FOREIGN KEY keyword in your CREATE TABLE statement. Here’s an example where we have a second table Courses, and each student can be enrolled in one course. The CourseID in the Students table is a foreign key that refers to the CourseID in the Courses table.

  CREATE TABLE Courses (
      CourseID INTEGER PRIMARY KEY,
      CourseName TEXT NOT NULL
  );

  CREATE TABLE Students (
      StudentID INTEGER PRIMARY KEY,
      FirstName TEXT NOT NULL,
      LastName TEXT NOT NULL,
      DateOfBirth DATE NOT NULL,
      Grade TEXT,
      CourseID INTEGER,
      FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
  );

This sets up a one-to-many relationship: each course can have many students, but each student is enrolled in one course.

For the sake of example, let’s assume we have the following data for Courses table:

  INSERT INTO Courses (CourseID, CourseName) VALUES
  (1, 'Mathematics'),
  (2, 'Science'),
  (3, 'English'),
  (4, 'Social Science'),
  (5, 'Physical Education');

And we will use the same student data as before, just adding the CourseID:

  INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, Grade, CourseID) VALUES
  (1, 'John', 'Doe', '2005-04-18', '7th', 1),
  (2, 'Jane', 'Doe', '2006-05-20', '6th', 2),
  (3, 'Alice', 'Smith', '2005-11-12', '7th', 1),
  (4, 'Bob', 'Johnson', '2005-01-08', '7th', 3),
  (5, 'Charlie', 'Brown', '2006-08-22', '6th', 2),
  (6, 'Eve', 'Davis', '2005-07-02', '7th', 1),
  (7, 'Frank', 'Miller', '2006-02-24', '6th', 4),
  (8, 'Grace', 'Lee', '2005-03-19', '7th', 5),
  (9, 'Henry', 'Garcia', '2005-10-07', '7th', 2),
  (10, 'Ivy', 'Wilson', '2006-07-15', '6th', 3);

Note that in practice, inserting data into tables with foreign key relationships requires care: we must ensure that a record that a foreign key refers to is already in the table (for example, a course must exist before a student can enroll in it).

Constraints in DBMS

What are Constraints? Constraints in DBMS are predefined rules and restrictions applied to columns in a table to limit the type of data that can be inserted into tables. Constraints ensure the accuracy and reliability of the data in the table and can be column level or table level.
Types of Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT
NOT NULL: This constraint ensures that a column cannot have NULL value.
UNIQUE: This constraint ensures that all values in a column are different.
CHECK: This constraint ensures that all values in a column satisfy certain conditions.
DEFAULT: This constraint provides a default value for a column when none is specified.
How to Apply Constraints in DB Browser When creating a table in DB Browser, you can specify constraints for each field in the ‘Create Table’ dialog. Or, if you’re writing the SQL statement, you can use the constraint keywords (NOT NULL, UNIQUE, CHECK, DEFAULT) in the CREATE TABLE statement. Here’s an example:

  CREATE TABLE Students (
      StudentID INTEGER PRIMARY KEY NOT NULL,
      FirstName TEXT NOT NULL,
      LastName TEXT NOT NULL,
      DateOfBirth DATE NOT NULL,
      Grade TEXT CHECK (Grade IN ('6th', '7th', '8th', '9th', '10th', '11th', '12th')),
      CourseID INTEGER DEFAULT 1,
      FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
  );

In this table, StudentID, FirstName, LastName, and DateOfBirth have the NOT NULL constraint, so they must have values. Grade has a CHECK constraint, so it must be a value from 6th to 12th grade. CourseID has a DEFAULT constraint, so if no value is provided, it will be 1.

CRUD Operations

Understanding CRUD: Create, Read, Update, Delete CRUD is an acronym for the four basic operations you can perform on data in a database: Create, Read, Update, and Delete.
Create: This refers to inserting new records into a table.
Read: This refers to retrieving or reading data from a table.
Update: This refers to modifying or updating existing records in a table.
Delete: This refers to removing records from a table.

Performing CRUD Operations in DB Browser

Create: You can create a new record using the INSERT INTO statement. For example:

INSERT INTO Students (FirstName, LastName, DateOfBirth, Grade, CourseID) VALUES ('Jacob', 'Taylor', '2006-06-30', '6th', 3);

Read: You can read data using the SELECT statement. For example, to select all students in the 7th grade:

SELECT * FROM Students WHERE Grade = '7th'

If we like to see certain columns from the Students table, then we replace * with those column names separated by comma. For example:

SELECT FirstName, LastName FROM Students WHERE Grade = '7th'

Now, if we like to see FirstName, LastName and CourseName of the 7th grade students, we need to add another WHERE clause which will combine the two tables together using the foreign key CourseID:

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students WHERE Grade = '7th' AND Students.CourseID = Courses.CourseID;

Update: You can update records using the UPDATE statement. For example, to change a student’s grade:

UPDATE Students SET Grade = '7th' WHERE StudentID = 1;

Delete: You can delete records using the DELETE statement. For example, to delete a student:

DELETE FROM Students WHERE StudentID = 1;

Note that for all these operations, especially Update and Delete, it’s crucial to be careful and double-check the conditions in your WHERE clause, as incorrect conditions can lead to the wrong data being updated or deleted.

Exporting Database from DB Browser and importing in MYSQL Workbench

In DB Browser for SQLite, exporting the database into a format that can be read by MySQL Workbench, like SQL, is relatively straightforward.

Export Database from DB Browser

Open DB Browser and open your database.
Click on “File” in the top menu and then “Export” -> “Database to SQL file…”.
In the Export SQL dialog box, choose the objects (tables) you want to export. To select all, click on “Select All”.
You can then select your desired options for the export. For example, you can choose whether to keep the schema, data, or both.
Click on “Save SQL file…” to choose where to save the export. Provide a filename and click on “Save”.
Then, click on “Export”. Your database should be exported into a .sql file.

Import Database in MySQL Workbench

MySQL Workbench allows you to import .sql files to visualize the database.

Open MySQL Workbench.
Connect to your MySQL Server Instance. If you don’t have one, create a new one.
In the top menu, go to “Server” -> “Data Import”.
In the Data Import dialog, choose “Import from Self-Contained File” and select the .sql file you exported from DB Browser.
Click on “Start Import”. The .sql file will be imported into MySQL.

View ER Diagram in MySQL Workbench

In MySQL Workbench, go to the “Navigator” panel on the left.
Under the “Schemas” tab, you should see your imported database. If not, click on the refresh button at the top of the Schemas panel.
Click on your database to expand it, then right-click on “Tables” and select “Alter Table”.
This will open a new tab that shows the structure of the table. You can switch to the “Table” tab at the bottom to see an ER diagram representation of the table.

Do note that transitioning between different DBMS tools can introduce incompatibilities, as different systems may support different features and SQL dialects. Therefore, while the method described above can generally work, it may require adjustments depending on the specific contents of your database. If you encounter errors during the import process in MySQL Workbench, they are likely due to incompatibilities in the SQL code.

Leave a Reply Cancel reply