How to create a database-An ultimate Guide

If your boss has decided to rely on managing a large amount of data, asking you to organize everything in a database. After researching and theoretically understanding how everything works, you have finally decided to put the acquired knowledge into practice. However, you are aware of not having the good idea of how to create a database in a simple way, without complicating your life between command lines, complex programs, and strange system configurations. Even if you have already designed its structure: in this regard, you would like some useful tips to get started on your work.

Make yourself comfortable in front of the computer, take a few minutes for yourself, and read carefully. We are sure that, at the end of this guide, you will be able to choose the solution that best suits your case and put it into practice just like a real professional. 

Primary information

Before getting to know how to create a database, it is good to make clear some concepts and, above all, some key terms concerning its structure. It is essential to understand them to become familiar with the databases.

·     SQL - is the most widely used language for managing and creating relational databases, i.e., structures that can contain one or more tables, even linked together, dedicated to managing small or large amounts of data.

·     Database - is the word that defines an archive of data, simple or complex, that can be organized, manipulated, and interrogated using common methods.

·     Table - is a set of rows and columns that contains a set of similar elements (i.e., of the same type) within a database. The columns, also called fields, indicate the properties of the elements: each column corresponds to a precise property. Rows, on the other hand, define a specific data belonging to the table.

- Entity - It is the technical definition assigned to the elements that are part of a table.

- Cell - is the intersection of a row with a column, which identifies the value assigned to it.

- Record - is the set of properties that define a specific entity of the table. In other words, a record corresponds to a row in the table.

- The primary key - is the field of a table that uniquely identifies each element. In fact, you can see the primary key as a unique property of a specific object: for example, thinking of an inventory of remote controls, the primary key could be its serial number.

- Query - is the main operation of manipulating a database. There are mainly two types of queries: selection queries (also called queries) and manipulation queries. The latter, in turn, can be queuing/deleting (to insert new data or delete obsolete ones), update (to change the value of the fields of a record), and creation queries (allow to create new tables starting from the selection query results).

- Report - it is the operation of showing in "readable" language the data generated by one or more queries, sorted and cataloged according to precise parameters.

Relationship - is a link that associates two different tables, not necessarily containing elements of the same type, useful for linking them and thus facilitating the creation of reports, queries, and other manipulation operations. Considering, for example, two tables, Teachers and Courses, you can create from left to right the relationship held (teacher holds course), and from right to left the report conducted by (course organized by the teacher.

Create a database with Access

Microsoft Access is the database management solution included in the Office productivity suite. It allows the creation and management of databases with the help of buttons and windows, and without having to remember long and complex commands in specific languages, such as the SQL one.

You can choose the type of data to assign to the selected field by clicking on it and then selecting the Fields tab from the top of the Office screen. Always from the same section, you can assign other attributes to the chosen field (Mandatory, Unique, or Indexed), assign a default value, an expression (or calculation on different fields), and so on.

To add fields to the table, click on the item Click to add placed inside the Access table and assign a value to each field by simply selecting it with the mouse and typing in the desired content.

Create a database with Excel

Do you think Access is an excellent database management solution, but a bit too complex for your needs? Do you simply need to manage a fair amount of data, with the possibility of ordering them and conducting targeted searches between them? In this case, you can take advantage of Microsoft Excel, the program dedicated to the spreadsheets included in the Office suite: if you haven't already installed it, you can proceed following the instructions we have given you in the previous section.

Complete the setup of the suite, start Excel recalling it from the Start menu of Windows (the icon located in the lower-left flag of the screen), or from the folder Applications of MacOS and presses the button Blank Workbook. At this point, use line number 1 of the worksheet to set the field titles (e.g., cell A1 by Name, B1 by Last Name, C1 by Tax Code, and so on) and uses the following lines to create the various database records.

Create an SQL database.

First of all, connected to the XAMPP Internet site and, if you have a Windows PC, presses on the XAMPP button for Windows located at the item download.

Once the Download is complete, double click on the file you just downloaded, click on the Yes button. Press the Next button; make sure there are tick marks next to the Server, Apache, MySQL, Program Languages, PHP, and phpMyAdmin items and click on the Next button for two consecutive times. During the procedure, the Windows firewall may ask you to add an Apache exception: when the dialog box appears, click on the button Allow Access. Do you want to start the Control Panel now? Click on the Finish button to start the XAMPP control panel immediately.

To create a new database, click on the new entry in the left sidebar and type the name to be assigned to the database in the Database name field. Then choose the utf8_general_ci item from the drop-down menu Character encoding and presses the Create button. After a few seconds, a new empty database will be created (which you can always access from the left side panel).

Once this operation is complete, you can create the first table of the database by typing its name in the Name text box, specifying the number of fields in the appropriate text box and pressing the Execute button.

Once the main parameters have been set, click on the Save button and then on the Structure tab, located at the top: from there, you can define further details of the table field.