PRACTICAL EXERCISES FOR THE COURSE OF

INFORMATICS

Dr. Várallyai László associate professor

University of Debrecen

Faculty of Agricultural and Food Sciences, and Environmental Management

1

A Debreceni Egyetem fejlesztése a felsőfokú oktatás minőségének és hozzáférhetőségének együttes javítása érdekébenEFOP-3.4.3-16-2016-00021

Content1. Introduction..........................................................................................................................3

2. Datatypes in Excel................................................................................................................4

3. Formulas and cell references...............................................................................................6

4. Creating diagram.................................................................................................................8

5. Overview of the Excel functions.........................................................................................10

6. Frequently used functions in Excel....................................................................................13

7. Excel lists (sort, filter and pivot)........................................................................................16

8. Database and data models.................................................................................................20

9. Database objects and entity relationships.........................................................................23

10. Database normalisation.....................................................................................................26

11. Database in practice (manage tables)...............................................................................29

12. Database in practice (creating queries).............................................................................32

13. Database in practice (creating forms)...............................................................................35

14. Database in practice (creating reports)...............................................................................37

2

1. Introduction

Our goal is for Food Engineering students to learn the Information Technology (IT) knowledge and computing skills and functions for calculating data, and then drawing diagrams, statements, including sorting and filtering operations in order to acquire training program competencies. This course is designed to give students an overview of the advanced tools of Microsoft Office applications, focusing on the Microsoft Excel and Access. The course requires the students to use the spreadsheet and database applications to produce advanced spreadsheet outputs. The students shall be able to use functions such as those associated with logical, statistical, financial and mathematical operations; create charts and apply advanced chart formatting features; embed functions; create array formulas and enhance productivity by working with named cell ranges.An important goal is to know the basic concepts of database management, to know each data model and modelling technique, and to create and use certain database objects (table, query, form, and report). There are several topics that build students' existing tables, charts, and database knowledge. The students will need the knowledge of Information technology when creating diploma thesis. That is why it would be difficult to place IT in the illustration of the subject-level relationship as it relates to almost every professional subject at some level.The course is usually recommended for electronic textbooks, but here is a list.

https://elearning.unideb.hu/course/view.php?id=1520https://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.tferi.hu/adatbaziskezeles-1/adatbaziskezeles-2https://support.office.com/hu-hu/article/access-2013-as-videók-és-oktatóanyagok-a4bd10ea-d5f4-40c5-8b37-d254561f8bcehttps://informatika.gtportal.eu/index.php?f0=adatbazishttp://www.functionx.com/access/index.htmhttp://www.gcflearnfree.org/topics/office2013; http://www.exceluser.com/explore/arrays1.htmhttp://www.techonthenet.com/excel/index.php.http://office.microsoft.com/en-us/excel-help/CH006252819.aspx?CTT=97 http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.html http://www.gcflearnfree.org/access2013

3

2. Datatypes in Excel

Purpose of the exercise: The purpose of this exercise to introduce students the data types that can be used in Excel.Required knowledge: data types generally, text or character, number or numeric, logical, date, error value, only hash mark characters in the cell.

The type of data you can enter determines what action you can take with it for future use. Data type can be:

• logical,• number or numeric,• date,• text or character.

If Excel cannot interpret the data as a number, it will look at whether it can be interpreted as a date, if it is, as a date, and if it does not consider the cell as a text data, except for TRUE, FALSE.TRUE and FALSE are used to enter logical values in Excel, and by default they are displayed in the cell in uppercase and horizontally aligned to centre.By default, number type (numeric) data is aligned to the right of the cell.When entering dates, be sure to use date separators or dashes that match the current Windows location settings (for example 25-08-2018). In the case of hyphens, the date appears in the cell according to the locale settings. The dates are handled in the same way as the numbers. 01-01-1900 is equal to 1.Text operations can be performed with text data (merging, cutting, etc.). If you start with ‘ – sign entering a data into a cell, the text will be text regardless of the data content you enter.A special type of data is the error value. The error values are displayed in the cell containing the formula is the result of a formula, a text error message starting with a hash sign #. For example, the error value for zeroing is the #DIVISION BY ZERO!If the full cell contains only (hash mark) #-sign, it is no error value indicates only that the data does not fit in the cell (the column is not enough wide), please increase the wide of the column.

4

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=UoUYmrcYMeU&list=PL6akx_FTMljM7IsuL6y55vDpFduBuPBxF&index=2

5

3. Formulas and cell references

Purpose of the exercise: The purpose of this exercise to learn creating formulas and using different cell references in Excel.Required knowledge: formula, left to right rule, bracket, cell references, relative reference, absolute reference, mixed reference.

In addition to entering data in Excel you can perform a variety of calculations formulas entered into the cells. In fact, the formula describes a sequence of actions that have rules for design. The formula = 6 * A1 ^ 2 calculates the surface of the cube where the data in cell A1 contains the edge of the cube, for example, in centimetres.Let's start with the formula = Equal sign and then can continue with

• cell references,• operators or, in other words, operational signals (+, -, *, /, ^),• constants,• functions,• and brackets.

