Home-Internet-Download presentation and topic microsoft access. Presentation on the topic: ACCESS Database

Download presentation and topic microsoft access. Presentation on the topic: ACCESS Database










1 of 9

Presentation on the topic:

Slide no. 1

Slide description:

Slide no. 2

Slide description:

Slide no. 3

Slide description:

As a rule, the database exists independently of individual programs. Separating data from programs allows different programs to use the same data for their own purposes. The ideological value of databases is explained by the fact that they are based on the concept of an information data model, that is, some abstraction of data representation. Information in the database is organized in different ways. There are three main structures for presenting data in a database: hierarchical (tree); network relational (tabular).

Slide no. 4

Slide description:

Structures for presenting data in a database Hierarchical Relational Network In most cases, relational databases are used, in which data is presented in the form of related files consisting of records. The structure of all records in the files is the same, and the number of records in the file is variable. The data elements that make up each record are called fields. One record contains information about one object of the real system, the model of which is presented in the table. Entry 1 Entry 2

Slide no. 5

Slide description:

Fields are various characteristics (attributes) of an object. Field values ​​refer to one object. Since all records have the same fields (with different values), it is convenient to give fields unique names. Records differ in the meaning of the keys. The main key in a database is a field (a set of fields) whose value is not repeated in different records. Very often, the sequential record number is used as the master key. Each field has one very important property associated with it - the field type. A type defines the set of values ​​that a given field can take on in different records. The type of quantity determines the actions that can be performed with it. Relational databases use four main types of fields: numeric (integer and real), character, date, and logical.

Slide no. 6

Slide description:

Example 1. Describe the structure of the “TV program for the week” database. In the table, an integer numeric type is used for the “Channel” field, and a real type is used for the “Time” field.

Slide no. 7

Slide description:

The database itself cannot service user requests for searching and processing information. A database is just a “warehouse” in which information is stored. The role of the “storekeeper” in this warehouse is performed by special software systems called database management systems (DBMS). All DBMSs support in one form or another four basic operations: add one or more records to the database; delete one or more records from the database; find one or more records in the database that satisfy a given condition; update the value of some fields in the database.

Slide no. 8

Slide description:

Most DBMSs also support a mechanism for connections between various files included in the database. For example, a connection can be established explicitly when the value of some fields is a link to another file; such DBMSs are called network DBMSs, or a connection can be established implicitly, for example, by the coincidence of field values ​​in different files. Such DBMSs are called relational. A relational database makes it easier to find, analyze, maintain, and protect data because it is stored in one place. MS Access is a functionally complete relational type DBMS, in which all the tools and capabilities typical of modern DBMSs are reasonably balanced.

Slide no. 9

Slide description:

Questions for self-controlWhat is a database? What data storage structure is used in the database? What is special about a relational database? What is the convenience of tabular presentation of information? How is the data structure described in a relational database? What is a record, a record field? What information do they contain? Define the following concepts: field name, field value, field type. What are the field types? What is a DBMS? What is the purpose of this type of software? Which DBMS are called relational? What are the main functions of a DBMS? What type of DBMS is Access?


  • Program definition;
  • Purpose and functions;
  • Objects;
  • Data types;
  • Program menu;
  • Methods for creating objects;
  • Examples of objects.

On modern personal computers, relational DBMSs are most widespread. ACCESS.

Application Microsoft Access is a desktop relational database management system ( DBMS), designed to work on a stand-alone personal computer (PC) or local area network running the Microsoft Windows family of operating systems).

Microsoft Access DBMS has powerful, convenient and flexible means of visual design of objects with the help of Wizards, which allows the user, with minimal preliminary preparation, to quickly create a full-fledged information system at the level of tables, queries, forms and reports.

Databases have extension .mdb.




MAIN DB OBJECTS

Table – an object designed to store data in the form of records and fields.

Form – an object designed to facilitate data entry.

Request – an object that allows you to obtain the necessary data from one or more tables.

Report – an object intended for printing data.


ANY TABLE CAN BE PRESENTED IN TWO MODES:

In mode tables, intended for data entry, viewing and editing.

