Course syllabus
Course information
Lecturer: Dr Loumngam
Duration : 32 hours
Class : BM 2
Prerequisite : None
Objective of the course
In this course, you will create relational databases,
write SQL statements to extract information to
satisfy business reporting requests, create entity
relationship diagrams (ERDs) to design databases,
and analyze table designs for excessive
redundancy. As you develop these skills, you will use
MS ACCESS to execute SQL statements and create
ERDs a database
Pedagogical approach
Lectures
Discussion base on lecture notes
Tutorials (exercises and case studies), group
discussions and presentations
Practical works in lab
Course outline
Lectures + Tutorials:
Introduction to database and database
management systems.
Relational databases.
Conceptual data modeling (Entity-Relationsip
Diagram).
Logical database design (schema conversion and
normalization).
Course outline
Practical works: Practice of relational databases with
MS ACCESS :
Creation and management of a relational database
Creation of data entry forms
Problem solving
Application of SQL queries
Process of evaluation
Continuons assessment (Tests + workshops) : 40 %
Final exam: 60% (duration : 2h00)
Calculation of the final grade : Theoritical part
(40%) + Practical work (60%)
In case of problem with your marks, do not contact
your teacher directly, but the academic secretary
Lab’s rules
Please do not eat in the lab
Throw the garbage in the trash
Turn off your computer before leaving the
lab
Put your chair in order before leaving the
lab
Introduction to database and data
base management systems
What is database?
A database is an organized collection of data.
“A database may be on paper, or held in computer files
such as spreadsheets or more formally in a software system
known as a computerized database management system
Examples of record keeping system:
File system on a computer
Word document
Excel Spreadsheet
Access database
Manual card index file
Files on a USB stick
Are these all databases?
Advantages of a database
Database systems are not just a mass of data
It isn’t just about what they can hold
They are organised
So we need a more precise definition
More detailed definition?
A database (DB) is a collection of information that is
organized so that it can easily be accessed, managed,
and updated. A database integrates collection of data
records, files, and other objects.
You can visualize it as a container of information
For example, suppose if you own a stationary shop, you
need to keep detailed records of the materials
available in your shop. You also need to store
information about pricing, stock levels for reordering,
old stocks, etc. While in the manual system, you would
maintain several files with different bits of information
More detailed definition?
“A database is an organized collection of logically
related data”.
Organized because:
Data is structured so as to be easily stored,
manipulated and retrieved by users.
It is no good just having some data if we don’t know
how to get it in order to look at it and use it
More detailed definition?
“A database is an organized collection of logically
related data”.
Logically related because:
Pieces of data do not exist in isolation
For example: In a salesperson’s database, it is natural
for the customer’s name and the customer’s address to
be stored together. They are related, together, with
other data about the customer, they are part of a
meaningful set.
More detailed definition?
“A database is an organized collection of logically
related data”.
Data are any raw facts, numbers, or text that can
be processed by a computer.
Database basic functions
Databases should be able to:
Store
Manipulate
Retrieve
Other functions of database
Organize and analyze information in different ways
– Sorting
– Grouping
– Querying
– Reporting
– Exporting for statistical analysis
Computerized database
– Speed
– Quality control
– Precision
– Automate repetitive tasks
Advantages of a database
Collection of data in a centralized location
Controls redundant data
Data stored so as to appear to users in one location
Type of databases
Data can be organized into two types:
Flat File: Data is stored in a single table. Usually
suitable for less amount of data.
Relational: Data is stored in multiple tables and
the tables are linked using a common field.
Relational is suitable for medium to large amount of
data.
Type of databases
In computing, databases are also sometimes classified
according to their organizational approach.
The relational database, this is the most prevalent
approach. It is a tabular database in which data is
defined so that it can be reorganized and accessed in
a number of different ways.
A distributed database is one that can be dispersed or
replicated among different points in a network.
An object-oriented programming database is one that
is congruent with the data defined in object classes and
subclasses
Databases versus Excel
Many databases offer audit functions required by certain
regulatory agencies
• Tracks date record created and modified
• Tracks original and changed values
• Requires user to give reason for the change
Databases are more suitable for importing data from multiple
sources
• More robust in connecting to different data sources
• Imports of different data types into different tables can be
linked via common identifiers such as subject ID
• Merging multiple data sources into Excel so that the rows line
up properly in a flat file format can be a challenge
DBMS versus Excel
Excel has some limited capabilities to sort data but its primary
function is to create financial spreadsheets
– Can create “what if” scenarios to determine financial
consequences
– Can be used for small and limited research data sets and
simple lists
– Not multi-user such that only one person can work on the file at
a time
DBMS versus Excel
Databases are designed to collect, sort, and manipulate data
– Data sets can process large amounts of data and is usually limited by
hardware constraints
– Structure is in the same format for each member record of a table
– Data quality control features ensure that valid data is entered
– A relational database allows for linking of an unlimited number of tables
– Databases are multi-user because the data can reside on a server and
multiple people can have access at the same time
– Many databases offer web interfaces thereby eliminating the need for
each user to have a copy of the program on their computer
Relational databases
Relational Database
Relational databases systems are systems where
data is organized into relations.
Relational databases were introduced by E. F.
Codd in 1969.
A relation comprises a set of tuples where each
tuple has the same number of attribute values,
where each attribute value is taken from some
corresponding domain, and where a domain
represents a set of valid values for an attribute.
Relational Database
A relation comprises a set of tuples where each tuple has the
same number of attribute values, where each attribute value
is taken from some corresponding domain, and where a
domain represents a set of valid values for an attribute.
Relational Database
Relations are typically implemented in commercial databases
as tabular structures comprising rows and a fixed number of
columns.
Relational Database
Example: a database with one relation, the list of
employees
Employees
Employee ID First Name Last Name Gender
123 Gambo Amani Male
333 Ze Ondoa Male
456 Tombi Aroko Female
842 Bouba Gomena Female
777 Cosmas Taokreo Male
Relational Database
This looks just like a two dimensional table of rows
and columns. The name of the table is Employees,
each column of the table has its own title, and each
row has the same structure.
Employees
Employee ID First Name Last Name Gender
123 Gambo Amani Male
333 Ze Ondoa Male
456 Tombi Aroko Female
842 Bouba Gomena Female
777 Cosmas Taokreo Male
Relational Database
Each row has a value for employee number, first
name, last name, and gender.
Employees
Employee ID First Name Last Name Gender
123 Gambo Amani Male
333 Ze Ondoa Male
456 Tombi Aroko Female
842 Bouba Gomena Female
777 Cosmas Taokreo Male
Relational Database
Information structured in tables is very concise; at a
glance we can obtain useful information.
Employees
Employee ID First Name Last Name Gender
123 Gambo Amani Male
333 Ze Ondoa Male
456 Tombi Aroko Female
842 Bouba Gomena Female
777 Cosmas Taokreo Male
Relational Database
The Employees table can be considered a relation
of 5 tuples where each tuple has 4 values drawn
from each of the employee identifier, first name,
last name, and gender domains.
Employees
Employee ID First Name Last Name Gender
123 Gambo Amani Male
333 Ze Ondoa Male
456 Tombi Aroko Female
842 Bouba Gomena Female
777 Cosmas Taokreo Male
Relational Database
A database designer must be able to express
structured information as verbalizations: i.e we use
values from a single row to create complete
statements that explain the meaning of a row.
Employees
Employee ID First Name Last Name Gender
123 Gambo Amani Male
333 Ze Ondoa Male
456 Tombi Aroko Female
842 Bouba Gomena Female
777 Cosmas Taokreo Male
Relational Database
A verbalization that fits the information in one row
of the Employees table is:
Employee with ID … has a first name …, a last
name …, and is of … gender
Employees
Employee ID First Name Last Name Gender
123 Gambo Amani Male
333 Ze Ondoa Male
456 Tombi Aroko Female
842 Bouba Gomena Female
777 Cosmas Taokreo Male
Advantages of Relational Database
The relational databases have a much more logical
structure in the way that it stores data. Tables can
be used to represent real world objects, with each
field acting like an attribute.
One major advantage of the relational model is
that, if a database is designed efficiently, there
should be no duplication of any data; helping to
maintain database integrity. This can also represent
a huge saving in file size, which is important when
dealing with large volumes of data.
Relational Database
Relational databases also have "built in" functions
that help them to retrieve, sort and edit the data in
many different ways. These functions save script
designers from having to worry about filtering out
the results that they get, and so can go quite some
way to speeding up the development and
production of web applications.
Avantages of Relational Database
Elimination of Multiple Value Data – a relational
database allows creation of relationships for
subordinate data.
Avoiding Update Anomalies – since data is stored in
only one place, it is easy to update (no other copies to
remember to update).
Avoiding Data Entry Anomalies – like updates, since
data is only stored in one place, it needs to be inserted
in one place.
Avoiding Data Deletion Anomalies – once again, since
data is in one place only, it is deleted only once.
Relational Database design
To design a database, a database engineer needs to
find good representations of how an organization uses
data.
Good sources include: input forms, reports, web pages,
etc. A challenge for database designers is to find these
sources and interpret them.
Activity n°1
Consider the following table of product information sold
by ABC Foods. Verbalize the information presented.
Activity n°2
Consider the following report that the Human Resources
department of ABC Foods must produce. Verbalize the
information in that report.
Activity n°3
Suppose the following input form is used to enter contact
information. Verbalize the information that is being
collected.
Database Management System
Two-File Processing System
The example used is of a car rental system.
One system processes CUSTOMER data, and
the other processes RENTAL data.
Each of the files and the applications that
use them are totally separate.
Although this is an improvement over older
manual systems, there are a number of
problems.
Two-File Processing System
Two-File Processing System
Data are separated and isolated.
Data are often duplicated.
Problem of Data integrity (meaning of data)
Application programs are dependent on file
formats.
Problem of Data Security.
It is difficult to represent data in a user’s
perspective.
Basic structure with DBMS
DBMS serves as intermediary between user and the
database by translating user requests into the
complex code required to fulfill those requests.
The notion of DBMS
A Database Management System (DBMS) is an
application software whose purpose is to create and
manage databases.
This is a software system which facilitates the formation,
maintenance as well as use of an electronic database. It
permits organizations to suitably develop databases for
a range of applications by database administrators and
certain specialists.
Example of DBMS: Microsoft Access, OpenOffice.org
Base, MySQL, SQL Server, Oracle, IBM DB2, Postgre,
SQLite.
Microsoft Access
MS Access is a relational database system for
workstations that run the Microsoft Windows operating
system. MS Access is typically used by individuals for
data they use personally, but in some situations a single
MS Access database may be used by a group of people
or small department.
MS Access databases are stored in a single file that has
a file suffix of “.accdb” or “.mdb”. Databases created
using MS Access 2007 and later have a file suffix
“.accdb”, and databases created using MS Access 2003
or earlier have a file suffix “.mdb”. We will be using
databases where the files have names ending in
“.accdb”.
Access Environnent
Access Environnent
Access Environnent
Access Environnent
Access Environnent
Access Environnent
Access Environnent
Access Environnent
Access Environnent
Create a database from a template
Before building a database from scratch, look at
the templates included in Access to see if any of
them match your needs.
1. Select
the File tab.
2. This will take
you
to Backstage
view.
Create a database from a template
Several templates
will appear below
the Blank desktop
database option
Create a database from a template
You can also click
a suggested search to find
templates or use
the search bar to find
something more specific.
3. Select a template to
review it. A preview of the
template will appear, along
with additional
information on how the
template can be used.
4. Click Create to use the
selected template.
Opening an existing database
Select the File tab to go to Backstage view.
Click Open.
Click Browse.
The Open dialog box will appear. Locate and
select the database, then click Open.
Opening an existing database
One or more warning messages may appear when
you open your database. If the database contains
customized functions, a yellow bar with a security
warning may appear below the Ribbon. If you trust the
source of your database, click Enable Content for your
database to display correctly.
After enabling all content in the database, you may see
a message asking if you want to make the database
a Trusted Document. Click Yes if you would like all
content to be automatically enabled each time you
open the database.
MS Access Objects
Databases in Access are composed of four
objects: tables, queries, forms, and reports.
Together, these objects allow you to enter, store,
analyze, and compile data however you want.
Working with objects
Access treats each of its objects (Tables, Queries,
Forms and reports) as separate documents, which
means you will have to open and save them
individually in order to work with them.
There is no instructions for saving a database
because you cannot save an entire database at
once. Rather, you must individually save the objects
contained within the database.
Working with objects: opening
In the Navigation pane, locate and double-click the
desired object.
The object will appear as a tab in the Document
Tabs bar. By default, the most recently opened
object will appear in the main window as
the current object. To view another open object,
click its tab in the Document Tabs bar.
Working with objects: saving
Select the object you want to save by clicking its tab
in the Document Tabs bar.
Click the Save command on the Quick Access
Toolbar, or press Ctrl+S on your keyboard.
The first time you save an object, you will be
prompted to name it. Enter the desired object name,
then click OK.
The object will be saved. Click the Save command
again to save any changes to the object.
Working with objects:
You can also Close or Rename an object just by
right-clicking its tab on the Document Tabs bar or in
the navigation bar.
Activity based on the workfile
Open the workfile ‘access2016sampledatabase’.
Click through all of the tabs, and notice how the
options on the Ribbon change.
Minimize the Ribbon, then Maximize it again.
Resize the Navigation pane to make it take up less
room.
Re-sort the objects in the Navigation pane.
Activity based on the workfile
Open an object.
Close the object.
Rename the object.
Close the database without saving your changes.
Activity based on the workfile
Recall that an elementary verbalization is one where
the verbalization cannot be simplified in any further
way. Simpler statements would result in a loss of
information.
1. Write the verbalization for the Customer table using
elementary verbalizations.
2. What verbalizations apply to the Menu items table
in the access2016sampledatabase database?
3. What verbalizations apply to the Products table in
the access2016sampledatabase database?
Activity based on the workfile
In the Library database, View the data in
the Loan table. Each row in the table corresponds to a
member borrowing a book. Notice how the call
number field contains values that appear in the Book
table and how the id field contains values that
appear in the Member table. All rows have a value
for the data borrowed field. Why would some of the
date returned fields appear to have no value at all?
Table
Tables are good for storing closely related information. Let's say you
own a bakery and have a database that includes a table with your
customers' names and information, like their phone numbers, home
addresses, and email addresses. Because these pieces of information
are all details on your customers, you’d include them all in the
same table. Each customer would be represented by a
unique record, and each type of information about these customers
would be stored in its own field. If you decided to add any more
information—say, a customer's birthday—you would simply create a
new field within the same table.
Understanding tables
All tables are composed of horizontal rows and
vertical columns, with small rectangles
called cells in the places where rows and columns
intersect. In Access, rows and columns are referred
to as records and fields.
Table
In Access, all data is stored in tables, which puts tables at the
heart of any database. tables are organized into
vertical columns and horizontal rows.
To open an existing table
Open your database, and
locate
the Navigation pane.
In the Navigation pane,
locate the table you want to
open.
Double-click the desired
table
How to navigate a table
How to navigate a table
How to navigate a table
How to navigate a table
How to navigate a table
Table
In Access, rows and columns are referred to as records and fields.
A field is more than just a column; it’s a way of organizing information by the type of
data it is. Every piece of information within a field is of the same type. For example,
every entry in a field called First Name would be a name, and every entry in field
called Street Address would be an address.
Likewise, a record is more than just a row; it's a unit of information. Every cell in a
given row is part of that row’s record.
Table
The number at the left of each row? It’s the ID number that identifies
each record. The ID number for a record refers to every piece of
information contained on that row.
Although tables store all of your data, the other three objects—
forms, queries, and reports—offer you ways to work with it. Each of
these objects interacts with the records stored in your database's tables.
Activity
Open the Customers table.
Add a new record to the table. Be sure to enter
data for every field.
Find the record with the name Sula Smart,
and replace it with a name of your choice.
Hide a field, then unhide it.
Change the alternate row color.