The formula entry is validated by a single value formula with "Enter"If the formula does not result a single data, it is an array formula. In this case, you need to select multiple cells for the result before entering the formula and close the formula with F2 and Ctrl-Shift-Enter so that the mouse cursor points to the formula bar. Unable to modify array formula. In case of an error, you need to delete the entire range.When calculating the results of formulas, Excel takes into account rules known from mathematics. Such is the rule from left to right, the rule of priority of the operation, and the rule of bracketing. If a formula does not contain parentheses, the actions are performed from left to right.One of the most important parts of the Excel formula is cell reference. By reference, specify which cell value is used in the calculation. Each cell can be referred by its column letter and line number. If you change the content of a cell that you refer to in a formula, Excel automatically recalculates the value of the formula.You can also use the mouse to select cell references in the formulas instead of typing, this is called pointing.Pointing is particularly useful when the referenced cell is in another worksheet or other file. In this case, the unambiguous identification requires the name of the file and the name of the worksheet in addition to the cell reference. Eg = [Excel_Advanced_English.xlsx] basic! A1. Often, the same calculation should be done in rows or columns of a table. In this case, the formulas are duplicated instead of retyping.However, in order to effectively apply the copy of formulas, we need to know the three reference types to specify cell coordinates.

• When referring to a cell from a formula with its address, it does not store the address of the cell, but the direction and distance of the cell relative to the formula. Thus, after copying in the formula, the links are modified and the formula is counted with cells at the same distance after copying. This is the relative reference.

• If you need the formula to use the same cell after copying, you use an absolute reference for counting. eg. $F$2. Such a reference does not change when copied. Entering $ symbols is supported by the F4 function key.

6

• It may be necessary to take the formula from a row or column after copying, so that a relative and absolute reference can be used within a reference. The classic example of a mixed reference is the creation of a multiplication table. In column A and row 1 are the numbers to be multiplied, then in B2 the formula is one of the data in column $A2 from the corresponding row, the other data from the first row is taken from the appropriate column B$1 that is, =$A2*B$1; Adding this formula to the table gives the multiplication table. The type of addressing can be changed quickly by pressing the cursor F4 to set the cell reference. Multiple switching effects F4: A1, $A$1, A$1, $A1, A1.

Car sales

Multiplication table

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htm

7

4. Creating diagram

Purpose of the exercise: The purpose of this exercise to learn creating diagrams and using different diagram types and properties in Excel.Required knowledge: chart, chart objects, graphical and non-correlated data lines, chart types (column, bar, circle, line, point).

You can graphically represent and calculate our recorded and calculated data using charts. Excel has several different types of charts, allowing you to choose the one that best fits your data. In order to use charts effectively, you'll need to understand how different charts are used. Types of Charts:

• Column charts use vertical bars to represent data. They can work with many different types of data, but they're most frequently used for comparing information.

• Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time.

• Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it's easy to see which values make up the percentage of a whole.

• Bar charts work just like Column charts, but they use horizontal bars instead of vertical bars.

• Area charts are similar to line charts, except that the areas under the lines are filled in. • Surface charts allow you to display data across a 3D landscape. They work best with large

data sets, allowing you to see a variety of information at the same time. In most cases, charts can be easily created, modified, and formatted. All diagrams consist of objects. Objects can be selected and in this case, our commands always apply to the selected object while editing, modifying, or formatting the chart. Possible objects are as follows:Diagram Area: The entire background of the chart. Drawing Area: The area bounded by the axes.Vertical axis: Used to display the selected values, the exact values can be read with the help of this. The equivalent of the y-axis. If multiple data sets are plotted together and differ in size from one another, they can be represented on a secondary axis.Horizontal axis: Can be a value axis - for some chart types such as Point. In this case, the negative values on the axis lie to the left of the vertical axis, and for the representation of 0, 1, 5, five times as far from 0 as 1. In the case of such an axis, the representation of 0, 1, 5 is as far from 1 as 1 of zero. In addition, there may be a category axis In the case of such an axis, the representation of 0, 1, 5 is as far from 1 as 1 of zero.Grid lines: appear in the drawing area, can be horizontal and vertical. Reading the data is easier.Data Rows: When you select graphically displayed data, the link to the data line appears on the editor and can be changed here.Legend: Contains the name of the data series.Titles: The chart title and axis titles.It is advisable to start creating a diagram by selecting the data to be represented. The easiest way to create a chart is to have the data in a table where the first row and column of the table contain the name of the data, and the other data is the number of the data series. The data of such a table can be represented by row and column. If the chart data lines are plotted per column, then the first text cell of the column will be used by Excel as the name of the data line and displayed in the legend;

8

You may need to assign non-related domains to create a chart. In this case, make sure that all data has a pair, that is, the range of axis subtitles should be the same as the data range, because this is the only way to represent the related points in Excel.After selecting the data, selecting the chart type, the diagram appears, which can be further shaped and coloured after the selection of its objects.A well-chosen chart will help you visualize your data and make reading results easier.You can create mixed charts by combining different chart types. This may be necessary if you want to compare data series with different types of data along the same category and value axis. Examples include Line and Column Chart.

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htm

9

5. Overview of the Excel functions

Purpose of the exercise: The purpose of this exercise to learn the Excel functions.Required knowledge: function, mathematical and trigonometric, date and time, statistics, text, logical, database, embedded functions, function wizard

