State budget professional
educational institution
"College of Automation
and informationIonic Technologies No. 20"
WORKING PROGRAMM
academic discipline _ OP.07 Basics of database design
specialty code/specialty 230401 INFORMATION SYSTEMS (by industry)
skill level: __ base ________
Moscow
2015
APPROVED
at a meeting of the PCC "Library science", "IP (by industry", "OTZI"
Protocol No. _from " » ______ 2015 G.
Chairman
_____________________________/HER. Swedish/
The program of the discipline was developed in accordance with the requirements of the Federal State Educational Standard in the specialty 230401 Information Systems and the curriculum
APPROVE
Head of the educational structural unit "BTM"
_____________________________/T.I. Stenyaeva/
AGREED
Head educational and methodological department
_____________________________ / S.E. Kovalenko/
"_____" ________________________ 20__
Developer (author): ____ Fedotkina M.V., lecturer _______ _________________________________________________
Full name, position, qualification category
Reviewer:
External:______________________________________________
(Full name, place of work, position, qualification category (academic degree, title)
CONTENT
page
PASSPORT OF THE PROGRAM OF THE EDUCATIONAL DISCIPLINE
STRUCTURE and content of the EDUCATIONAL DISCIPLINE
conditions for the implementation of the academic discipline
Monitoring and evaluation of results Mastering the academic discipline
1. passport of the WORKING PROGRAM OF THE EDUCATIONAL DISCIPLINE
"OP.07 Basics of database design"
Scope of the work program
The work program of the discipline is part of the main professional educational program in accordance with the Federal State Educational Standard in the specialty SPO 230401 “Information systems (by industry) (basic level) of the enlarged group of specialties 230000 Informatics and computer technology.
The work program of the discipline can be used in additional vocational education as an optional course for enlarged groups of specialties 230000 Informatics and Computer Engineering, as well as in advanced training courses for the adult unemployed population.
The academic discipline "Fundamentals of database design" is a general professional discipline that forms the basic level of knowledge for the development of special disciplines.
The teaching of the discipline has a practical orientation and is carried out in close connection with other general professional disciplines: "Information Technology", "Operating Systems and Environments", "Computer and Computing Systems Architecture".
1.2. The place of the academic discipline in the structure of the main professional educational program:
The academic discipline belongs to the cycle of professional disciplines to the block of general professional disciplines.
1.3. Goals and objectives of the academic discipline - requirements for the results of mastering the academic discipline:
The study of the discipline "Fundamentals of database design" is aimed at the formation of general competencies (OK 1-10) and PC 1.1, PC 1.2, PC 1.3, PC 1.7, PC 1.9. according to the Federal State Educational Standard, specialty 230401 Information systems (by industry):
OK 1. Understand the essence and social significance of your future profession, show a steady interest in it.
OK 2. Organize their own activities, choose standard methods and methods for performing professional tasks, evaluate their effectiveness and quality.
OK 3. Make decisions in standard and non-standard situations and be responsible for them.
OK 4. Search and use the information necessary for the effective implementation of professional tasks, professional and personal development.
OK 5. Use information and communication technologies in professional activities.
OK 6. Work in a team and team, communicate effectively with colleagues, management, consumers.
OK 7. Take responsibility for the work of team members (subordinates), the result of completing tasks.
OK 8. Independently determine the tasks of professional and personal development, engage in self-education, consciously plan advanced training.
OK 9. Navigate in conditions of frequent change of technologies in professional activity.
OK 10. Perform military duty, including with the application of acquired professional knowledge (for boys).
PC 1.1. Collect data to analyze the use and functioning of the information system, participate in the preparation of reporting documentation, take part in the development of project documentation for the modification of the information system.
PC 1.2. Interact with related specialists in the development of methods, tools and technologies for the use of objects of professional activity
PC 1.3. Modify individual modules of the information system in accordance with the work assignment, document the changes made.
PC 1.7. Perform installation and configuration of the information system within its competence, document the results of the work.
PC 1.9. Follow the regulations for updating, technical support and data recovery of the information system, work with technical documentation.
As a result of mastering the discipline, the student must
be able to:
Design a relational database;
Use a query language to programmatically retrieve information from databases;
know:
Fundamentals of database theory;
data models; features of the relational model and database design, visual tools used in ER modeling;
Fundamentals of relational algebra; database design principles,
Ensuring data consistency and integrity;
Design tools for database structures; SQL query language
1.4. The recommended number of hours for the development of an exemplary program of the academic discipline:
maximum student workload 168 hours, including:
obligatory classroom teaching load of the student 112 hours,
student's independent work 56 hours.
2. STRUCTURE AND EXAMPLE CONTENT OF THE EDUCATIONAL DISCIPLINE
2.1. Volume of academic discipline and types of educational work
Type of study work
Watch Volume
168
Mandatory classroom teaching load (total)
112
including:
laboratory works
48
workshops
10
test papers
-
term paper (project) ( if provided)
-
Independent work of the student (total)
56
including:
independent work on a term paper (project) not provided
-
Preparation of a report on the topic:
- "Information technologies of the future";
- “For which Acces objects you can create reports”;
- "The meaning of new promising directions for the development of DBMS";
Preparing a presentation on:
- "Methods of setting tables in Access";
- "The process of creating a request - a selection";
Preparing a message on the topic:
- "Objects of the software tool (PS) Access and their purpose";
- "Object - Form";
- "DBMS direction - Postgres";
Abstract on the topic:
- "Methods for deleting an attribute in a table";
- "Describe the process of establishing a relationship between two tables in Acces";
Execution of an individual project theme:
- "Timetable of classes";
9
Final certification in the form exam
2.2. Thematic plan and content of the discipline op.07 BASICS OF DATABASE DESIGN
Name
sections and topics
The content of the educational material, laboratory and practical work, independent work
students, term paper (project)
Watch Volume
Level
development
1
2
3
4
Introduction
Introduction to Database Theory
Laboratory works not provided
Workshops not provided
Test papersnot provided
Independent work students not provided
Prepare an essay on the topic: "Connection of the database with other disciplines."
Section 1.
Database. Basic concepts
12
Topic 1.1. Basic concepts and types of data models
Content of educational material
6
Give the concepts of object, entity, parameter, attribute, data model. Consider the composition of the information data model.
3
DBMS and its place in the computer software system.DBMS functions. Data presentation levels.
Dialectical transition from one model to another. Three types of logical models: hierarchical, network and relational. The concept of logical and physical data independence.
Laboratory works not provided
-
-
Workshops not provided
Test papersnot provided
Independent work students
1. Preparation of a report on the topic "Information technologies of the future"
3
Topic 1.2. DBMS architecture
Content of educational material
2
Database architectures (two- and three-tier structures, client-server, file-server).
Laboratory worksnot provided
-
Workshopsnot provided
Test papersnot provided
Independent work students
2. Message on the topic: "Objects of the software tool (PS) Access, their purpose"
1
Section 2. Database Design
114
Topic 2.1. Design concept
Content of educational material
2
Types of data models of an enterprise data warehouse. Ensuring data consistency and integrity. The main stages of database development.
3
Laboratory works not provided
Workshops
Domain analysis.
Designing a conceptual database model.
Formalization of the relational model.
6
Test papersnot provided
-
Independent work students
3. Presentation on the topic: " Ways to set tables in Access»
4
Topic 2.2. data models. Relational data model.
Content of educational material
6
The relationship types in the model are one-to-one, one-to-many, and many-to-many. Relational approach to building a data model. Features of the relational model and their impact on database design.
3
Figurative tools used in ER modeling Converting a many-to-many relationship into a cross-link table.
Basic Relational Algebra Operations
Laboratory works not provided
-
Workshopsnot provided
Backbone-modular principle of building a computer. The internal architecture of the computer; processor, memory. Peripherals: keyboard, monitor, disk drive, mouse, printer, scanner, modem, joystick; multimedia components. The software principle of computer control. Operating system: purpose, composition, loading. Types of programs for computers. The concept of a file, directory (folder) and the rules for setting their names. File name pattern. The path to the file. Entering commands. Installation of programs. Working with directories and files.
-
Test papersnot provided
-
Independent work students
4. Abstract on the topic: "Methods for deleting an attribute in a table."
3
Topic 2.3. Database design
Content of educational material
16
Concept, purpose and principle of construction.
Indexing: the concept of an index, types of index files. Creating, activating, and deleting an index. Reindexing. Sorting, searching and filtering data.
Relationships between tables: establishing and deleting. Key types. Ways to join tables.
Creation of program files. Modularity of programs. Scope of variables.
Menu types. Working with menus: creating, modifying, activating and deleting.
Working with windows: opening and closing a window, getting help.
Creating a screen form: properties, events and methods. Control elements: properties, events and methods.
Formation and output of reports
Laboratory works
1. Creating a database in MS Access. Creating tables.
2. Creation of tables.
3. Import and export data
4. Import and export data
5. Create requests
6. Create requests
7. Create requests
8. Create Forms
9. Create Forms
10. Create forms
11. Reporting
12. Reporting
13. Create reports
14.
15. Creating the main button form
30
Workshops
Database structure design.
Table normalization.
4
Test papersnot provided
-
Independent work students
Implementation of an individual project theme:
- "Organization of the work of the student library";
- "Organization of the work of the university printing house";
- "Organization of educational excursions for schoolchildren";
- "Organization of control over the student's progress";
- "Timetable of classes";
25
Topic 2.4.
Physical organization of data
Content of educational material
6
Storage Environment Mechanisms and DBMS Architecture
Stored data structure
Types of addressing stored records. Organization of links between stored records
Laboratory works not provided
-
Workshopsnot provided
Test papersnot provided
Independent work students
Presentation on the topic: "For which Acces objects you can create reports"
3
Topic 2.5.
Relational database management
Content of educational material
4
Data management is the basis of database administration. Basic concept of data management.
Organization of data management. Database administration.
Laboratory works not provided
-
Workshopsnot provided
Test papersnot provided
Independent work students
Message on the topic: "Object - Form"
2
Section 3
Database languages
14
Topic 3.1
Language SQL
Content of educational material
6
SQL query language
SQL query language commands for changing: creating a database file, creating a table, adding, editing and deleting records.
Data selection query: data selection from one table or several tables, with data sorting and grouping, with record selection condition (filtering).
Laboratory works
16. Creating SQL queries
17. Creating SQL queries
18. SQL queries
19. SQL queries
8
Workshopsnot provided
-
Test papersnot provided
Independent work students
Presentation on the topic: "The process of creating a query - a selection"
7
Section 4. Using the database
30
Topic 4.1.
Ensuring the functioning of databases
Content of educational material
4
Organization of the database management system
Generalized database technology
Laboratory worksnot provided
-
Workshopsnot provided
Test papersnot provided
Independent work students
Abstract on the topic: "Describe the process of establishing a relationship between two tables in Acces"
2
Topic 4.2. New database technologies
Content of educational material
6
Modern information technologies - monitoring of information resources;
Application of case-technologies for designing databases and applications;
Modern information technologies - the dissemination of data with the widespread use of Web - technologies. GIS for data visualization and creation of electronic reference manuals.
Laboratory worksnot provided
-
Workshopsnot provided
Test papersnot provided
Independent work students
Report on the topic: "The meaning of new promising directions for the development of DBMS"
3
Topic 4.3.
Modern DBMS
Content of educational material
4
Multi-platform DBMS. DBMS focused on specific platforms.
DBMS of the XBase, dBase family. Prospects for the development of databases and DBMS
Laboratory worksnot provided
-
Workshopsnot provided
Test papersnot provided
Independent work students
Message on the topic: "The direction of the DBMS - Postgres"
2
Total:
168
3. conditions for the implementation of the academic discipline
3.1. Minimum Logistics Requirements
The implementation of the academic discipline requires the presence of an educational office of informatics, mathematics and informatics.
Study room equipment:
List of main equipment:
network computer class with Internet access;
seats by the number of students;
cabinets for methodical literature;
information stands.
Technical training aids:
interactive whiteboard - Interwrite;
projector Epson ;
computer workplace for the teacher;
Printer-HP Deskjet 1280 ;
Scanner-Epson perfection v200 PHOTO.
Description of equipment in the workplace:
Processor type Intel® Core™ i5-2400
Processor clocked at 3.10Ghz ;
RAM 4.0 GB ;
HDD 2 Tb;
Acoustic system –Genius;
operating system - Windows 7x 32;
antivirus program -Microsoft Security Essentials;
Program archiver-Winrar;
office software: word processor, spreadsheet, program for creating multimedia presentations - Microsoft office 2007;
database management system - Microsoft office 2007;
integrated software development environment - Microsoft office 2007;
visual design system - Microsoft office 2007.
3.2. Information support of training
Main sources:
Matrosov V.L., Zhdanov S.A., Soboleva M.L. Information systems in structural logical schemes.-M.: MPGU, 2014.-105p.
Fufaev E.V., Fufaev D.E. Databases-M.: "Academy", 2011-320s.
Additional sources:
Matrosov V.L., Zhdanov S.A., Ivanova N.Yu., Manyakhina V.G., Kostin A.N. Informatics-M.: "Academy", 2012-336s.
4. Monitoring and evaluation of the results of mastering the EDUCATIONAL Discipline
Control and evaluation the results of mastering the academic discipline is carried out by the teacher in the process of laboratory work, testing, as well as the performance of individual tasks by students, term paper.
Learning Outcomes
(learned skills, acquired knowledge)
Forms and methods of monitoring and evaluating learning outcomes
1
2
Skills:
design a relational database;
use the SQL language to programmatically extract information from databases.
Combined: laboratory workshop, abstracts (reports), laboratory workshop reports.
Knowledge:
fundamentals of database theory;
Group: abstracts (reports)
data models;
features of the relational model and their impact on database design,
Group: abstracts (reports).
visual tools used in ER modeling;
Group: abstracts (reports).
fundamentals of relational algebra;
Group: abstracts (reports).
database design principles,
Group: abstracts (reports).
ensuring the consistency and integrity of data;
Group: abstracts (reports
database structure design tools;
Group: abstracts (reports
SQL query language
Group: abstracts (reports).
The essence of database design (DB), as well as any other design process, is to create a description of a new system that did not previously exist in this form, which, when implemented, is capable of supposedly functioning under appropriate conditions. It follows from this that the stages of database design should consistently and logically reflect the essence of this process.
Content of database design and stages
The design idea 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 result of the design. Therefore, the database design process begins by examining a given need from the point of view of the users and the functional environment of its intended location. That is, the first stage is the collection of information and the definition of a model of the subject area of the system, as well as a look at it from the point of view of the target audience. In general, to determine the requirements for the system, the scope of actions is determined, as well as the boundaries of the database applications.
Further, the designer, who already has certain ideas about what he needs to create, clarifies the tasks supposedly solved by the application, forms a list of them (especially if the design development has a large and complex database), clarifies the sequence of solving problems and analyzes the data. Such a process is also a phased design work, but usually in the design structure these steps are absorbed by the conceptual design stage - the stage of selecting objects, attributes, relationships.
The creation of a conceptual (information model) involves the preliminary formation of the conceptual requirements of users, including requirements for applications that may not be immediately implemented, but the consideration of which will improve the functionality of the system in the future. Dealing with representations of object-abstractions of the set (without specifying the methods of physical storage) and their relationships, the conceptual model corresponds to the content of the domain model. Therefore, in the literature, the first stage of database design is called infological design.
Next, a separate stage (or addition to the previous one) is followed by the stage of formation of requirements for the operating environment, where the requirements for computing resources that can ensure the functioning of the system are evaluated. Accordingly, the larger the size of the database being designed, the higher the user activity and the intensity of requests, the higher the requirements for resources: for the computer configuration, for the type and version of the operating system. For example, the multi-user operation of a future database requires a network connection using an operating system that is appropriate for multitasking.
The next step the designer must choose a database management system (DBMS), as well as software tools. After that, the conceptual model must be transferred to a data model compatible with the selected control system. But often this is associated with the introduction of amendments and changes to the conceptual model, since not always the relationships of objects to each other, reflected by the conceptual model, can be implemented using the means of this DBMS.
This circumstance determines the emergence of the next stage - the emergence of a specific DBMS-supported conceptual model. This step corresponds to the stage of logical design (creation of a logical model).
Finally, the final stage of database design is the physical design - the stage of linking the logical structure and the physical storage environment.
Thus, the main stages of design in a detailed form are represented by the stages:
- infological design,
- formation of requirements for the operating environment
- choice 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 is the semantic basis of infological design. An entity here is such an object (abstract or concrete), information about which will be accumulated in the system. In the infological model of the subject area, in user-friendly terms that do not depend on the specific implementation of the database, the structure and dynamic properties of the subject area are described. But the terms are taken on a typical scale. That is, the description is expressed not through individual objects of the subject area and their relationships, but through:
- description of object types,
- the integrity constraints associated with the described type,
- processes leading to the evolution of the subject area - its transition to another state.
An infological model can be created using several methods and approaches:
- The functional approach is based on the tasks set. It is called functional because it is used if the functions and tasks of persons who, with the help of the designed database, will serve their information needs, are known.
- The subject approach focuses on information about the information that will be contained in the database, while the structure of queries may not be determined. In this case, in the research of the subject area, they are guided by its most adequate display in the database in the context of the full range of supposed information requests.
- An integrated approach based on the "entity-relationship" method combines the advantages of the two previous ones. The method is reduced to dividing the entire subject area into local parts, which are modeled separately, and then re-combined into a single area.
Since the use of the entity-relationship method is a combined design method at this stage, it becomes a priority more often than others.
Local representations in case of methodical separation should, if possible, include information that would be enough to solve a separate problem or to meet the needs of some group of potential users. Each of these areas contains about 6-7 entities and corresponds to any 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, while 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 "an entity instance" (an object characterized by specific property values) and the concept of "entity type" - an object that is 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 derivatives (calculated based on the values of other attributes);
- simple (indivisible one-component) or composite (combined from several components).
After that, the specification of the attribute, the specification of the links in the local representation (divided into optional and mandatory), and the union of local representations are performed. With up to 4-5 local regions, they can be combined in one step. In the case of an increase in the number, 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 agree on integrity attributes on the same attributes in different applications).
Choice of 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 parameters:
- type of data model and its compliance with the needs of the subject area,
- margin of opportunity in case of expansion of the information system,
- performance characteristics of the selected system,
- operational reliability and convenience of the DBMS,
- tooling geared towards data administration personnel,
- the cost of the DBMS itself and additional software.
Mistakes in the choice of DBMS will almost certainly provoke the need to adjust the conceptual and logical models later.
Logical database design
The logical structure of the database must correspond to the logical model of the subject area and take into account the relationship of the data model with the supported DBMS. Therefore, the stage begins with the choice of a data model, where it is important to take into account its simplicity and clarity.
Preferably, the natural data structure matches the model that represents 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 more often determined by the database management system, and not by the data model. Therefore, the conceptual model is actually translated into a data model that is compatible with the selected database management system.
Here, too, the nature of design is reflected, which allows for the possibility (or need) to return to the conceptual model to change it if the relationships between objects (or attributes of objects) reflected there cannot be implemented using the means of the selected 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 generated using one of two divergent approaches:
- or using a bottom-up approach, when working from the lower levels of attribute definition, grouped into relationships that represent objects, based on the relationships existing between attributes;
- or using the reverse, top-down approach, applied with a significant (up to hundreds and thousands) increase in the number of attributes.
The second approach involves the definition of a number of high-level entities and their relationships, followed by detailing to the desired level, which reflects, for example, a model created on the basis of the "entity-relationship" method. In practice, however, 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 such a physical storage medium 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 is associated with the solution of contradictory problems in many respects:
- data storage space minimization problems,
- objectives to achieve integrity, security and maximum performance.
The second task conflicts with the first because, for example:
- for the efficient functioning of transactions, you need to reserve disk space for temporary objects,
- to increase the 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 the data, the database will be backed up 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 tasks are optimally solved by competently placing data in memory space, but not at the expense of database protection, which includes both protection from unauthorized access and protection from failures.
To complete the creation of a physical model, its operational characteristics are evaluated (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 out of the scope of the direct database design.
Creating a database begins with design.
Database design stages:
Domain research;
Data analysis (entities and their attributes);
Defining relationships between entities and defining primary and secondary (foreign) keys.
During the design process, the structure of the relational database is determined (composition of tables, their structure and logical connections). The structure of a table is determined by the composition of the columns, the data type and sizes of the columns, and the keys of the table.
The basic concepts of the DB model "entity-relationship" include: entities, relationships between them and their attributes (properties).
Essence- any concrete or abstract object in the subject area under consideration. Entities are the basic types of information that are stored in the database (in a relational database, each entity is assigned a table). Entities can include: students, clients, departments, etc. Entity instance and entity type are different concepts. The concept of an entity type refers to a set of homogeneous persons, objects or events acting as a whole (for example, a student, a client, etc.). An entity instance refers, for example, to a particular person in a set. An entity type can be a student, and an instance can be Petrov, Sidorov, etc.
Attribute is a property of an entity in the subject area. Its name must be unique for a particular entity type. For example, for the student entity, the following attributes can be used: last name, first name, patronymic, date and place of birth, passport data, etc. In a relational database, attributes are stored in table fields.
Connection– the relationship between entities in the subject area. Relationships are connections between parts of the database (in a relational database, this is a connection between table records).
Entities are data that are classified by type, and relationships show how these data types relate to each other. If we describe a certain subject area in terms of an entity-relationship, then we get an entity-relationship model for this database.
Let's consider the subject area: Dean's office (Student achievement)
The database "Dean's Office" should store data on students, groups of students, student grades in various disciplines, teachers, scholarships, etc. We confine ourselves to data on students, groups of students, and student grades in various disciplines. Let's define entities, entity attributes and basic requirements for database functions with limited data.
The main subject-significant entities of the database "Dean's office" are: Students, Groups of students, Disciplines, Progress.
The main subject-significant attributes of entities:
students - last name, first name, patronymic, gender, date and place of birth, a group of students;
groups of students - name, course, semester;
disciplines - name, number of hours
academic performance - assessment, type of control.
Basic requirements for database functions:
select the student's progress in disciplines with an indication of the total number of hours and the type of control;
select students' progress by groups and disciplines;
choose disciplines studied by a group of students in a particular course or a particular semester.
From the analysis of the subject area data, it follows that each entity must be assigned the simplest two-dimensional table (relationships). Next, you need to establish logical relationships between tables. Between the tables Students and Progress, it is necessary to establish such a relationship so that each record from the Students table corresponds to several records in the Progress table, i.e. one-to-many, as each student can have multiple grades.
The logical relationship between the entities of the Group - Students is defined as one-to-many based on the fact that there are many students in the group, and each student is part of one group. The logical relationship between the entities Disciplines - Progress is defined as one-to-many, because for each discipline, several grades can be given to different students.
The arrow is a symbol of the connection: one - to - many.
7.1. Database Design FundamentalsThe developed functional model of the system answers the questions "What should the system do?" and “Through what actions can the desired result be achieved?”. This model also allows you to conceptually define the datasets used in the system.
At the same time, it does not answer the question "How is the data organized in the system?". To answer it, it is necessary to build an information model (to design a database).
Entity (table, in RDB - relation) - a set (class) of the same type of real or imaginary objects that are essential for the subject area under consideration, information about which is to be stored. Examples of entities: worker, part, statement, exam results, etc.
Entity instance (record, string, in RDB - tuple) is a uniquely identifiable object.
Connection - some association between two entities that is significant for the subject area under consideration. Examples of connections can be kinship relations "father-son", industrial - "boss-subordinate" or arbitrary - "own", "possess property".
Attribute (column, field) - a property of an entity or relationship.
Most modern data modeling tends to support several graphical notations for building information models. In particular, the ERwin system from Computer Associates supports two notations: and (English Information Engineering - information design). These notations are one-to-one, i.e. the transition from one notation to another and back is performed without loss of model quality. The difference between them is only in the form of displaying model elements.
When using any, a logical database diagram is first built in the form of a diagram indicating the entities and the relationships between them. Logic diagram is called a universal description of the data structure, independent of the final implementation of the database and hardware platform. Based on the received logical scheme, the physical data scheme is transferred. Physical scheme is a diagram containing all the necessary information to generate a database for a specific DBMS or even a specific DBMS version. If in the logical schema it does not matter what identifiers the tables and attributes have, the data type of the attributes, etc., then in the physical schema there should be a complete description of the database in accordance with the syntax adopted in it, indicating the types of attributes, stored procedures, etc. .d. Several physical ones can be created according to the same logical scheme. For example, ERwin v9.2 allows, on the basis of a logical scheme, to form physical ones for more than 10 industrial DBMSs (ORACLE, MySQL, DB2, MS SQL Server, etc.) and their various versions. Based on the physical schema, you can generate either the database itself or a DDL script 1 , which, in turn, can be used to generate the database.
The above procedure is called Forward Engineering DB . also allow you to reverse engineering DB (Reverse Engineering DB) , i.e. based on the database system catalog or DDL script, build a physical and, further, a logical data schema.
In addition to the forward and reverse engineering modes, CASE tools usually support synchronization between the schema and the database system catalog, i.e. when changing the schema, they can automatically make all the necessary changes to the existing database and vice versa.
Advanced CASE tools also have a built-in subsystem for finding and correcting errors in the scheme . This feature is especially useful when designing large databases containing dozens or hundreds of tables, as well as in reverse engineering.
It should be noted that modern DBMS have their own built-in tools for visual data modeling. Some of them even support classic ERD notations. The disadvantages of such modeling are the construction of only a physical data schema and the impossibility of a quick transition to another DBMS, if such a decision is made. The advantage of this approach is a more complete use of the potential of the DBMS, because the developers of the DBMS know its features and capabilities better than others.
The following describes the direct design procedure using the IDEF1X methodology. The IDEF1 methodology was developed by T. Ramey. At present, on the basis of IDEF1, its new version has been created - the IDEF1X methodology, which in 1981 was adopted by ICAM as a US federal standard.
1 Data Definition Language - data definition language, a subset of the SQL language.
Organization and maintenance of databases using MS ACCESS DBMS
Before creating a database, it is necessary to have a description of the selected subject area, which should cover real objects and processes, have all the necessary information to meet the expected user requests and determine the needs for data processing.
Based on this description, at the database design stage, the composition and structure of the data that must be in the database and ensure the execution of the necessary queries and the solution of user tasks is determined.
The process of designing and creating a relational database consists of the following steps:
1) creation of an information-logical model of the subject area, i.e. selection of information objects and determination of links between them;
2) building a logical structure of a relational database, where each object of the infological model is displayed by a relational table, and the links between the tables correspond to the identified information links between the objects;
3) constructing tables corresponding to the information objects of the constructed data model;
4) creation of a data schema, which fixes the existing logical relationships between tables;
5) input of data contained in the documents of the subject area.
Particular attention should be paid to the first two stages, since without their careful study it is impossible to create a database that fully meets the needs of the user.
Building an infological data model. Infological Model (ILM) displays data of the subject area in the form of a set of information objects and links between them.
Information object is an informational description of some real object, process or event. An information object is formed by a set of logically interconnected attributes that represent the qualitative and quantitative characteristics of a certain entity of the subject area. For example, the GOODS object is characterized by such details as name, unit of measurement, manufacturer, grade, price, etc.
Each information object is assigned a unique name. For example, when describing a subject area goods supply objects such as GOODS, SUPPLIER will be selected.
An information object has many implementations - instances (records). For example, each instance of the PRODUCT object represents a specific product. An instance is formed by a set of specific attribute values and must be uniquely identified by the value of the information object key. The key can be one ( simple) or several key props ( composite).
When designing a relational database, it is necessary to decide on the most efficient data structure. In doing so, the following objectives are pursued:
Provide quick access to data in tables.
Eliminate unnecessary repetition of data, which can cause input errors and waste of computer disk space.
Ensure data integrity in such a way that when some objects are changed, a corresponding change in related objects automatically occurs.
The next step at the ILM design stage, after identifying information objects, is to determine the relationship between them.
Attitude is a relationship between two tables that shows how the data in those tables relate to each other. When creating a relationship, the same fields are specified in two different tables. For example, you can create a relationship between the PRODUCT and SUPPLIER tables using the product ID as a linking field.
ACCESS supports the following types of relationships between tables:
One - one-digit (1:1),
One - many-valued (1: M),
Many - many-valued (N: M).
One-to-one relationships (1:1) take place when each instance of one object (A) corresponds to only one instance of another object (B) and, conversely, each instance of the object (B) corresponds to only one instance of the object (A).
One-to-many relationships (1:M)- these are such relationships when each instance of one object (A) can correspond to several instances of the object (B), and each instance of the object (B) can correspond to only one instance of the object (A). In this connection, object A is the main object, and object B is the subordinate object.
Many - many-valued (N: M) - take place if each instance of object A can correspond to several instances of object B and, conversely, each instance of object B can correspond to several instances of object A. IDs of objects A and B.
In ILM, objects are placed by levels. At the zero level, objects are placed that are not subordinate to other objects. The level of other objects is determined by the longest path to the object from the zero level. This placement of objects gives an idea of their hierarchical subordination, makes the model more visual and makes it easier to understand the relationships between objects.
Building a logical database model. The logical structure of the database is an adequate reflection of the resulting infological model. Each data model information object is mapped to a corresponding relational table. The structure of the table is determined by the attribute composition of the object, where each column corresponds to one attribute. Table rows correspond to object instances and are formed when the table is loaded.
Relationships between data model objects are implemented by the same requisites − communication keys in the respective tables. In this case, the link key must always be the identifier of the main object.
Loading...
