Basics of creating MySQL databases. How to make a simple database

Creating a new normalized relational database Access data carried out in accordance with its structure obtained as a result of design. The process of designing a relational database was discussed in the previous chapter. The structure of a relational database is determined by the composition of the tables and their relationships. The relationships between two tables are implemented through the connection key, which is part of the fields of the linked tables. Recall that in the normalized relational database table data is in a one-to-many or one-to-one relationship. For one-to-multivalued relationships, as a rule, the unique key of the main table is used as the connection key; in a subordinate table, this can be any of the fields called foreign key.

Creating a relational database begins with the formation of a table structure. This determines the composition of the fields, their names, the data type of each field, the size of the field, keys, table indexes and other properties of the fields. After defining the table structure, a data schema is created in which relationships between tables are established. Access remembers and uses these relationships when populating tables and processing data.

When creating a database, it is important to set parameters that allow Access to automatically maintain data integrity. To do this, when defining the table structure, the key fields of the tables must be specified, restrictions on permissible data values ​​must be specified, and when creating a data schema based on normalized tables, parameters for maintaining the integrity of database links must be specified.

The creation of the database is completed by the loading procedure, i.e., filling the tables with specific data. Of particular importance is the technology for loading interrelated data. A convenient tool loading data into interrelated tables are input/output forms that provide an interactive interface for working with database data. Forms allow you to create a screen analogue of the source document through which you can enter data into interrelated tables.

ATTENTION!
The user can start working with the database with any number of tables created even before creating a complete database displaying all objects of the data model subject area. The database can be created in stages, and at any time it can be supplemented with new tables and relationships between tables can be entered into the data schema; existing tables can be supplemented with new fields.

Creating a database in MS Access 2010

The page that opens after launching Access displays the view Microsoft Office Backstage - a set of commands on a tab File(File) - which is designed to execute commands that apply to the entire database. At the same time, on the tab File(File) command selected Create(New). The area of ​​this command contains Available templates(Available Templates), which allow you to create a database using numerous templates located on your computer or accessible via the Internet.

A template is a ready-to-use database that contains all the tables, queries, forms, and reports needed to perform a specific task. These ready-made databases allow you to quickly create applications that support a wide range of tasks. There are new application templates available all the time that you can download from Microsoft Office Online. These standard applications You can use them without any modification or customization, or, using them as a basis, adapt the template to your needs and create new fields and tables, forms, reports and other database objects.
The Open command is intended to open any previously created database. A list of the 4 most recently opened databases is also available here. The Recent command opens a longer list of recently opened databases. By clicking on the button icon you can add a database to the list of recent ones, and by clicking on the icon you can remove it from the list.

Team Open(Open) is designed to open any previously created database. A list of the 4 most recently opened databases is also available here. Team Latest(Recent) opens a longer list of recently opened databases.

And we have a video on this topic:

Read about creating an Access database file in the next section.

IN modern world We need tools that would allow us to store, organize and process large amounts of information that are difficult to work with in Excel or Word. Such repositories are used to develop information websites, online stores and accounting add-ons. The main means of implementing this approach, are MS SQL and MySQL. The product from Microsoft Office is a simplified version in terms of functionality and is more understandable for inexperienced users. Let's take a step-by-step look at creating a database in Access 2007.

Description of MS Access

Microsoft Access 2007 is a database management system (DBMS) that implements a full-fledged GUI user, the principle of creating entities and relationships between them, as well as the structural query language SQL. The only disadvantage of this DBMS is the inability to work on an industrial scale. It is not designed to store huge amounts of data. Therefore, MS Access 2007 is used for small projects and for personal, non-commercial purposes.

But before showing the step-by-step creation of a database, you need to familiarize yourself with basic concepts from database theory.

Definitions of basic concepts

Without basic knowledge about the controls and objects used when creating and configuring the database, it is impossible to successfully understand the principle and features of setting up the subject area. So now I'll try in simple language explain the essence of all important elements. So, let's begin:

1. A subject area is a set of created tables in a database that are interconnected using primary and secondary keys.
2. An entity is a separate database table.
3. Attribute – the title of a separate column in the table.
4. A tuple is a string that takes the value of all attributes.
5. A primary key is a unique value (id) that is assigned to each tuple.
6. The secondary key of table "B" is a unique value from table "A" that is used in table "B".
7. An SQL query is a special expression that performs a specific action with the database: adding, editing, deleting fields, creating selections.

Now that in general outline If you have an idea of ​​what we will be working with, we can start creating a database.

Creating a database

For clarity of the whole theory, we will create a training database “Students-Exams”, which will contain 2 tables: “Students” and “Exams”. The main key will be the “Record Number” field, because this parameter is unique for each student. The remaining fields are for more complete information about students.

So do the following:

That's it, now all that remains is to create, fill and link tables. Continue to the next point.

Creating and populating tables

After successful creation An empty table will appear on the database screen. To form its structure and fill it out, do the following:

Advice! For fine tuning data format, go to the “Table Mode” tab on the ribbon and pay attention to the “Formatting and Data Type” block. There you can customize the format of the displayed data.

Creating and editing data schemas

Before you start linking two entities, by analogy with the previous paragraph, you need to create and fill out the “Exams” table. It has the following attributes: “Record number”, “Exam1”, “Exam2”, “Exam3”.

To execute queries we need to link our tables. In other words, this is a kind of dependency that is implemented using key fields. To do this you need:

The constructor should automatically create the relationship, depending on the context. If this does not happen, then:

Executing queries

What should we do if we need students who study only in Moscow? Yes, there are only 6 people in our database, but what if there are 6000 of them? Without additional tools it will be difficult to find out.

It is in this situation that SQL queries come to our aid, helping to extract only the necessary information.

Types of requests

SQL syntax implements the CRUD principle (abbreviated from the English create, read, update, delete - “create, read, update, delete”). Those. with queries you can implement all these functions.

For sampling

In this case, the “read” principle comes into play. For example, we need to find all students who study in Kharkov. To do this you need:

What should we do if we are interested in students from Kharkov who have more than 1000 scholarships? Then our query will look like this:

SELECT * FROM Students WHERE Address = “Kharkov” AND Scholarship > 1000;

and the resulting table will look like this:

To create an entity

In addition to adding a table using the built-in constructor, sometimes you may need to perform this operation using SQL query. In most cases, this is necessary while performing laboratory or coursework as part of a university course, because in real life there is no need for this. Unless, of course, you are engaged in professional application development. So, to create a request you need:

1. Go to the “Creation” tab.
2. Click the “Query Builder” button in the “Other” block.
3. In the new window, click on the SQL button, then enter the command in the text field:

CREATE TABLE Teachers
(Teacher Code INT PRIMARY KEY,
Last name CHAR(20),
Name CHAR(15),
Middle name CHAR (15),
Gender CHAR (1),
Date of birth DATE,
main_subject CHAR(200));

where "CREATE TABLE" means creating the "Teachers" table, and "CHAR", "DATE" and "INT" are the data types for the corresponding values.

Attention! Each request must have a “;” at the end. Without it, running the script will result in an error.

To add, delete, edit

Everything is much simpler here. Go to the Create a Request field again and enter the following commands:

Creating a Form

At a huge number fields in the table, filling the database becomes difficult. You may accidentally omit a value, enter an incorrect one, or enter a different type. In this situation, forms come to the rescue, with the help of which you can quickly fill out entities, and the likelihood of making a mistake is minimized. This will require the following steps:

All basic functions We have already reviewed MS Access 2007. There is one last important component left – report generation.

Generating a report

A report is a special MS Access function that allows you to format and prepare data from a database for printing. This is mainly used for creating delivery notes, accounting reports and other office documentation.

If you have never encountered such a function, it is recommended to use the built-in “Report Wizard”. To do this, do the following:

1. Go to the "Creation" tab.
2. Click on the “Report Wizard” button in the “Reports” block.
   
   
3. Select the table of interest and the fields you need to print.
   
   
4. Add the required grouping level.
   
   
5. Select the sort type for each field.

A database is a collection of structured and interconnected data and methods that enable the addition, selection and display of data. Microsoft Access allows you to manage all your information from one database file. The following objects are used within this file:

tables for storing data;

queries to search and retrieve only the required data;

forms for viewing, adding and changing data in tables;

reports for analyzing and printing data in a specific format;

Successful database design ensures that it is easy to maintain. Data should be stored in tables, and each table should contain the same type of information, then it is enough to update specific data in only one place for the updated information to appear throughout the database.

The database for solving the problem consists of:

1. The “Products” table consists of a list of the product range and contains the name of the product and its code;

   The “Shops” table consists of a list of shops that produce products and contains the name of the shop and its code;

   The “Warehouses” table consists of a list of warehouses where products are stored and contains the name of the warehouse and its code;

   The “Units of Measurement” table consists of a list of the minimum units of production of the assortment and contains the name of the unit of measurement and its code;

   The “Months” table contains the number and name of the month;

   The “Production Plan” table reflects the range and quantity of planned production by month and workshop;

   The “Shop invoices” table contains the numbers and dates of invoices for products produced by the workshops;

   The table “Specifications of shop invoices” contains information about the quantity and range of products issued under a specific invoice.

Requests

1. The “Plan” query retrieves data on the quantity and range of planned product output for a certain period at a given warehouse;

   The “Fact” query retrieves data on the quantity and range of products produced for certain period to a given warehouse;

   The “Deviation” query retrieves data on the difference between the quantity of planned product output and the actual quantity for a certain period at a given warehouse.

2. The “Products” form allows you to edit the “Products” table;

   The “Shop invoices” form allows you to work with the “Shop invoices” table and the subordinate table “Shop invoice specifications”.

3. The “Surplus Products” report is the result of the “Variance” query

Table structure.

Data in a database is stored in tables, each of which has its own unique name in the database. In tables, data is organized into columns (called fields) and rows (called records). All data contained in a table field must be of the same type. Each table field is characterized by the name, type and width of the field. When you set a field's data type, you can also specify the size, format, and other settings that affect the display of the field's value and the precision of the numeric data. Main data types:

Text. No text or numbers requiring calculations.

MEMO. This type of field is designed to store small text data (up to 64,000 characters). A field of this type cannot be key or indexed.

Numerical. This data type contains many subtypes. The accuracy of calculations depends on the choice of subtype (size).

Counter. Unique, sequentially increasing numbers automatically entered when added new entry to the table.

Logical. Boolean values, as well as fields that can contain one of two possible values.

Monetary. Monetary values ​​and numerical data, used in mathematical calculations.

Date Time. Date and time are stored in a special fixed format.

Object fieldOLE. Includes sound recording, drawing and other types of data. A field of this type cannot be key or indexed.

Hyperlink. Contains web page addresses.

Data types in table fields

Unit of Measurement Table

Months table

Release plan table

Workshop invoice specification table

Workshop table

One of the main requirements for a DBMS is the ability to quickly search for required records among a large amount of information. Indexes are the most effective tool that can significantly speed up searching for data in tables.

An important feature of indexes is that you can use indexes to create primary keys. In this case, the indexes must be unique. Primary keys and secondary indexes are used to define relationships between tables and data integrity conditions.

The database contains many tables, the relationship between which is established using matching values ​​in key fields. In most cases, you associate a key field in one table with a corresponding field (often with the same name), which is called a foreign key field in a second table. The table containing the key field is called the main one, and the table containing external key- connected.

Data schema.

Considering all of the above, let’s draw a data diagram

Fig.3 Data diagram

Custom forms.

Access forms allow you to create a user interface for database tables. Although you can use Datasheet view to perform the same functions, Forms provide benefits for presenting data in an organized and attractive way. Forms also allow you to create lists of values ​​for fields in which to represent a set acceptable values codes are used. A properly designed form speeds up the data entry process and minimizes errors.

Forms are created from a set of individual controls: text fields for entering and editing data, buttons, checkboxes, radio buttons, lists, field labels, and frames objects for displaying graphics and OLE objects. The form consists of a window that houses two types of controls: dynamic (displaying data from tables), and static (displaying static data such as labels and logos).

Access forms are feature rich; they allow you to perform tasks that cannot be completed in table view. Forms allow you to check the correctness of the data contained in the table. Access allows you to create forms that contain other forms (a form within a form is called a subform). Forms allow you to calculate values ​​and display the result.

In this work, the main push-button form, shop invoices, is used, which contains the subform Specifications of shop invoices.

Fig.4 Form “Workshop invoices”

Fig.5 “Products” form

Figure 6. “Release Plan” form

Creating queries.

Queries are an important tool in any database management system. They are used to highlight, update and add new records to tables. Most often, queries are used to select specific groups of records to satisfy a specific criterion. They can also be used to retrieve data from different tables, providing a single view of related data items. With these powerful, flexible tools you can:

Generate complex criteria for selecting records from one or more tables;

Specify the fields that should be displayed for the selected records;

Perform calculations using selected data.

There are four types of queries in Access for different purposes:

Select queries display data from one or more tables in table form.

Cross queries collect data from one or more tables in a format similar to spreadsheet. These queries are used to analyze data and create charts based on the total values ​​of numeric values ​​from a set of records.

Change queries are used to create new tables from query results and to make changes to data in existing tables. They can be used to add or remove records from a table and modify records according to expressions specified in query design mode.

Queries with parameters are queries whose properties are changed by the user each time they are run. When you run a query with a parameter, a dialog box appears in which you need to enter a selection condition. This type of request is not standalone, i.e. the parameter can be added to any type of request.

In this work, the query was created using the wizard

Generating reports.

The end product of most database applications is a report. In Access, a report is a special type of continuous form designed to be printed. Access combines data in tables, queries, and even forms to create a report that can be printed and shared. A printed version of the form can serve as a report.

Reports generated by Access are divided into six main types:

Single column reports are one long column of text containing the values ​​of all fields in each table or query record. The caption indicates the name, and to the right of it the field value is indicated. The new Access AutoReport tool allows you to create a single-column report by clicking the AutoReport toolbar button. Single-column reports are rarely used because this format of presenting data wastes paper.

IN tape reports For each field of a table or query, a column is allocated, and the values ​​of all fields of each record are displayed on lines, each in its own column. If a record has more fields than can fit on a page, additional pages will be printed until all the data is printed; then printing of the next group of records begins.

Multi-column reports are created from single-column reports using "newspaper"-style columns or "snake" columns, as is done in desktop publishing systems and word processors. Information that does not fit in the first column is moved to the beginning of the second column, and so on. The multi-column table format saves some paper, but is not applicable in all cases because the column alignment is unlikely to meet user expectations.

In general, reports are easiest to create using the Report Wizard. The Report Wizard tries to create the best possible final report on the first try. Typically, the wizard is close enough to being complete that much less time is spent editing the master's basic report than it would take to create a report from a blank template.

In this work, the report was created using the Report Wizard and then edited in the Report Designer.

Fig.8 Report designer

As a result of running the report, its printed form is obtained.

Fig.9 Report

Conclusion

In business or personal life, you often have to work with data from different sources, each of which is associated with a specific type of activity. Coordinating all this data requires certain knowledge and organizational skills. Microsoft Access combines information from different sources into one relational database. The created forms, queries and reports allow you to quickly and efficiently update data, get answers to questions, search for the necessary data, analyze data, print reports, charts and mailing labels.

In the database, information from each source is stored in a separate table. When working with data from multiple tables, relationships are established between the tables. To search and select data that meets certain conditions, a query is created. Queries also allow you to update or delete multiple records at once, and execute inline and ad-hoc messages.

Forms are used to view, enter or change data directly in the table. A form allows you to select data from one or more tables and display it on the screen using a standard or custom layout.

Reports are used to analyze data or print it in a specific way. For example, you can create a report that groups data and summarizes it, or a report that prints mailing labels.

In the database window you can work with all its objects. To view objects of a certain type, select the appropriate tab. Using the buttons, you can open and modify existing objects and create new ones.

The developed database allows you to quickly and efficiently manage the sales department of finished products. User-friendly interface The program, on the one hand, makes it easy to navigate the program without requiring the user to have any special skills in working with electronic computers, on the other hand, it provides the user with operational information.

Nowadays, databases are very widely used in various fields: in enterprises, companies, educational institutions and so on. And you can work with them using a program from the Office package - Microsoft Access, which gives users very wide opportunities for interacting with tables. Microsoft Access skills will be useful to every modern user. In this article we will take a closer look at how to create a database in Access. Let's figure it out. Go!

A database is a collection of interconnected information, combined in the form of a table. Having figured out what it is, you can proceed directly to creation.

An example of creating a database with a multi-level list

With Microsoft Access running, select New from the File menu. Then click the New Database button. On the right side of the window, specify the file name and the location where it will be stored. After that, click the “Create” button. You can also use one of the list of templates offered by the utility. In some cases, it will be much more convenient to simply edit the template than to create everything from scratch yourself.

How to start creating a database

A table with one single field will appear in front of you. Hover over the tab and select Design. A window will appear asking you to change the name. Now you can start filling out the cells in the “Field Name” column. The next Data Type column will be filled in automatically depending on whether you entered words or numbers. The data type in each cell can be changed. The following data types exist:

• text;
• MEMO field (used for object descriptions, as it allows you to enter a large number of characters, >255);
• numerical;
• date Time;
• monetary;
• counter;
• logical (convenient for fields containing “yes” or “no”);
• field OLE object(allows you to insert multimedia files: photos, videos);
• hyperlink;
• attachment (used to store multiple files in one field);
• calculated (allows you to calculate the sum, difference, product based on other data from the table);
• master of substitutions.

In most cases, you will only need “numeric”, “text”, “cash”, “calculated” and “Date/time”, these are the data types that are used most often. Below, in the Field Properties section, you can see the size of the field. If you have a text data type, there will be 255. This means that you can enter 255 characters in this cell. Of course, this is too much, so you should reduce the size of the specified field to 30 (you can put any other number, depending on challenges). This is done so that the database takes up less disk space.

For different attribute different type data, example in the screenshot.

By selecting a numeric data type, you can set a so-called input mask. It is needed in order to display the entered numeric values ​​in a certain way. The mask is specified using spaces and hash marks. For example, if your table contains passport numbers, then to display these numbers in the appropriate format, set the mask “####_######”. That is, 4 characters, then a space and another 6 characters. For phone numbers, you can specify the mask “8(###)-###-##-##”.

For the “Date/Time” type you can select various formats display in the “Field Properties” section.

There are databases of different complexity, we will create the simplest one. We will create a database in Access; this program does not require much time to master. We will create simple base“Movie Library” data, other databases can be created on this basis. So, first install it on your computer office application, and let's start working. Launch the program and click “create” on the toolbar new base data.

A window opens and we are asked to create our database. Next, you must decide what you will name the database and in which folder you will create it. When you have decided, click on the “create” button.

Now we will create the table. You need to select the “table” object to create a table in design mode and click the left mouse button.

A table appeared in front of us that we must fill out.

I suggest filling out the fields this way:

You can fill in as you wish. The data type must match what you want to populate. For example, if it’s a year, then it will naturally be a numeric data type; if you want to attach an object (drawing), then it’s OLE. Next, click on “file”, “save as”, set a name and save the table.

There is no need to set key fields; we don’t need them for this database. Now that the table has been created, let's move on to creating the form. To do this, select “forms” “create”. We select our table as the source.

A data area has appeared in front of us, where we will construct our form. If you want to stretch the area, then move the cursor to the edge and a cross will appear and you can stretch the area.

As we can see, the data is not arranged neatly; in order to arrange it the way you want, you need to left-click on the object and move it behind the black square at the top.

You can arrange objects as you wish, as well as stretch and move them individually. Next we need to give our shape a colorful look. First we will fill the background, for this right click Open the menu with your mouse, hover over the fill color and select a color. Now you can change the color of the text; to do this, select the object and click on the button on the toolbar that will change the color of the text. The text color can be changed immediately or individually for each object.

Selecting objects to change the text color occurs by pressing the left mouse button and stretching the area. You can change the text size, you need to select objects and change the size or font.

You can change the color of the lines; to do this, select the object in the toolbar and click on the button that changes the color of the lines.

Now we will add a checkbox element to the form, and when we fill out the database, we will check the box if the movie is available, and if it is not available, then we will leave the checkbox empty. To add a checkbox, select the checkbox in the toolbar and insert it into the data area.

Let's add additional buttons in the form, the first two buttons will go to the record. To do this, click on the button in the toolbar and click on the data area.

A window appears where we need to select a button and action, select previous entry and click done. If you want to change the button’s design or anything else, then “next”. Buttons can be freely moved around the data area and positioned as desired.

We do the same for the second button, only we change the actions. Change “next entry”. Let's add a database search. Press the button again and click on the area. As a result, we should have three buttons, if you want, you can add more.

Our form is ready to be filled out, but first let's save it. We move the cursor over the “File” “Save As” toolbar, give our form a name and save it. Now you can start populating the database. Switch to form mode and fill in the data.

As a result, we will have a database like this.

In order to insert an object into an OLE field, you need to click add object in the toolbar, in form mode, you can add pictures, clips and much more. To navigate through the records, use the buttons that you created or the buttons that are located at the bottom of the form. In my next articles we will analyze very complex databases with queries, macros, and reports. Don't forget that databases are convenient thing, which can always come in handy in your life. There are many programs for creating a database; they can be useful for those who want to create their own online store, and for other purposes. If you have gained experience in creating a database, you can make a decent amount of money from it.