One of the most powerful features in Excel is the ability to calculate numerical information using formulas.Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers.The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas.Rather than typing cell addresses manually, you can point and click on the cells you wish to include in your formula. This method can save a lot of time and effort when creating formulas.Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet.A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula has more than one mathematical operator, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations.Excel calculates formulas based on the following order of operations:

• Operations enclosed in parentheses • Exponential calculations (3^2, for example) • Multiplication and division, whichever comes first • Addition and subtraction, whichever comes first

In the example below, we will demonstrate how Excel solves a complex formula using the order of operations. Here, we want to calculate the cost of sales tax for an invoice. To do this, we'll write our formula as =(D2+D3)*0.075 in cell D4. This formula will add the prices of our items together and then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.Excel works by grouping functions to find them faster. There is a group called "Recently Used Group" where previously used functions can be searched for later use, and the “All” group where we can search if we do not know the function group.Groups include the following functions:

• Financial,• Date and time• Math and Trigonometric,• Statistics,• Matrices,• Database,

10

• Text,• Logic,• Information.

Functions consist of a list of input data, also called parameters, given in parentheses after the function name and the name of the function. Here, of course, there is an equal sign before the name of the function, because here we count something!After the name, the two parentheses are also required in this case, as this indicates to Excel that it should not search for names but between functions.Some functions return an array of values as a result or require a array of values as their parameters. Such functions are introduced as described in array formula.

11

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=X12oQeJWpEs https://www.youtube.com/watch?v=PU8ACyYxJBkhttps://www.youtube.com/user/learnexcelfunctions

12

6. Frequently used functions in Excel

Purpose of the exercise: The purpose of this exercise to learn the frequently used Excel functions.Required knowledge: function types, statistical function, text function, date function, search function, database function, function parameterization

Excel has a variety of functions available. Here are some of the most common functions you'll use:

• SUM: This function adds all of the values of the cells in the argument. • AVERAGE: This function determines the average of the values included in the argument.

It calculates the sum of the cells and then divides that value by the number of cells in the argument.

• COUNT: This function counts the number of cells with numerical data in the argument. This function is useful for quickly counting items in a cell range.

• MAX: This function determines the highest cell value included in the argument. • MIN: This function determines the lowest cell value included in the argument.

The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX. In our example below, we'll create a function to calculate the total cost for a list of recently ordered items using the SUM function.While there are hundreds of functions in Excel, the ones you use most frequently will depend on the type of data your workbooks contains. There is no need to learn every single function, but exploring some of the different types of functions will be helpful as you create new projects. You can search for functions by category, such as Financial, Logical, Text, Date & Time, and more from the Function Library on the Formulas tab.LEFTFormula: =LEFT (text, [num_chars])The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".RIGHTFormula: =RIGHT (text, [num_chars])The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, RIGHT("apple",3) returns "ple".MIDFormula:=MID (text, start_num, num_chars)The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".LEN Formula: =LEN(A1) The LEN formula counts the number of characters in a cell. This includes spaces!TODAYFormula: =TODAY() Returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations.YEARFormula: =YEAR(serial_number)

13

Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.MONTHFormula: =MONTH(serial_number)Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).DAYFormula: =DAY(serial_number)Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.DATEFormula: = DATE(year,month,day)Returns the sequential serial number that represents a particular date.WEEKDAYFormula: = WEEKDAY(serial_number, [return_type])Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.VLOOKUP Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Basically, VLOOKUP lets you search for specific information in your spreadsheet. For example, if you have a list of products with prices, you could search for the price of a specific item.IF Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false). Example Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next to the name. Now you can use an IF statement that says: “IF the salesperson met their quota, say “Met quota”, if not say “Did not meet quota”

The arguments of the database functions are the same, only the name after “D” that is different depending on what they are doing.DName (Database; Field; Criteria) where:

• Database. The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

• Field. Indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

• Criteria. Is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

DSUM(database, field, criteria)In the column specified by the field argument, the result is the sum of the values that meet the criteria.DAVERAGE(database, field, criteria)

14

In the column specified by the field argument, the result is the average of the values that meet the criteria.DCOUNT(database, field, criteria)In the column specified by the field argument, the result is the count of the values that meet the criteria.DMIN(database, field, criteria)In the column specified by the field argument, the result is the minimum of the values that meet the criteria.DMAX(database, field, criteria)In the column specified by the field argument, the result is the maximum of the values that meet the criteria.DGET(database, field, criteria)Extracts a single value from a column of a list or database that matches conditions that you specify.

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=X12oQeJWpEs https://www.youtube.com/watch?v=PU8ACyYxJBkhttps://www.youtube.com/user/learnexcelfunctions

15

7. Excel lists (sort, filter and pivot)

Purpose of the exercise: The purpose of this exercise to learn the possibilities to handle Excel the list of data, which is similar to databases.Required knowledge: Excel list, database, field, record, sort, filter, (auto and special), report creating

As you add more content to a worksheet, organizing that information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways.Sorting dataWhen sorting data, it's important to first decide if you would like the sort to apply to the entire worksheet or just a cell range.

• Sort sheet organizes all of the data in your worksheet by one column. • Sort range sorts the data in a range of cells, which can be helpful when working with a

sheet that contains several tables. Sorting a range will not affect other content on the worksheet.