In mode designer , designed to create a table structure, change the data type, change the table structure (adding and removing fields).


ORGANIZATION OF WORK WITH TABLES

Table- main (base) database object. All other objects are created based on existing tables.

  • IN tables all data available in the database is stored; And also tables
  • IN tables all data available in the database is stored;
  • And also tables store the database structure (fields, their types and properties)


There are the following modes for creating a request:

  • In master mode.
  • In design mode.

In mode designer. A window appears on the screen, a new request, which lists all the methods for creating a request. This:

  • Constructor.
  • Simple request.
  • Cross request.
  • Duplicate entries.
  • Records without subordinates.

ORGANIZATION OF WORK WITH REQUESTS

Request is a database object that allows you to perform basic data processing operations:

  • sorting,
  • filtration,
  • combining data from different sources,
  • data conversion
  • save the results with a name in order to use them later as needed.

  • Form is the most convenient means of viewing and entering data.
  • Form is created on the basis of already developed tables and can include not only one, but also several related tables.
  • The fastest way to create forms- with the help of a master.
  • Form may have a tabular form, but most often they use a ribbon form - each object has a separate card.

  • Report– the ability to create various forms of presentation of output information.
  • Convenience of computer reports is that they allow you to group information according to specified characteristics, enter the final fields for counting records into groups throughout the entire database.
  • Report is a convenient form of presenting information for printing.


DATABASE PROTECTION

Access has the best protection system among all desktop DBMSs. The built-in wizard allows you to create groups, users, and assign access rights to all objects, including modules. Each user can be provided with an individual password.

The protection system is available both visually and programmatically.


Microsoft Access is the most popular desktop database management system today. Its success is largely due to its inclusion in the Microsoft Office family. This software product is designed for both beginners and advanced users.

General information. An essential and integral component Creating tables for a new database Tables are an essential and integral component of any database. This course, which walks you through creating tables for a new database in a step-by-step manner, requires only a beginning level of knowledge.


Course objectives 1.create tables in table mode; 2.set data types for fields in the table; 3.create tables in design mode, set the primary key and data types for the table; 4.create lookup fields with a list of selection options; 5.change values ​​in existing lookup fields in design mode. Creating tables for a new database


Creating tables Creating tables for a new database Methods for creating tables. This course will create an asset tracking database, eliminating the need for cumbersome spreadsheets. The previous course looked at designing tables for a new database (fields, data types, primary and foreign keys). Now it's time to start creating tables.


Creating tables Creating tables for a new database Methods for creating tables. In a relational database, all information is stored exclusively in tables, making them a key component of the database. No tables means no database. This course focuses on the basic tools for creating tables, table mode and designer mode.


Creating tables Creating tables for a new database Methods for creating tables. Here's a breakdown of the process: In Datasheet view, to create a table, you click the header of an empty field, select a data type, and enter a name for the field. This way, you just need to click on the fields in the table and enter text, as will be demonstrated next. For some tables, you can save time by using the Quick Start fields, predefined sets of fields that meet some basic business needs, such as storing addresses and start and end dates. You just need to select the appropriate set of fields from the menu.


Creating tables Creating tables for a new database Methods for creating tables. Here's a description of the process: Unlike Datasheet view, Design view allows you to control all the fields and properties of a table. In this course, you will use Design view to create a table and change the values ​​in a lookup field (a field that contains a list of choices). Keep in mind that in a database that is intended to be published to SharePoint, you can only create tables in Datasheet view.


Creating a table in Datasheet mode Creating tables for a new database Process in Datasheet mode. Table mode is a visual tool for creating tables. Both when you create a new empty database or when you add a table to an existing one, the new table opens in Datasheet view. Please note that the new table contains a "Code" field. This is the primary key, so you no longer need to create it.


Creating a table in Datasheet mode Creating tables for a new database Process in Datasheet mode. Table mode is a visual tool for creating tables. To add fields, click the title of the first empty field (words Click to add). A data type menu appears from which you can select a data type for the field. After this, you also have the opportunity to change the field title.


