General information. The most important and integral component Creating tables for new base Data Tables are an essential and integral component of any database. This course, which walks you through creating tables for a new database step by step, requires only a beginner level of experience.
Course Objectives 1.Create spreadsheets in spreadsheet 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 choices; 5.Change values in existing lookup fields in Design view. Creating tables for a new database
Creating tables Creating tables for a new database Ways to create tables. In this course, you will create an asset tracking database, which will eliminate cumbersome spreadsheets. The previous course covered 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 Ways to create tables. In a relational database, all information is stored exclusively in tables, which makes it a key component of the database. No tables means no database. This course focuses on the basic tools for creating tables in table and design modes.
Creating tables Creating tables for a new database Ways to create tables. Here is a description of the process: In Datasheet view, you need to click the heading to create a table empty field, select a data type, and enter a field name. Thus, it is enough to just click on the fields in the table and enter text, which will be demonstrated next. For some tables, you can save time by using the fields in the Quick Start section, predefined sets of fields that meet some basic business needs, such as storing addresses, start dates, and end dates. You just need to select the appropriate set of fields in the menu.
Creating tables Creating tables for a new database Ways to create tables. Here is a description of the process: Unlike Datasheet View, Design View allows you to manipulate all the fields and properties of a table. This course will create a table in Design view and modify 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 a SharePoint environment, tables can only be created in Datasheet view.
Creating a Table in Datasheet View Creating tables for a new database A process in Datasheet View. Table View is a visual tool for creating tables. Whether you create a new empty database or add a table to an existing one, the new table opens in Datasheet view. Please note: the new table contains the "Code" field. This is the primary key, so you don't need to create it anymore.
Creating a Table in Datasheet View Creating tables for a new database A process in Datasheet View. Table View is a visual tool for creating tables. To add fields, click the heading of the first empty field (the words Click to Add). The data type menu appears, allowing you to select the data type for the field. After that, it is also possible to change the title of the field.
Creating a Table in Datasheet View Creating tables for a new database A process in Datasheet View. Table View is a visual tool for creating tables. Type a name for the field, and then press ENTER. The focus will move to the next field, for which you need to repeat the same procedure. Remember that in the names of fields consisting of several words, you should not use spaces between them.
Creating a Table in Datasheet View Creating tables for a new database A process in Datasheet View. Table View is a visual tool for creating tables. When you have finished creating the fields, press CTRL+S or press the Save button on the panel quick access. The Save dialog box opens, prompting you to enter a name for the table and save it.
Save time with fields from the Quick Start Create tables for a new database Add fields from the Quick Start 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 with fields from the Quick Start Create tables for a new database Add fields from the Quick Start to your table. Open the table in Datasheet view, and on the Fields tab, in the Add/Remove group, select More Fields. A list will open. Scroll down 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 with fields from the Quick Start Create tables for a new database Add fields from the Quick Start to your table. New fields can be used immediately by starting to enter data in them, or renamed or deleted. It may appear that there are spaces in the field names. These are not really names, but captions, display friendly text associated with each field name.
Creating a table in design mode Creating tables for a new database Using design mode. In Design view, you can create tables from scratch, as well as set and change any properties of each of the fields. You can also open existing tables in Design view to add, remove, 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 the Table Design button. In the Field Name column of the designer, enter the names of the table fields. Typically, the first field created is the primary key field. Remember that on this stage no need to add foreign keys: this can be done when creating relationships.
Creating a table in design mode Creating tables for a new database Using design mode. In the Data Type column, select the data type for the field in the list next to the field name. As always, save your changes and give the new table a name that describes the data it contains. 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 have finished creating tables, you can add multiple records. This is the easiest way to test tables and make sure they store the data you want. 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, you do not need to click the Save button to put it in the database. It is enough to move the focus to another record. To do this, in a table or so-called multi-element form, you can click on another row. You can also move the focus to the next record using the Tab key or the arrow keys. All these actions lead to saving new data.
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. Use these buttons to navigate to the desired data. The First Record button navigates to the first record in the table or query results. The Previous entry button allows you to navigate to the previous entry. The Current Record field contains the 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. Use these buttons to navigate to the desired data. The Last entry button allows you to jump 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 want to capture information about the location of the company's 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 choices can be stored in the lookup field itself, or loaded from a field in another table. Here's how to create 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 the table in Datasheet view, and on the Fields tab, in the Add/Remove group, select More Fields. Select Substitution and Relation from the menu. The lookup wizard will start. On the first page of the wizard, select the A fixed set of values will be entered option, and then 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 the choices in 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 the Suppliers table in Datasheet view. 2.Create the "Support" table in design mode. 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 (Requires Access 2010)
Question 1 of the test 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 a new table always contains a primary key. When you create a table in Datasheet view, you must specify a primary key field. Answer: 2. False.
Test question 3 Creating tables for a new database You can change the data types if needed, but they are already set. When you create a table using the fields in the Quick Start section, you must specify data types for them. Answer: 2. False.
Test Question 4 Choose the syntactically correct list of values from the options 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 Creating tables for a new database Choices are enclosed in double quotes and separated by semicolons. Choose a syntactically correct list of values from the options 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.
DB 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 the data in one file. You can use the following elements in an Access database file:
tables for saving data;
queries to search and extract only the necessary data;
forms for viewing, adding and changing data in tables;
reports to analyze and print 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, they are automatically updated wherever they appear.
4. Data display and printing is carried out 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 is referenced by an application by linking external tables, all other Access objects refer directly or indirectly to tables.
Requests
A query helps you find and retrieve data that meets specified conditions, including data from multiple tables. A query can also be used to update or delete multiple records at once, and to perform predefined or custom calculations based on data.
Databases: concept, basic elements. The database is information model, which allows you to store data in an orderly manner. Software designed to work with databases is called a database management system (DBMS). In a hierarchical database, records are ordered in a certain sequence, like rungs of a ladder, and data can be searched for by sequential “descent” from rung to rung. A relational database is a two-dimensional table. Elements of a relational database: Table columns are called fields: each field is characterized by its name and data type (text, number, date, boolean, counter) Table rows are records about an object. A database entry is a table row containing a set of values for a certain property, Classification of databases: By the nature of the information stored: - Factual (file cabinets), - Documentary (archives) By the method of data storage: - Centralized (stored on one computer), - Distributed ( used in local and global computer networks). According to the structure of data organization: - Tabular (relational), - Hierarchical,
- Program definition;
- Purpose and functions;
- Objects;
- Data types;
- Program menu;
- Ways to create objects;
- Object examples.
On modern personal computers, relational DBMS are most widely used. ACCESS.
Application Microsoft Access is a desktop relational database management system ( DBMS), designed to work on a standalone personal computer(PC) or local computer network family run operating systems Microsoft Windows).
DBMS Microsoft Access has powerful, convenient and flexible means of visual design of objects with the help of Wizards, which allows the user with a minimum preliminary preparation quite quickly create a complete information system at the level of tables, queries, forms and reports.
Databases have the 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 get the necessary data from one or more tables.
Report – object for printing data.
ANY TABLE CAN BE PRESENTED IN TWO MODES:
In mode tables, designed for data entry, viewing and editing.
In mode constructor , designed to create a table structure, change the data type, change the table structure (adding and deleting fields).
ORGANIZATION OF WORK WITH TABLES
Table- the main (basic) database object. All other objects are created based on existing tables.
- IN tables all data available in the database is stored; And tables
- IN tables all data available in the database is stored;
- And tables store the database structure (fields, their types and properties)
There are the following request creation modes:
- In master mode.
- In constructor mode.
In mode constructor. A window appears on the screen, a new request, which lists all the ways in creating a request. This:
- Constructor.
- Simple request.
- Cross request.
- Recurring 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 transformation
- save the results with a certain 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 may include not only one, but also several related tables.
- Most fast way creation forms- with the help of a wizard.
- Form can have a tabular view, but most often they use a tape one - each object has a separate card.
- Report– the possibility of creating various forms of presentation of output information.
- Convenience of computer reports lies in the fact that they allow you to group information according to given criteria, enter the final fields for counting records by groups throughout the database.
- Report is a convenient form of information presentation for printing.
DATABASE PROTECTION
Access has the best protection system among all desktop DBMS. The built-in wizard allows you to create groups, users, assign access rights to all objects, including modules. Each user can be given an individual password.
The protection system is available both visually and programmatically.
Microsoft Access is the most popular desktop database management system today. Her success has a lot to do with her inclusion in the family. Microsoft Office. This software Designed for both beginners and advanced users
Description of the presentation on individual slides:
1 slide
Description of the slide:
Base management system Microsoft data ACCESS Completed by: Pavlova A.A. Gr. 331-LD Checked by: Tunakova G.A. Presentation
2 slide
Description of the slide:
DBMS objects Access Tables are the basic objects of any database. The tables store: all the data available in the database, the structure of the database (fields, their types and properties). Table object (tab) Creation methods
3 slide
Description of the slide:
Access DBMS objects Queries are objects that are used to extract data from tables and provide them to the user in a convenient form. With the help of queries, operations such as selecting data according to a given criterion, sorting and filtering are performed. With the help of queries, you can perform data transformation according to a given algorithm, create new tables, automatically populate tables with data imported from other sources, perform simple calculations in tables, and much more. Object (tab) requests
4 slide
Description of the slide:
Access DBMS objects Forms are the means for entering data or viewing data. Provide the user with the means to fill in only those fields that he is supposed to fill out. At the same time, special controls (counters, drop-down lists, radio buttons, checkboxes, and others) can be placed in the form to automate input. Forms tab
5 slide
Description of the slide:
Access DBMS objects Reports are designed to output data to a printing device (for example, a printer). They take special care to group output data and to display special elements design typical for printed documents (header and footer, page numbers, service information about the time the report was created, etc.). Macros automate the execution of a specific database operation without programming. Modules contain programs in the language Visual Basic used to configure, design and expand the database. Access pages are intended for viewing and working via the Internet with data stored in MS Access or MS SQL Server databases.
6 slide
Description of the slide:
Microsoft Access Data Types Text is a data type used to store regular, unformatted text of limited size (up to 255 characters). Memo field - special type data to store large amounts of text (up to 65,535 characters). The text is not physically stored in the field. It is stored in a different place in the database, and a pointer to it is stored in the field, but such a separation is not always noticeable to the user. Numeric - data type for storing real numbers. Date/Time is a data type for storing calendar dates and the current time.
7 slide
Description of the slide:
Microsoft Access Data Types Monetary is a data type for storing monetary amounts. Theoretically, fields of a numeric type could also be used to record them, but for monetary amounts there are some features (for example, related to rounding rules) that make it more convenient to use a special data type, rather than setting a numeric type. Counter - a special data type for unique (not repeated in the field) natural numbers with automatic increment. The natural use is for sequential numbering of records. Boolean - a type for storing logical data (can take only two values, for example Yes or No).
8 slide
Description of the slide:
Microsoft Access Data Types Field OLE object- a special data type designed to store objects, such as multimedia, inserted by embedding or linking (OLE). In reality, of course, such objects are not stored in the table. As in the case of MEMO fields, they are stored elsewhere in the internal structure of the database file, and only pointers to them are stored in the table (otherwise working with tables would be extremely slow). A hyperlink is a special field for storing the URLs of Internet Web objects. Clicking on a link automatically launches the browser and displays the object in its window. The lookup master is not a special data type. This is an object that can be configured to automate data entry in the field so that it is not entered manually, but selected from a drop-down list.
9 slide
Description of the slide:
Microsoft Database Object Creation Tools Access DBMS Microsoft Access provides several tools for creating basic objects: manual - developing objects in Design mode; automated - development with the help of wizards; automatic - means of accelerated development of the simplest objects.
10 slide
Description of the slide:
Tools for creating database objects Microsoft Access DBMS Recommendations for using the tools for creating objects: When developing training tables and queries, it is recommended to use manual tools - work in Design mode. The use of wizards speeds up the work, but does not contribute to the development of concepts and methods. When designing training forms, reports, and access pages, it's best to use the automated tools provided by the wizards. The design of these objects is time-consuming, so it is better to entrust it to the program, and the student to focus on the content of the work.
11 slide
Description of the slide:
12 slide
Description of the slide:
Creating the structure of database tables In the database window, select the Tables object Select the item Create a table in design mode In the design window, specify the field name and data type Define the primary key of the table
13 slide
Description of the slide:
14 slide
Description of the slide:
Establishing relationships between tables Tools menu - Data Schema Add tables involved in relationships and close the window Add Drag the field of the Student table to the corresponding field of the Books table.
15 slide
Loading...