In our example, we'll sort a T-shirt order form alphabetically by Last Name (column C). Select a cell in the column you wish to sort by. In our example, we'll select cell C2. Select the Data tab on the Ribbon, then click the Ascending command to Sort A to Z, or the Descending command to Sort Z to A. In our example, we'll click the Ascending command. The worksheet will be sorted by the selected column. In our example, the worksheet is now sorted by last name. Filtering dataIf your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Click the drop-down arrow for the column you wish to filter. The Filter menu will appear. Uncheck the box next to Select All to quickly deselect all data. Check the boxes next to the data you wish to filter, then click OK. To remove all filters from your worksheet, click the Filter command on the Data tab.Filtering is very convenient in Excel, because text, date, and numeric filters are available depending on the type of data. In the Advanced (Special) Filters menu item, two conditions can be combined for each type with the help of And, Or logical operators. You can also configure more complex filtering conditions than the AutoFilter under the Special filter conditions. Here, you have the ability to get records that meet the specified criteria in a different cell ranges. It is also possible to narrow the fields, create new lists, if we do not specify a blank cell in the Copy field, but contain the target range field names, only the requested fields are added to the new range from records that meet the specified criteria.The Filter Range is simple if you only specify one field. Field name and condition below. The Filter range can also consist of two empty cells (or an empty cell below the field name), in which case all records are processed.In the case of a complex filter range, we add conditions to multiple fields. Field names are written in the first line of the filter range, and the conditions are below line. The AND conditions are written in one line and the OR condition in a separate line.

16

For large databases, we can create more comprehensive summary tables by producing reports. To create a statement, use the Insert menu's Pivot command, where you can also create a Pivot table and a Pivot Diagram. After specifying the data to be analysed and the location of the report, you can select the fields in the pivot and the procedures used for the calculation. After selecting the fields for the pivot, you can rearrange the fields in the pivot in the drop-down menus and modify the counting procedures. The inserted diagram is, of course, modifying as described in the diagram creation.

17

18

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://support.office.com/en-us/article/video-sort-data-in-a-range-or-table-ffb9fcb0-b9cb-48bf-a15c-8bec9fd3a472https://support.office.com/en-us/article/video-filter-data-in-a-range-or-table-7fbe34f4-8382-431d-942e-41e9a88f6a96https://www.youtube.com/watch?v=C_2cjh5Pd8ohttps://www.contextures.com/xladvfilter01.htmlhttps://support.office.com/en-us/article/create-a-pivotchart-c1b1e057-6990-4c38-b52b-8255538e7b1c

19

8. Database and data models

Purpose of the exercise: The purpose of this exercise to introduce students the databases and data modelsRequired knowledge: database, data models, hierarchical data model, mesh data model, relational data model, object oriented data model

Depending on what logical connections are allowed in the data model between entity sets and how these connections are handled, database management systems can be classified into four main database types:

• hierarchical• network• relational database management systems• Object-oriented systems (which are not yet used in practice).

A hierarchical model represents the data in a tree-like structure in which there is a single parent for each record. To maintain order there is a sort field which keeps sibling nodes into a recorded manner. These types of models are designed basically for the early mainframe database management systems, like the Information Management System (IMS) by IBM.This model structure allows the one-to-one and a one-to-many relationship between two/ various types of data. This structure is very helpful in describing many relationships in the real world; table of contents, any nested and sorted information.The hierarchical structure is used as the physical order of records in storage. One can access the records by navigating down through the data structure using pointers which are combined with sequential accessing. Therefore, the hierarchical structure is not suitable for certain database operations when a full path is not also included for each record.For example, where each individual person in a company may report to a given department, the department can be used as a parent record and the individual employees will represent secondary records, each of which links back to that one parent record in a hierarchical structure.The network model is a database model conceived as a flexible way of representing objects and their relationships. Its distinguishing feature is that the schema, viewed as a graph in which object types are nodes and relationship types are arcs, is not restricted to being a hierarchy or lattice.The network database model was invented by Charles Bachman in 1969 as an enhancement of the already existing database model, the hierarchical database model. Because the hierarchical database model was highly flaw, Bachman decided to create a database that is similar to the hierarchical database but with more flexibility and less defaults. The original and existing hierarchical database has one owner file linked strictly to one member file, creating a ladder affect that restricted the database to find relationships outside of its category.To simplify the purpose of relational database is to imagine storing a large volume of data and place the data into several tables instead of a large table of data. The relational database system allows database system to define the relationship among the tables. Then, the relationships will enable relational database system to combine those tables for querying and reporting purposes. In Microsoft Access, the task to combine the data from several tables for querying and reporting is accomplished through the keys or database fields “used to uniquely identify specific records in a table.” As a result, the relational database system will allow the database to be larger in volume, to be faster in speed, and to be more efficient in quality.

20

The relational database concept was originally created by Edger and Codd in 1970.The data model based on the tabular representation of the data is called a relational data model. In the relational data model, each relationship is a named table. The domains of the relationship are properties. The columns in the table correspond to the columns, each column has its own name. A data type is characterized by the relationship and domain name. The data type corresponds to the data field of traditional data processing.Some Relational Database Management System Terminology:Relation: Information about a single subject such as customers, orders, employees, products, or companies. A relation is usually stored as a table in a relational database management system.Attribute: A specific piece of information about a subject, such as the address for a customer or the dollar amount of an order. An attribute is normally stored as a data column, or field, in a table.Instance: A particular member of a relation - an individual customer or product. An instance is usually stored in a table as a record, or row.Join: The process of linking tables or queries on tables via their related data values. For example, customers might be joined to orders by matching customer ID in a customers table and an orders table.