Creating a table in Datasheet mode Creating tables for a new database Process in Datasheet mode. Table mode is a visual tool for creating tables. Type a name for the field and press ENTER. The focus will move to the next field for which you need to repeat the same procedure. Remember that field names that contain multiple words should not use spaces between them.


Creating a table in Datasheet mode Creating tables for a new database Process in Datasheet mode. Table mode is a visual tool for creating tables. When you've finished creating fields, press CTRL+S or the Save button on the Quick Access toolbar. The Save dialog box opens, asking you to enter a name for the table and save it.


Save time using Quick Start fields Create tables for your new database Add Quick Start fields to your table. The fields in the Quick Start section allow you to quickly create individual table segments. These fields store standard business data, and all field names and data types are already defined.


Save time using Quick Start fields Create tables for your new database Add Quick Start fields to your table. Open a table in Datasheet view, and on the Fields tab, in the Add/Delete group, click More Fields. A list will open. Scroll to the Quick Start section and select the field type you want (for example, Address or Name). As a result, the fields, along with their names and data types, will be automatically added to the table by Access.


Save time using Quick Start fields Create tables for your new database Add Quick Start fields to your table. New fields can be used immediately after you start entering data into them, or you can rename or delete them. It may appear that there are spaces in the field names. These are not actually names, but labels - displayable, clear text associated with each field name.


Creating a table in design mode Creating tables for a new database Using design mode. In Design mode, you can create tables from scratch, as well as set and change any properties for each field. You can also use Design view to open existing tables to add, delete, and edit fields.


Creating a table in design mode Creating tables for a new database Using design mode. On the Create tab, in the Tables group, click Table Designer. In the Designer Field Name column, enter the table field names. Typically, the first field created is the primary key field. Remember that you don't need to add foreign keys at this stage; you can do that when you create the relationships.


Creating a table in design mode Creating tables for a new database Using design mode. In the Data Type column, in the list next to the field name, select a data type for it. As always, save your changes and give the new table a name that describes the data it stores. In the Field Properties area, you can set properties for individual fields.


Adding and saving data Creating tables for a new database The process of saving data. Once you've completed creating your tables, you can add more records. This is the easiest way to test the tables and make sure that the necessary data is stored in them. In this case, you need to remember some rules.


Adding and saving data Creating tables for a new database The process of saving data. When you enter or change data to place it in the database, you do not need to click the Save button. All you have to do is move the focus to another entry. To do this, in a table or so-called multi-item form, you can click on another row. You can also move focus to the next entry by using the Tab key or the arrow keys. All of these actions result in new data being saved.




Using record navigation buttons Creating tables for a new database Record navigation buttons in Access. After creating tables, you need to learn how to use the record navigation buttons. They are located in the lower left corner of tables, as well as in query results and on most forms.


Using record navigation buttons Creating tables for a new database Record navigation buttons in Access. Using these buttons you can go to the desired data. The First Record button allows you to move to the first record in the table or query results. The Previous Entry button allows you to go to the previous entry. The Current Record field displays records in sequential order and also displays the selected record. The Next Entry button allows you to move to the next entry.


Using record navigation buttons Creating tables for a new database Record navigation buttons in Access. Using these buttons you can go to the desired data. The Last Entry button allows you to go to the last entry. To add data, click the New (empty) entry button.


Adding a Lookup Field to a Table Creating Tables for a New Database Using the Lookup Wizard. You can also use a lookup field instead of a table. Let's assume that you need to record information about the storage locations of company assets. If you have a large number of locations, such as rooms on multiple floors, you can create a table for this data to make it easier to work with. However, if there are only a few locations, it makes sense to store information about them in a lookup field.


Adding a Lookup Field to a Table Creating Tables for a New Database Using the Lookup Wizard. The list of options can be stored in the lookup field itself or loaded from a field in another table. Below are instructions for creating a lookup field with an internal list of choices (called a list of values ​​in Access).


Adding a Lookup Field to a Table Creating Tables for a New Database Using the Lookup Wizard. Open a table in Datasheet view, and on the Fields tab, in the Add/Delete group, click More Fields. Select Substitution and Relation from the menu. The Lookup Wizard will launch. On the first page of the wizard, select the A fixed set of values ​​will be entered option and click Next.


