Database Specialist (Excel, Access). Subd courses Visual design of the project

From the author: they called you teapot? Well, this is fixable! Every samovar was once a teapot! Or was every professional once a samovar? No, something is wrong again! In general, MySQL for beginners.

Why dummies need MySQL

If you are seriously going to connect your life with the Internet, then immediately at the very first steps in the "web" you will encounter this DBMS. MySQL can be safely called the "whole internet" database management system. Not a single more or less serious resource can do without it, it is present in the admin panel of each hosting. And most of all popular CMS and even "self-made" engines are built with her participation.

In general, you can not do without this platform. But to study it, you will also need the right approach, the right tools, and most importantly, desire and patience. I hope you have enough of the last components. And be prepared for the fact that your brains will boil, and steam will pour out of your head, like from a real kettle

But MySQL is so hard for dummies only if you start learning it wrong. We will not make such a mistake, and we will begin our acquaintance with this technology from the very beginning.

Basic concepts

First, let's go through the basic concepts that we will mention in this publication:

Database (DB) is the main constituent unit of the DBMS. The database includes tables, which consist of columns and records (rows). The cells formed at the intersection contain structured data of a certain type.

DBMS (database management system) - a set of all software modules for database administration.

SQL is a structured query language with which the developer "communicates" with the core (server) of the DBMS. Like any programming language, SQL has its own syntax, set of commands and operators, supported data types.

I think that theoretical knowledge is enough for us to start. We will “paint” the missing gaps in theory with practice. Now it remains to choose the right software tool.

Choosing the Right Tool

Pretty "digging" in the entire range of MySQL shells for beginners, I realized that these simply do not exist. Everything software products for administering a DBMS, you need to have already installed server DB. In general, I decided once again not to invent a “scooter”, and opted for the domestic Denwer package. You can download it on the official website.

It already includes all the components of the DBMS, allowing a beginner to begin practical acquaintance with MySQL immediately after a simple and understandable installation. In addition, Denwer includes several more tools necessary for a novice developer: a local server, PHP.

First steps

I will not describe the installation process of the "gentleman's" set, since everything happens automatically there. After starting the installation, only have time to press the necessary keys. Just what you need in the MySQL variant for dummies.

When the installation process is over, start the local server, wait a couple of seconds. Then type in address bar localhost browser.

On the page "Hurrah, it worked!" follow one of the links in the picture. After that, you will be taken to phpMyAdmin - a shell for administering databases.

By clicking on the link http://downloads.mysql.com/docs/world.sql.zip, you will download an example of a test database from the official MySQL website. Again, go to phpMyAdmin, in the main menu at the top, go to the "Import" tab. In the "Import to Current" window, in the first section ("File to import"), set the value to "Overview of your computer".

In the explorer window, select the archive with the downloaded sample database. Don't forget to click OK at the bottom of the main window.

I advise you not to change the specified parameter values ​​yet. This may lead to incorrect display of the imported source data. If the phpMyAdmin system gave an error that it cannot recognize the database compression algorithm, then unzip it and repeat the entire import process from the beginning.

If everything went well, then a program message will appear at the top that the import was successful, and on the left in the list of databases there is another one (word).

Let's look at its structure from the inside so that you can more clearly imagine what you will have to deal with.

Click on the name of the MySQL database for beginners. Under it, a list of tables of which it consists will be displayed. Click on one of them. Then go to the top menu item "Structure". The main work area displays the structure of the table: all column names, data types, and all attributes.

Today it is impossible to imagine the work of the largest companies, banks or government organizations without the use of databases and tools. Databases allow us to store and access large amounts of information, while a database management system (DBMS) allows us to manage the available information repositories. To date, the largest manufacturers of DBMS are multinational corporations such as Microsoft and Oracle.

Tools are used for business analysis and modeling various processes. They allow you to deal with redundant information, focusing users on key performance factors, modeling the outcome various options actions, tracking the results of making certain decisions.

You can learn about databases and business intelligence tools at coursesBI() Center "Specialist" at MSTU. N.E. Bauman - presenter Microsoft Authorized Training Center in Russia.

Also in the Center "Specialist" are held courses inSQL Server , which describe fundamentals relational databases data, starting with the main objects, access to which is provided using the SQL language. In progress learningSQL Server discusses access strategies and data retrieval, modifying data with instructions; tables, types, and declarative data integrity; complex queries, programming in Microsoft SQL Server in T-SQL custom stored procedures, functions, triggers and views; query performance enhancement techniques, extension functionality Microsoft SQL Server by XML usage, SQLCLR, and Filestream, as well as using spatial types, full-text search, and Service Broker. Classes are taught by Microsoft certified trainers. Prestigious international certificates, state training programs, the largest Prometric Testing Center in Russia are waiting for you!

In addition, the Specialist Center conducts courses onOracle, which clearly and comprehensively disclose the main features of the Oracle DBMS architecture, which fundamentally distinguish it from others popular systems database management. And on courses onOracle exactly those possibilities, means and features are described in detail and illustrated with many examples. Oracle that provide development effective applications for this DBMS and its successful operation.

Also, the Center "Specialist" conducts courses on MySQL. With us you can go from the level of a novice developer to the level of an expert developer MySQL DB.

Benefits of learning to work with databases at the Specialist Center:

• Center "Specialist" - the best training center Microsoft in Russia, Eastern and Central Europe!
• Center "Specialist" has the highest partner status Microsoft Gold Learning in the field of training, as well as competence Silver Data Platform, confirming the highest qualification of the teachers of the Center in the field of administration, development and business intelligence based on SQL Server 2012.
• Center "Specialist" has a prestigious status Oracle Academy.
• We have the best teachers. Bauman traditions of quality .
• The highest level training of administrators, developers of databases and analytical solutions based on SQL Server. Over 80% administrators and developers of DBMS, as well as business intelligence (BI) specialists in Russia, completed the courses of the Specialist Center.
• Prestigious

Start dates

Access and Excel Database Course Program

excel

1. The main elements of the interface of the Excel program

• 1.1. Main menu, toolbars, workspace
• 1.2. Cell name bar, formula bar, scrollbars
• 1.3. System status bar
• 1.4. Entering and editing data
• 1.5. Inserting and deleting cells, rows and columns

2. Visual design of the project

• 2.1. Cell content formatting, font options.
• 2.2. Different styles of line drawing and table design
• 2.3. Ways to align text in cells
• 2.4. Built-in formats excel data
• 2.5. Tools for creating and designing projects

3. Data entry forms in Excel tables.

• 3.1. Create a database in Excel
• 3.2. Controlling the Data Form Window
• 3.3. Changing an entry
• 3.4. Input new entry
• 3.5. Deleting entries
• 3.6. Recording Search
• 3.7. List sorting

4. Formulas and functions.

• 4.1. The concept of formulas and functions in Excel.
• 4.2. Standard Features Excel.
• 4.3. Moving and copying formulas in cells.
• 4.4. Fixed and floating cell names in spreadsheet formulas.
• 4.5. Function Wizard.
• 4.6. User-defined names.
• 4.7. A practical example of creating a complete project for calculating the income of shareholders.

5. Charts and graphs.

• 5.1. Diagram Wizard.
• 5.2. Chart design. Auto-format charts.
• 5.3. Editing a chart.
• 5.5. Creation, editing, grouping and binding to sheets Excel graphic objects

6. Macros in Excel

• 6.1. Creating a Macro with the Auto Recorder
• 6.2. Editing a Macro Using the Visual Basic Editor

7. Pivot tables.

• 7.1. PivotTable Wizard.
• 7.2. Consolidation of data.
• 7.3. Methods for changing the structure of a spreadsheet sheet.

1. Access tables

• 1.1. Table structure
• 1.2. Creating tables. General information about field types
• 1.3. Data entry, editing, highlighting, copying and pasting in a table
• 1.4. Long text fields and memo fields. Input area.
• 1.5. OLE object field.
• 1.6. Hyperlink field.
• 1.7. Field order. Counter field. Field labels.
• 1.8. Numeric fields.
• 1.9. Input masks and value condition.

2. Learn more about Access tables

• 2.1. Search operations
• 2.2. Search and Replace
• 2.3. Deleting data
• 2.4. Other data formats. Import and join tables.
• 2.5. Import and Attach Excel sheets and text files.
• 2.6. Working with a large table image.
• 2.7. Applying filters.
• 2.9. Keys.

• 3.1. General representations. Query constructor.
• 3.2. Writing selection criteria.
• 3.3. Create calculated fields. Expression builder.
• 3.4. Select query with group operations.
• 3.5. Request to create a table.
• 3.6. Update request.
• 3.7. Add request.
• 3.8. Removal request. Cross query

4. SQL queries

• 4.1. Entering SQL statements
• 4.2. Select Statement
• 4.3. Statistical functions Count, First, Last
• 4.4. Statistical functions Min, Max, Avg
• 4.5. Sum function
• 4.6. Add request
• 4.7. Update Request
• 4.8. Request for deletion
• 4.9. Query to create a table

5. Fundamentals of multi-table databases

• 5.1. Splitting data into separate tables. Link fields.
• 5.2. Understanding Database Normalization
• 5.3. Multi-table select query. Data schema. Subtables.
• 5.4. Working with sub-tables. Data integrity issues.
• 5.5. Data integrity support. Merge options.
• 5.6. Data integrity support. Substitution.
• 5.7. Practical examples of substitutions.
• 5.8. Other types of relationships between tables.

6. Shapes

• 6.1. Form Wizard
• 6.2. Forms. Autoforms. Basic elements of the Form Builder.
• 6.3. Actions in the form designer.
• 6.4. Form areas. Entering labels. Formatting fields and labels.
• 6.5. Entering free, calculated fields.
• 6.6. Links to form elements. Form modes. Form properties.
• 6.7. Related forms. Create a main and subform.
• 6.8. Methods for linking forms.

7. Reports

• 7.1. Printing tables and forms. Save the form as a report.
• 7.2. Report constructor. Report zones. Tabular report.
• 7.3. Changing the source of records, numbering of records and summary functions.
• 7.4. Sorting and grouping the report.
• 7.5. Pagination. Expanded inscriptions.
• 7.6. Letter report. address labels.
• 7.7. Selecting the output mode and data for the report from the form.
• 7.8. Report printing options. Snapshots of reports.
• 7.9. Export reports to various formats

The course introduces students to the basic principles of working with structured data in relational model, teaches how to design data, describe database objects in terms of a real DBMS, write SQL queries, use views, procedures, functions and triggers, create indexes, manage concurrent access to data and manipulate the transaction mechanism

About the course

The course is based on the study and application of the SQL language for creating, modifying database objects and managing data in an arbitrary relational database.
The implementation of practical tasks within the framework of the course involves the use of the My SQL DBMS.

The course covers the design stages of relational databases, the rules for compiling queries, the main methods of indexing data. The course will explore the use of transactions and data access rights.

The course also provides an overview of current trends in data science in connection with the advent of BigData. At the end of the course, the scope of NoSQL databases will be shown and modern approaches to big data processing will be indicated.

Format

The form of education is part-time (distance). Weekly classes will include watching thematic video lectures, reading an electronic abstract and performing test tasks with automated verification of results, testing on the material covered. To obtain a certificate, you must complete all tasks, tests and write the final exam.

Requirements

For the successful development of the course, knowledge of the basics of programming is required, basic concepts set theory, fundamentals of linear algebra and fundamentals of mathematical statistics. The course is designed for undergraduate students. The course can be useful not only for students majoring in Computer and Information Sciences, but also for other professionals who need to manipulate large amounts of data.

Course program

Week 1. Introductory lecture, which tells about the main content of the course, about the sections of the course, about the place of databases for various information systems.
Week 2. Database design. Model "Entity-Relationship".
Week 3. Relational algebra. Normalization of relational relations.
Week 4. Basic database objects and their description in SQL.
Week 5. SQL queries.
Week 6. Views, procedures, functions, triggers.
Week 7. Data indexing.
Week 8. Optimization of query execution.
Week 9. Managing contention.
Week 10. Directions and trends in the development of databases. Modern approaches to Big Data processing.

Learning Outcomes

The results of the course will be the ability of students to master the terminology and basic objects of databases, SQL fundamentals, the ability to design data and create database objects in terms of a relational model, write queries in SQL, create indexes to speed up query execution, construct views, functions, procedures and triggers, manage database access for many users, use transaction mechanisms, and also formation of the ability to apply database methods in various fields.

Formed competencies

At the end of the course, users will know:

1. Principles of description of the conceptual infological (infological) data model
2. Principles of data design in terms of the relational model
3. Basic operators of the SQL language
4. Modern tendencies in the development of data science
5. Main types of NoSQL databases
6. Main methods of data analysis

At the end of the course, users will be able to:

1. Describe data structures in terms conceptual model"Entity-relationship"
2. Create database objects
3. Describe integrity constraints on data
4. Build additional structures to speed up the search for data
5. Write queries in SQL
6. Create views, functions, procedures and triggers
7. Create new users and give them access to the database
8. Use the transaction mechanism for concurrent access to data

At the end of the course, users will understand:

1. Basic principles of data design
2. How indexes work
3. How the query optimizer works
4. How the transaction manager works
5. What are the challenges in processing Big Data