Hierarchical data model

Network data model

21

Relational data model

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=r6G8oCaOU28Basic tasks in Access; https://support.office.microsoft.com/en-us/article/Basic-tasks-for-an-Access-2013-desktop-database-5ddb8595-497c-4366-8327-ae79d2abdc9c?CorrelationId=afe8a4cf-ec04-4ab7-98ee-a47dfd284828&ui=en-US&rs=en-US&ad=USDummies in Access; http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.htmlAccess 2013 tutorials; http://www.gcflearnfree.org/access2013Microsoft Access tutorial; http://www.quackit.com/microsoft_access/tutorialAccess 2013 training courses, https://support.office.com/en-IN/ article/-a4bd10ea-d5f4-40c5-8b37-d254561f8bce

22

9. Database objects and entity relationships

Purpose of the exercise: The purpose of this exercise to learn students the database objects and relationships among themRequired knowledge database objects, table, field, record, key, primary key, secondary key, compound key, foreign key, connection, switchboard, connection types (1: 1, 1: N, M: N)

The relational database stores the data in relationships that appear to the user in the form of tables. In the table, a row is called record, and a column is field.The tables are the most important structures of the database, each table describes a single well-defined object. The order of records and fields is irrelevant, each table contains at least one field that uniquely identifies the records of the table, this is called the primary key. Tables that provide information based on stored data are called data tables. The data stored in such tables is usually dynamic, as it can be modified and processed in a variety of ways.The field is the smallest structure of the database that specifies a feature of the object of the table. Fields store the actual data. In a well-designed database, each field contains one or more values, and the field name refers to the stored value.A row of the database, also called a record, describes a unique instance of a table object. The record includes the entire field set, regardless of whether the fields contain values. The field used as the primary key identifies the rows of the table, the records.Keys are fields that play a special role in the table. A table may contain a number of different types of keys, the most important being the primary and secondary keys. The primary key is a field or group of fields that uniquely identifies records within a table. If the primary key consists of several fields, it is called a compound primary key.The primary key is the most important part of the table.

• The value of the primary key identifies a record in the database.• The primary key field identifies a table in the database.• Primary key provides table-level integrity and helps in linking tables.• All tables in the database must have a primary key.

To establish a connection between two tables, the primary key of one table must be incorporated into the structure of another table, where this value becomes a foreign key. The foreign key term is derived from the fact that the second table also has a primary key, so the primary key taken from the first table is foreign to the second table.If the records of two tables are in one sense, then the two tables are linked together. The relationships between the tables can be realized with primary and secondary keys and a lookup table.Data models can be defined using the following three features:

• • entity set• • attribute, property• • key

Different sets of individuals may be related to each other. The logical relationships expressed by the common properties of the sets of entities are called relationships.In practice, three types of relationships are distinguished:

• One type of connection (1: 1 connection) where each element of one set of entities is associated with exactly one element of the other entity set. For example, there is a type of relationship between a set of rural municipalities and a set of mayors.

23

• One or more types of connections (1: N connection), where one element of one entity set may include several elements of the other set of entities, but one element of one entity set may contain only one element of one entity set. For example, in a relationship between Warehouses and Employees, a warehouse may have more than one worker, but a worker can only work in one warehouse.

• Multiple types of connections (N: M connections), where one element of one entity set may include several elements of the other set of entities and the reverse is true. For example, the relationship between Goods and Shipments is more than one type of connection, as they can carry several goods in a single shipment, but they can also deliver a commodity with multiple shipments.

1:1 relationship

1:N relationship

M:N relationship

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=r6G8oCaOU28

24

Basic tasks in Access; https://support.office.microsoft.com/en-us/article/Basic-tasks-for-an-Access-2013-desktop-database-5ddb8595-497c-4366-8327-ae79d2abdc9c?CorrelationId=afe8a4cf-ec04-4ab7-98ee-a47dfd284828&ui=en-US&rs=en-US&ad=USDummies in Access; http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.htmlAccess 2013 tutorials; http://www.gcflearnfree.org/access2013Microsoft Access tutorial; http://www.quackit.com/microsoft_access/tutorialAccess 2013 training courses, https://support.office.com/en-IN/ article/-a4bd10ea-d5f4-40c5-8b37-d254561f8bce

25

10. Database normalisation

Purpose of the exercise: The purpose of this exercise to learn students the database normalisation

Required knowledge Database, normalization, first normal form, second normal form, third normal form, key, foreign key, lookup table

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data. It divides larger tables to smaller tables and links them using relationships. The inventor of the relational model Codd proposed the theory of normalization with the introduction of First Normal Form, and he continued to extend theory with Second and Third Normal Form. Later he joined with Raymond F. Boyce to develop the theory of Boyce-Codd Normal Form.Assume a video library maintains a database of movies rented out. Without any normalization, all information is stored in one table. Here you see Movies Rented column has multiple values.Now let's move into first Normal Forms. 1NF (First Normal Form) Rules.

• Each table cell should contain a single value.• Each record needs to be unique.

Let's move into second normal form 2NF 2 Normal Form (Second Normal Form) Rules:

• Rule 1- be in 1 Normal Form• Rule 2- single Column Primary Key

