A guide to database structure and design. Requirements Analysis: Determining the purpose of the database. Data Integrity Rules

The essence of database design, like any other design process, is to create a description of a new system that has not previously existed in this form, which, when implemented, is capable of expectedly functioning under appropriate conditions. It follows from this that the stages of database design must consistently and logically reflect the essence of this process.

Contents of database design and phasing

The design intent is based on some formulated social need. This need has an environment for its occurrence and a target audience of consumers who will use the design result. Consequently, the database design process begins with studying a given need from the point of view of consumers and the functional environment of its intended placement. That is, the first stage is collecting information and defining a model of the system’s subject area, as well as a look at it from the point of view of the target audience. In general, to determine system requirements, the scope of activities as well as the boundaries of database applications are determined.

Next, the designer, who already has certain ideas about what he needs to create, clarifies the tasks supposedly solved by the application, creates a list of them (especially if the project development is a large and complex database), clarifies the sequence of solving problems and performs data analysis. Such a process is also a staged design work, but usually in the design structure these steps are absorbed by the conceptual design stage - the stage of identifying objects, attributes, and connections.

Creating a conceptual (information model) involves the preliminary formation of conceptual user requirements, including requirements for applications that may not be immediately implemented, but taking into account which will improve the functionality of the system in the future. Dealing with representations of set abstraction objects (without specifying physical storage methods) and their relationships, the conceptual model essentially corresponds to the domain model. Therefore, in the literature, the first stage of database design is called infological design.

Next, a separate stage (or an addition to the previous one) follows the stage of forming requirements for the operating environment, where the requirements for computing resources capable of ensuring the functioning of the system are assessed. Accordingly, the larger the volume of the designed database, the higher the user activity and intensity of requests, the higher the requirements for resources: for the computer configuration, for the type and version of the operating system. For example, multi-user operation of a future database requires a network connection using an operating system suitable for multitasking.

The next step is for the designer to select a database management system (DBMS), as well as software tools. After this, the conceptual model must be transferred to a data model compatible with the selected management system. But often this involves making amendments and changes to the conceptual model, since the interconnections between objects reflected in the conceptual model cannot always be implemented using the means of a given DBMS.

This circumstance determines the emergence of the next stage - the emergence of a conceptual model provided with the means of a specific DBMS. This step corresponds to the stage of logical design (creating a logical model).

Finally, the final stage of database design is physical design - the stage of linking the logical structure and the physical storage environment.

Thus, the main stages of design in detailed form are presented in the following stages:

• information design,
• formation of requirements for the operating environment
• selection of control system and database software,
• logical design,
• physical design

The key ones will be discussed in more detail below.

Infological design

Identification of entities forms the semantic basis of infological design. An entity here is an object (abstract or concrete), information about which will be accumulated in the system. In the infological model of the subject area, the structure and dynamic properties of the subject area are described in user-friendly terms that do not depend on the specific implementation of the database. But the terms are taken on a standard scale. That is, the description is expressed not through individual objects of the subject area and their relationships, but through:

• description of object types,
• integrity constraints associated with the described type,
• processes leading to the evolution of a subject area - its transition to another state.

An information model can be created using several methods and approaches:

1. The functional approach is based on the assigned tasks. It is called functional because it is used if the functions and tasks of the persons who will serve their information needs with the help of the designed database are known.
2. The subject approach focuses on information about the information that will be contained in the database, despite the fact that the query structure may not be defined. In this case, research on a subject area focuses on its most adequate display in the database in the context of the full range of expected information requests.
3. An integrated approach using the “entity-relationship” method combines the advantages of the previous two. The method comes down to dividing the entire subject area into local parts, which are modeled separately and then recombined into a whole area.

Since the use of the “entity-relationship” method is a combined design method at this stage, it most often becomes a priority.

When methodically divided, local representations should, if possible, include information that would be sufficient to solve a separate problem or to meet the requests of a certain group of potential users. Each of these areas contains about 6-7 entities and corresponds to a separate external application.

The dependence of entities is reflected in their division into strong (base, parent) and weak (child). A strong entity (for example, a reader in a library) can exist in the database on its own, but a weak entity (for example, this reader’s subscription) is “attached” to a strong one and does not exist separately.

It is necessary to separate the concepts of “entity instance” (an object characterized by specific property values) and the concept of “entity type” - an object characterized by a common name and a list of properties.

For each individual entity, attributes (a set of properties) are selected, which, depending on the criterion, can be:

• identifying (with a unique value for entities of that type, making them potential keys) or descriptive;
• single-valued or multi-valued (with the appropriate number of values ​​for an entity instance);
• basic (independent of other attributes) or derived (calculated based on the values ​​of other attributes);
• simple (indivisible one-component) or composite (combined from several components).

After this, the attribute is specified, the connections are specified in the local view (divided into optional and mandatory) and the local views are merged. If the number of local areas is up to 4-5, they can be combined in one step. If the number increases, the binary merging of areas occurs in several stages.

During this and other intermediate stages, the iterative nature of design is reflected, which is expressed here in the fact that in order to eliminate contradictions it is necessary to return to the stage of modeling local representations for clarification and change (for example, to change the same names of semantically different objects or to coordinate integrity attributes on same attributes in different applications).

Selecting a control system and database software

The practical implementation of the information system depends on the choice of the database management system. The most significant criteria in the selection process are the following parameters:

• type of data model and its compliance with the needs of the subject area,
• reserve of possibilities in case of expansion of the information system,
• performance characteristics of the selected system,
• operational reliability and convenience of the DBMS,
• tools aimed at data administration personnel,
• the cost of the DBMS itself and additional software.

Errors in choosing a DBMS will almost certainly subsequently provoke the need to adjust the conceptual and logical models.

Logical database design

The logical structure of the database must correspond to the logical model of the subject area and take into account the connection of the data model with the supported DBMS. Therefore, the stage begins with choosing a data model, where it is important to take into account its simplicity and clarity.