Adding a Lookup Field to a Table Creating Tables for a New Database Using the Lookup Wizard. On the next page of the wizard, enter 1 in the Number of Columns field, and then enter choices into the table (one per row). On the third page of the wizard, enter a name for the new field and click Finish.


Exercise Suggestions 1.Create a Suppliers table in Datasheet view. 2.Create the Support table in Design view. 3.Create the “Assets” table. 4.Create lookup fields in Datasheet view. 5.Create lookup fields in Design view. Create tables for a new database Interactive exercisesInteractive exercises (Access 2010 required)




Test question 1 Creating tables for a new database Correct. The "Code" field is used as the primary key in the new table. You can change its name or replace it with a different primary key, but the new table always contains a primary key. When creating a table in Datasheet view, you must specify a primary key field. Answer: 2. Incorrect.








Test question 3 Creating tables for a new database Data types can be changed if necessary, but they are already set. When you create a table using the fields from the Quick Start section, you must set their data types. Answer: 2. Incorrect.


Test question 4 Select the syntactically correct option for the list of values ​​from those suggested below. (Choose one answer.) Creating tables for a new database 1."Option 1","Option 2","Option 3" 2."Option 1";"Option 2";"Option 3" 3."Option 1" :"Option 2":"Option 3"


Test Question 4 Create Tables for a New Database Selections are enclosed in double quotes and separated by semicolons. Select the syntactically correct option for the list of values ​​from those suggested below. Answer: 2. “Option 1”; “Option 2”; “Option 3”





MS Office Access interface

Microsoft Office Access 2010

A database in Access is the set of all the tables, queries, forms, reports, macros and modules that make up a complete system.

Database objects – tables, queries, forms, reports, macros and modules. In addition, the Access application contains several other objects, including relationships, database properties, and import and export specifications.

You can use Access to manage all your data in one file. You can use the following elements in an Access database file:

tables for storing data;

queries to search and retrieve only the necessary data;

forms for viewing, adding and changing data in tables;

reports for analyzing and printing data in a specific format.

Microsoft Access Database Files

Report Form

Microsoft Access Database Files

1. Data is stored once in one table, but viewed from different locations. When data changes, it is automatically updated wherever it appears.

4. Data is displayed and printed using a report.

Tables and relationships

Table – data storage; this is the starting point when creating an application. Whether the data is stored in an Access database or the application references it by linking external tables, all other Access objects reference tables directly or indirectly.

Requests

A query helps you find and retrieve data that meets specified conditions, including data from multiple tables. The query can also be used to update or delete multiple records at once and to perform prescribed or custom calculations based on the data.

  • Size: 233 KB
  • Number of slides: 37

Description of the presentation Presentation Access presentation by slides

2Basic concepts A subject area is a part of the real world that is to be studied in order to automate management in this area. A real world object (entity) is something that exists and is distinguishable, for which there is a name and a way to distinguish one similar object from another (school, store, bank, etc.)

3Basic concepts 1. An object can be a set of objects - a class of objects. 2. For example, a product in a warehouse is a collection of different products. 3. A class of objects is a collection that has the same set of properties. 4. Each object in a set is called an instance of an object.

4Properties of objects A property of an object is a certain quantity that characterizes the state of the object at any point in time. A single instance of an object can be described by specifying a sufficient number of values ​​for its properties. For example, all instances of the Product object are characterized by the following properties: product article, product name, product price.

5Identifying properties of objects An identifying property of an object is a property whose value can be used to uniquely distinguish one instance of an object from another within a class of objects. For example, the Employee object. The following properties cannot be considered identifying properties: Last name, First name, Patronymic due to the presence of namesakes with the same first and middle names. You can add properties to the list: Date of birth, Nationality. You can use the identifying property Passport number, Passport series, or Personnel number.

6The concept of an object attribute (details) Each object is characterized by a set of properties, which are called object attributes or details. An attribute is an information display of a separate property of some object, process or phenomenon.

