Informatics
Database management system - I
Petr Suchánek
Informatics

Outline of the lecture
•Database
•Database types
•Relational database
•Relationship
•Microsoft Access 2016
•Tables
•Table Wizard
•Table – Design View

Database
•A database is a collection of data that is organized so that it can be easily accessed, managed
and updated.*
•Computer databases typically contain aggregations of data records or files, containing information
about sales transactions or interactions with specific customers.*
• Most databases contain multiple tables, which may each include several different fields.**
•For example, a company database may include tables for products, employees, and financial
records.**
•Each of these tables would have different fields that are relevant to the information stored in
the table.**
*https://searchsqlserver.techtarget.com/definition/database
**https://techterms.com/definition/database

Relational database
•A relational database, invented by E.F. Codd at IBM in 1970, is a tabular database in which data
is defined so that it can be reorganized and accessed in a number of different ways.*
•Relational databases are made up of a set of tables with data that fits into a predefined
category.*
•Each table has at least one data category in a column, and each row has a certain data instance
for the categories which are defined in the columns.*
•When creating a relational database, you can define the domain of possible values in a data column
and further constraints that may apply to that data value.**
*https://searchsqlserver.techtarget.com/definition/database
**https://searchdatamanagement.techtarget.com/definition/relational-database

Relational database
•For example, a domain of possible customers could allow up to 10 possible customer names but be
constrained in one table to allowing only three of these customer names to be specifiable. Two
constraints relate to data integrity and the primary and foreign keys:*
ØEntity integrity ensures that the primary key in a table is unique and that the value is not set
to null.
ØReferential integrity requires that every value in a foreign key column will be found in the
primary key of the table from which it originated.
*https://searchdatamanagement.techtarget.com/definition/relational-database

Relational database*
*https://neo4j.com/business-edge/relational-to-relationships/


Relational database - advantages
•The main advantage of relational databases is that they enable users to easily categorize and
store data that can later be queried and filtered to extract specific information for reports.*
•Relational databases are also easy to extend and aren't reliant on physical organization.*
•After the original database creation, a new data category can be added without all existing
applications being modified.*
•
*https://searchdatamanagement.techtarget.com/definition/relational-database

Relational database - advantages
•Other relational database advantages include:*
•Accuracy: Data is stored just once, eliminating data deduplication.
•Flexibility: Complex queries are easy for users to carry out.
•Collaboration: Multiple users can access the same database.
•Trust: Relational database models are mature and well-understood.
•Security: Data in tables within a RDBMS can be limited to allow access by only particular users.
•
*https://searchdatamanagement.techtarget.com/definition/relational-database

Database & Relational database
•The majority of software products in today's market incorporate both relational database and
regular database compliances.*
•Therefore, they can manage databases in the relational tabular form as well as in file form, or
both.*
•Essentially, in today's market, a relational database is a database and vice versa; however, there
are still major differences in data storage between the two systems.*
•The most important difference is that a relational database stores data in a tabular form - or
arranged in a table with rows and columns - while a database stores data as files.*
*https://searchdatamanagement.techtarget.com/definition/relational-database

Database & Relational database
•Database normalization is present in a relational database while it is not present in a database.*
•A relational database supports a distributed database while a database does not support a
distributed database.*
•In a relational database, the data values are stored in the form of tables and each table
possesses a primary key. In a database, data is normally stored in hierarchical or navigational
form.*
•Since data is stored in the form of tables in a relational database, then the relationship between
these data values is stored as well. Since a database stores data as files, then there is not
relationship between the values or tables.*
*https://searchdatamanagement.techtarget.com/definition/relational-database

Database & Relational database
•In a relational database, the integrity constraints are defined for the purpose of an ACID On the
other hand, a database does not utilize any security to protect against data manipulation.*
•While a relational database is designed to support large amounts of data and multiple users, a
database is designed to deal with small amounts of data and one single user.*
•One final, major distinction is that the data storage in a relational database is accessible,
meaning the value can be updated by the system.*
•Furthermore, the data within an RDBMS is physically and logically independent.*
*https://searchdatamanagement.techtarget.com/definition/relational-database

Database & Relational database
•https://searchdatamanagement.techtarget.com/definition/relational-database
•https://en.wikipedia.org/wiki/Relational_database
•https://dev.to/lmolivera/everything-you-need-to-know-about-relational-databases-3ejl
•https://www.codecademy.com/articles/what-is-rdbms-sql

Database - relationship
•A relationship, in the context of databases, is a situation that exists between two relational
database tables when one table has a foreign key that references the primary key of the other
table.*
•Relationships allow relational databases to split and store data in different tables, while
linking disparate data items.*
•
*https://www.techopedia.com/definition/24438/relationship-databases

Database – Relationship – one-to-one
•A row in table A can have only one matching row in table B, and vice versa.*
*https://database.guide/the-3-types-of-relationships-in-database-design/
•This is not a common relationship type, as the data stored in table B could just have easily been
stored in table A.*
•However, there are some valid reasons for using this relationship type.*
•A one-to-one relationship  can be used for security purposes, to divide a large table, and various
other specific purposes.*

Database – Relationship – one-to-many
•This is the most common relationship type.*
•In this type of relationship, a row in table A can have many matching rows in table B, but a row
in table B can have only one matching row in table A.*
*https://database.guide/the-3-types-of-relationships-in-database-design/
•In the above example, the Customer table is the “many” and the City table is the “one”.*
•Each customer can only be assigned one city. One city can be assigned to many customers.*

Database – Relationship – many-to-many
•In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice
versa.*
•A many-to-many relationship could be thought of as two one-to-many relationships, linked by an
intermediary table.*
•So in order to create a many-to-many relationship between the Customers table and the Products
table, we created a new table called Orders.*
*https://database.guide/the-3-types-of-relationships-in-database-design/

Database – Relationship
•
•https://database.guide/the-3-types-of-relationships-in-database-design/
•https://www.techrepublic.com/article/relational-databases-defining-relationships-between-database-
tables/
•https://www.lifewire.com/database-relationships-p2-1019758
•https://www.techopedia.com/definition/24438/relationship-databases
•

MS Office Access
•Microsoft Access is a database management system (DBMS) from Microsoft that combines the
relational Microsoft Jet Database Engine with a graphical user interface and software-development
tools.*
•It is a member of the Microsoft Office suite of applications, included in the Professional and
higher editions or sold separately.*
•In Access, most tasks can be performed via the graphical user interface (GUI), but more advanced
users can also develop applications using Visual Basic for Applications (VBA) code.*
•Advanced users can also use SQL code to write or modify queries.*
*https://database.guide/what-is-microsoft-access/

MS Office Access – main tasks
•Some of the main tasks performed in Access include:*
ØCreate and maintain databases and their tables.
ØSet up rules and restrictions over the type of data that can be entered into the database.
ØCreate and run queries.
ØCreate forms to allow non-technical users to enter data, search the database, and perform other
tasks.
ØCreate reports so that the data can be presented in a more readable format, or emailed, printed
out, etc.
ØCreate macros so that multi-step tasks can be performed with the click of a button (or even upon
opening the database).
ØImport and export data from external sources (such as Excel spreadsheets, .csv files, etc).
*https://database.guide/what-is-microsoft-access/

MS Office Access – tables
•To start, go to the Create tab.*
•Then, click on the Table icon.*
•A new table would be created with a default name of ‘Table1:’.*
*https://datatofish.com/create-table-access/

MS Office Access – tables
•To save your table with a different name, right-click on the tab that displays the default name of
‘Table1.’*
•Then, from the drop-down menu, choose Save.*
•Now, type a new table name within the ‘Save As’ input box.*
*https://datatofish.com/create-table-access/

MS Office Access – tables
•Steps to Add Columns to the Table Created*
*https://datatofish.com/create-table-access/

MS Office Access – table wizard
•Open Database window.*
•Click Tables / New Table in objects bar.*
•Double click Create table by using wizard. The Table Wizard will start.*
•Select fields in your new table.*
*https://www.desktopclass.com/computer-it/procedure-creating-table-using-table-wizard.html

MS Office Access – table – design view
•Datasheet View displays the table as a grid. The fields are displayed as columns, and the records
are displayed as rows. The field names are listed as the column headers.*
•Datasheet View displays the data. If this table had data, it would be displayed in the cells.*
•Each row represents a record. If the table had say, five records, there would be five rows of
data.*
•In Datasheet View, if you want to find out a field's data type, you need to select that field,
then select the Fields tab on the Ribbon. The field's data type and other properties will be listed
on the right side of the Ribbon.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_table_in_design_v
iew_in_microsoft_access.cfm

MS Office Access – table – design view
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_table_in_design_v
iew_in_microsoft_access.cfm

MS Office Access – table – design view
•Design View doesn't display any data. Therefore, there's more space available to display other
settings.*
•In Design View, the fields are listed vertically. They are listed above and below each other as
opposed to side-by-side.*
•In Design View, you can see the data type listed next to each field.*
•The way Design View works is, when you click on a field (in the top frame), the bottom frame
displays the properties for that field. You can then change these properties as required.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_table_in_design_v
iew_in_microsoft_access.cfm

MS Office Access – table – design view
•Now we will enter each field and select their data type.*
•In the first cell under Field Name add a field called GenreId. Select Autonumber as its data
type.*
•Under that field, create a new one called Genre and select Short Text for its data type.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_table_in_design_v
iew_in_microsoft_access.cfm

MS Office Access – table – design view
•Right-click on the GenreId field and select Primary Key from the drop down list.*
•This makes the field a primary key field. Once you've done this, you'll see a little key icon to
the left of GenreId.*
*https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_table_in_design_v
iew_in_microsoft_access.cfm

The end