It is preferable when the natural data structure coincides with the model representing it. So, for example, if the data is presented in the form of a hierarchical structure, then it is better to choose a hierarchical model. However, in practice, such a choice is often determined by the database management system rather than by the data model. Therefore, the conceptual model is actually translated into a data model that is compatible with the selected database management system.

This also reflects the nature of design, which allows for the possibility (or necessity) of returning to the conceptual model to change it if the relationships between objects (or object attributes) reflected there cannot be implemented using the chosen DBMS.

Upon completion of the stage, database schemas of both levels of architecture (conceptual and external) should be generated, created in the data definition language supported by the selected DBMS.

Database schemas are formed using one of two different approaches:

• or using a bottom-up approach, when work comes from the lower levels of defining attributes, grouped into relationships representing objects, based on the relationships existing between attributes;
• or using a reverse, top-down approach, used when the number of attributes increases significantly (up to hundreds and thousands).

The second approach involves identifying a number of high-level entities and their relationships with subsequent detailing to the required level, which is reflected, for example, in a model created based on the “entity-relationship” method. But in practice, both approaches are usually combined.

Physical database design

At the next stage of the physical design of the database, the logical structure is displayed in the form of a database storage structure, that is, it is linked to the physical storage environment where the data will be placed as efficiently as possible. Here the data schema is described in detail, indicating all types, fields, sizes and restrictions. In addition to developing indexes and tables, basic queries are defined.

The construction of a physical model involves solving largely contradictory problems:

1. tasks of minimizing data storage space,
2. challenges to achieve integrity, security and maximum performance.

The second task conflicts with the first because, for example:

• for transactions to function effectively, you need to reserve disk space for temporary objects,
• to increase search speed, you need to create indexes, the number of which is determined by the number of all possible combinations of fields involved in the search,
• To restore data, database backups will be created and a log of all changes will be kept.

All this increases the size of the database, so the designer is looking for a reasonable balance in which problems are solved optimally by intelligently placing data in memory space, but not at the expense of database security, which includes both protection from unauthorized access and protection from failures.

To complete the creation of a physical model, its operational characteristics are assessed (search speed, efficiency of query execution and resource consumption, correctness of operations). Sometimes this stage, like the stages of database implementation, testing and optimization, as well as maintenance and operation, is taken outside the immediate design of the database.

Before you start creating a database, you need to spend some time working on it. design.

The main goal of database design is to reduce the redundancy of stored data, and therefore save the amount of memory used, reduce the cost of multiple update operations of redundant copies and eliminate the possibility of inconsistencies due to storing information about the same object in different places . A so-called “pure” database project (“every fact in one place”) can be created using the relationship normalization methodology. Normalization should be used at the final verification stage of database design.

Poor design of the database structure almost always leads to wasted time spent on its further processing. Experienced developers spend as much time designing databases as they do creating them. In general, database development includes the following stages:

1. Determine the purpose of the database.

2. Deciding what input data the database should contain.

3. Determine the source database tables.

4. Determining the fields that will be included in the tables and selecting fields that contain unique values.

5. Assignment of relationships between tables and final review of the resulting structure.

6. Creating tables, linking them together and experimentally filling the database with trial data.

7. Creation of forms, reports and queries for operations with entered data.

Determining the purpose of the database

The development of every database begins with an examination of the problem it is intended to solve or the need it is intended to satisfy.

As an example, let's try to create the simplest database of a fiction library “Library”. The database is designed to store data about books purchased by the library, information about the location of individual copies of each publication and information about readers.

Selecting information to include in the database

To maintain library catalogs, organize searches for required books and library statistics, information must be stored in the database, most of which is placed in annotated catalog cards. An analysis of requests for literature shows that to search for suitable books (by topic, author, publisher, etc.) and select the right one (for example, by abstract), the following should be highlighted attributes index card:

2. Title of the book.

3. Place of publication (city).

4. Publisher (name of publishing house).

5. Year of manufacture.

6. Abstract.

Attributes that make it possible to characterize the storage locations of individual copies of books include:

1. Room number (room for storing books).

2. Shelf number in the room.

3. Shelf number on the rack.

4. Number (inventory number of the book).

5. Date of purchase.

6. The date of placement of a specific book in a specific place.

7. Date of removal of the book from the designated place.

Attributes that help characterize readers include:

1. Library card (form) number.

2. Reader's last name.

3. Reader's name.

4. Reader's middle name.

5. Reader's address.

6. Reader's phone number.

7. Date of issue of a specific book to the reader.

8. The period for which a particular book is issued to the reader.

9. Book return date.

Defining Source Tables

Analysis of the objects and attributes defined above allows us to determine the following tables for database construction for the designed database:

2. Books. The table is designed to store information about books.

3. Publishers.The table is intended to store information about publishers.

4. Storage. The table is intended to describe where books are stored.

5. Issue.The table is intended to store information about issued books.

6. Readers.The table is intended to store information about library readers.

Selecting required table fields

Having determined the set of tables included in the database, you need to think about what information about each object will be included in each of the tables. Each field must belong to one separate table. At the same time, the information in each field must be structurally elementary, that is, it must be stored in the fields in the form of the smallest logical components.

Based on the above, we determine fields in the selected tables and type stored data.

Books:

· book code– a numeric field designed to uniquely identify each specific book in the database;

· book title

· annotation- text field;

· publication date;

· date of admission to the library;

· storage.
Publishers:

· publisher code– a numeric field designed to uniquely identify each specific publisher in the database;

· publishing house name– character field, no more than 256 characters;

· city ​​where the publishing house is located– character field, no more than 25 characters.

Storage:

· location code– a numeric field designed to uniquely identify each specific shelf in the database;

· room number– numeric field;

· rack number– numeric field;

· shelf number– numeric field.

Issue:

· issue code– a numeric field designed to uniquely identify each specific issue in the database;

· issued book number– numeric field;

· reader code– numeric field;

· date of issue;

· date of issue(amount of days);

· return date.

Readers:

· library card number– a numeric field designed to uniquely identify each specific reader in the database;

· surname

· Name– character field, no more than 50 characters;

· surname– character field, no more than 50 characters;

· address– character field, no more than 256 characters;

· telephone– character field, no more than 20 characters.

Selecting unique fields

In a relational database, tables can be related to each other. This relationship is established using unique fields. Unique fields– these are fields in which values ​​cannot be repeated. For example, the series and number of a passport uniquely identifies any person holding a passport. A field (or combination of fields) that uniquely identifies a record in a table is called primary key.The primary key field can also be the serial number of an entry in the catalogue, the personnel number of an enterprise employee, or the article number of a product in retail trade.

For our database, the primary keys are the following fields:

· Books – book code.

· Publishers – publisher code.

· Storage – location code.

· Issue – issue code.

· Readers – number of ticket.

Assigning relationships between tables

Inter-table relationships link two tables using a common field that exists in both tables. There are three types of such connections:

· one to one– each record of table A cannot be associated with more than one record of table B;

· one-to-many– one record in table A can be associated with many records in table B (for example, there can be many students in each class);

· many-to-many– each record in table A can be associated with many records in table B, and each record in table B can be associated with many records in table A (for example, each student can have several teachers, and each teacher can have many students).

Relational databases do not allow many-to-many relationships to be created directly. However, in real life such relationships occur very often, so they are implemented through auxiliary tables, linking several tables with one-to-many relationships.

In order to link one table to another, you need to enter the primary key field from the first table into the second table, i.e. enter into second table external key. The relationship between two tables is performed by connecting the primary key of the main table (located on the “one” side of the relationship) to the same foreign key field of the related table (located on the “many” side of the relationship). A foreign key field in a related table must be the same data type as the primary key in the parent table, with one exception. If the main table's primary key has a Counter data type, then the foreign key field in the related table must have a Numeric data type.

In our database we will establish the following types of relationships between tables:

1. Authors - Books. Here's the connection many-to-many, any author can have more than one book, and any book can be written by more than one author. Therefore, we introduce an auxiliary table “Authors–Books” with the following fields:

· book code.

2. Books – Publishers. Here's the connection many-to-many, any book can be published by several publishing houses, and any publishing house publishes more than one book. Therefore, we introduce another auxiliary table “Books-publishing houses” with the following fields:

· book code;

· publisher code.

3. Storage - Books. Here's the connection one-to-many, many books can be placed on one shelf, but any book can only be on one shelf in storage. Therefore, we define the “Storage Location” field in the “Books” table as a foreign key, and connect the “Storage” and “Books” tables with the primary key “Location Code” and the foreign key “Storage Location”.

4. Books – Issue. Here's the connection one-to-many, i.e. the same book can be issued several times on different dates to different readers. Therefore, we define the “Issue book number” field in the “Issue” table as a foreign key, and connect the “Books” and “Issue” tables with the primary key “Book code” and the foreign key “Issue book number”.

5. Readers - Issue. Here's the connection one-to-many, i.e. The same book can be issued several times to different readers at different times. Therefore, we define the “Reader Code” field in the “Issue” table as a foreign key, and connect the “Readers” and “Issue” tables with the primary key “Library Card Number” and the foreign key “Reader Code”.

Normalization of relations

Having finished designing tables and identifying the relationships that exist between them, you need to carefully double-check the resulting structure before starting to create tables and enter information. Normalization of relations allows you to significantly reduce the amount of stored information and eliminate anomalies in the organization of data storage.

Rule 1: Each table field must represent a unique type of information.

In the database we designed, there are no fields in different tables containing the same information (with the exception of foreign keys).

Rule 2: Each table must have a unique identifier, or primary key, which can consist of one or more fields.

In the database we designed, all tables (with the exception of the auxiliary “Authors - books” and “Publishers - books”) contain a primary key.

Rule 3: For each primary key value, the values ​​in the data columns must relate to the table object and completely describe it.

This rule is used in two ways. First, the table should not contain data that is not related to the object defined by the primary key. For example, although each book requires information about its author, the author is an independent object, and data about him should be in the appropriate table. Secondly, the data in the table must fully describe the object.

Rule 4: It should be possible to change the values ​​of any field (not included in the primary key) without affecting the data of other fields.

The last rule allows you to check whether problems will arise when changing data in tables. Since in the database we designed, the data contained in different fields of the tables is not repeated anywhere, we have the opportunity to adjust the values ​​of any fields (with the exception of primary keys).

Filling the database, creating forms and reports

To determine how well the database structure corresponds to the task at hand and how convenient it is to work with this database, you need to enter a few simple entries. Typically, after this you have to return to the structure of the database and adjust it in accordance with what results were obtained during such a test.

At the final stage, forms are created for entering information into the database, reports for displaying information, and queries that are used to retrieve information from several tables. If the database is intended to be transferred to other users, then, most likely, it is necessary for someone from outside to check how convenient it is to work with forms and reports.

Received data schema The developed database in MS Access is shown in Fig. 4.1.

Rice. 4.1. Data schema of the developed database in Microsoft Access

Control questions

1. Define an information system.

2. Explain the concept of a database.

3. What is a subject area?

4. Define DBMS.

5. What is a data model?

6. Explain the basic principles of the relational data model.

7. Explain the features of the Microsoft Access DBMS.

8. What are the main objects of an Access database?

9. Explain the structure of an Access table.

10. Explain the concepts: request, form, report, data access page, macro, module.

11. What are the main stages of database design?

12. How is the information included in the database selected?

13. Explain the concepts: primary key, foreign key.

14. What is the purpose of relationships between tables?

15. Explain the main types of relationships between tables.

16. What is the normalization of database relationships?

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Posted on http://www.allbest.ru/

INVeating

interface program user system

Today, there are hundreds of millions of personal computers operating in the world. Scientists, economists, and politicians believe that by the beginning of the third millennium:

The number of computers in the world will be equal to the number of inhabitants of developed countries.

Most of these computers will be included in global information networks.

all information accumulated by humanity by the beginning of the third millennium will be translated into computer (binary) form, and all information will be prepared with the help (or with the participation) of computers; all information will be stored indefinitely in computer networks;

a full-fledged member of the society of the third millennium will have to interact every day with local, regional or global networks using computers.

With such computerization of almost all sectors of human activity, the question arises of creating programs that allow the creation of such databases. Therefore, this program was developed, which allows you to create a database that stores information about the progress of schoolchildren.

1. Database and methods of its presentation

A database (DB) is information presented in the form of two-dimensional tables. The database contains many rows, each of which corresponds to an object. For each object, certain independent positions are used, which are called fields. Let's imagine such a database containing rows and columns (the simplest case). Each line, also called a record, corresponds to a specific object. Each column contains the values ​​of the corresponding object data.

A database may consist not of one table, but of two, three or more. Additional information about an object can be stored in additional tables.

One of the powerful features of the database is that information can be organized according to the criteria specified by the user. In Pascal, the database is provided as a list of terms of the form: database_predicate_name (record_fields). Database names are described in the section. Database records are accessed using a base predicate. pascal provides quite a lot of tools for working with such databases: loading, writing, adding, etc.

A database is an organized structure designed to store information. Modern databases store not only data, but also information.

This statement is easy to explain if, for example, we consider the database of a large bank. It contains all the necessary information about clients, their addresses, credit history, status of current accounts, financial transactions, etc. A fairly large number of bank employees have access to this database, but among them there is hardly a person who has access to the entire database and at the same time is able to single-handedly make arbitrary changes to it. In addition to data, the database contains methods and tools that allow each employee to operate only with the data that is within their competence. As a result of the interaction of the data contained in the database with the methods available to specific employees, information is generated that they consume and on the basis of which, within their own competence, they enter and edit data. Closely related to the concept of a database is the concept database management systems. This is a set of software tools designed to create the structure of a new database, fill it with content, edit content and visualize information. Under information visualization database refers to the selection of displayed data in accordance with a given criterion, their ordering, design and subsequent delivery to output devices or transmission via communication channels. There are many database management systems in the world. Although they may work differently with different objects and provide the user with different functions and features, most DBMSs rely on a single, established set of core concepts. This gives us the opportunity to consider one system and generalize its concepts, techniques and methods to the entire class of DBMS. As such a training object, we will choose the Pascal 7.0 DBMS, included in the Pascal 7.0 package.

2. Properties of database fields

Database fields not only define the structure of the database - they also determine the group properties of the data written to the cells belonging to each of the fields. Listed below are the main properties of database table fields using the Pascal 7.0 DBMS as an example.

Field name - determines how the data of this field should be accessed during automatic operations with the database (by default, field names are used as table column headings).

Field type - determines the type of data that can be contained in this field.

Field size - determines the maximum length (in characters) of data that can be placed in this field.

Field format - determines how data is formatted in cells belonging to the field.

Input mask - defines the form in which data is entered into the field (data entry automation tool).

Signature - defines the table column heading for a given field (if a signature is not specified, then the Field Name property is used as the column heading).

The default value is the value that is entered into the field cells automatically (data entry automation tool).

A value condition is a constraint used to check the correctness of data entry (an input automation tool that is typically used for data that has a numeric, currency, or date type).

Error message is a text message that is displayed automatically when you try to enter incorrect data in a field.

Mandatory field - a property that determines whether this field must be filled in when filling out the database.

Empty lines - a property that allows the entry of empty string data (it differs from the Required field property in that it does not apply to all data types, but only to some, for example, text).

Indexed field - if a field has this property, all operations related to searching or sorting records by the value stored in this field are significantly speeded up. In addition, for indexed fields, you can make sure that the value in records will be checked against this field for duplicates, which allows you to automatically eliminate data duplication.

Since different fields can contain data of different types, the properties of the fields can vary depending on the data type. For example, the list of the above field properties refers mainly to text-type fields.

Fields of other types may or may not have these properties, but can add their own to them. For example, for data that represents real numbers, the number of decimal places is an important property. On the other hand, for fields used to store pictures, sound recordings, video clips, and other OLE objects, most of the above properties are meaningless.

3 . Tsewhether and tasks

When creating this program, the following goals were set:

· Write a program that would allow you to process, sort and change information about parking lots.

Also, when creating this program there were the following tasks:

· This program should have a simple and convenient user interface.

· This program should have low resource consumption.

4. System menu development

The system menu or main menu should provide convenient user interaction with the program. The menu should include options for saving, viewing, entering new data, etc. The user just needs to press the `enter' button. There are six items in the menu of this program:

1 - File creation

2 - Adding an entry

3 - Record correction

4 - View a record from a file

5 - Delete an entry

6 - Exit

1 - Creating a new file - A new file is created with a name specified by the program user

2 - Viewing the contents of the file - previously created records are displayed on the screen one by one in the form of:

Owner's last name:

Owner's name:

car make:

scale model:

body type:

number of the car:

region:

year of issue:

color:

3 - Adding an entry - Create a new entry and add it to the file at the end of the entry.

4 - Search by ward number - Allows you to find data about a vacationer by the ward number in which the vacationer is registered.

5 - Exit the program - exit the program

Conclusion

The work done allows any user to easily create large amounts of information, process them, sort them, and make selections according to certain criteria.

The use of such a program in the modern world greatly facilitates human activity.

Posted on Allbest.ru

Similar documents

Determining the necessary program modules and database file structure. Description of program development, debugging and testing. Development of the Organizer.exe application, menu and user manual. Algorithm for processing main menu events (schedules).

course work, added 02/11/2014


Features of designing a program in C++ for processing data from database tables. The main functions of the program, creating a conceptual database model and class diagram, developing a user interface and database queries.

course work, added 06/08/2012


Selecting the composition of hardware and software for system development. Description of input and output data. Selecting a database model. Development of a subsystem for filling the database and generating reports. User interface development, system testing.

course work, added 12/04/2014


Stages of database creation and development. Building a domain model. Development of datalogical and physical data models, methods of processing data about the organization’s employees. Designing user applications. Creating a button form.

course work, added 02/14/2011


Drawing up a conceptual data model diagram. Development of relational database structure and user interface. Features of the main stages of database design. Methods for implementing queries and reports. Specifics of the user manual.

course work, added 12/18/2010


The process of developing a database for storing and processing information. Keys, indexes, triggers, stored procedures. User interface and database development. Basic tools for developing client and server parts.

thesis, added 05/18/2013


Stages of database design, defining goals and table contents. Adding data and creating other database objects. Datalogical model: structuring, normalization, data schemas. The order and principles of creating a user interface.

course work, added 03/26/2013


User interface development technology in Delphi environment. Creation of tables, menus, forms for entering and editing data. Principles of menu organization as an element of the user interface. Implementation of sorting, filtering, calculations in the table.

course work, added 11/13/2012


Basic rules for user interface design. Creation of a database using the developed models. Coding software system modules to create a prototype. The primary window when the program starts. Protection against information loss.

laboratory work, added 06/13/2014


Description of the development subject area. Features of storing information about cars and owners. Description of the database structure. Main tables: cars, owners, types of work, spare parts, orders, services. Instructions for the programmer and user.

The following stages of database development can be distinguished:

· design;

· software implementation;

· filling and operation.

The design stage is the theoretical construction of the initial information model of the database. It includes:

· collecting information about the subject area, its structure, input and output information flows of data, studying automation tasks, analyzing and identifying objects of the source system, and identifying connections between them;

· determination of properties and characteristics for each object in the database, to which fields (attributes) are assigned, source tables and relationships between them are compiled, data elements included in the database are determined, restrictions on data values, etc.

· assignment of primary keys (fields) for each object and normalization (partitioning) of source tables;

· checking the correctness of the project, which must display all selected objects, their attributes and described processes at the required level of detail, display the subject area that requires solving the problem;

· determination of the logical structure of the database;

· solving issues of protecting and maintaining database integrity. Ensuring data integrity refers to a system of measures aimed at maintaining the correctness of data in the database at any time.

The software implementation stage is associated with the development of applications on a computer, for which the following steps must be performed:

· describe the resulting tables using a DBMS and enter them into the computer;

· for users of the information system, develop interfaces for working with the database, that is, screen forms for entering and displaying data, reports for printing summary data, queries for obtaining data;

· develop a procedure for maintaining and maintaining the database in working order, the work of end users;

· test the system, draw up instructions for working with it and train staff.

The operation and population phase begins with populating the database with specific data. It includes the direct maintenance of the database and its maintenance.

When developing databases for large enterprises and corporations, analysis and modeling are performed using special software tools, such as CASE tools, which allow you to model data flows, processes and functions of the enterprise, identify bottlenecks and make recommendations for the effective organization of the structure and business processes in the enterprise .

In addition to building models of the current state of the enterprise and analysis, modeling software allows you to create specifications and build a project for a future system; moreover, program code for the most common DBMS can be obtained. Thus, the modeling stage can cover the design stage and part of the implementation stage of the information system.

Conceptual database design

The first phase of the database design process is called conceptual database design. It consists in creating a conceptual data model for the analyzed part of the objects of the system under study. This data model is created based on the information recorded in the user requirement specifications. The conceptual design of a database is absolutely independent of such details of its implementation as the type of DBMS chosen, the set of application programs to be created, the programming languages ​​used, the type of computing platform chosen, as well as any other features of the physical implementation. The created conceptual data model is the source of information for the logical database design phase.

Logical Database Design

The second phase of database design is called logical database design. Its purpose is to create a logical data model. The conceptual data model created in the previous step is refined and transformed into a logical data model. The logical data model takes into account the features of the selected data organization model in the DBMS (for example, a relational or network model).

If the conceptual data model does not depend on any physical aspects of the implementation, then the logical data model is created based on the selected data organization model in the DBMS. In other words, at this stage it should already be known which DBMS will be used - relational, network, hierarchical or object-oriented. However, at this stage, all other aspects of the selected DBMS are ignored - for example, any features of the physical organization of its data storage structures and index construction.

During the development process, the logical data model is constantly tested and verified to ensure it meets user requirements. To check the correctness of the logical data model, the normalization method is used. Normalization ensures that the relationships derived from the existing data model do not have data redundancy that could cause update anomalies after they are physically implemented. Among other things, the logical data model must support all transactions required by users.

The constructed logical data model informs the physical design phase and provides the physical database designer with the means to make trade-offs necessary to achieve set goals, which is very important for effective design. The logical data model also plays an important role at the stage of operation and maintenance of a ready-made system. With properly organized support, an up-to-date data model allows you to accurately and clearly represent any changes made to the database and assess their impact on application programs.

Database normalization

When designing databases, the most important thing is to define table structures and relationships between them. Errors in the data structure are difficult, and often impossible, to correct programmatically. The better the data structure, the easier it is to program the database. Database design theory contains the concept of normal forms intended to optimize the structure of the database. Normal forms are a linear sequence of rules applied to the database, and the higher the number of the normal form, the more perfect the structure of the database. Normalization is a multi-step process in which database tables are organized, separated, and data brought into order. The purpose of normalization is to eliminate some undesirable characteristics from the database. In particular, the goal is to eliminate some types of data redundancy and thereby avoid anomalies when changing data. Data change anomalies are difficulties during insertion, modification and deletion of data that arise due to the structure of the database. Although there are many levels, it is usually sufficient to normalize to Third Normal Form.

Let's consider an example of normalizing the order delivery management database. An unordered “Sales” database would consist of one table (Fig. 7).

Fig.7. DB "Sales"

In the table, each record contains information about several orders from one customer. Because the product information column contains too much data, it is difficult to obtain organized information from this table (for example, creating a report on total purchases for various types of products).

First normal form

First normal form determines the atomicity of all data contained in the columns. The word "atom" comes from the Latin "atomis", which literally means "not divisible". First normal form specifies that there is only one value at each position defined by row and column, rather than an array or list of values. The benefits of this requirement are obvious: if lists of values ​​are stored in a single column, there is no easy way to manipulate those values. Of course, this increases the number of records in the table.

Let's normalize the "Sales" database to the first normal form (Fig. 8).

Fig.8. First normal form

3.3.2. Second normal form

You can move to Second Normal Form from a table that already corresponds to First Normal Form. Additionally, the following condition must be met: each non-key field must be completely dependent on the primary key.

Let's normalize the "Sales" database to second normal form. We will separate all information not related to individual orders into a separate table. As a result, instead of one “Sales” table, we get two - the “Orders” table (Fig. 9) and the “Products” table (Fig. 10).

Fig.9. Table "Orders"

Fig. 10. Table "Products"

Thus, the product type is stored in only one table. Please note that no information is lost during normalization.

3.3.3. Third normal form

A table is considered to conform to Third Normal Form if it conforms to Second Normal Form and all non-key columns are mutually independent. A column whose values ​​are derived from data from other columns is one example of a dependency.

Let's normalize the "Sales" database to third normal form. To do this, remove the “Total” column from the “Orders” table. The values ​​in this column do not depend on any key and can be calculated using the formula ("Price")*("Quantity"). Thus, the “Sales” database with an optimal structure was obtained, which consists of two tables (Fig. 11).

Rice. 11. Normalized database "Sales"

3.2 Software implementation of the database

The software implementation of the database is carried out by creating a target DBMS in the data definition language (DDL). DDL commands are compiled and used to create schemas and empty database files. At the same stage, all specific user views are defined.

Application programs are implemented using third or fourth generation languages. Some elements of these application programs will be database processing transactions written in the data manipulation language (DML) of the target DBMS and called from programs in the underlying programming language - for example, Visual Basic, C++, Java. This phase also creates other components of the application project, such as menu screens, data entry forms, and reports. It should be noted that many existing DBMSs have their own development tools that allow you to quickly create applications using non-procedural query languages, a variety of report generators, form generators, graphics generators and application generators.

This stage also implements the application's database security and integrity support features. Some of them are described using DDL, while others may need to be defined by other means - for example, using additional DBMS utilities or by creating application programs that implement the required functions.

3.2.1. Application Development

Application development is the design of the user interface and application programs designed to work with a database. In most cases, application design cannot be completed until the database design is completed. On the other hand, the database is designed to support applications, and therefore information must be constantly exchanged between the phases of designing the database and designing applications for that database.

You must ensure that all functionality required by the user requirement specifications is supported by the user interface of the relevant applications. This applies both to the design of application programs for accessing information in a database, and to the design of transactions, i.e. designing database access methods.

In addition to designing how a user can access the functionality they need, you should also design the appropriate user interface for your database applications. This interface should provide the information the user needs in the most convenient way for him.

3.2.2 Database testing

Testing is the process of executing application programs in order to find errors. Before using a new system in practice, it should be thoroughly tested. This can be achieved by developing a well-thought-out testing algorithm using real data, which must be structured in such a way that the entire testing process is carried out strictly sequentially and methodically correctly. The task of testing is not the process of demonstrating the absence of errors; it is unlikely to be able to demonstrate the absence of errors in the software - rather, on the contrary, it can only show their presence. If testing is carried out successfully, then errors in application programs and database structures will certainly be revealed. As a by-product, testing can only demonstrate that the database and application programs perform within their specifications while meeting existing performance requirements. In addition, the collection of statistical data at the testing stage allows us to establish indicators of the reliability and quality of the created software.

As with database design, users of the new system must be involved in the testing process. Ideally, system testing should be carried out on a separate set of equipment, but often this is simply not possible. When using real data, it is important to first create backup copies of it in case it is damaged due to errors. Upon completion of testing, the process of creating an application system is considered complete, and it can be transferred to industrial operation.

3.3 Operation and maintenance of the database

Operation and maintenance - support for the normal functioning of the database.

In the previous steps, the database application was fully implemented and tested. The system now enters the last stage of its life cycle, called operation and maintenance. It includes performing actions such as:

· monitoring system performance. If performance falls below acceptable levels, additional database reorganization may be required;

· maintenance and modernization (if necessary) of database applications. New requirements are incorporated into the database application when previous life cycle steps are re-executed.

Once the database is put into use, its operation should be monitored continuously to ensure that performance and other indicators meet the requirements. A typical DBMS usually provides various database administration utilities, including utilities for loading data and monitoring the functioning of the system. Such utilities can monitor system performance and provide information on various metrics, such as database utilization, the effectiveness of the locking system (including information about the number of deadlocks that have occurred), and the selected query execution strategies. The database administrator can use this information to tune the system to improve performance (for example, by creating additional indexes), speed up query execution, change storage structures, or join or split individual tables.

The monitoring process must be maintained throughout the life of the application, allowing for effective reorganization of the database at any time to meet changing requirements. Such changes provide information about the most likely improvements to the database and the resources that may be required in the future. If the DBMS you are using does not have some of the necessary utilities, then the administrator will have to either develop them himself or purchase the required additional tools from third-party developers.

4. Microsoft Access DBMS

4.1.Purpose and general information about the Microsoft Access DBMS

Microsoft Access is a database management system that uses a relational data model and is part of the Microsoft Office application package. It is designed to store, enter, search and edit data, as well as display it in a convenient form.

The areas of application of Microsoft Access include the following:

· in small business (accounting, order entry, maintaining customer information, maintaining information about business contacts);

· in large corporations (applications for workgroups, information processing systems);

· as a personal DBMS (address directory, investment portfolio management, cookbook, catalogs of books, records, videos, etc.).

Access is one of the most powerful, convenient and simple database management systems. Because Access is part of Microsoft Office, it shares many of the same features as Office applications and can exchange information with them. For example, when you work in Access, you can open and edit files, and use the clipboard to copy data from other applications.

The tools for developing objects in Access are “wizards” and “constructors.” These are special programs that are used to create and edit tables, queries, various types of forms and reports. Typically, the “master” is used to create, and the “constructor” is used to edit objects. The editing process involves changing the appearance of some object in order to improve it. When editing a form, you can change the names and order of the fields, increase or decrease the size of the data entry area, etc. You can use the “constructor” to create forms, but this is a very labor-intensive job. Access includes special software tools that help you analyze data structures, import spreadsheets and text data, improve application performance, and create and customize applications using built-in templates. To fully automate your applications, you can use macros to link data to forms and reports.

Access implements relational database management. The system supports primary and foreign keys. Ensures data integrity at the kernel level, which does not allow incompatible update or delete operations. Tables in Access are equipped with data validation tools, i.e. Invalid input is not allowed. Each table field has its own format and standard descriptions, which makes data entry easier. Access supports the following field types, including: Tab, Text, Numeric, Counter, Currency, Date/Time, MEMO, Boolean, Hyperlink, OLE Object, Attachment, and Calculated fields. If there are no values ​​in the fields, the system provides full support for empty values.

Access allows you to use graphics tools, just like Microsoft Word, Excel, PowerPoint, and other applications, to create different types of graphs and charts. You can create histograms, 2D and 3D charts. You can add all kinds of objects to Access forms and reports: pictures, diagrams, audio and video clips. By linking these objects to a developed database, dynamic forms and reports can be created. You can also use macros in Access to automate certain tasks. They allow you to open and close forms and reports, create menus and dialog boxes to automate the creation of various application tasks.

In Access, you can get context-sensitive help by clicking , and reference information on the issue that interests the user at the moment will appear on the screen. At the same time, you can easily navigate to the table of contents of the help system, specific information, a history of previous accesses, and bookmarks. Database information is stored in a file with the extension .accdb.

4.2. Microsoft Access Objects

When you start the Access DBMS, a window appears for creating a new database or for working with previously created databases, or existing templates (Fig. 12).

Rice. 12. Launch Access

Templates are empty database structures in which field types are defined, basic objects are created, relationships between tables are established, etc.

When creating a new database, Access will open an empty table containing one row and two columns (Figure 13).

Fig. 13. New Database Window

The left side of the window (navigation area) shows all created database objects, while we only see an empty table, because the created objects are no longer in the new database (Fig. 13). The main objects of the Access DBMS include the following.

Tables. Tables are the main objects of databases because they store all the data and define the structure of the database. A database can contain thousands of tables, the size of which is limited only by the available space on the computer's hard drive. The number of records in the tables is determined by the size of the hard drive, and the number of fields is no more than 255.

Tables in Access can be created as follows:

· in “designer” mode;

· in the mode of entering data into a table.

You can create a table by importing or creating a link to data stored elsewhere. This can be done, for example, with data stored in an Excel file, a Windows SharePoint Services list, an XML file, or another MS ACCESS database. A SharePoint list allows you to provide access to data to users who do not have the MS ACCESS application installed. When you import data, a copy of it is created in a new table in the current database. Subsequent changes made to the original data will not affect the imported data, and vice versa. When data binding is performed, a linked table is created in the current database that provides a dynamic connection to data stored elsewhere. Changes to data in a linked table are reflected in the source, and changes in the source are reflected in the linked table.

Datasheet view displays the data stored in the table, while Design view displays the structure of the table.

If the tables have common fields, you can use a subtable to insert records from another table into one table. This approach allows you to simultaneously view data from multiple tables.

Requests. Queries are special tools designed to search and analyze information in database tables that meet certain criteria. The records found, called query results, can be viewed, edited, and analyzed in a variety of ways. In addition, the results of a query can be used as the basis for creating other Access objects. There are different types of queries, the most common of which are select queries, parametric and cross-over queries, record deletion queries, change queries and others. Less commonly used are action queries and SQL (Structured Query Language) queries. If the required request does not exist, you can create it additionally.

Requests are generated in various ways, for example, using the “wizard”; you can also create a request manually in the “designer” mode. The simplest and most commonly used type of query is the select query. These queries select data from one or more tables and form it into a new table whose records can be modified. Select queries are used to calculate sums, averages, and other totals. Thus, queries use data from the main tables and create temporary tables.

Forms. Forms are used to enter and edit records in database tables. Forms can be displayed in three modes: a mode designed for data entry, a table mode where data is presented in a tabular format, and a "layout" and "design" mode that allows you to make changes and additions to the forms.

The main elements of the form are inscriptions, which indicate the text that is directly displayed in the form, and fields containing the values ​​of the table fields. Although Builder mode allows you to create a form from scratch, it is typically used to refine and improve forms created using the Wizard. In addition to the above tools, forms can also be created using the following tools:

· "form";

· “divided form”;

· “several elements”;

· "empty form".

It is most effective to use forms for data entry in the form of special forms, since the form can look like a form. The use of forms allows you to enter data in a user-friendly form of familiar documents. I/O forms allow you to enter data into the database, view it, change field values, add and delete records. The form may contain a button that is used to print a report, open other objects, or perform other tasks automatically.

Reports. Reports are used to display information in tables in a formatted form that is clearly presented both on the monitor screen and on paper. A report is an effective means of printing data from a database in the form required by the user (in the form of certificates, examination papers, tables, etc.). In addition to data extracted from multiple tables and queries, reports can include design elements found in printed documents, such as titles, headers, and footers.

The report can be displayed in four modes: in the “designer” mode, which allows you to change the appearance of the report, in the sample view mode, in which you can display all the elements of the finished report, but in an abbreviated form, in the “layout” mode, which allows you to display it more clearly (by compared to design mode) and format the report, and in preview mode, where the report is displayed as it will be printed.

Tables, queries, forms, and reports are the objects most widely used in Access database development.

However, the capabilities of the database can be significantly expanded if you use access pages, macros and modules.

Pages. To provide Internet users with access to information, special data access pages can be created in the database. Using data access pages, you can view, add, change, and manipulate data stored in the database. Data access pages can also contain data from other sources, such as Excel. To publish information from a database in Web Access, a “wizard” is included, which ensures the creation of an access page.

Macros. Macros are small programs of one or more macro commands that perform specific operations, such as opening a form, printing reports, clicking a button, etc. This is especially useful if you intend to share the database with unskilled users. For example, you can write macros that contain a sequence of commands that perform routine tasks, or associate actions such as opening a form or printing a report with buttons on a pushbutton.

Modules A module is a database object that allows you to create libraries of routines and functions used throughout the application. Using module codes, you can solve problems such as handling input errors, declaring and using variables, organizing loops, etc.

INTRODUCTION

1.2 Database

1.3 Database system architecture

1.4 Data model

1.5 Relational model

2. PROBLEM STATEMENT

3. THEORETICAL FOUNDATIONS OF RELATIONAL DATABASES

3.1 Relational algebra

3.1.1 General interpretation of relational operations

3.1.2 Closedness of relational algebra and the renaming operation

3.1.3 Features of set-theoretic operations of relational algebra

3.2 Relational calculus

3.2.1 Tuple variables and well-formed formulas

3.2.2 Target lists and relational calculus expressions

3.2.3 Relational domain calculus

3.3 Data integrity

3.4 Database design

4. DATABASE DEVELOPMENT

4.1 Database domain

4.2 Construction of an information model

4.3 Database design

5. DEVELOPMENT OF A CLIENT APPLICATION

5.1 Rationale for choosing a programming environment

5.2 Delphi tools for working with databases

5.3 Application implementation

5.3.1 General description of forms and modules

5.3.2 MainForm and Main module

5.3.3 DataModule1 and DBUnit module

5.3.4 EditForm and Edit module

5.3.5 DeleteForm and Delete module

5.3.6 FindForm and Find module

5.3.7 FilterForm and Filter module

5.3.8 DirSourceForm and DirSource module

5.3.9 PathForm and Path module

5.3.10 UserForm and User module

5.3.11 AboutBox form and About module

5.3.12 Files module

6. ECONOMIC PART

6.1 Database subject area and its development

6.2 Development of a network schedule for research work

6.3 Calculation of cost estimates for research work

7. OCCUPATIONAL SAFETY

7.1 General occupational safety issues

7.2 Industrial sanitation

7.3 Safety precautions

7.4 Operational measures

7.5 Fire safety

7.6 Environmental protection

8. CIVIL DEFENSE

LIST OF LINKS

APPLICATIONS

INTRODUCTION

To make informed and effective decisions in production activities, in economic management and in politics, a modern specialist must be able to use computers and communications to receive, accumulate, store and process data, presenting the result in the form of visual documents. In modern society, information technologies are developing very rapidly; they penetrate into all spheres of human activity.

The purpose of this thesis is to develop a remote database and client application for accessing electronic sources of literature contained on the hard drive of an enterprise server in the form of files and file packages (text documents of various types, HTML hypertext, executable files, etc.). The client-server architecture used to implement the task is currently the most progressive. It makes it possible to divide the task into two subtasks: the development of the actual remote database, physically located on the server and managed by the DBMS, and the application that accesses this database using SQL queries and is located on the workstations of network users. With this implementation, the load is also distributed between the server and workstations, which increases the speed of the program.

To manage the database, InterBase 6.0 DBMS from Borland was chosen. To develop the client part of the application, the Borland Dalphi 7.0 Eneterprise Edition programming environment was used, which provides convenient tools for quickly and visually creating such applications.

The database developed during the thesis allows you to increase the speed of search and user access to the necessary sources of literature, allows you to organize and systematize them. And this, in turn, can affect the productivity of the user, who does not have to spend a lot of time searching for the necessary information.

1. ANALYTICAL REVIEW OF LITERARY SOURCES

1.1 Basic concepts of database systems

A database system is a computerized record storage system, i.e. a computerized system whose main purpose is to store information, providing users with a means of retrieving and modifying it.

Advantages of a system with a database compared to the traditional accounting method:

1) compactness;

2) speed;

3) low labor costs;

4) relevance;

5) centralized data management;

6) data independence.

A database system has four main components: data, hardware, software (specifically a database management system, or DBMS), and users.

Database systems can be single-user or multi-user. A single-user system is a system in which no more than one user can access the database at a time, while a multi-user system is one in which several users can access the database at once.

In general, the data in a database is integrated and shared. The concept of data integration means the ability to imagine a database as a combination of several separate data files, completely or partially eliminating redundancy in information storage. The concept of data sharing refers to the ability to use individual elements stored in a database by several different users.

The system hardware includes the following:

1) Secondary (external) memory volumes used to store information, as well as corresponding input/output devices, device controllers, input/output channels, etc.

2) A hardware processor (or processors) together with main (primary) memory, designed to support the operation of database system software.

Between the actual physical database and the users of the system is a layer of software that can be called variously: a database manager, a database server, or a database management system (DBMS). All user requests to access the database are processed by the DBMS. All available facilities for adding files (or tables), selecting and updating these files or tables are also provided by the DBMS. The main task of a DBMS is to provide the database user with the ability to work with it without going into details at the hardware level.

Users can be divided into three large and somewhat overlapping groups. The first group is application programmers, who are responsible for writing application programs that use the database. Application programmers gain access to the database by issuing a corresponding query to the DBMS. The second group is end users who work with the database system directly through a workstation or terminals. The end user can access the database using one of the interactive applications or an interface integrated into the software of the DBMS itself. The third group is database administrators (DBAs). They are responsible for administering the database and the entire database system according to the requirements set by the data administrator.

Table “Account” Table “Product” Table “Product by account” Table “Product groups” Laboratory work No. 2. Development of queries for data selection and calculations The goal of the work is to acquire skills in describing database queries in the QBE (Query by Example) language. Selection of unpaid invoices Execution result: Selection of deliveries Execution result: Search...

Project 1. Introduction. The purpose of this course project is to structure data and develop a user interface. The course project covers the following theoretical issues and practical tasks: ü a system-complex analysis of the selected automation object was carried out ü the structure of the user interface of the automated system was developed...