7Relational data model Developed by E. F. Codd in 1970. RMD is an organization of data in which information objects are represented in the form of two-dimensional arrays (tables) with the following properties: 1. each table element is one data element; 2. all columns in the table are homogeneous, that is, all elements in the column are of the same type; 3. each column has a unique name; 4. The order of rows and columns can be arbitrary. In set theory, tables are called relations or relations. Rows are records (tuples). Columns are fields (relationship attributes).

8Relational data model This model is based on relational algebra, where mathematical operations such as union, subtraction, intersection, join, etc. are defined. In any relational database, several tables can be created. Tables must be interconnected to allow simultaneous work with all tables. The connection between two tables is made through identical fields. The field used to link tables is called a key field or key.

9MS ACCESS MS Access is a relational database management system and is one of the most popular desktop DBMSs. MS Access is a set of application programs designed to perform the following operations: creating databases; providing access to data; data processing. MS Access has a large set of visual tools (for entering, analyzing and presenting data), and also allows you to use the capabilities of an object-oriented programming language (VBA - Visual Basic for Applications).

10MS Access DBMS objects Table – saves data. Query – selects the desired data from one or more tables. Form – displays data from tables or queries in a user-friendly form. Report – generates an output document for printing. Macro is a program consisting of a sequence of macro commands designed to automate specific database processing operations. A module is a program in VBA, which is developed by the user to implement non-standard operations when creating an application.

12Tables 1. Each object of the conceptual model of the subject area corresponds to one table. 2. Each table field contains one characteristic (attribute) of a subject area object. 3. The record contains information about one instance of this object.

13Ways to create tables 1. Table mode (by entering data). 2. Table designer. 3. Table Wizard. 4. Import tables. 5. Connection with tables - an automatic direct connection of the current application with data from other applications is established. The table remains in the source application and can be used by multiple applications.

14Table data types 1. Text – default data type. The number of characters in the field should not exceed 255. 2. MEMO (comment - text storage) - text and numbers up to 65535 characters long. You can only view data in a form or report. 3. Numeric – integers and decimals. 4. Monetary – numbers in monetary format. 5. Counter – sequential or random numbers. 6. Date/time – date and time.

15Table Data Types 7. Boolean – data that can have one of two possible values ​​(yes/no, true/false, 0/1, -1/0, on/off). 8. OLE object – the field contains links to OLE objects. 9. Hyperlink – the field contains file addresses or website URLs.

16Field properties are set in the Table Designer mode on the General tab and depend on the selected data type.

17Field properties 1. Field size For text - from 0 to 255 For numeric - the size is selected from the list: byte (0-255); integer (-32768 +32767) ; long integer (-2.14 109 + 2.14 109) ; single float (-3.4 10 38 + 3.4 1038); double float (-1.797 10308 + 1.797 10308).

18Field properties 2. Field format. 3. Number of decimal places. 4. Input mask - used to specify the display of constant characters in the field and limit the length. An example of a mask for displaying a 4-digit signed decimal number: #9999 -854 ; 1854; -25 # 0000 (entry required) Mask LLL ? ? ? can be used for a text field: street, house, roof (L—entry required; ?—entry optional). 5. Signature – the second field identifier, used in forms or reports. 6. Default value – a value that is automatically entered as a preset when creating a record.

19Field properties 7. Value condition - a restriction imposed on the field values ​​(256 OR 512). 8. Error message - the text of the message is compiled by the developer and appears on the screen when the previous property is violated. 9. Required field - if YES is selected, then all cells of this field must be filled in. 10. Blank lines - if NO is selected, the field should not contain data consisting of only spaces. 11. Indexed field - if YES is selected, then an index (prime number) is assigned to each field value. An index table is used to speed up retrieving data from a table.

20Organizing connections between tables Key fields can be of two types: primary key; foreign key. A primary key is a field in which each element uniquely identifies a table record. A foreign key is a field that is entered into a table specifically to perform table linking. Student Number Full name Date of birth Group Session Number Score 1 Score 2 Score 3 Result Scholarship Result Scholarship amount

