Hi all! In this lesson we will look at step-by-step creation of tables in an Access database. All you have to do is read and repeat. Go!
Goal: learn to create database tables, set their structure, select field types and manage their properties; master techniques for filling tables with specific content.
The head of a small enterprise that assembles personal computers from ready-made components ordered the development of a database based on two tables of components. One table contains data that can be displayed to customers when agreeing on a product specification - it shows the retail prices of components. The second table is intended for analyzing the results of the enterprise's activities - it contains wholesale prices for components and brief information about suppliers (customers of the enterprise do not have access to the data in this table).
1. Launch the program Microsoft Access(Start - Programs - Microsoft Office - Microsoft Access).
2. In the window Microsoft Access turn on the switch New database.
Creating an Access database - Step-by-step creation of tables in an Access database
3. In the sidebar, select the location where the database will be stored, give the file a name: Accessories. Make sure the File Type is selected Microsoft Access Databases and click on the button Create. As a result, a new database window should appear on the screen. The first table creation mode will be started automatically:
4. Switch from table mode to design mode by selecting it in the menu:
When the program asks for a table name, enter the name: Accessories and click Ok. A designer window will open in which we will create the table structure:
Creating an Access Table - Step by step creating tables in an Access database
5. For the first table, enter the following fields:
Please note that in this case the field Price specified not by the monetary type, but numerical. Data related to the monetary type have a dimension expressed in rubles (if the work is performed with a version of Microsoft Access localized in Russia). But it is not customary to express the cost of computer components in this unit of measurement. To compare prices from different suppliers, “conventional units” are usually used. In such cases it is convenient to use the field numerical type, so as not to reconfigure the entire DBMS.
Table "Components" - Step-by-step creation of tables in an Access database
6. Click on the field Price. At the bottom of the form, set the property Number of decimal places, equal to 2.
7. To connect with the future supplier table, you need to set a key field. Since not a single field here clearly claims to be “unique,” we use a combination of fields Component And Model. Select both fields at the top of the form (while holding down the SHIFT key). Right-click to open the context menu and select Key field.
A “key” will then appear next to these fields.
8. Close the Designer window (the usual closing is through the “cross” in the upper right corner of the Designer window).
Answer the request to save the changed table structure with consent.
9. Repeat steps 4-8 to create a table Suppliers, which includes the following fields:
To do this you need to do the following: menu Create - Table.
Select mode Designer.
Please note that the phone number field is text, despite the fact that phone numbers are usually written in numbers. This is due to the fact that they do not have numerical content. Phone numbers are not compared by size, they are not subtracted from one number to another, etc. This is a typical text field.
The key field can be omitted– it is not required for the current task.
10. In the left side panel of the database window, double-click to open the created tables one by one and fill them with experimental content (10 records each).
11. When finished, close the tables and exit the program.
This lesson showed step-by-step creation of tables in an Access database using a simple example. If you were interested, I recommend watching the video tutorial below.
To be continued…
LESSON 1: Step-by-step creating tables in an Access database
LESSON 2: Creating inter-table relationships in the Access database
LESSON 3: Creating a query to select data in an Access database
LESSON 4: Creating queries with a parameter in the Access database
LESSON 5: Creating a cross-query in an Access database
LESSON 6: Creating change requests in an Access database
LESSON 7: Creating and filling out forms in the Access database
A large number of different objects are involved in organizing a database. All database objects are either physical or logical. Physical objects are concerned with organizing data on physical devices (disks). The physical objects of the Database Engine are files and filegroups. Logical objects are user-defined views of a database. Examples of logical objects include tables, columns, and views (virtual tables).
The first database object you need to create is the database itself. The Database Engine manages both system and user databases. User databases can be created by authorized users, while system databases are created when the DBMS is installed.
There are two main methods used to create a database. The first method uses the SQL Server Management Studio Object Explorer as shown earlier, and the second uses a Transact-SQL statement CREATE DATABASE. The following is the general form of this instruction, and then its components are discussed in detail:
CREATE DATABASE db_name ( file_spec1),...] Syntax conventions
The db_name parameter is the name of the database. The database name can contain a maximum of 128 characters. One system can manage up to 32,767 databases. All databases are stored in files, which can be specified explicitly by the administrator or provided implicitly by the system. If the CREATE DATABASE statement contains parameter ON, all database files are specified explicitly.
The Database Engine stores data files on disk. Each file contains data from one database. These files can be organized into file groups. Filegroups provide the ability to distribute data across different disk drives and to back up and restore parts of a database. This is useful functionality for very large databases.
The file_spec1 parameter represents the file specification and may itself contain additional options such as the logical file name, physical name, and size. Parameter PRIMARY specifies the first (and most important) file, which contains system tables and other important internal database information. If the PRIMARY parameter is missing, then the first file specified in the specification is used as the primary file.
The Database Engine account used to create the database is called database owner. A database can only have one owner, which always corresponds to an account. An account belonging to the database owner has a special dbo name. This name is always used in relation to the database that the user owns.
Option LOG ON The dbo parameter specifies one or more files as physical storage for the database transaction log. If the LOG ON option is not present, the database transaction log will still be created because every database must have at least one transaction log. (The Database Engine keeps a record of all the changes it makes to the database. The system stores all of these records, especially the values before and after the transaction, in one or more files called transaction logs. Each database in the system maintains its own log transactions.)
In option COLLATE specifies the default sort order for the database. If the COLLATE option is not specified, the database is assigned a default collation order that is exactly the same as the database system's default collation order.
In option FOR ATTACH indicates that the database is created by connecting an existing set of files. When using this option, you must explicitly specify the first primary file. In option FOR ATTACH_REBUILD_LOG indicates that the database is created by attaching an existing set of operating system files.
The Database Engine creates a new database using the model database template. The properties of the model database can be customized to suit the system administrator's personal concepts. If a specific database object must be present in every user database, then that object must first be created in the model database.
The example below shows code to create a simple database, without providing any additional details. To run this code, enter it into Management Studio's query editor and press
USE master; CREATE DATABASE SampleDb;
The example code creates a database called SampleDb. This shortened form of the CREATE DATABASE statement is possible because almost all of its parameters have default values. By default, the system creates two files. The data file has the logical name SampleDb and the initial size is 2 MB. And the transaction log file has the logical name SampleDb_log and the initial size is 1 MB. (The sizes of both files, as well as other properties of the new database, depend on the corresponding model database specifications.)
The example below shows how to create a database by explicitly specifying the database and transaction log files:
USE master; CREATE DATABASE Projects ON (NAME=projects_dat, FILENAME = "D:\projects.mdf", SIZE = 10, MAXSIZE = 100, FILEGROWTH = 5) LOG ON (NAME=projects_log, FILENAME = "D:\projects.ldf", SIZE = 40, MAXSIZE = 100, FILEGROWTH = 10);
The database created in the example is called Projects. Since the PRIMARY option is not specified, the first file is assumed to be the primary file. This file has the logical name projects_dat and it is saved in the projects.mdf file on disk. The original size of this file is 10 MB. If necessary, the system allocates additional disk space to this file in 5 MB increments. If not specified MAXSIZE option or if this option is set to UNLIMITED, then the maximum file size can increase and will be limited only by the size of the entire disk space. (The file size unit can be specified using the suffixes KB, TB, and MB, meaning kilobytes, terabytes, and megabytes, respectively. The default size unit is MB, which is megabytes.)
In addition to the data file, a transaction log file is created, which has the logical name projects_log and the physical name projects.ldf. All transaction log file specification options have the same names and meanings as the corresponding options for the data file specification.
In Transact-SQL, you can specify a specific database context (that is, which database to use as the current one) using USE instructions. (An alternative is to select the name of the required database from the Database drop-down list in the SQL Server Management Studio toolbar.)
The system administrator can assign the current default database to a user using the CREATE LOGIN statement or the ALTER LOGIN statement. In this case, users do not need to issue a USE statement unless they want to use a different database.
Creating a Database Snapshot
In addition to creating a new database, you can use the CREATE DATABASE statement to take a snapshot of an existing database (the source database). A database snapshot is a transactionally consistent copy of the original database at the time the snapshot was taken. The following is the syntax for creating a database snapshot:
Thus, to create a database snapshot, you must insert the clause in the CREATE DATABASE statement AS SNAPSHOT OF. The example below illustrates taking a snapshot of the SampleDb database and storing it in the D:\temp folder. (Before you can run this example, you must create this directory.)
USE master; CREATE DATABASE SampleDb ON (NAME = "SampleDb_Data" , FILENAME = "D:\temp\snapshot_DB.mdf") AS SNAPSHOT OF SampleDb;
A snapshot of an existing database is a read-only copy of the source database that reflects the state of that database at the time it was copied. (This way, you can create multiple snapshots of an existing database.) The snapshot file (in the example above, D:\temp\snapshot_DB.mdf) contains only the changed data from the source database. Therefore, the code to create a snapshot must specify the logical name of each data file in the source database, as well as the corresponding physical names.
Because a snapshot contains only changed data, each snapshot requires only a small fraction of the disk space required by its corresponding primary database.
Database snapshots can only be created on disks with the NTFS (New Technology File System) file system, because This file system is the only file system that supports sparse file technology, which is used to store snapshots.
Database snapshots are commonly used as a mechanism to protect data from corruption.
Attaching and detaching databases
All database data can be detached and then reattached to the same or a different database server. This functionality is used when moving a database.
To disconnect a database from the database server, use system procedure sp_detach_db. (The database to be detached must be in single-user mode.)
To attach a database, use the CREATE DATABASE statement with the FOR ATTACH clause. All required files must be available for the database to be joined. If any data file has a path different from the original path, then the current path must be specified for that file.
Creating Databases
Purpose of work: study the basic principles of operation and methods of creating databases in the MS Access environment
Brief information about MS Access
A database (DB) is an organized structure designed to store information. A database management system (DBMS) is a set of software tools designed to create the structure of a new database, fill it with content, edit content and visualize information.
MS Access objects:
Tables are the main object of any database; tables store all the data available in the database; tables also store the structure of the database (fields, their types and properties).
Queries are an object used to select and analyze data from tables and present them to the user in a convenient form.
Forms are an object used to enter new data, as well as display them using special design tools.
Reports are an object intended only for outputting data to a printing device.
Pages are a special database object (MS Access 2000) executed in HTML code and placed on a Web page, providing an interface between the client, server and database located on the server.
Macros and modules are categories of objects designed to automate repetitive operations when working with a DBMS.
The MS Access DBMS provides several tools for creating each of the main database objects. These funds can be classified as:
manual (developing objects in Design mode);
automated (development using wizard programs);
automatic (means for accelerated development of simple objects).
When developing tables and queries, it is recommended to use manual tools - work in Design mode. When developing forms and reports, on the contrary, it is better to use automated tools provided by wizards. This is due to the fact that for these objects the appearance (the design of these objects) plays an important role. The development of Pages, Macros and Modules is not considered within the framework of the general technical course “Computer Science”, as these tools are aimed at professional database developers.
Working with any Access object begins with the database window. The database window contains a list of all database objects on tabs. The database window is initially opened on the “Tables” tab. On the right side of the database window panel there are three control buttons: “Open”, “Designer”, “Create”.
Working with tables
Tables are the main objects of any database. The structure of a two-dimensional table is made up of columns and rows. Their analogues in the structure of the simplest database are fields and records.
Fields are (vertical) columns of data in tables. Each field represents an element reserved for a specific type of data.
Records are (horizontal) rows of data in tables. Each record represents an element of related data.
DB field properties:
The field name determines how the data of this field should be accessed during automatic operations with the database. Field names are used as table column headings. The field name must not contain periods. For example: Full name - invalid field name. The correct name is Full Name.
The field type determines the type of data that can be contained in the field. Database tables, as a rule, allow working with a larger number of different types of data than MS Excel.
MS Access works with the following data types:
- - text - a data type used to store plain unformatted text of limited size (up to 255 characters);
- - Memo field - special data types for storing large text objects (up to 65,535 characters);
- - numeric - data type for storing real numbers.
- - monetary - data type for storing monetary amounts.
- - counter - a special data type for unique (not repeated in the field) natural numbers with automatic growth.
- - date/time - data type for storing calendar dates and current time.
- - logical - type for storing logical data (can only take two values, for example YES or NO).
Creating tables
There are two main ways to create a table: manually and using the Table Wizard. Following the recommendations above, let's look at how to create a table manually.
To create a table manually:
in the database window, on the “Tables” tab, click the “Create” button;
In the “New Table” window, click the “Design” button.
The Table Designer window that appears is actually a form for creating and editing the structure of tables.
In the first column, enter the names of the fields that will become the names of the columns of the future table. The data type for each field is specified in the second column from the drop-down list opened by the data type selection button. This button is a hidden control. It is displayed only after clicking on each row of the “data type” column in the form field. MS Access has a lot of hidden controls that don't appear until you start entering data.
The lower part of the form contains a list of properties of the field highlighted in the upper part. Some of the properties are already set by default. Field properties are optional and can be customized as desired.
When creating a table, it is advisable (though not required) to specify a key field. This will help later when organizing connections between tables. To set a key field, just right-click on its name and select “key field” in the context menu that opens.
A key field or primary key is a field whose values uniquely identify a record in a table.
Having finished creating the table structure, the window is closed (at the same time the system issues a request to save the table), after which the table is given a name, and from that moment it is available among other tables in the main database window. From there it can be opened if necessary.
The created new table is opened in the database window by double-clicking on its icon or clicking on the “Open” button. The new table has no records - only the names of the columns that characterize the structure of the table. Filling the table with data is done in the usual manner. The input cursor is positioned in the desired cell using the mouse pointer. The width of columns can be controlled by dragging their borders. You can use automatic formatting of columns “by content”. To do this, place the mouse pointer on the border between the columns (in the column header line), and when the pointer changes shape, double-click.
After filling the table with data, there is no need to save it - everything will be saved automatically. However, if, while working with a table, its layout was edited (for example, the width of the columns was changed), the DBMS will ask you to confirm saving these changes.
If there is a need to change the structure of the table (the composition of the fields or their properties), the table must be opened in Design mode. To do this, select it in the database window and click on the “Design” button.
Creating inter-table relationships
The relational relationship between tables has two main purposes:
- - ensuring data integrity;
- - automation of database maintenance tasks.
The relationship between tables allows you to:
- - or exclude the possibility of deleting or changing data in a key field of the main table, if this field is associated with any fields of other tables;
- - or make it so that when you delete (or change) data in a key field of the main table, the corresponding data in the fields of related tables will automatically (and absolutely correctly) be deleted or changed.
Intertabular relationships are created in a special “Data Schema” window, which is opened by a button on the toolbar or by the command Tools - Data Schema. When you open this window, the Add Table dialog box opens, allowing you to select the tables between which you want to create relationships. In this case, one of the tables is considered the main one, and the other is considered related. The main table is the table that participates in the relationship with its key field (the name of this field is displayed in bold on the data diagram).
On the “Data Schema”, use the mouse to select the key field of the main table and, without releasing the mouse button, drag this field into the linked table. When you release the mouse button, the Edit Links dialog box will automatically open. The next step is to select the Ensure Data Integrity, Cascading Update of Related Fields, and Cascading Deletion of Related Records checkboxes, then click OK. The “Ensure data integrity” checkbox prohibits deleting data from a key field in the main table. If the “Cascading update of related fields” and “Cascading deletion of related records” checkboxes are enabled along with it, then, accordingly, editing and deleting data operations in the key field of the main table are allowed, but are accompanied by automatic changes in the related table.
Working with requests
When forming and executing a Request, the active database is queried. The result (response) is a temporary result table that can be viewed on screen or printed.
Using queries, operations such as data selection, sorting and filtering are performed. Using queries, you can transform data according to a given algorithm, create new tables, automatically fill tables with data imported from other sources, perform simple calculations in tables, and much more. In general, the resulting table may not correspond to any of the underlying database tables. Its fields can represent a set of fields from different tables, and its records can contain filtered and sorted records of the tables on the basis of which the query was formed.
The main types of requests are:
- - Selection queries - information is obtained from tables based on a given criterion.
- - Cross queries - a criterion is used that is specified to group table data in a tabular format. Cross queries are the most difficult to use, but are very useful in a number of situations.
- - Summary queries - calculations are performed on all records of a numeric field.
Create a request
Queries can be created in two ways: manually and using query wizards. There are four query wizards:
simple query wizard;
Cross Query Wizard;
Recurring Record Wizard;
master of records without subordination.
In the database window, on the “Queries” tab, click the “Create” button. In the “New Query” window, select “Designer” and click the “OK” button. The request form window opens. The request form consists of 2 areas. The top area displays the structure of the tables to which the request is addressed. These tables are selected from the “Add Table” window, which opens on the toolbar with the “Add Table” button.
The lower area of the form is divided into columns - one column for each field of the future resulting table. To fill in these columns in the upper area of the form, double-click on the names of the fields required for the resulting table (query), and the columns in the lower area of the form are automatically filled in. The structure of the request is formed: the lines are filled in: “Field”, “Table name”, from which this field was selected.
If it is necessary for the selected data to be ordered by some field, sorting is used. For this purpose, in the lower area of the form there is a special line “Sorting”. Clicking on this line opens a drop-down button from which you can select the sorting method: ascending or descending. In the resulting table, the data will be sorted by the field for which the sort order is specified. Multi-level sorting is possible: by several fields at once. In this case, the data is sorted by fields from left to right.
The Display line displays all fields included in the query by default, but this is not always practical. Resetting the "Output to Screen" checkbox prevents the contents of this field from being displayed on the screen.
In the Selection condition line, a criterion (individual condition) is specified for each field by which data is selected from the table fields. Symbols used when specifying selection criteria:
- > (more),< (меньше), = (равно), >< (не равно).
For example: a) The selection condition for the “Name” text field is set to >K. This means that in the “Full Name” field, surnames starting with the letters from K to Z will be selected.
- b) The selection condition for the numeric field “Price” is specified
- - * - replacement of several characters.
For example: The selection condition for the “Name” text field is set to *s. This means that in the “Full Name” field, last names ending in “ov” will be selected, regardless of how many characters are in front.
Replacing one character.
For example: The selection condition for the numeric field “Price” is set to 0. This means that in the “Price” field only the values 10, 20, 30, 40, 50, 60, 70, 80, 90 will be selected.
- - is null - search by “empty” field value;
- - is not null - search by “non-empty” field value.
For example, in the source table, the “Bonus Amount” field contains, in addition to the bonus amount for some employees, empty, unfilled lines for employees who did not receive a bonus. The selection condition for the "Premium amount" field is set to is null. This means that in the resulting table (query), if the “Name” field is entered into it, the names of employees who did not receive a bonus will be returned.
- “between value 1 and value 2” - search by the interval between value 1 and value 2.
For example, the selection condition for the "Date of Birth" field is set between 01/01/82 and 12/31/82. This means that only dates born in 1982 will be selected in the “Date of Birth” field.
When specifying selection conditions, a logical “AND” is set between them in a row for several fields, and a logical “OR” is set in a column. Having formed the request structure in the request form according to the sample, close it, give it a name and then launch it by double-clicking on the icon in the Database window. You can also run a request in the request form window by clicking on the button with a red exclamation mark on the Toolbar. The procedure discussed above allows you to create a simple query called a select query.
Working with Forms
Forms are tools for entering data. Using forms, data can not only be entered, but also displayed using special design tools. Forms provide the user with only those fields that he is required to fill out. At the same time, special controls can be placed on the form to automate input.
Thus, forms, on the one hand, allow the user to enter data into database tables without outside access to the tables themselves, on the other hand, they allow the results of queries to be displayed not in the form of result tables, but in the form of beautifully designed forms.
A simple form is a form that is created based on only one table or query.
A complex form is a form that is based on fields from several related tables or is a composition of several forms.
Creating a Form
There are two types of form structure formation: table-based and query-based. It is more convenient to prepare forms using automation tools, although there is also a way to create a form manually.
Fully automatic form creation tools are called autoforms. There are three types of autoforms:
- - “to column” - all fields of one record are displayed in a column;
- - tape - a group of records is displayed simultaneously;
- - tabular - in appearance it does not differ from the table on which it is based.
To create an autoform:
- 1. Open the Forms tab in the Databases window and click the Create button.
- 2. In the New Form window that opens, select the autoform type, as well as the table (or query) on which it will be based, and click OK.
- 3. After clicking on the OK button, the autoform is automatically generated and is immediately ready for use, that is, for entering or displaying data.
AutoShape creates only simple forms.
Create a form using the wizard
Form Wizard is a special software tool that creates the structure of a form in dialogue mode with the developer. Form wizards can be launched from the main Database window by clicking the Create a form using a wizard icon on the Forms tab.
- 1. At the first stage of the Form Wizard, select the tables (or queries) and fields that will be included in the future form.
- 2. At the second stage, the appearance of the form is selected.
- 3. At the third stage, the form design style is selected.
- 4. At the last stage, the form is saved under the specified name.
Working with reports
In their properties and structure, reports are in many ways similar to forms, but are intended only for outputting data to a printing device. Reports use special measures to group the output data and to display special design features characteristic of printed documents (headers and footers, page numbers, service information about the time the report was created, etc.).
Create a report
Reports, like forms, are designed in three ways: manually, automatically (AutoReport) and in an automated way (Report Wizard). It is recommended to create reports using the last two methods, and edit the structure of the finished report in Design mode. The auto report is launched by the command: Database - Create - New report - Auto report to column. In addition to the “column” auto-report, there are “tape” auto-reports. Their structures are similar to the columnar and ribbon forms, respectively.
The Report Wizard is launched by the command: Database - Create - New Report - Report Wizard. The Report Wizard works in six steps: selecting base tables or queries; selecting the fields displayed in the report; selection of grouping fields, selection of fields and sorting methods, selection of the form of the printed layout and design style.
The structure of the finished report differs from the structure of the form only in the increased number of sections. Additionally, the report contains header and footer sections where service information, such as page numbers, is printed. Group headings are displayed in individual sections of the report if grouping is applied to some report fields.
Exercises
Creating base tables
- 1. Launch MS Access.
- 2. In the MS Access window, turn on the New Database switch and OK.
- 3. In the New Database File window, select the My Documents folder and give the file the name Dean’s Office. Make sure that MS ACCESS Databases is selected as the file type and click the Create button. A new database window will open - Dean's Office: database.
- 4. The "Tables" tab is open. Click on the button - Create.
- 5. In the New Table window, select Design mode and click OK. A form for creating a table structure will open.
- 6. For the first table, enter the following fields:
- 7. Click on the Year of Birth field. At the bottom of the Field Properties form, from the drop-down list, set the field format: Short date format. access query intertable
- 8. Set the Key field. A unique field in the table is the Gradebook No. field. Select it by right-clicking, open the context menu and select Key Field.
- 9. Close the Design window. When you close the window, name the first table Student Information.
- 10. On the Tables tab, create a second Group Details table. To do this, click on the Create button.
- 11. Repeat steps 6-9, create a Group Information table that includes the following fields:
Key field - group number
- 12. In the Dean’s Office: database window, open the created tables one by one and fill them in: there are 20 records in the Student Information table, and 5 records in the Group Information table. Moreover, the group number of the Student Information table must contain all group numbers (they can and should be repeated) from the Group Information table.
- 13. When finished, close the tables.
Creating base tables
- 1. While in the main window of the Dean's Office database, find the Data Schema button on the toolbar or execute the menu bar command Tools - Data Schema.
- 2. Simultaneously with the opening of the Data Schema window, the Add Table dialog box opens, on the Tables tab, select Group Information, click the Add button. Then select Student Information, click the Add button. Click the Close button.
- 3. In the Data Schema window, lists of fields in the Group Information and Student Information tables will open. With the left mouse button pressed, select the key field Group No. in the Group Information table, and drag this field into the Student Information table with the mouse. When you release the mouse button, the Edit Links dialog box will automatically open.
- 4. Select the Ensure data integrity, Cascade field update, and Cascade delete related records check boxes.
- 5. Close the Edit Links window and examine the resulting link in the Data Schema window. The connection type is one-to-many (1 -), which means there are many students in one group.
- 6. Close the Data Schema window.
Create a select query
Assignment: From the general list of students of this faculty, select only 1st year students; students whose last names begin with the letter “A”; students of the same group; students born in the same month; Find information about students by grade book number.
- 1. While in the main window of the Dean's Office database, open the Queries tab and click the Create button.
- 2. In the New Query window, select Design mode and OK. A sample request form will open. At the same time, the Add Table dialog box will open, select the Student Information table and click the Add button.
- 3. Select the Group Details table and click the Add button. Close the Add Table window.
- 4. Place the following fields from the Student Information table in the lower area of the form: Full name, Year of birth, Address; from the Group Information table - Course field.
- 5. Set sorting in ascending order by the full name field (alphabetically from A to Z).
- 6. To search (or select) 1st year students from the general list of students, set the selection condition for the Course field: enter 1 in the appropriate line.
- 7. Complete the request form according to the sample. When closing the request, enter his name 1st year students.
- 8. In the Dean's Office: Database window, open the query you just created and view the resulting table.
- 9. To search for students whose last names begin with a specific letter (for example, A), repeat steps 1 to 6, then set the selection condition for the Full Name field. In the appropriate line of the request form, enter: A*.
- 10. Review the created request. To do this, in the request form window on the Toolbar, click on the button! - "Exclamation point".
- 11. Close the request and enter his name - Last name starting with A.
- 12. Similarly, find information about students born in the same month. Name the query Born in the same month.
- 13. Find students studying in the same group. Give the request the name Students of the same group.
- 14. Find students whose record book numbers are greater than a certain number. Give the request a name By record book number.
- 8.6.4 Creating a summary query
In the final query, the sum of values or the average value for all cells of the field can be calculated, and the maximum or minimum data value in the field can be selected. Another final function may also be executed.
Assignment: prepare a final query with which you can determine the total amount of scholarships for students of different courses.
- 1. In the Dean's Office: Database window, open the Tables tab, select the Student Information table.
- 2. By clicking on the Designer icon, open the table in design mode: you need to create an additional field Scholarship Amount, which will store information about the scholarship amount for each student.
- 3. Select the Group No. field and press the INSERT key; a new field will be inserted before the Group No. field. Enter the name of the new field Scholarship Amount and define its type - Cash.
- 4. Close the designer window. When closing, confirm the need to change the table structure.
- 5. Open the Student Information table and fill in the Scholarship Amount field with content.
- 6. Close the Student Information table.
- 7. Open the Queries tab and click the Create button.
- 8. In the New Query window, click the Design button.
- 9. In the Add Table window that opens, select the Student Information and Group Information tables. Close the Add Table window.
- 10. In the lower area of the sample request form, enter the following fields: from the Group Information table - Course, from the Student Information table - Scholarship amount.
- 11. For the Course field, enter sorting in ascending order.
- 12. On the Toolbar, click on the button - group operations (or the View/group operations menu). The line Group operations will appear at the bottom of the form. All fields selected for the query will receive a grouping value in this row.
- 13. For the field by which records are grouped (in our case, Course), leave the Grouping value in the Group operations line. For other fields, click in this row, a drop-down button will appear from which you can select the resulting function to calculate the values in this field.
- 14. For the Scholarship Amount field, select the Sum final function to determine the total scholarship amount.
- 15. Close the request form and name it Total Course Scholarship. Run the request and make sure it works correctly.
- 16. Check on the Requests tab for the presence of six requests: “1st year students”, “Surnames starting with A”, “Born in the same month”, “Students of the same group”, “Total scholarship for courses”.
Creating an AutoForm
Task: create three simple forms using AutoForms: columnar, ribbon and table.
- 1. In the main window Dean's office: database, open the Forms tab and click on the book. Create.
- 2. In the New Form window, select the autoform type - “column” and in the lower right corner of the window, select the Student Information table from the drop-down list. Click OK. The autoform is generated automatically. Give it a name Student Information Form.
- 3. Repeat steps 1-3 to create a ribbon autoform based on the Group Information table, give it the name Group Information Form.
- 4. Repeat steps 1-3 to create a tabular form based on the query 1st year students. Give it the name Form 1st year students.
Creating a form using the Form Wizard
Task: Create a complex form based on two tables.
- 1. In the main window Dean's office: database, open the Forms tab and click the Create button.
- 2. In the New Form window, select Form Wizard. A description of the wizard appears on the left side of the dialog box. OK.
Note. When using the Form Wizard, the data source for the form must be specified in the following Create Forms dialog box.
- 3. In the Form Creation window, select the Student Information table name. Using the button, transfer to the right empty column Selected Fields all the fields from the left column Available Fields, which contains fields from the Student Information table.
- 4. Select the table name Group Details. From the Available fields column, move the Rate field to the right column of Selected fields. Click "Next.
- 5. In the next Create Forms window that appears, select the appearance of the form - one column. Click Next.
- 6. In the next window, the Wizard offers several different form design styles. Look through them (images of these styles and types of captions on them appear on the left). Select the Twilight style and click Next.
- 7. Set the name of the Student Data Entry form. Click Finish.
- 8. Review the finished form. Enter information for the new student.
- 9. Close the Student Data Entry form window.
- 10. Open the Tables tab. Select the Student Information table. Click the Open button. See if information about a new student appears there.
- 11. Close the Student Information table.
Creating an auto report
- 2 In the New report window, select the auto-report type - “column” and in the lower right corner of the window, from the drop-down list, select the query Born in the same month. Click OK.
- 3. The auto report is generated automatically. Give it a name Report on those born in the same month.
- 4. Repeat steps 1-3 to create a tape auto-report based on the request Last names starting with A, give it the name Report about students whose last names begin with A.
Creating a report using the Report Wizard
Task: Create a final report based on two tables.
- 1. In the main window Dean's office: database, open the Reports tab and click the Create button.
- 2 In the New Report window, select Report Wizard. A description of the wizard appears on the left side of the dialog box. Click OK.
Note. When using the Report Wizard, the data source for the report must be specified in the following Create Report dialog box.
- 3. In the Create Report window, select the table name Student Information. Using the button, transfer to the right empty column Selected Fields all the fields from the left column Available Fields, which contains fields from the Student Information table. Select the Group Details table name. From the Available fields column, move the Rate field to the right column of Selected fields. Click "Next.
- 4. In the next window of the Report Wizard, grouping levels are selected. Select grouping by Course field. Click Next.
- 5. In the next window, you specify the order of sorting records by fields (no more than four), if necessary. Select sorting in ascending order by the Full Name field. The final report is generated in the same window. To do this, click the Results button. In the window that appears, specify the calculation of the Sum function in the Scholarship Amount field; to do this, check the box under this function. Click Next.
- 6. In the Report Creation window that appears, select the layout type for the report - stepped, orientation - portrait. Click Next.
- 7. In the next window, the Wizard offers several different report styles. Look through them (images of these styles and types of captions on them appear on the left). Select the Formal style and click Next.
- 8. Set the name of the report: Student Report by Course. Click Finish.
- 9. View the finished report and close the report window.
Individual tasks
Create a database for a given subject area (assignment options are provided by the teacher).
The database should contain:
at least 2 tables, each containing at least 10 records;
inter-table connections;
three simple selection queries with different selection conditions and one final query;
simple forms for entering data from source tables and one complex form;
reports created on the basis of developed queries, including the final report.
To create a promising, expandable and effective website of any complexity, you should start with something simple. This process is not easy, it requires certain basic knowledge of PHP and MySQL, but if you consider it point by point, you can create a kind of “work plan” that will be useful when creating new sites. Let's prepare the “core” and base for the project. At first it will be a regular business card website, but then, by adding functionality, it can be turned into anything. So let's get started.
1. Preparing the database. Create the first table in the MySQL database
Create a new database, for example “mysite”. Personally, I’m used to working with UTF-8 encoding, so I’ll make a reservation right away: make sure that all text files on the site, the database itself, tables and table fields are in the same encoding.
We create a table in the new database. Let's call it “pages”. This table will store static pages of the future site and information about them. The table must contain the following fields:
- page_id - page identifier (SMALLINT, primary key, auto_increment);
- page_alias - page alias for the CNC address line (VARCHAR, 255);
- page_title - title of the page in the browser window (VARCHAR, 255);
- page_meta_d - meta description of the page for the meta description tag (VARCHAR, 255);
- page_meta_k - meta keywords for the meta keywords tag (VARCHAR, 255);
- page_h1 - page title (VARCHAR, 255);
- page_s_desc - a brief description of the material, for example, if the site materials will be in the form of a blog (TEXT);
- page_content - the main text of the page, which will be displayed in the central column of the site (TEXT);
- page_publish - contains “Y” - if the page is published, or “N” - if it is hidden (CHAR, default “Y”).
Immediately after creating the table, we insert into it the values for the main page of the site. I suggest inserting the value “home” into the “page_alias” field for the main page. Meta tags correspond to the theme of the entire site. In the same way, you can create other pages, for example, “About the company” with the alias “about” and your own meta tags, or “Contacts” with the alias “contacts”, etc.
2. Create a site configuration file
In the root folder of the site, which should be empty at this stage, we create a “cfg” folder, and in it, using .htaccess, we close access with the “deny from all” directive. Create a core.php file with the following content:
// MYSQL
class MyDB
{
var $dblogin = "root"; // YOUR LOGIN TO THE DATABASE
var $dbpass = ""; // YOUR PASSWORD TO THE DATABASE
var $db = "mysite"; // NAME OF THE DATABASE FOR THE SITE
var $dbhost="localhost";
Var $link;
var $query;
var $err;
var $result;
var $data;
var $fetch;
Function connect() (
$this->link = mysql_connect($this->dbhost, $this->dblogin, $this->dbpass);
mysql_select_db($this->db);
mysql_query("SET NAMES utf8");
}
Function close() (
mysql_close($this->link);
}
Function run($query) (
$this->query = $query;
$this->result = mysql_query($this->query, $this->link);
$this->err = mysql_error();
}
function row() (
$this->data = mysql_fetch_assoc($this->result);
}
function fetch() (
while ($this->data = mysql_fetch_assoc($this->result)) (
$this->fetch = $this->data;
return $this->fetch;
}
}
function stop() (
unset($this->data);
unset($this->result);
unset($this->fetch);
unset($this->err);
unset($this->query);
}
}
This file currently contains only a simple database connection class, but in the future you can add various useful functions to it that will be accessible from anywhere in the site code. Don't forget to change the login and password for your database.
If you are working in a Windows environment, I can recommend using the . This editor has line numbering and easily converts text from one encoding to another. ATTENTION! If you work in UTF-8 encoding, convert files to UTF-8 without BOM - this will help avoid problems in the future.
3. Create index.php - the main site controller
The configuration file has been created. Now in the root folder of the site we create index.php - this will be the main script of the site, a kind of “main controller”. Contents of the index.php file:
define("INDEX", ""); // SETTING THE MAIN CONTROLLER CONSTANT
Require_once($_SERVER."/cfg/core.php"); // CONNECTING THE KERNEL
// CONNECT TO DB
$db = new MyDB();
$db->connect();
// MAIN CONTROLLER
switch ($_GET) (
case "page":
include($_SERVER."/com/page.php");
break;
default:
include($_SERVER."/com/home.php");
break;
}
Include($_SERVER."/template.php");
$db->close();
The $_GET variable will tell the main controller which site component to load when requested. Currently, our site has only two components: “page” and “main page” (in principle, you can get by with one component for displaying a regular page, but often the appearance of the site’s main page differs from regular menu item pages). The logic of the main controller is as follows: the name of the required component is extracted from the URL string (the value of the $option variable), and depending on its value, the file of the component itself is included (contained in the /com folder). The component file does all the necessary work, extracts data from the database and writes it into variables for transfer to the design template. At the very end, the site design file is connected, into which all the variables and data extracted in the components are transferred. This sounds a lot more complicated than it works.
4. Create a regular page output component
In the root of the site, create a “com” folder - component files will be stored in it. A site component, in my understanding, is a file in which data is processed for different sections of the site. For example, a regular page component retrieves the title, description and text of the material from the database and writes them into the variables $title, $meta_d, $meta_k, $content, etc. This data is then transferred to the design template (you can create your own design template for each component ) and are displayed to the user as an HTML page. For example, a catalog component that could be created in the future would do almost the same thing, but with data about products - and it has its own specifics, other fields in the table, etc. Therefore, for each functional section of the site it is worth creating a separate component. In the MVC (Model-View-Controller) scheme, the component acts as a model.
Create a file “page.php” in the “com” folder. The contents of the file are as follows:
/* PAGE COMPONENT */
$alias = $_GET;
$query = "SELECT * FROM pages WHERE page_alias="".$alias."" AND page_publish="Y" LIMIT 1";
$db->run($query);
$db->row();
// COMPONENT VARIABLES
$id = $db->data;
$alias = $db->data;
$title = $db->data;
$h1 = $db->data;
$meta_d = $db->data;
$meta_k = $db->data;
$s_desc = $db->data;
$component = $db->data;
//IF THE PAGE DOES NOT EXIST
if (!$id) (
header("HTTP/1.1 404 Not Found");
$component = "ERROR 404! This page does not exist";
}
$db->stop();
5. Create the main page output component
The main page in our database is stored under the pseudonym “home”, and so far its structure does not differ from regular site pages - it’s just an article. Nevertheless, we will create a separate component for it - for the future, so to speak.
The contents of the “home.php” component in the “com” folder are almost identical to the contents of the regular page component, with the exception of the database query string and the component name. The query string now looks like this:
$query = "SELECT * FROM wx_pages WHERE page_alias="home" LIMIT 1";
6. Create a design template for the entire site
In the root of the site we create a template.php file. In essence, this is a regular web design layout in HTML+CSS format, only with PHP variables in the right places. Insert between title tags, in the central column of the site there is an insertand so throughout the template we place the necessary variables that are declared in the components.
The root folder should also have "css" and "images" folders for design elements. In the file /css/style.css - you can customize the styles at your discretion.
7. Clean links and .htaccess file
To create clean links, I use mod_rewrite with direct instructions for the rules for each component separately, since I consider parsing the address bar using the controller itself to be unnecessary functionality. The contents of .htaccess at this stage are:
RewriteEngine On
RewriteBase /
RewriteCond %(REQUEST_FILENAME) !-d
RewriteCond %(REQUEST_FILENAME) !-f
# PROHIBITED FILES
RewriteRule .htaccess - [F]
RewriteRule template.php - [F]
# RULES mod_rewrite
RewriteRule page/(+)([\/](0,1))\.htm$ index.php?option=page&alias=$1 [L]
In the future, we will add rules for the search components, catalog, article blog, etc. There is only one point: convert links like “mysite.com/index.php?option=pages&alias=about” into a link like “mysite.com/pages/about.htm” - it looks pretty nice. Try to avoid the $_GET array in development for safety reasons and do not rely on it. It is advisable to store in it only the parameters for the main controller (the $option variable) and for the component (the $alias variable).
Also, in each site folder, “just in case,” create an empty index.html file - this is necessary so that when accessing the directory through the address bar, nothing is displayed.
Tags: php, mysql, site engine, controller, site creation, mvc
Hello, dear readers of the blog site! Today we will dwell in detail on such actions in the hosting admin panel () as creating a database, adding a new site and additional domains (aliases).
These are the most common and frequently performed operations by users. As always, practice allows you to gain the necessary skills and in the future similar events will be carried out by you fully automatically, easily and naturally.
I would like to note that I have considered all possible actions related to , and will continue to do so using the example of my provider Sprinthost, since I have been using its services for quite a long time and know all the intricacies well (on Sprinthost.ru).
Creating a database in the hosting admin panel
The database is the most important and, perhaps, the most complex component of a modern website. However, thanks to modern technologies, managing it does not pose serious difficulties even for inexperienced novice webmasters.
For example, with (or another CMS, for example, Joomla) immediately occurs automatic database creation, after which you receive from the hoster a login and password to enter the website’s control panel, that is, almost at the same moment you can start working with your project.
However, sometimes it is useful to perform individual operations at the stages of building your web resource and subsequent administration manually or in a semi-automatic mode. This will not only allow you to feel and understand the essence of what is happening, but such experience can be very useful in the future.
It must be said that the vast majority of hosters use the most, which is used to manage databases built on the principle of tables using a special SQL language. Such databases are usually called relational.
In today's publication we will look at how to create a SQL database for a website in the admin panel, and in the next article I will tell you how to download WordPress (you will find the link below), link the created database to the site and install this engine on your hosting manually. So, go ahead and select the desired link in the “Databases” section of the PU:
After this, you go to a page where, along with information about existing MySQL databases, there is a button for creating a new database:
The next step is to come up with a name for the database and enter a user name. The prefix for them will be automatically generated based on the login that you use when logging into the administrative panel of your hosting.
By the way, when filling out the second part of the database name, the system can automatically fill in the user name column, duplicating the name of the database (in our example, “wn01”). You can change it at your discretion immediately or leave the option proposed by the system (in the future you can edit the composition of users):
You also need to create a more complex password. The entire password or part of it (by adding your own symbols) can be generated immediately by using the “Custom” button. If we are already talking about passwords, then in order to facilitate their memorization and enhance overall security, I advise you to use the KeePass program (there is a detailed article about this manager). After filling in all the fields, click the resulting create database button:
In practice, you may need a username and password if you decide to increase the level of security when working with MySQL databases and use it to connect to them SSH tunnel using special software (for example, PuTTY programs).
Also, the data provided in the screenshot will be useful in the next step when you do, so be sure to write down all this information somewhere safe. Now, if you follow the link again to the “Databases” section, the newly created database will appear in the list:
When you click on its name, you will be taken to a page where you can check, restore, optimize tables (the new database does not have them), as well as download or upload a backup copy of the database.
If you click on the number next to the name of any of the databases, then by moving to a separate editing tab, you can create a new user, adding it to an existing one, or delete existing ones (remember, I talked about this a little higher when creating the database). In addition, there is also an option to change the password:
If the admin panel has, so to speak, global options for working with the database, then for more subtle operations you can use the application, which is usually logged in directly from the control panel of your hosting account. On Sprinthost, for example, to do this, just click on the desired link, again in the “Databases” section (see the previous screenshot), after which the transition will take place:
By following the link, we get to the program interface, where on the left side there is a list of all installed databases (in the picture above). Having selected one of them, we have the opportunity to perform all sorts of actions with this database and the tables included in it. After creating the database and connecting it to the WordPress site, the composition of the tables will be as follows:
I have already described some basic operations with the database and tables in the material about phpMyAdmin, the link to which is given above. I consider it especially important to regularly perform backups of the database, which will help you completely restore it in the event of damage as a result of some severe force majeure.
Thus, you will save the results of your many days of work. Agree, for this it is worth studying this issue. We will return to this topic more than once in the future, since this is a very important point in website management. Since so many sites are hosted on hosting, where management is done via cPanel, I offer a corresponding video:
");">
Adding a new site in the control panel
Now let’s move on to how to add a website in addition to those already available on the hoster’s servers. Many hosting providers allow you to do this with the current tariff. For example, on Sprinthost I can add one project with the lowest tariff plan. To do this, follow the “Add site” link:
Then we find ourselves on a page where you can perform the desired operation:
As you probably understand, here you need to enter , which will serve as the name of the new web project. If you tick the option "Register this domain", this will be done automatically, since in this case the provider is a partner of several domain name registrars, in this case no further actions need to be taken.
If the domain has already been purchased (for example, I used the services of the reseller Reghouse), then there is no need to check the box. Click the resulting button and the site will be added in a few seconds. To check, you can return to the main page of the administrative panel, where the newly added site will show off among others.
How to communicate between a domain and hosting
But that's not all. If, when adding a site, you did not check the checkbox that activates the automatic registration of a domain name, then you need to register the DNS server addresses () for this second-level domain in order to link it to the hosting server. Let's say, after I bought a domain from the registrar, the data about it in mine was presented in the following form:
As you can see, the domain has not yet been delegated, that is, it is not associated with the IP address of the server on which the web resource will be located. In order to eliminate this shortcoming, go to the admin panel of your hosting account in the “Site Management” section, the link to which can be found in the left menu:
Click on it and you will be taken to a web page with a list of links to various tabs where you can perform various project administration actions. Choose there "DNS Settings":
After which you will be redirected to the next page where the necessary information is located:
We copy the DNS addresses, go to the registrar account, where we paste the copied data. Forms for entering DNS servers may differ between registrars, but this does not change the essence of the matter. At Reghouse, you need to click on the pencil icon, which will open a page for editing information about the DI, one of the items of which will be the field for entering the necessary data:
Enter the addresses copied from the provider and click the “Next” button at the very bottom:
The final step is to once again check the correctness of the entered data. If you make a mistake, you simply go back a step and correct the mistake. After you are convinced that the information is correct, click “Forward”:
After a short period of time (it took me about half an hour), the domain status changes:
Now this domain has already been delegated, that is, roughly speaking, it is associated with the IP of the server on which the website will be located. However, the process of updating DNS databases will take some time, from 24 to 72 hours, but in practice everything usually happens much faster. After some time, try entering the URL of a web resource, part of which will be a registered 2nd level domain, into the address bar of your browser:
http://vkusnyi-sait.ru
If the update has completed by this time, the hosting provider's stub will be displayed on the browser page:
This will mean that the place for your future project is ready. All that remains is to install, for example, WordPress, which can be installed either manually or through the hosting control panel using the high-speed method (links to the relevant materials are given closer to the beginning of this article).
By the way, you will be able to see the folders and files that will be located in the root directory of the new site (as well as your other resources). To do this, try connecting via FTP to the server where the resource is located using a special program (which will provide you with all possible assistance in the future):
So, to summarize, I once again remind you of the necessary actions when creating the next website:
- Buying a domain (on any service). By the way, in most cases you can register a DI with the hosting provider itself;
- Adding a site to the hosting control panel;
- The operation of delegating a domain, that is, linking it to the hosting server. If, when creating your first or subsequent projects on hosting, you check the “Register a domain” option (it may be called differently depending on the provider, but the essence is the same), then you do not need to take any actions, everything will be done automatically;
- Database creation. Again, when installing the engine using the hosting functionality, you can skip this step, since the database will be created automatically;
- Installation of a CMS (), for example, WordPress, which includes connecting the database to the site (for automatic installation, see the paragraph above).
Please note that if you use standard virtual hosting (and this is most likely if you are a novice webmaster), then all tariff limits (disk space, number of files, etc.) will apply to all websites.
Of course, the server load these websites place will add up. But there are savings in financial expenditures, since the projects are located on the same tariff plan and there is no need to pay for each of them.
In the future, you will simply need to ensure that the web server resource consumption limit is not significantly exceeded. This will happen only at a certain stage in the development of web projects when a significant level of traffic is achieved. But in this case, your possible income that you will receive from the sale of certain means of monetization will increase, so the transition to a more expensive tariff will be completely painless.
How to add an additional domain and create an alias, or site synonym
In the standard version, every self-respecting hoster has the opportunity to easily add several domains and sites (of course, their number depends on the tariff plan). On Sprinthost, the initial tariff provides for the creation of 2 additional sites (3 in total) and an unlimited number of domains.
You may ask, why are such conditions needed if each site must have a single domain? After all, if you can add 3 sites to the control panel, then there should be the same number of domains. It would seem that in this case the ability to add any number of domains simply does not make sense.
However, do not rush, everything is not so simple and unambiguous. The only thing that is certain is that each domain name (second, third level, etc.) can only correspond to one website. It is no coincidence that when registering a domain, we first check whether it is busy.
But a site can have as many DIs (pseudonyms) as desired. Let's say I have a web resource that corresponds to a 2nd level domain:
Website
This is the main domain at which the site files are located. However, let’s say I wanted the following URL to appear when the user entered the address bar of the browser:
http://goldbusinessnet.ru
The web page of my main project (site) would open. First of all, to do this you need to register a second-level domain name in the.ru zone:
Goldbusinessnet.ru
The DI must first be checked for employment; in general, you need to follow the standard registration scheme. After purchasing this domain name, go to the hoster’s administrative panel in the “Site Management” section.
There, select the required resource (in our case, “site”) and in the “Site Domains” tab, click the button "Add new domain":
After that, on the next web page, enter the CI and click the add button:
Again, I would like to draw your attention to the fact that in this case, you can register the DI using a hoster (if you have not done this previously with another registrar), by checking this option. Otherwise, if registration has already been completed, click the add button.
After some time, the alias (alias or synonym) of the main project will appear in the same “Site Domains” section. There you will also receive information about the DNS, the ability to add domain names for the main web resource, and also delete any of them at any time. This way, it is possible to control the number of aliases for each of your web projects.
For the example I gave, it would be most optimal to use it in an additional domain name, which is what I demonstrated. However, if the project name in DI includes some keywords, then you can experiment on this side and create several aliases in the same domain zone (although it is possible in different ones), but with different options for second-level domains. Example:
Main site: kak-sozdat-sajt.ru Site synonyms (aliases): sozdanie-sajtov.ru kak-sozdavat-sajty.com
Well, we should probably say why you need to create alias at all, what practical benefits can aliases bring? The most obvious reason is to increase brand awareness and get an additional interested audience to your website. After all, even if a visitor suddenly types his address incorrectly, he will still end up on your project. Large global corporations often take advantage of this opportunity.
There is another important nuance here. When creating synonyms, copies of the site are formed, so it must be implemented on the main domain to avoid duplication of content. Of course, most likely, a competent provider will automatically set up a redirection, but you must check its effect. Finally, one more video
Loading...
