Let's create a simple database application that displays information from the Tourists table and an entry in the Tourist Information table from a Microsoft Access database associated with the current record in the Tourists table.
To do this, let's create a blank Windows application. Environment appearance
development is shown in Figure 39.
Rice. 39. Empty application
In Figure 39, the "Data" component group is highlighted, which contains components for accessing and manipulating data.
The binding of the database data to the form is carried out by the "Binding Source" component. Let's transfer it to the form. After placing it on the form, the development environment takes the following form (Fig. 40).
Rice. 40. Component Binding Source on the form
The component is not visual, so it is displayed in an additional panel. The main property of the component is the DataSource property, which points to the data source. By default, the property is empty, so you need to form its value. When this property is selected, the following window appears in the properties window (Fig. 41).
Rice. 41. List of data sources
The list is currently empty, so you need to create a new data source by choosing the Add Project Data Source command to create a new data source and connect to it. The following dialog box appears (fig. 42).
Rice. 42. List of data sources
This dialog provides the following selection of data sources:
Database - Database;
Service - A service is some kind of service that provides data. Most often it is a Web service;
Object - An object for selecting an object that will generate data and objects to work with.
In our case, you need to select the "Database" item. A window for selecting a data connection appears (Fig. 43).
Rice. 43. Choosing a data connection
The purpose of this dialog is to create a connection string that will describe the connection parameters for the ADO engine, such as the type of database, its location, usernames, security features, etc.
The drop-down list of the dialog contains all previously created connections. If the required connection is not in the list, then you should use the "New connection" button. Pressing the button leads to the appearance of the following dialog (Fig. 44).
In this dialog, you select the type of data source (in this case Microsoft Access), the name of the database (in this case, the name and location of the database file), the username and password used to connect to the database. The "Advanced" button allows you to set a large number of parameters related to various details of the ADO mechanism. Using the "Test Connection" button will make sure that the entered parameters are correct and that the connection is working.
Rice. 44. Creating a new connection
The last step of the dialog is to select those tables or other database objects that are required in this data source. The selection window is shown in Figure 45.
Rice. 45. Selecting the required tables
In this window, the tables "Tourists" and "Tourist Information" are selected. Since no objects other than tables were created in the database, only tables are displayed in Figure 45. This completes the creation of the data source. When you click Finish, a DataSet appears next to the BindingSource on the form.
Now the data connected above must be displayed on the form. The simplest way to display data is to use the DataGridView component from the Data component group. The component is visual and looks like this on the form (Fig. 46).
Rice. 46. Component DataGridView
The component settings window immediately appears, which determines its ability to edit data: "Enable Adding", "Enable Editing", "Enable Deleting"; the ability to change the sequence of columns: "Enable Column Reordering"; and also the ability to dock in the parent container.
In order for the component to display data, you must select a data source in the drop-down list. Selecting the drop-down list leads to the appearance of the following dialog (Fig. 47).
Rice. 47. Selecting a data source for DataGridView
In this case, we have selected the "Tourists" table as the data source. This selection changes the display as follows (Fig. 48).
Rice. 48. Component DataGridView displays the structure of the table
In the figure, you can see that there is another BindingSource component and a TableAdapter component that works with the Tourists table. Please note that at design-time or during development, data from the table is not displayed.
Now you need to display the data from the linked Tourist Information table. To do this, place another DataGridView component on the form and select the following as a data source (Fig. 49).
Rice. 49. Selecting a data source for the second DataGridView
Here, the data source is not the "Tourist Information" table itself, but the Binding Source between the "Tourists" and "Tourist Information" tables. This selection ensures that only those rows are selected from the Tourist Information table that are associated with the current row in the Tourists table. This choice also ensures that the associated data is updated and deleted correctly. The operation of the resulting application is shown in Figure 50.
Rice. 50. Database application at work
Navigating through the data using the arrow keys is inconvenient. There is a BindingNavigator component to simplify navigation through the data. Place it on the form (fig. 51).
Rice. 51. The BindingNavigator component on the form
This component allows you to navigate between table records, add and delete table rows. The functionality and appearance of the component is customizable as it is a ToolStripContainer menu bar.
The property that defines the table to be navigated through is the BindingSource property. Set the value of this property to TouristBindingSource. In operation, the component looks like this (Fig. 52).
Rice. 52. The BindingNavigator component at work
Editing data in the cells of the DataGridView component with appropriate settings is possible, but inconvenient and not rational. In particular, it is difficult to check entered values for errors. Therefore, for the "Tourists" table, we will make a screen form that allows displaying data in the TextBox components and editing them. To do this, place a Panel-type container on the form, and on it three TextBox components as follows (Fig. 53).
Rice. 53. Screen panel for editing records of the table "Tourists"
Now it is necessary to bind the TextBox components to the corresponding fields of the "Tourists" table. To do this, use the property from the DataBindings group - Advanced, shown in Figure 54.
Rice. 54. Property "DataBindings - Advanced"
Selecting this property leads to the appearance of the dialog shown in Figure 55. This dialog allows you to perform not only data binding, but also to set an event within which the data will be updated, as well as formatting the data when it is displayed.
For the top TextBox component in the Binding drop-down list, select the data source "touristsBmdmgSource" and the source field - "Lastname". For the middle and bottom TextBox components, select the same data source and the "Name" and "Patronymic" fields, respectively.
The developed application in operation looks as follows (Fig. 56).
Rice. 55. Dialog box for the "DataBindings - Advanced" property
Rice. 56. Binding data to visual components
However, when changes are made, all new data remains only on the form. They are not stored in the database, and of course they will not be present when the application is called again. This is because the data has been loaded into a DataSet, which is an in-memory copy of the table. All actions are performed with this copy. In order for the changes to be reflected in the database, you must execute the Update method of the TableAdapter class. Thus, in the developed application it is necessary to place the Update button and write the following program code to the Click event handler:
touristsTableAdapteгUpdate (bDTur_firmDataSet); info_about_touristsTableAdapter.Update (bDTur_firmDataSet);
This code updates the information in the "Tourists" and "Tourist Information" tables provided by the data source. Note that this method is overloaded, and its variants allow you to update both a single row of a table and a group of rows.
Introduction
Database (DB) - it is a named collection of data that reflects the state of objects and their relationships in the domain.
DBMS - it is a computer program responsible for creating, editing, deleting and usually storing databases (DB) or collections of data records.
DBMS functions:
1. Determination of the structure of the created database, its initialization and initial loading.
2. Providing users with the ability to manipulate data (fetching the necessary data, performing calculations, developing an input / output interface, visualization).
3. Ensuring the logical and physical independence of data.
4. Protection of the logical integrity of the database.
5. Protection of physical integrity.
6. Managing user permissions to access the database.
7. Synchronization of work of several users.
8. Management of resources of the storage environment.
9. Support for the activities of system personnel.
Chapter 1
1.1. DBMS types:
Relational
Relational DBMSs are the most common type of database management systems at the moment. They are really easy to use. Relational DBMSs have this name because they store ordered data in tables. The relational model is based on storing ordered data in columns and rows. Data can be linked within the same table or different tables. Types of relationalDBMS can be inferior in performance compared to other types of database management systems, but they do not have problems with computing power and memory search in modern PCs. Data in this type of DBMS is stored in a precisely defined form and is usually managed using the SQL (Structured Query Language) programming language. You can also specify the most popular types of DBMS - Oracle, MSSqlServer, IBMDB2, PostgreSQL and many others.
Database management systems with flat files
Flat file DBMSs are arguably the simplest of all. They are also sometimes called the flat model. The idea is to run a lonely application that contains the program settings and the original storage model format. Flat files in formatted rows and columns assume that each item in a particular model contains the same information. One well-known example of this type of DBMS is CSV (Comma Separated Values) and another is spreadsheets like MS Excel.
Hierarchical
Hierarchical DBMSs are based on the use of a family tree model. Here, the elements usually have a one-to-many relationship and it is good to store information with a description of the elements, characteristics, etc. Here you can store a book with chapters and verses. Hierarchical typesDBMS can be used to store databases of songs, recipes, phone models and anything else that can be stored in a nested format. Hierarchical types of database management systems can be inferior in efficiency when working with various real objects. One example of hierarchical DBMS types is XML document.
Network
Networked database management systems use a data model similar to a hierarchical one. The main difference is that the structure of a networked DBMS allows for many parents for many descendants. Network types of DBMS are based on records and sets, and use SQL for their work. Web-based database management systems tend to be very flexible, but they became rarely used after 1960-1970. In search of the required element, the requesting program goes through all the installed elements, which can be too cumbersome. This type of database management systems has been practically replaced by relational ones.
Choosing a program for writing a database
In my term paper, I used a relational database using Microsoft Access.
Microsoft Access Is one of the most popular DBMS for the Microsoft Windows operating system. In addition, Access is a powerful development platform with a flexible and functional IDE. Microsoft Access can be used as a tool for the development and deployment of broad-based information business systems.
Access uses the following basic field types:
text: intended for textual information and numbers, when there is no need to perform mathematical operations with them;
numerical: intended for numbers when used in mathematical calculations;
MEMO: intended for storing arbitrary text or comments (up to 64,000 characters);
monetary: designed to store numbers representing monetary amounts;
date Time: designed to store information about date and time;
counter: a special numeric field designed to automatically add a unique number to the current record in the data table.
logical: designed to store only two values "True" and "False";
OLE object field: intended for storing objects created by other applications (pictures, graphs, diagrams).
There are several ways in Microsoft Access to select only the data you want when you search for a specific value, single record, or group of records.
Using the Search dialog box easy to find specific records or specific values in fields. As each occurrence of the required element is found, it traverses the records. If you need to replace specific values found during the search, use the Replace dialog box.
Inquiries make it possible to work with a specific set of records that satisfy the conditions specified for one or more database tables. When a query is executed, it becomes possible to independently work with this set of records in a specific form or in an object in table mode.
Sorting makes it possible to arrange the rows in a table according to the contents of one or more columns. For sorting, select the field by which the sorting will be carried out, and press the button “Sort ascending” or “Sort descending” on the toolbar.
Filtration allows you to temporarily isolate and view a specific set of records that meet specific conditions. For filtering, you select the data by which you want to filter, and click the "Filter by selection" button on the toolbar. In order to correct the filter, use the "Change filter" button. To set a more detailed filtering condition in the Record menu, use the Filter command and the Advanced filter subcommand. To apply a regular or advanced filter, use the "Apply Filter" button on the toolbar.
Chapter 2
Formulation of the problem
The purpose of the Hotel database is to automate the process of accounting and analysis of hotel data.
Tasks solved when using the database:
Storage and retrieval of data about hotel employees;
Storage and retrieval of information about hotel clients;
Storing and searching information about rooms;
Storing data on booking, check-in, check-out of clients and calculation of payment for accommodation
Calculation and analysis of information about the receipt of payment for accommodation from the company's clients;
Search for information on the main items: booked rooms, check-in for the period, check-out for the period.
Database creation
To create a MS Access database, the Table Designer was used, which allows you to define the required table structure.
To display complete information on the state of the hotel, areas of its activities and reporting on the work performed, the following tables have been created:
1. "Employees"
The table has the following fields: employee code(key field) - data type: counter; Full Name- data type: text, size - 50, optional, no matches allowed; Date of Birth–Data type: date \ time, optional; education–Data type: substitution wizard - a set of fixed data (higher, secondary); position–Master of substitutions (list of available positions); address- data type: text, size-50, optional; telephone- data type: numeric, optional field; salary- data type: monetary, field format - rubles, optional. The table in design mode looks like this:
2. "Positions"
This table has the following fields: position code- counter (key field); position- data type: text (set of basic positions).
3. "Rooms"
This table has the following fields: room code- data type counter (key field); category- data type: master of productions (single, double, junior suite, suite)
Fields: category code- data type: counter (key field), room- text; category- substitution wizard (numbers); number of seats- data type: numeric long integer, optional; number of rooms- similarly; Additional Information- data type: MEMO; cost per day- data type: monetary, format - rubles, optional.
5. "Room status"
Fields: room code- counter, key field; room- data type of substitution mater (similar field from the table "Room categories"); employee(maid) - substitution master (from a query according to the "employees" table, with the selection condition "maid"), fields - booking, check-in, check-out- data type: date \ time, optional field; client - lookup wizard (from the Clients table).
6. "Clients"
Fields: Client code Full Name- data type: text, field size - 50, required; Date of Birth- data type: date \ time, required; floor- master of productions (husband, wives); type of document- master of productions (passport, high-quality, ID card), obligatory; series- text, required; room- numeric, required; issued by- text, field size - 50, required, date of issue- date \ time, mandatory.
Fields: child code- data type: counter (key field); Client code- data type: substitution wizard (similar field from the "clients" table); Full Name- data type: text, field size - 50; Date of Birth- data type: date \ time.
The data schema looks like:
The table State of rooms and Employees are linked through a request for Employees, with the selection condition "maid".
For the convenience of work, requests have been created for the Hotel database.
Request # 1 - "Reservation"
The Criteria line contains the following condition:
> = [s] And<=[по]
Significant selection of fields for a specific period of time.
Queries 2 and 3 are similarly created.
Request # 2 - "Check-in to the rooms"
Request # 3 - "Check out of the rooms"
Request # 4 - “Clients with children”.
The query in design mode looks like this:
This request systematizes information about the presence of children for clients, which is necessary for certain discounts, benefits, etc.
Request # 5 - "Payment for accommodation".
The request contains an expression that reflects the calculation of the cost of living for each client.
Request # 6 - "Paid per month".
The request contains an expression reflecting the calculation of the cost of living for each client:
Payment for accommodation: (State_number. Check-out-State_number.Check-in + 1) * Categories_of rooms. [Cost per day].
And also the selection of fields for the period.
Request # 7 - "Payment for the schedule".
Same as query 6, except for selecting a period for selecting fields. Based on the data from this query, charts for analysis and pivot tables are built.
Request # 8 - "Posts Request".
Request with the condition of selection by employees "maid". It is used to substitute data into the Number state table.
The creation of these requests greatly simplifies the reporting in the hotel, conveniently organizes the information, and allows you to quickly use it and make changes. The list of requests is presented below:
The reports are as follows:
Working with the database begins by opening the Hotel form:
On the left are buttons for opening reports, on the right for entering and viewing data on main items.
For example, the Information about numbers form looks like this:
Here you can see all the information about the hotel room, as well as the list of all rooms, in which I choose the position, we can immediately see all the information about the room. In the lower left corner there are buttons for the convenience of working with records. In the upper right, there is a button to open the Enter room category form.
Using the button Reservation Check-in Check-out, we will open the form:
In which there are three tabs:
Room Information - complete information about available rooms;
Dynamics of receipts - dynamics of receipts by number:
Payment summary - summary of payment per room:
The Hotel Staff button opens the following form:
Here you can view information about employees, as well as enter the required information.
By displaying reports, the tasks of finding information about the status of rooms are solved: booking, check-in, check-out, as well as displaying information about payment, displaying a receipt for the client.
Thus, this base solves the assigned tasks.
Conclusion
In the course of the course work, a database "Hotel" was created. This database is intended for employees as it simplifies the customer service process.
This database includes: tables, queries, forms and reports, as well as an autorun window for ease of use.
Literature:
1. Date, K., J. Introduction to database systems. 6th ed. - TO.; M., St. Petersburg: "Williams", 2000. - 848p.
2. Khomonenko A.D., Tsygankov V.M., Maltsev M.G. Databases: Textbook for higher educational institutions / Ed. prof. HELL. Khomonenko. - SPb .: KORONA print, 2002 .-- 672s.
3. V.V. Korneev, A.F. Gareev, S.V. V. V. Vasyutin Reich Databases. Intelligent information processing. - M .: Knowledge, 2001.- 496s.
4. Pragh K., Irwin M., “Access User Bible for Windows 2000”, K.: Dialectics, 2001 - 576s ..
5. Garnaev A.S., "VBA Self-Study Guide", St. Petersburg, 1999. - 104p.
6. Microsoft Access 2010. Application development on a real example - G. Hurwitz - 496s.
7. Designing databases. Microsoft Access DBMS. Study guide - N.N. Grinchenko, E.V. Gusev, N.P. Makarov - 240p.
8. Access 2007. Effective use - V. E. Koshelev - 590s.
Almost every organization has its own database. But what is really there, even sites use them to make it easier and easier to work with information. Indeed, they allow you to make calculations without any problems, quickly find the data you need, and in general, simply create order in any information.
Often, programmers are engaged in their creation, because this is a complex process that is taught in higher educational institutions. There are also many lessons, courses and sets of programs for creating software for developing databases, a really great variety, you can easily get confused. This article will discuss some of the basic programs for database development.
About SQL
SQL is a programming language used to create databases. If you install it on your computer and start creating a database, it will not be very convenient. This is due to the fact that SQL itself does not have any graphical shell, and queries to the database must be sent in general via the command line. For this reason, various kinds of programs have appeared that simplify the development of databases. However, learning the basics of this language is still worth it. Suddenly you need to make a request, and the program does not work correctly.
Microsoft Access
This program for creating databases is exactly familiar to many. After all, it comes in the Microsoft Office suite of programs. This program is one of the easiest to learn, because knowledge of the SQL programming language is practically not needed there. You can only designate which query to make, and the program will compose the SQL query itself.
About the relevance of the program. Until now, the databases of many organizations have been made using Microsoft Access. Indeed, the program itself is very light, there is an intuitive interface. What's more, the basics of Access are even taught in high school and early college!
PhpMyAdmin
Access, of course, is a good program, but if you need a database for a website, it won't do it. Then PhpMyAdmin comes to the rescue. It is a very useful program for creating databases. Installation on a computer takes some time, and during installation it is easy to do something wrong and will not work. Therefore, when installing this program to create databases, you must strictly follow the instructions. But another plus of PhpMyAdmin is that it can be accessed via the Internet as a website! For example, you have a website that is powered by WordPress. He will have a database. And if you have a site on some good hosting, then, most likely, work with databases will be carried out through PhpMyAdmin, and it will be possible to access it through the hosting control panel.
Another program for creating databases. It's free, but there is also a paid version with improved features. It is easy to create links with tables in this program, and in general, it is just convenient to work with. Also a plus is that you can show the database in graphical form. Most people prefer this program when working with databases. In principle, PhpMyAdmin is not inferior in its capabilities, but nevertheless it is more intended for working with a database of sites.
This article has covered the basic programs for creating databases. In fact, there are a lot of them, so everyone chooses a tool for themselves, but if you are just getting started and want to study this area, then it is recommended to work with MySQL WorkBench. Once you learn the basics of SQL, it won't make a big difference for you where to work, because the queries are the same everywhere. It is also convenient that, having created a database in one program, you can open it through another software, which is also designed to work with the database. When creating software with a database, you cannot do without this knowledge. Moreover, having mastered SQL, you can even create your own software for developing and editing databases.
Many companies create various multifunctional applications to facilitate the management, development and administration of databases.
Most relational databases, with the exception of MS Access, have two separate components: the back-end, where the data is stored, and the front-end, the user interface for interacting with the data. This type of design is smart enough as it parallelizes a two-tier programming model that decouples the data layer from the user interface and allows the software market to focus directly on improving its products. This model opens the door to third parties who create their applications to interact with various databases.
On the Internet, everyone can find many products for developing and administering MySQL databases. We decided to collect the 10 most popular tools in one article so that you can save your time.
1. Workbench
The first place rightfully belongs to the Workbench tool (developed by Sun Systems / Oracle), which can run on Microsoft Windows, Mac OS X and Linux platforms. Workbench combines database development and administration and is the successor to DBDesigner4.MySQL Workbench distributed under a free license - Community Edition and with an annual paid subscription - Standard Edition. The latter includes additional features that can significantly improve the productivity of both developers and database administrators.
The cost is free.
What makes the Workbench popular?
- the ability to present the database model in graphical form, as well as editing data in the table;
- the presence of a simple and functional mechanism for creating relationships between table fields, among which a many-to-many relationship is implemented with the ability to create a table of relationships;
- the Reverse Engineering function allows you to restore the structure of tables and links from the one that was implemented earlier and stored on the database server;
- the presence of an editor for SQL queries, which makes it possible to receive a response in a tabular form when sending to the server, and other features.
2. Navicat
Second place goes to Navicat(developed by PremiumSoft CyberTech Ltd) is a database development and administration tool that runs on any MySQL server starting from version 3.21. For MySQL, Navicat is available to run on Microsoft Windows, Mac OS X and Linux platforms.
Product prices range from $ 199 to $ 379.
What makes Navicat popular?
- the presence of a visual query designer;
- the ability to import, export and backup data;
- the ability to create reports;
- SSH and HTTP tunneling;
- migration and synchronization of data and structure;
- tool for scheduling tasks and other features.
3. PHPMyAdmin
PHPMyAdmin is a free open source application designed to administer the MySQL database management system. PHPMyAdmin is a web interface with which you can administer the MySQL server, run commands and view the contents of tables and databases through a browser.
The cost is free.
What makes PHPMyAdmin popular?
- the ability to manage the MySQL DBMS without directly entering SQL commands;
- how the PHPMyAdmin control panel provides the ability to administer dedicated databases;
- intensive development;
- the ability to integrate PHPMyAdmin into your own development thanks to the GNU General Public License and other features.
4.dbForge Studio for MySQL
dbForge Studio for MySQL is a tool of interest to both MySQL users and database developers. With its help, you will be able to easily automate routine work and save time. Today dbForge Studio for MySQL is available in three editions: Express, Standard and Professional, which allows you to choose the tool that is right for you. DbForge Studio for MySQL is available in both commercial and free versions.
There are both free and paid versions, with the latter priced at $ 49.95 (Standard Edition) and $ 99.99 (Professional Edition).
What makes dbForge Studio popular?
- availability of funds for centralized administration;
- database comparison tools;
- visual query profiler;
- the ability to manage user privileges;
- the presence of the Database Designer, which allows you to build visual diagrams;
- improved work with database projects and other features.
5. HeidiSQL
HeidiSQL is a free tool for database management. A decent alternative to PHPMyAdmin that allows you to create and edit tables, views, triggers, procedures, and view and edit data. HeidiSQL also provides the ability to export data both to a SQL file and to the clipboard on other servers.
The cost is free.
What makes HeidiSQL popular?
- the ability to connect to the server using the command line;
- the ability to batch optimize and restore tables;
- the ability to edit columns, indexes and foreign keys of tables, edit the body and parameters of SQL procedures, triggers, etc.;
- simple formatting of unordered SQL;
- synchronization of a table between different databases and other possibilities.
6. SQL Maestro for MySQL
SQL Maestro for MySQL- a tool for administration, development and management of the most demanded DBMS. The user-friendly graphical interface makes it possible to execute SQL queries and scripts, manage user privileges, export and create backup copies of data.
Depending on the chosen license and use case, the cost of this tool ranges from $ 99 to $ 1,949.
What makes SQL Maestro for MySQL popular?
- support for MySQL server versions from version 3.23;
- the presence of a database designer;
- the ability to edit, group, sort and filter data;
- visual query designer;
- SSH and HTTP tunneling;
- BLOB editor and other features.
7. EMS SQL Manager for MySQL
EMS SQL Manager for MySQL is a database development and administration tool that supports various MySQL functions and works with all MySQL versions older than 3.23. With it, you have the ability to visually edit, import and export databases, execute SQL scripts, manage user privileges, and visually design MySQL databases.
There are paid and free versions of the application. The latter has a number of functional limitations. The cost of the paid version ranges from $ 95 - $ 245.
What makes EMS SQL Manager for MySQL popular?
- support for UTF8 data;
- easy management of various MySQL objects;
- compatibility with all versions from 3.23 to 6.0 inclusive;
- availability of graphic and text tools for generating queries;
- SSH and HTTP tunneling;
- convenient report designer and other features.
8. SQLyog
SQLyog is one of the most powerful tools that combines the capabilities of MySQL Administrator, PHPMyAdmin and some other tools for database administration and development. SQLyog runs on Microsoft Windows, Windows NT platforms. and Linux with Wine.
Both free and paid versions of SQLyog are available. Paid version costs from $ 99 to $ 1499 (varies depending on the number of users and license, with or without support).
What makes SQLyog popular?
- convenient query builder;
- the ability to synchronize data;
- support for unicode;
- SSH and HTTP, HTTPS tunneling;
- "Smart" auto-completion of work;
- intelligent code completion and other features.
9. DBTools Manager
DBTools Manager- application for data management, with built-in support for MySQL, PostgreSQL, MSAccess, MSSQL Server, Oracle and other databases. Supported platforms: Windows 2000, XP, Vista, 7.
DBTools Manager is available in free (Standard) and paid (Enterprise) versions. The cost is $ 69.90 per license and discounts apply when purchasing multiple licenses.
You can learn more about and purchase DBTools Manager here
