Fundamentals of Databases: Microsoft Access Week 1

1
Fundamentals of DatabasesMicrosoft AccessWeek 1
2
Building successful applications

• Welcome to CIF 102/ FIF 102
• In this module you will learn how to analyse the
  data in a system in order to build a robust
  database.
• You will build and test the system using
  Microsoft Access.
• The end product will be a fully functional
  application with a user-friendly interface that
  is capable of querying the data and outputting
  the results through reports.

3
Outcomes from the Week 1 lecture

• To be able to distinguish between flat data
  sheets and relational data models
• 2. To identify entities in a system
• 3. To be able to describe the cardinal
  relationships between entities

4
Outcomes from the tutorial

• 1. To be able to open the Access application,
  use the menu to choose design view
• To be able to set up tables with primary and
  foreign keys and relevant data types
• To be able to validate data

5
Introduction to the module content

• Outcomes from this module-these are statements of
  the learning you will gain by the end of the
  module.
• Assessment what you need to hand in and pass for
  success in the module.
• Outline of course a brief overview of the topics

6
Learning outcomesKnowledge

• 1. An understanding of concepts of relational
  databases.2. An understanding of introductory
  SQL (Structured Query Language)3. An awareness
  of Interactive System Design.4. Introductory
  systems analysis and design techniques (including
  data modelling, entity data modelling, entity
  modelling and fact finding techniques).5. An
  appreciation of the importance of simple team
  management concepts.

7
Weeks 1-8Basic principles of Access design
8
Weeks 9-1Queries and reports
9
Added features and functionality
10
Assessment

• 20 Individual - Time Constrained Test (TCT1)
  Systems Analysis
• 20 Individual - Time Constrained Test (TCT2)
• Practical database design
• 60 Group project - database application

11
What is an Access Database?

• Access is a database management system that
  stores and manages data or information. Data are
  stored in tables that have been modelled
  logically to avoid duplication of data. These
  tables and their data are linked by relations
  that enables the user to search for and retrieve
  data.
• There are four main components in Access that you
  will be using
• Tables
• Queries
• Reports
• Screen forms
• Access is a relational database because it allows
  you to relate data from different tables.

12
Data models

• You will be building a relational database
  management system (RDBMS) but there are other
  data models used in database systems
• Typical data models
• Flat
• Hierarchical
• Network
• Relational
• Object-oriented
• For a light-hearted approach to explaining data
  models using a food metaphor see JW Riders
  account on the www at
• http//www.jwrider.com/lab/gourmetdata.htm

13
Gourmet's Guide to Data Modelsby J. W.
Riderhttp//www.jwrider.com

• Information merely doesn't lay there in a data
  base waiting to be scooped out and plopped onto
  some consumer's plate.
• Information varies in consistency and richness.
  Different parts of information go better together
  than others. A number of data model archetypes
  have been proposed for organizing information.
  These archetypes are much like culinary
  techniques in a couple of important ways.

14
Flat fileWhy is a flat file like a slice of
bread?

• If a text file is like a whole loaf of bread,
  then flat files slice up the loaf. You might also
  slice up the salami, but you're talking about a
  separate flat file.
• Loosely, flat files view information as an
  ordered collection of "records." Well-defined
  subsets of each record are known as "fields."
  Each data field encapsulates a well defined data
  type (TEXT, NUMBERS, DATES) and contains a single
  value.
• Carving something into smaller pieces makes both
  food and information easier to manipulate

15
Data modelling

• How data are organised is important for being
  able to search and retrieve data efficiently
• The way data are organised in data systems is
  called data modelling
• We are not going to consider other models further
  but it is important to understand the difference
  between flat files and RDBMS

16
Flat files

• e.g.Text file, Spreadsheet file
• A text editor can be used to access/update
  information. There are no restrictions on what
  kinds of data may be stored or how long the
  individual data items may be.
• Many people use a spreadsheet file to store data,
  search and find data.
• In flat files, records are said to exist at the
  same level.
• Question think of data stored in a spreadsheet
  or a text file. Can you think of disadvantages of
  searching for data in flat files?

17
Relational data model / databases, Relational
theory

• Relational database design / normalisation the
  efficient organisation of data to avoid data
  duplication and enable more efficient database
  operation
• Relational theory provides a firm mathematical
  foundation for data management- however at this
  stage we are going to look only at the practical
  implementation of data in a relational database.

18
Entities and the functional dependency between
them

• A database based on the relational model
  developed by E.F. Codd. A relational database
  allows the definition of data structures, storage
  and retrieval operations and integrity
  constraints.
• In such databases the data and relations between
  them are organised in tables. A table is a
  collection of records and each record in a table
  contains the same fields. Tables can be
  considered entities in a system so e.g Tutor and
  Module are both entities.
• A tutor teaches many modules in one academic
  year. In each table you can identify one
  attribute that all the others are dependent on
  (functional dependency). For example, in the
  Tutor table, all attributes are dependent on the
  Staff_no.
• Functional dependency for Tutor table is Name
  Title, Address, Telephone_no
• Activity 1- write down the functional
  dependencies for Module.

MODULE
TUTOR
Staff_no Name Title Addresss Telephone_no
Module_code Module _name Value
19
Primary and foreign keys

• Every table in a relational database must have a
  unique attribute. This attribute cannot exist in
  any other table (except as a foreign key). A
  foreign key is an attribute that exists in a
  parent entity and that is needed for a relational
  link.
• In the Tutor and Module example the primary keys
  will be Staff_no and Module_code respectively.
• The usual convention when designing databases is
  to underline the primary key and put an asterix
  next to the foreign key. This happens in the
  design documentation, not in the database itself.
• Activity 2 Use the following notation for the
  cardinal relationships between the entities
  (one-to-one, one-to-many, many-to-many) to draw
  the relation between the Tutor and Module
  entities.

TUTOR
MODULE
Staff_no Title Name Address Telephone_no
Module_code Module_name Value Staff_no
20
Cardinality see p27, Warrender, 2003

• Cardinality is the specific name given to the
  number of entities of one type that can be
  associated with the number of entities of another
  type. Also called the degree of the relationship.

21
Examples of records in a table
Fields
TUTOR
1st record
2nd record
22
How to Relate data in an information system

• In the next lecture you will be introduced to the
  Flatmates Case Study.
• In the accompanying tutorial you will build this
  database and link the tables.