It is clear that we can't move forward to make our simple database in second Normalization form unless we partition the table above. We have divided our 1NF table into two tables Table 1 and Table2. Table 1 contains member information. Table 2 contains information on movies rented. We have introduced a new column called Membership_id which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id Why do you need a foreign key (memnership_ID) in Table 2? Suppose an idiot inserts a record in Table 2 where this value is 101. You will only be able to insert values into your foreign key that exist in the unique key in the parent table. This helps in referential integrity. The above problem can be overcome by declaring membership_ID from Table2 as foreign key of membership_ID from Table1. Now, if somebody tries to insert a value in the membership id field that does not exist in the parent table, an error will be shown! What are transitive functional dependencies?A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change Consider the table 1. Changing the non-key column Full Name may change Salutation. 3NF (Third Normal Form) Rules

• Rule 1- be in 2NF• Rule 2- has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table. We have again divided our tables and created a new table which stores Salutations.  There are no transitive functional dependencies, and hence our table is in 3NF

26

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3

Original table

1. Normal Form

2. Normal Form

Transitive dependencies

27

3. Normal Form (3NF)

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=r6G8oCaOU28Basic tasks in Access; https://support.office.microsoft.com/en-us/article/Basic-tasks-for-an-Access-2013-desktop-database-5ddb8595-497c-4366-8327-ae79d2abdc9c?CorrelationId=afe8a4cf-ec04-4ab7-98ee-a47dfd284828&ui=en-US&rs=en-US&ad=USDummies in Access; http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.htmlAccess 2013 tutorials; http://www.gcflearnfree.org/access2013Microsoft Access tutorial; http://www.quackit.com/microsoft_access/tutorialAccess 2013 training courses, https://support.office.com/en-IN/ article/-a4bd10ea-d5f4-40c5-8b37-d254561f8bce

28

11. Database in practice (manage tables)

Purpose of the exercise: The purpose of this exercise to learn students manage tables in practiceRequired knowledge Database, table definition, field types, field properties, data entry masking, record entry to table

Microsoft Access is a Relational Database Management System (RDBMS), designed primarily for home or small business usage. Access has traditionally been known as a desktop database system because its functions are intended to be run from a single computer.The first step to creating a database is to, well, create the database! We will create a blank database first. Then throughout the rest of the tutorial, we'll make additions to it so that it suits the needs of our favourite space travel company.Creating a database in Microsoft Access is as easy as creating a Word document! This lesson demonstrates how to create a database in MS Access.With database management systems, you need to create your tables before you can enter data. Microsoft Access makes creating tables extremely easy. In fact, when you create a database, Access creates your first table for you (and calls it Table1). Normally when you need to create a new table, you'll select CREATE menu > Table from the Ribbon toolbar. But we can do that later.For now Access has already created our first table, so all we need to do is modify that table so that it suits our needs.What we just did was create the column names and specify the type of data that can be entered into them. Restricting the data type for each column is very important and helps maintain data integrity. It can ensure that the user enters the data in the intended format. It can also guard against accidents like for example, inadvertently entering an email address into a field for storing the current date.You might notice that now when you click on the field names, you can't see or change their data types. Instead, if you click on the inverted triangle within a field, you see a different menu of options (eg, Sort Oldest to Newest etc) depending on the field's data type. This shows that the field is ready for data - once you have data, you can sort it using this menu.Don't worry if you accidentally entered the wrong data type. You can change the datatype in the Ribbon toolbar by adjusting the data type under the Data Type dropdown (top right area of the toolbar). If you can't see this option in the toolbar be sure to select the FIELDS tab first:As you continue working with MS Access, you will find yourself switching between Design view and Datasheet view often. So it's good to familiarize yourself with this concept early on.We have just set a default value for the DateCreated field. This means that, whenever a new record is entered, this field will automatically be populated with the current date and time. We have also specified a format for all dates that are stored in this field.You can see that we have configured just two of many options in this pane. These options can be used to further specify exactly what type of data can be entered into the database as well as any constraints or defaults that you'd like to set against each field. Some options include restricting the length of data (under Field Size), setting a default value (which we've just done), specifying whether it is a required field, and more.In practice, you will probably want to tighten some of these rules down - like specifying whether a field is a required field or not

29

You can type directly into the table while it's in Datasheet view. While this is fine for smaller tables with a small number of records that are rarely updated, it's not suitable if you plan to maintain a lot of records. Maintaining even a small database can become a time-consuming task if records change frequently - especially if you need to update data that's spread across multiple tables. It's also not really suitable if there will be non-technical users maintaining the database. For non-technical users, it's better to create a nice user-friendly form.You can set up a form, so that non-technical users can enter data into the form. Once they submit the form, the data is automatically inserted into the database. One of the great things about forms is that they can insert into many tables - saving you from having to open up each table to manually insert the data.Access provides a form wizard, which steps you through the process to building a form.There may be times when you need to load your database with data that already exists. For example, you might have data in an Excel spreadsheet or even a .csv file that you want to transfer to an Access database. This can be done!Access 2013 allows you to import data from the following file types:

• Microsoft Excel• Microsoft Access• ODBC Databases, such as SQL Server• Text or comma-separated value (CSV) files• SharePoint List• XML• Data Services• HTML Document• Outlook folder

To import data from any of these file types, select EXTERNAL DATA from the Ribbon, select the file type then follow the prompts:

Import external data

30

Table in design mode

Relationship among tables

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=r6G8oCaOU28Basic tasks in Access; https://support.office.microsoft.com/en-us/article/Basic-tasks-for-an-Access-2013-desktop-database-5ddb8595-497c-4366-8327-ae79d2abdc9c?CorrelationId=afe8a4cf-ec04-4ab7-98ee-a47dfd284828&ui=en-US&rs=en-US&ad=USDummies in Access; http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.htmlAccess 2013 tutorials; http://www.gcflearnfree.org/access2013Microsoft Access tutorial; http://www.quackit.com/microsoft_access/tutorialAccess 2013 training courses, https://support.office.com/en-IN/ article/-a4bd10ea-d5f4-40c5-8b37-d254561f8bcehttps://www.tutorialspoint.com/ms_access/ms_access_create_tables.htmhttps://support.office.com/en-us/article/video-build-tables-with-the-table-designer-ac5a2bfb-4537-469c-8580-50eb24053383

31

12. Database in practice (creating queries)

Purpose of the exercise: The purpose of this exercise to learn students manage queries in practiceRequired knowledge: Database, query design, select queries, update queries, delete queries, append queries

When you want to use data, you rarely want to use all of the data from one table. For example, when you want to use data from a Contacts table, you usually want to look at one specific record, or maybe just the telephone number. Sometimes you want to combine data from more than one table, such as combining Customer information with Order information. To select the data that you want to use, you use a select query.A select query is a database object that shows information in Datasheet view. A query does not store data, it displays data that is stored in tables. A query can show data from one or more tables, from other queries, or from a combination of the two.A query refers to the action of instructing the database to return some (or all) of the data in your database. In other words, you are "querying" the database for some data that matches a given criteria. The queries are run against one or more tables to return only the data that you're interested in. For example, you might like to see a list of all individuals whose last name is "Griffin". Or you might like to see a list of all users who have registered with your database over a given time period. You might also want to see which customers have ordered a particular product. To do all this, you need to perform a query.If you use fields from data sources that are not related to each other, the Query Wizard asks you if you want to create relationships. The wizard opens the Relationships window for you, but you must restart the wizard if you edit any relationships. Therefore, before you run the wizard, consider creating any relationships that your query needs.Combine data from several data sources. A table usually only displays data that it stores. A query lets you pick and choose fields from various sources, and specify how the information should be combined.Use expressions as fields. For example, you could use the Date function as a field, or you could use the Format function with a field to control the way the data from the field is formatted in the query results.Of course, the conditions may be more complex with "and / or" links, even for multiple fields. During selecting queries, we also have the ability to create aggregate queries using (Sum - Amount; Avg - Average, Min - Minimum; Max - Maximum; Count - Number) functions. If you want to create groups and use the above aggregation functions, you should select "Group by" in the summary line.As a best practice when creating an update query, first create a select query that identifies the records that you want to update, and then convert that query to an update query that you can run to update the records. By selecting data first, you can verify that you’re updating the records you want before you actually change any data. Back up your database before you run an update query. You cannot undo the results of an update query, and making a backup helps make sure that you can reverse your changes if you change your mind.When you want to either quickly delete a lot of data or delete a set of data on a regular basis in an Access desktop database, a delete query might be useful because the queries make it possible to specify criteria to quickly find and delete the data. Using a query can also be a timesaver because you can reuse a saved query. Back up your database before you run an update query. You cannot

32

undo the results of an update query, and making a backup helps make sure that you can reverse your changes if you change your mind.An append query selects records from one or more data sources and copies the selected records to an existing table.For example, suppose that you acquire a database that contains a table of potential new customers, and that you already have a table in your existing database that stores that kind of data. You'd like to store the data in one place, so you decide to copy it from the new database into your existing table. To avoid entering the new data manually, you can use an append query to copy the records.In case of the last three queries it is not enough to change the view, we have to choose in Design mode the “Exclamation sign”

33

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=r6G8oCaOU28Basic tasks in Access; https://support.office.microsoft.com/en-us/article/Basic-tasks-for-an-Access-2013-desktop-database-5ddb8595-497c-4366-8327-ae79d2abdc9c?CorrelationId=afe8a4cf-ec04-4ab7-98ee-a47dfd284828&ui=en-US&rs=en-US&ad=USDummies in Access; http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.htmlAccess 2013 tutorials; http://www.gcflearnfree.org/access2013Microsoft Access tutorial; http://www.quackit.com/microsoft_access/tutorialAccess 2013 training courses, https://support.office.com/en-IN/ article/-a4bd10ea-d5f4-40c5-8b37-d254561f8bcehttps://www.youtube.com/watch?v=3RVoxLd7tEkhttps://www.youtube.com/watch?v=QRQhBVF-6Nghttps://www.tutorialspoint.com/ms_access/ms_access_query_data.htm

34

13. Database in practice (creating forms)

Purpose of the exercise: The purpose of this exercise to learn students manage forms in practiceRequired knowledge Database, form design, form wizard, control options for forms, embedded forms for linked tables

Using the forms in the database, you can easily enter, search, modify, and delete data easily.There are two ways to create a form:

• Form Wizard (Create menu, under additional forms icon),• Design view (Create menu, form design icon).

The use of the wizard is infinitely simple and requires no special expertise. All you have to do is select the table for which you want to create the form. Of course, it is also possible to create forms for split or linked tables. In the following example, we write down the account number for the account number, and below it we show the article, unit price, VAT and quantity you purchased. As you can see from the previous board connection, we need more tables, so this is a split form.You can also view the same in the design view. In this case, the situation is the same as if we started out from a design view, but we can switch between the two views (form and designer) in the given state of creation. The following figure shows how the form is divided into three parts (form head, strain, form). You can freely edit them in design view.Of course, editing the forms is also possible in the design view, as already mentioned above. in this case, you can place various controls (input field, list, combined list, radio button, command key) to simplify the display of the data. You may also want to add knitted or informal controls that display different objects, such as (image, sound, or text), or create a computationally calculated control element, which will of course require the values of other fields. You can also use functions and mathematical operations in the input fields. You can use the radio button to select different data, each of these controls must be linked to the source, that is, a field in the table. For ease of reference, each control element has a label or can create one. (Page headers and footers are also used to display.).It is very important at the end to draw attention to the fact that the form is always based on a table, so that the data entered into the form is recorded directly in the table, not as a record in the form!

35

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=r6G8oCaOU28Basic tasks in Access; https://support.office.microsoft.com/en-us/article/Basic-tasks-for-an-Access-2013-desktop-database-5ddb8595-497c-4366-8327-ae79d2abdc9c?CorrelationId=afe8a4cf-ec04-4ab7-98ee-a47dfd284828&ui=en-US&rs=en-US&ad=USDummies in Access; http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.htmlAccess 2013 tutorials; http://www.gcflearnfree.org/access2013Microsoft Access tutorial; http://www.quackit.com/microsoft_access/tutorialAccess 2013 training courses, https://support.office.com/en-IN/ article/-a4bd10ea-d5f4-40c5-8b37-d254561f8bcehttps://www.youtube.com/watch?v=Rj0ZBLlbsRchttps://www.youtube.com/watch?v=_qS1qr5tUe0https://www.tutorialspoint.com/ms_access/ms_access_create_form.htm

36

14. Database in practice (creating reports)

Purpose of the exercise: The purpose of this exercise to learn students manage reports in practiceRequired knowledge Database, reports, report wizard, composite reports, group levels and other options

In many respects, the design, use, and management of reports are similar to forms, and the basic difference is that we can organize here, and we can group data for printing. You can also use functions and calculated fields here, just like in the forms. The question may be why reports are needed in databases. The answer is simple, you often have to print out the results of a query, and we can only solve it with the help of reports.Let's look at how to create a report in Access.

• There are two options here. Reporting Wizard (Create menu, Report Wizard icon),• Design view (Create menu, report designer icon).

The specific task is to display the invoice date, the partner name, and the account number in a monthly breakdown so that the partners appear in each group in alphabetical order.Selecting the report wizard will require the "invoice" and "partner" tables to select the field names specified in the task.Going two steps, we have the option of grouping. To do this, double-click on the “Invoice_date” in the left pane. At the bottom left, you can specify "Group settings". In this example, this is a monthly grouping. This is shown in the figure below.If necessary, we can assign sorting based on the fields in the next step in ascending or descending order, even at multiple levels. In this example, we want to sort the partners in a group in alphabetical order in our report. It should be noted here that if we had chosen a numerical field, we would have the opportunity to use aggregate functions (Sum, Avg, Min, Max and Count) as we have seen in the queries. Since we are not working with a numeric field now, this option is not offered in this case.In the next step, you can choose the layout (the most common and default) and orientation (portrait or landscape) for the report. In the last step we can choose a style and name for our report.By following the steps above, you can access the final form of our report in the wizard, which is shown in the last figure of the chapter.After we have successfully passed the report wizard on each step, we can also view our report in the design view, where we can make various changes to our baseline report. This is similar to the form, but due to its nature, there are, of course, differences between the Report Head, Page Head, Header, Body and Page Footer, Report Footer. Often, we need to set a condition for the report. In this case, you first need to create a query and build on the report. Unfortunately, we cannot formulate a condition directly in the report.

37

38

Recommended additional knowledge:http://informatika.gtportal.eu/index.php?f0=tablazathttps://www.excel-easy.com/basics.htmlhttp://www.functionx.com/excel/index.htmhttps://www.youtube.com/watch?v=r6G8oCaOU28Basic tasks in Access; https://support.office.microsoft.com/en-us/article/Basic-tasks-for-an-Access-2013-desktop-database-5ddb8595-497c-4366-8327-ae79d2abdc9c?CorrelationId=afe8a4cf-ec04-4ab7-98ee-a47dfd284828&ui=en-US&rs=en-US&ad=USDummies in Access; http://www.dummies.com/how-to/computers-software/ms-office/Access/Access-2013.htmlAccess 2013 tutorials; http://www.gcflearnfree.org/access2013Microsoft Access tutorial; http://www.quackit.com/microsoft_access/tutorialAccess 2013 training courses, https://support.office.com/en-IN/ article/-a4bd10ea-d5f4-40c5-8b37-d254561f8bcehttps://www.youtube.com/watch?v=tKQLpq13mkMhttps://www.tutorialspoint.com/ms_access/ms_access_reports_basics.htm

39