Informatics
Database management system - II
Petr Suchánek
Informatics

Outline of the lecture
•Relationship
•Database types

Relationship
•In relational database terms, a relationship is a situation where multiple tables can contain
related data that is linked by a common field.*
•A relationship consists of a parent table and a child table.*
•The child table references the parent table by having a field that matches a field in the parent
table.*
•The child's field is referred to as a foreign key.*
•The parent's field is the primary key.*
•In a relationship, any data entered into the child's foreign key field must match a value from the
parent's primary key field.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_relationship_in_m
icrosoft_access.cfm

Relationship
•By ensuring that the foreign key's data matches data in the primary key, we know that all records
in the child table will have an associated record in the parent table.*
•So we can create a one-to-many relationship between the Albums and Artists table.*
•Our relationship will determine that an artist can have many albums, but an album can only belong
to one artist.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_relationship_in_m
icrosoft_access.cfm

Relationship
• Click Relationships from the Database Tools tab on the Ribbon.*
•The Show Tables dialog box should appear.*
•Select both the Artists and Albums tables from the list and click Add.*
•Click Close to close the dialog box.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_relationship_in_m
icrosoft_access.cfm

Relationship
• Click and drag the Albums.ArtistId field over the Artists.ArtistId field and release.*
•The Edit Relationships dialog box appears.*
•Check Enforce Referential Integrity and click Create.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_relationship_in_m
icrosoft_access.cfm

Relationship
• https://www.dummies.com/software/microsoft-office/access/how-to-set-table-relationships-in-access
-2016/
•https://database.guide/how-to-create-a-relationship-in-access/
•http://en.tekstenuitleg.net/articles/software/create-a-one-to-many-relationship-in-access
•https://support.microsoft.com/en-us/help/304466/how-to-define-relationships-between-tables-in-an-a
ccess-database

Query
•Queries are very useful tools when it comes to databases and they are often called by the user
through a form.*
•They can be used to search for and grab data from one or more of your tables, perform certain
actions on the database and even carryout a variety of calculations depending on your needs.*
•Luckily for us, Microsoft Access allows for many types of queries, some of the main ones being
select, action, parameter and aggregate queries.*
•They can be thought of as just another part of your database – essentially an object like a table
or a macro.*
• Either use the Query Wizard that Microsoft Access provides for you, or Create your own queries
from scratch.*
*http://www.databasedev.co.uk/microsoft-access-query-types.html

Select query
•The select query is the simplest type of query and because of that, it is also the most commonly
used one in Microsoft Access databases.*
•It can be used to select and display data from either one table or a series of them depending on
what is needed.*
•In the end, it is the user-determined criteria that tells the database what the selection is to be
based on.*
•After the select query is called, it creates a "virtual" table where the data can be changed, but
at no more than one record at a time.*
*http://www.databasedev.co.uk/microsoft-access-query-types.html

Action query
•When the action query is called, the database undergoes a specific action depending on what was
specified in the query itself.*
•This can include such things as creating new tables, deleting rows from existing ones and updating
records or creating entirely new ones.*
•Action queries are very popular in data management because they allow for many records to be
changed at one time instead of only single records like in a select query.*
*http://www.databasedev.co.uk/microsoft-access-query-types.html

Action query
•Append Query
Øtakes the set results of a query and "appends" (or adds) them to an existing table.
•Delete Query
Ødeletes all records in an underlying table from the set results of a query.
•Make Table Query
Øas the name suggests, it creates a table based on the set results of a query.
•Update Query
Øallows for one or more field in your table to be updated.
*http://www.databasedev.co.uk/microsoft-access-query-types.html

Action – delete query*
•
*http://www.databasedev.co.uk/delete_query.html
•Create a SELECT query to determine the records that will be deleted. Apply any required query
criteria;
•In the query design view, click on the drop-down arrow to the right of the Query Type button and
choose Delete Query;
•If needed, modify the query further so that the correct fields will be deleted with the desired
new data;
•Click on the Run (!) button to run the action query;
•When informed of the number of records to be deleted in the Microsoft Access dialog box, click
Yes;
•Close the query, saving if required.
•

Action – make table query*
•
*http://www.databasedev.co.uk/make-table-query.html
•Using our scenario, we will create a make-table query for all customers from the specified area
that have ordered products in the past 12 months.*
•We will need to perform the following steps to create the query:
ØCreate a new query, use the Customers and Orders tables;
ØFrom the Query Type button on the toolbar, select Make Table;
ØSelect the mailing information fields, in our case CustomerTitle, CustomerName, Address, City,
Postcode from the Customers table, and OrderDate from the Orders table;
ØSpecify the chosen City criteria in the City field and add the criteria required in the OrderDate
field to only show records from the last 12 months - using the DateAdd function:
>=DateAdd("yyyy",-1,Now());

