The Quickest Way to Create Company Database Using SQL
Every company has its own database which includes their number of sales, employees, clients etc. SQL programmes are used by businesses and other organizations to access and manipulate information and data in the databases, as well as to create and alter new tables.
Here, We will learn how to create a company database using MySQL schema. Later, We will see how to alter the tables and use some functions as to get the desired results as to learn more basics in MySQL.
MySQL Schema is a set of tables with rows and columns that allow users to query the database. It’s a database template that specifies the size, type, and grouping of data. Data types, functions, and operators are all part of the MySQL Schema.
Let’s take an example of the given company database (Fig 1.0). Now, how do we take this database and turn it into a MySQL database?
Let’s get started . . .
A primary key is used to ensure that each column’s value is unique. The foreign key is what connects the two tables together.
CREATE TABLE employee ( employee.emp_id INT PRIMARY KEY,employee.first_name VARCHAR(40),employee.last_name VARCHAR(40),employee.birth_day DATE,employee.sex VARCHAR(1),employee.salary INT,employee.super_id INT,employee.branch_id INT );
This Employee table will be created. We have an integer for our employee ID. And this is going to be the table’s primary key. Then there’s first name, last name, and birth day. As a result, a birthday is truly a DATE in the format (YYYY-MM_DD).VARCHAR Datatype is a SQL datatype for storing character strings of variable length but no more than the maximum size given. Super_id is a foreign key that refers to another employee from employee table. In addition, the branch_ID is a foreign key that refers to the branch table.
Now here’s the deal, We can’t make these foreign keys yet because the employee table doesn’t exist in the database.
We haven’t generated the branch table yet, so it doesn’t technically exist.
CREATE TABLE branch ( branch_id INT PRIMARY KEY, branch_name VARCHAR(40), mgr_id INT, mgr_start_date DATE, FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL ); ALTER TABLE employee ADD FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL; ALTER TABLE employee ADD FOREIGN KEY(super_id) REFERENCES employee(emp_id) ON DELETE SET NULL;
The CREATE TABLE branch will be used. As a result, it has a branch id that is the PRIMARY KEY, as well as a Branch name and a Manager ID. The manager ID is a foreign key as well. So, we’re going to define the manager ID as a foreign key that points to the employee table. Then there’s DATE, which is the manager’s start date.
CREATE TABLE client ( client_id INT PRIMARY KEY, client_name VARCHAR(40), branch_id INT, FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL );
So, you’ll notice the CREATE TABLE client over here. And we’re just going to make the branch id a foreign key by storing the client id as a primary key, client name, and branch id.
CREATE TABLE works_with ( emp_id INT, client_id INT, total_sales INT, PRIMARY KEY(emp_id, client_id), FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE );
The Works With table follows next. Because it has a composite primary key, the Works With table is actually quite special.
The employee ID and customer ID are stored in the primary key. In fact, each component of the primary key is a foreign key, which is what makes it unique. As a result, emp_id and Client_id is a foreign key.
CREATE TABLE branch_supplier ( branch_id INT, supplier_name VARCHAR(40), supply_type VARCHAR(40), PRIMARY KEY(branch_id, supplier_name), FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE );
Finally, we’ll establish the Branch Supplier table, which will be our last table. And this is comparable to the Works With table in several ways. There’s also a composite key. As a result, its key is made up of several columns. Although the branch id field is a foreign key, the supplier name column is not.
Businesses utilise SQL not simply to store information, but also to retrieve and manipulate data. We’ve learned how to create tables in a company database using various functions and the concept of primary keys , foreign keys and different types of data types ( VARCHAR , INT , DATE etc. ).
So,
Now we have all of these tables created. We created all the tables for our database schema. What we’re going to do now is actually put data into those tables , and that is possible only in a specific way because all the tables created have foreign keys relationships that references to another table.
I’m going to walk you guys through how we might do something like this and it’ll give you an idea of how you can do it too. Check out the link Inserting data into your database using SQL.
