Access database management system
The purpose of the work: to master the technology of working with a DBMS using the example of Microsoft Access. Development of an information model of the database; creating database objects. Development of the organization of relations between tables in Access. Learn to independently master the capabilities of the Microsoft Access database management system, which are not reflected in this work.
1) Create a single-table database containing information about students.
2) Carry out a search by a given attribute.
3) Change the structure of the database by adding a new field. Create a select request.
4) Create a form and use it to add new entries.
5) Create a report using the Report Wizard for the specified fields.
6) Transfer data from Access to Word in two ways: “Merge with MS Word” and “Publish to MS Word”.
A database is a computer-implemented information model that reflects the state of objects and their relationships. An information model (or data structure ) is a collection of interrelated data. Databases (DB) are divided into three classes according to the types of information structures: tabular (relational), network, hierarchical.
The concept of a database management system (DBMS) is closely related to the concept of a database. DBMS is a set of software tools designed to create the structure of a new database, fill it with content, edit content, select displayed data in accordance with a given criterion, arrange, design and then issue it to output devices or transmission via communication channels.
A database created in the Access DBMS is a relational database, the main object of which is interrelated two-dimensional tables consisting of rows of the same type of records. Each line, in turn, is made up of fields and is called a record . If there are no records in the table, then this means that the database structure is formed only by a set of fields. By changing the composition of the fields of the base database, we change the structure of the database and, accordingly, get a new database.
Relationships between database tables make it possible to share data from different tables.
In Access DBMS, the process of creating a relational database involves creating a data schema. The data schema visually displays tables and relationships between them and ensures the use of relationships in data processing. The data schema sets the parameters for ensuring cohesive integrity in the database.
The main properties of the fields of tables in Microsoft Access DBMS:
Field name ─ determines how the data of this field should be accessed during automatic operations with the database (by default, field names are used as table column headings).
Field type ─ defines the type of data that can be contained in this field.
Field size ─ determines the maximum length (in characters) of data that can be placed in this field.
Field Format ─ determines how the data in the cells belonging to the field is formatted.
Input mask ─ defines the form in which data is entered in the field (data entry automation tool).
Caption ─ defines the header of the table column for the given field (if the caption is not specified, then the property Field name is used as the column header).
The default value is the value that is automatically entered into the field cells (data entry automation tool).
An assignment condition is a constraint used to validate data entry.
Error message is a text message that is displayed automatically when you try to enter erroneous data in the field.
Mode selection buttons
work with objects
Figure 1 Microsoft Access DBMS window.
Mandatory field ─ a property that determines the mandatory filling of this field when filling the database.
Empty strings ─ a property that allows the input of empty string data (mainly for text data).
Indexed field ─ If a field has this property, all operations related to searching or sorting records by the value stored in this field are significantly accelerated. This field also checks the value of records for duplicates.
Field properties differ depending on the data type . Microsoft Access databases work with the following types of data:
text ─ data type used to store regular unformatted text of limited size (up to 255 characters);
the Memo field is a special type for storing large amounts of text (up to 65,535 characters). Physically, only a pointer to the place in the database where the text is stored is stored in the field, but such a division is imperceptible to the user;
numeric ─ data type for storing real numbers;
date/time ─ data type for storing calendar date and current time;
monetary ─ data type for storing monetary amounts;
counter ─ a special data type for storing unique (not repeated in the field) natural numbers with automatic increment;
logical ─ type for storing logical data (can take only two values, for example, yes or no);
OLE object field ─ a special data type designed to store OLE objects, such as multimedia;
hyperlink is a special field for storing the URLs of Internet Web objects.
Before creating a database, the logical structure of the database must be determined – the composition of the table, their structure and inter-table relationships.
DB objects, in addition to tables, are queries, forms, reports, macros and modules, the creation of which greatly simplifies the user’s work with data arrays.
Queries provide fast and efficient access to data from one or more related tables. The result of the query execution is a table that can be used along with other database tables when processing data. When working with queries, data can be ordered, filtered, combined, and perform final calculations with them.
Forms are used to organize the entry of information into tables by various users, their use makes it easy to automate data entry and eliminate input errors. Forms can also be used to view queries and tables on the screen.
The report is generated to create a paper document, i.e. to print data.
In the Access database window, along with the list of objects, there are shortcuts for quickly launching Wizards or Constructors for creating a new object.
Access wizards automate the process of creating database tables, forms, queries, reports, and data access pages.
Creating database objects