21Organizing relationships between tables The Student and Session tables are linked by primary keys, and the Session and Scholarship tables are linked by a foreign key. When linking tables using a foreign key, the following definitions are used: the table with the primary key is called the main table; a table with a foreign key – a subordinate table.

22Types of connections 1. One to one. The primary key of the main table is related to the primary key of the child table. 2. One to many. The primary key of the main table is linked to the foreign key of the child table. 3. Many to many. The relationship is implemented through a link table and splits into two One-to-Many relationships. Suppliers Products Orders1: M M: M

24Types of primary keys A primary key can be of two types. 1. Simple – consisting of one field. 2. Composite – consisting of two or more fields. A composite key is a unique set of individual field values.

25Understanding Data Integrity Maintaining data integrity ensures that existing relationships between tables are maintained when records are entered and deleted, and prevents the accidental deletion of related data. For example, the Buyers and Orders tables are linked by the Buyer Code field. If the Buyer with code 4 made 3 orders, then in the Orders table there will be 3 records with this buyer code. These records will be associated with one record in the Customers table. If you try to delete any of the three records in the Orders table, you will receive a message about data integrity violation.

27The concept of data integrity To maintain data integrity, you must select the Ensure data integrity checkbox in the Links dialog. Access can automatically cascade delete and update related data if you select the Cascading update of related fields and Cascading deletion of related fields check boxes in the relationships dialog.

28Sampling queries - used to select information of interest to the user from the database. A selection is a dynamic table with data records that satisfy certain query conditions. The selection is generated anew each time the query is launched. You can treat the selection as if it were a real table, that is, edit its records. The changes made will be automatically reflected in the real tables.

29Ways of forming queries 1. QBE (Query By Example) is a query based on a sample, i.e. a query that is created using the query designer. 2. SQL (Structured Query Language) – the query is written in SQL language. When generating a QBE query, Access automatically generates a corresponding SQL query, which can be viewed using the View - SQL Mode command.

30Queries Simple query – the wizard for creating a selection query is launched. A cross-query is a pivot table that is created by the Excel Pivot Table Wizard. Duplicate records – groups records with the same values ​​of the specified field. Records without Children - Searches for records in the main table that do not have associated records in the child table.

31Creating a query in design mode 1. Open the designer window. 2. Add the necessary tables. 3. Set request parameters.

32Sampling request parameters 1. Sorting – used to sort the selection by the specified field (sorting key). There may be several keys. Sorting by the second key is performed if there are duplicate values ​​in the first key field.

33Selection request parameters 2. Fields whose checkbox is cleared in the Display line are included in the request, but are not displayed in the selection. 3. Selection condition – an expression that is used as a criterion for selecting records by the field in the column in which this expression is entered. After entering the expression, you must press ENTER.

34Creating selection conditions in queries 1. Using the Like mask. The * character replaces any number of characters. For example: the expression Like “M*” will allow you to select all values ​​of the current field that begin with the letter M 2. Using comparison signs. For example: > 5 ; >= 10 ; >= “M*” . 3. Selection within a range of values: use of comparison signs and the AND operator; using the BETWEEN keyword and the AND operator. For example: >=“ M* ” AND<=“ Р* ” ; BETWEEN 5 AND 10.

35Drawing up selection conditions in queries 4. Using the OR operator. For example: for the Date field, the condition * 08 OR * 09 (the * symbol replaces the symbols corresponding to the day and month) will allow you to select orders for two years. For the same purpose, you can use the OR string together with the Selection Condition line.

36Drawing up selection conditions in requests List of functions. 1. Grouping – identical values ​​of this field are grouped. 2. SUM – the sum of grouped values ​​is calculated. 3. AVG – the average of the grouped values ​​is determined. 4. MIN – the smallest of the grouped values ​​is determined. 5. MAX – the largest of the grouped values ​​is determined. 6. COUNT – the number of grouped values ​​is calculated. 7. FIRST – the first of the grouped values ​​is determined.

37Creating selection conditions in queries 8. LAST – the last of the grouped values ​​is determined. 9. Condition – selected from the list if a selection condition has been created for this field. 10. Expression – selected from the list if a calculated expression has been created for this field.



Related publications: