Database creation. What operations can be performed using MS Access? How it's done


Topic 2.3. Presentation software and office programming basics

Topic 2.4. Database management systems and expert systems

2.4.11. Training database with the main button form "Training_students" - Download

DBMS and expert systems

2.4. Database management systems and expert systems

2.4.3. Creating a database (methods of creating tables and creating a table structure for the "Dean's Office" database)

When you first open a database window, Access always activates the Tables tab and displays a list of table creation modes:

  • creating a table in design mode;
  • creating a table using the wizard;
  • creating a table by entering data

You can select any of these modes to create a new table. You can select the Table Wizard to define table fields using lists of sample tables and fields. To create a custom table, it is advisable to use the Design mode. The Create a table by entering data mode is used, as a rule, to edit and enter data into existing tables.

Let us remember that an Access table is a collection of data united by a common theme. A separate table is assigned to each entity so that there is no repetition in the stored data. Tables consist of records and fields. The number of fields in a record is determined at the table design stage, so before creating a table using Access applications, it is necessary to clearly understand its structure.

The size and type of fields are determined by the user. It is necessary to choose field sizes that are not too large, since inflated field sizes waste database memory. To create relationships between tables, they must have a key field, so you need to assign a key field to each table.

To set primary key In Design mode, you need to select the required field, and then click on the “Key Field” icon located on the toolbar. To assign a Foreign (Secondary) key in Design mode, you need to select a field and in the properties area of ​​this field in the Indexed field line, select the value Yes (Coincidences are allowed) from the list.

To select the required mode for creating tables, you can double-click on one of them in the list of modes, the required mode will open. In addition, you can click on the “Create” icon in the database window, a dialog box will open “ New table", and in it select the required table creation mode.


Rice. 1.

When you select the Table Wizard mode, the “Create Tables” window will open, in which you can easily create fields for a new table using sample tables and fields.



Rice. 2.

But if the required table sample is not in the “Create Tables” window, then you need to select the Design mode, the Table Designer window will open



Rice. 3.

The composition (structure) of the table is determined in the table project area, which consists of three columns:

  • field name;
  • data type;
  • description.

Data types must be selected from the drop-down list:

  1. Text – alphanumeric data (up to 255 bytes).
  2. MEMO field - long text or numbers, such as notes or descriptions (up to 64,000 bytes).
  3. Numeric - text or a combination of text and numbers (stores 1, 2, 4 or 8 bytes).
  4. Date/time – dates and times (8 bytes).
  5. Currency - used for monetary values ​​(stores 8 bytes).
  6. Counter – automatic insertion unique sequential (increasing by 1) or random numbers when adding an entry (4 bytes).
  7. Logical – data that takes only one of two possible values, for example, “Yes/No” (1 bit).
  8. Field OLE object– to insert the following objects: drawings, pictures, diagrams, etc. (up to 1 GB).
  9. Hyperlink – the address of the link to the file on offline computer or online (stores up to 64,000 characters).
  10. Lookup Wizard - Creates a field that allows you to select a value from another table or from a list of values ​​using a combo box. When you select this option in the list of data types, a wizard is launched to automatic detection this field.

The Field Properties area assigns properties to each field (for example, size, format, indexed field, etc.).

When creating a table structure, enter the Field Name in the first column, then click Enter key and select the data type (by default, Access assigns a data type; if this data type is not suitable, then select it yourself from the drop-down list). Then enter a field description in the third column.

So, you have installed MySQL, and we are starting to master SQL language. In Lesson 3 on Database Basics, we created a conceptual model of a small database for the forum. It's time to implement it in the MySQL DBMS.

To do this, first of all you need to run MySQL server. Let's go to system menu Start - Programs - MySQL - My SQL Server 5.1 - MySQL Command Line Client. A window will open asking you to enter a password.

Press Enter on the keyboard if you did not specify a password when setting up the server, or specify a password if you specified one. We are waiting for the mysql> invitation.

We need to create a database, which we will call forum. There is an operator for this in SQL create database

Create database database_name;


The maximum length of a database name is 64 characters and can include letters, numbers, the "_" character, and the "$" character. The name may begin with a number, but should not consist entirely of numbers. Any database query ends with a semicolon (this character is called a delimiter). Having received the request, the server executes it and, if successful, displays the message "Query OK ..."

So, let's create a forum database:

Press Enter and see the response “Query OK...”, meaning that the database has been created:

It's that simple. Now we need to create 3 tables in this database: topics, users and messages. But before we do this, we need to tell the server which database we are creating the tables in, i.e. you need to select a database to work with. For this purpose the operator is used use. The syntax for selecting a database to work with is as follows:

Use database_name;


So, let’s choose our forum database for work:

Press Enter and see the response “Database changed” - the database is selected.

You must select a database in each session of working with MySQL.

To create tables in SQL there is an operator create table. Creating a database has the following syntax:

Create table table_name (first_column_name type, second_column_name type, ..., last_column_name type);


The requirements for table and column names are the same as for database names. Each column has a specific data type associated with it, which limits the type of information that can be stored in the column (for example, preventing letters from being entered into a number field). MySQL supports several data types: numeric, string, calendar, and a special type NULL, which denotes no information. We will talk about data types in detail in the next lesson, but for now let's return to our tables. In them we have only two data types - integer values ​​(int) and strings (text). So, let's create the first table - Topics:

Press Enter - the table is created:

So, we created a topics table with three columns:
id_topic int - topic id (integer value),
topic_name text - topic name (string),
id_author int - author id (integer value).

Let's create the remaining two tables in a similar way - users (users) and posts (messages):

So, we have created a forum database and there are three tables in it. Now we remember this, but if our database is very large, then it is simply impossible to remember the names of all the tables and columns. Therefore, we need to be able to see what databases we have, what tables are present in them, and what columns these tables contain. There are several operators for this in SQL:

show databases- show all available databases,

show tables- show a list of tables in the current database (you must first select it using the operator use),

describe table_name- show a description of the columns of the specified table.

Let's try. Let's look at all the available databases (you have only one so far - forum, I have 30, and they are all listed in a column):

Now let’s look at the list of tables in the forum database (to do this, you must first select it), do not forget to press Enter after each request:

In the answer we see the names of our three tables. Now let's look at the description of the columns, for example, the topics table:

The first two columns are familiar to us - this is the name and data type, the values ​​of the rest we still have to find out. But first, we will still find out what types of data there are, which ones and when to use.

And today we will look at the last operator - drop, it allows you to delete tables and databases. For example, let's delete the topics table. Since two steps ago we selected the forum database for work, now there is no need to select it, you can simply write:

Drop table table_name;


and press Enter.

Now let's look at the list of tables in our database again:

Our table is indeed deleted. Now let's delete the forum database itself (delete it, don't be sorry, it will still have to be redone). To do this we write:

Drop database database_name;


and press Enter.

And verify this by querying all available databases:

You probably don’t have a single database; I have 29 of them instead of 30.

That's all for today. We learned how to create databases and tables, delete them and retrieve information about existing databases, tables and their descriptions.

Designing a database management system in Delphi.

What you need to master:

1) how to connect the database to Delphi components;

2) the procedure for creating an application to manage a simple local database;

3) how to control the display of database tables;

4) how to organize data sorting and filtering;

5) how to use the SQL query language.

Create a folder in which you will save the applications you develop. For each application in the future, you should create a separate directory in the main folder.

STEP 1. CREATE A SIMPLE DATABASE

Before you start developing a database management system (DBMS), you should create this database. Let's use it for this purpose Microsoft application Office Access, since office is installed on almost any computer and Access was most likely installed by default.

This tutorial will look at the possibilities of accessing databases using only one technology - ADO (Active Data Objects), developed by Microsoft. This modern library, first of all, allows you to work with local MS Access and client-server MS SQL Server databases. Studying this library will allow you in the future to easily move on to databases built on the basis of other technologies.

Let's create for now local database store data computer literature, consisting of one table. Table like two-dimensional array consists of columns and rows. Each table column contains a single field, such as book title or author. Each row of the table contains one record consisting of several fields containing, for example, the title of the book, author, price, year of publication.

Run Microsoft Office Access. In the menu, click File/New and then in the wizard select New base data. You will be asked to select the database storage location and file name. Specify the path to your first future application (where you will save the Delphi project in the future) and a meaningful name for the computer book store database, for example, mkl.mdb.

A window for organizing work with the database will open (see figure).

Select double click Click on the item “Creating a table in design mode” – the table designer will open, in which you should specify the names of the table fields and the type of data contained in the corresponding fields.

An example of a description of table fields is shown in the figure below. Let there be six fields in the table. In Access, field names can be given names in both English and Russian. The names of fields No. 2-5 are obvious, as is the data type of these fields. Let's look at field #1. Field name: id_kn – book identifier. This field has a special meaning for the database - this is a key field in the table, it carries a unique record identifier. You can set the “Key Field” option via context menu, which appears when you right-click on the corresponding field in the table designer. Save the table by clicking on the save button, you will be asked to choose a name for the table - set the name store.


Using the View menu, set the view to Table Mode:

The table is already ready to be filled out, but we will not do this now, since our main goal is to study Delphi features on database management. Let's create an application in Delphi and there we will start editing the table.

STEP 2. CREATE A SIMPLE APPLICATION FOR DATABASE MANAGEMENT

The simplest application should be able to view the contents of the database (in our case, the contents of one table), in addition, there should be functions for correcting records, deleting them, and adding them. A similar task can, of course, be accomplished without the use of database processing technologies, but the development of such an application will take two to three orders of magnitude longer.

So, start Delphi, create a new application and save the project to the folder where the database file is located. Let the module name be magazin.pas, and the project name ProjectMagazin.dpr.

Now let's determine which components from the palette are needed to work with the database. The database consists of tables, to view which you need the appropriate visual component– DBGrid from the Data Controls tab. In addition, there must be other components that provide communication between the application and the location of the database, recognize its format, and make a selection from a specific database table. For these purposes, we use the following three components: ADOConnection and ADOTable from the ADO tab and DataSource from the Data Access tab.

Let's configure the properties of all components on the form.

1. ADOConnection1. Double-click on the component (or in the object inspector on the ConnectionString property line) - you will be given the opportunity to enter a connection string (Use Connection String), launch the wizard by pressing the Build key and on the “Data Provider” tab select the database connection driver Microsoft data Jet OLE DB Provider. Click “Next” to go to the “Connection” tab and there, in the “Select or enter a database name” line, enter the file name - in our case it is mkl.mdb. You can, of course, press the key next to the line and directly point to the file, but in this case, it is better to immediately remove the path to the file, leaving only the name, so that when moving the application to another location, there will be no problems with accessing the database. Click OK in the wizard and OK on the connection string selection form. Switch the LoginPromt property to False so that every time you connect to the database, the user is not prompted for a password.

2. ADOTable1. In the Connection property, in the drop-down list, point to ADOConnection1, in the TableName property, select the table (we have only one store for now). Set the Active property to True (note that in the future, when changing settings, you will often have to return this property to True). And, for convenience, rename the component to TableMagazin.

3. DataSource1. For this intermediary component, you must set the DataSet property in the drop-down list to the TableMagazin table.

4. DBGrid1. Let's connect the grid with DBGrid1 to the store table from the database using DataSource1 by setting the DataSource property in the drop-down list of available components to DataSource1 in the object inspector.

This is the creation the simplest base The data is complete, but we haven’t written a single line of code. If you already had experience working with these components, you would spend less than a minute developing such a DBMS.

Launch the application using the F9 key and work on filling the database (control keys: F2 – edit cell, Ins – add record, Ctrl+Del – delete record). Close the application and then launch it again and you will see that the changes you made are saved.

Example of filling a database:

Not everyone prefers to work with a keyboard if they have a mouse. Therefore, sometimes the DBNavigator component from the Data Controls tab can be useful. Place it for testing (in the future it should be deleted) on the form at your discretion and connect it using the DataSource1 intermediary - point to it in the DataSource property. If necessary, you can disable some keys from the database control panel in the VisibleButtons property. And, although the control buttons are intuitive, it is possible to provide them with tooltips, for which you set the ShowHint property to True, and the text of the tooltips can be set/changed in the Hints property. A possible view of the application after connecting the DBNavigator component can be seen in the figure:

What is database creation? There are two types: desktop and client-server. To create desktops, you need a program like Access. You can easily find it in any office package. Now Access is produced by Infra-Office, Microsoft and many other creators of office suites. As for the client-server model, a simple model can be implemented using the Delphi “training” program. Serious databases today are written in Oracle and Visual Fox Pro. Data software platforms equipped with graphical tools for creating processed queries to databases of 500,000 and 1,000,000 or tens of millions of rows.

Let's consider the most promising platform for programming past years Delphi 6.0. Let's make a reservation right away that creating in Delphi is not just getting *.db or *bdb files, but installing it on Personal Computer, configuration and automation. So, in Delphi 6.0, database files are created in a subroutine located in the main menu of the development environment, Date Base Desktop 6.0.

Here you will find everything necessary tools to create database files, assign database type, and prepopulate. Please note that you will be able to create and operate only those files for which a driver is installed on the platform. For example, to work with a type, Visual Fox Pro 9.0 must be additionally installed in operating system external driver.

As for the work of a program with a database created in Delphi 6.0, without coexistence with the platform itself, then this problem solved by installation and configuration software package Data Base Engine 6.0 (BDE).

So, if you are determined to work with the Paradox file type, then by writing a program in specific folder on the hard drive, you must subsequently register the settings in the Data Base Engine 6.0 package, which include the paths to the files, their properties and the name of the database. This scheme works when you move the programs you create to another computer.

The Engine 6.0 package must be installed on the computer where you are installing machine code created in Delphi 6.0.

Creating a database in Delphi 6.0 is quite automated. A simple program for storing data with one database file is obtained with a few keystrokes. The main difficulty here lies in the implementation of relational forms of the database. So SDNF 3 can be implemented by setting up two DBGRID tables. And receiving various types reports in QReport is a whole story that I don’t even want to touch on. Let's just say that better code Do not throw the created program into the trash, since each custom report for people who do not know SQL will have to be programmed again...


Access creating a database in the office suite is not difficult. By logging into the program, you get access to the necessary tools for creating or editing an existing database. Here you don't have to put much effort into creating a report or a new project form. Everything is decided almost intuitively. Thus, to establish a connection between tables, graphical tools are used, which can connect the right key two necessary tables.

This article discusses only the creation of a database using educational and desktop packages. As for serious programming, without knowledge of the existence of these platforms you will never solve the problem of operating large databases.

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 the configuration 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.


2024 gtavrl.ru.