Action – make table query*
•
*http://www.databasedev.co.uk/make-table-query.html
ØTo check that the results are returned that we expect, click on the datasheet button Datasheet
view button on the toolbar. Once you have verified this, switch back to query design view;
ØIn query design, deselect the Show: property for the OrderDate field, as we do not need this to be
visible in our new table;
ØClick on the Run button on the toolbar. Microsoft Access now displays a message to indicate how
many records will be copied to the new table;
ØClick Yes to complete the query, and create the new table.

Action – make table query*
•
*http://www.databasedev.co.uk/make-table-query.html

Action – update query*
•
*http://www.databasedev.co.uk/update_query.html
•
•Create a SELECT query to determine the records that will be updated. Apply any required query
criteria, and view the data that will be updated by pressing the Datasheet button;
•In the query design view, click on the drop-down arrow to the right of the Query Type button and
choose Update Query;
•After you are satisfied that the information to be updated is correct Run the query using the Run
Icon.

Parameter query
•In Microsoft Access, a parameter query works with other types of queries to get whatever results
you are after.*
•This is because, when using this type of query, you are able to pass a parameter to a different
query, such as an action or a select query.*
•It can either be a value or a condition and will essentially tell the other query specifically
what you want it to do.*
•It is often chosen because it allows for a dialog box where the end user can enter whatever
parameter value they wish each time the query is run.*
•The parameter query is just a modified select query.*
*http://www.databasedev.co.uk/microsoft-access-query-types.html

Aggregate query
•A special type of query is known as an aggregate query.*
•It can work on other queries (such as selection, action or parameter) just like the parameter
query does, but instead of passing a parameter to another query it totals up the items by selected
groups.*
•It essentially creates a summation of any selected attribute in your table.*
•This can be further generated into statistical amounts such as averages and standard deviation,
just to name a couple.*
*http://www.databasedev.co.uk/microsoft-access-query-types.html

Query
•Click Query Design from the Create tab on the Ribbon.*
•The Show Table dialog box will appear.*
•Select both the Artists and Albums tables and click Add.*
•Click Close to close the dialog box.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsof
t_access.cfm

Query
•Now we get to design our query. We'll keep it simple, and create a query that returns all albums
from Iron Maiden.*
•Select the options as per the screenshot.*
•Run the query by clicking the ! Run button on the Ribbon.*
•If you can't read the screenshot, the query options are:
•Fields:
•Albums.AlbumName
•Albums.Genre
•Albums.ReleaseDate
•Artists.ArtistName
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsof
t_access.cfm

Query
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsof
t_access.cfm

Query wizard
•On the Create tab, in the Queries group, click Query Wizard.*
•In the New Query dialog box, click Simple Query Wizard, and then click OK.*
•Next, you add fields. You can add up to 255 fields from as many as 32 tables or queries.*
•For each field, perform these two steps:*
ØUnder Tables/Queries, click the table or query that contains the field.
ØUnder Available Fields, double-click the field to add it to the Selected Fields list. If you want
to add all fields to your query, click the button with the double right arrows (>>).
ØWhen you have added all the fields that you want, click Next.
*https://support.office.com/en-us/article/create-a-simple-select-query-de8b1c8d-14e9-4b25-8e22-7088
8d54de59

Query wizard
•If you did not add any number fields (fields that contain numeric data), skip ahead to step 9. If
you added any number fields, the wizard asks whether you want the query to return details or
summary data.*
•Do one of the following:
ØIf you want to see individual records, click Detail, and then click Next. Skip ahead to step 9.
ØIf you want to see summarized numeric data, such as averages, click Summary, and then click
Summary Options.
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsof
t_access.cfm

Query wizard
*https://support.office.com/en-us/article/create-a-simple-select-query-de8b1c8d-14e9-4b25-8e22-7088
8d54de59

Query wizard
•In the Summary Options dialog box, specify which fields you want to summarize, and how you want to
summarize the data. Only number fields are listed.*
•For each number field, choose one of the following functions:*
ØSum    The query returns the sum of all the values of the field.
ØAvg    The query returns the average of the values of the field.
ØMin    The query returns the smallest value of the field.
ØMax    The query returns the largest value of the field.
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsof
t_access.cfm

Query wizard
•If you want the query results to include a count of the records in a data source, select the
appropriate Count records in data source name check box.*
•Click OK to close the Summary Options dialog box.*
•If you did not add a date/time field to the query, skip ahead to step 9. If you added a date-time
field to the query, the Query Wizard asks you how you would like to group the date values.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsof
t_access.cfm

Query wizard
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsof
t_access.cfm

The end