Database Systems

What is a Database?
• “A set of information held in a
computer”
Oxford English Dictionary
• “One or more large structured sets of
persistent data, usually associated with
software to update and query the data”
Free On-Line Dictionary of Computing
• “A collection of data arranged for ease
and speed of search and retrieval”
Dictionary.com
Databases
• Web indexes • Train timetables
• Library catalogues • Airline bookings
• Medical records • Credit card details
• Bank accounts • Student records
• Stock control • Customer histories
• Personnel systems • Stock market prices
• Product catalogues • Discussion boards
• Telephone directories • and so on…
File System vs Database
Systems
Database Systems
• A database system • Database systems
consists of allow users to
• Data (the database) • Store
• Software • Update
• Hardware • Retrieve
• Users • Organise
• We focus mainly on • Protect
the software their data.
Database Users
• End users • Database
• Use the database Administrator (DBA)
system to achieve • Designs & manages
some goal the database system
• Application • Database systems
developers programmer
• Write software to • Writes the database
allow end users to software itself
interface with the
database system
Database Management
Systems
• A database is a • Examples:
collection of • Oracle
information • DB2 (IBM)
• A database • MS SQL Server
management system • MS Access
(DBMS) is the • Ingres
software than • PostgreSQL
controls that • MySQL
information
What the DBMS does
• Provides users with • DBMS provides
• Data definition • Persistence
language (DDL) • Concurrency
• Data manipulation • Integrity
language (DML) • Security
• Data control language • Data independence
(DCL)
• Data Dictionary
• Often these are all
• Describes the
the same language database itself
Data Dictionary - Metadata
• The dictionary or • The dictionary holds
catalog stores • Descriptions of
information about database objects
the database itself (tables, users, rules,
views, indexes,…)
• This is data about • Information about
data or ‘metadata’ who is using which
• Almost every aspect data (locks)
of the DBMS uses • Schemas and
mappings
the dictionary
File Based Systems
• File based systems • Problems:
• Data is stored in files • No standards
• Each file has a specific • Data duplication
format • Data dependence
• Programs that use • No way to generate
these files depend on ad hoc queries
knowledge about that • No provision for
format security, recovery,
concurrency, etc.
Relational Systems
• Problems with early • Then, in 1970,
databases E. F. Codd wrote “A
• Navigating the Relational Model of
records requires Data for Large
complex programs Shared Databanks”
• There is minimal data and introduced the
independence
relational model
• No theoretical
foundations
Relational Systems
• Information is stored • The relational model
as tuples or records covers 3 areas:
in relations or tables • Data structure
• There is a sound • Data integrity
mathematical theory • Data manipulation
of relations
• Most modern DBMS
are based on the
relational model
Database Model

• Database model defines the logical

design of data.
• Database model describes the relation
between different parts of data.
• There are three database models:
1. Hierarchical Model
2. Network Model
3. Relational Model
Hierarchical model

• Data are organized in an upside down tree

• Each entity has one parent and many
children
• Old and not used now
Network model

• Entities are organized in a graph

• Entities can be accessed through several
paths
• Old and not used
Relational model

• Data are organized in two dimensional tables

(relations)
• Tables re related to each other
• Relational Database Management System (RDBMS)
are more common model used today
Relation (Name, Attributes, Tuples)
• Attributes are the
column heading
• Each column must • Tuple is a
have a unique collection of
heading attribute value
• Number of columns • Total number of
is called the degree rows is called
of the relation Cardinality of
the relation
• Each relation must
have a unique
name

Name

• Relation appears in 2 dimensional table

• That doesn’t mean data stored as table; the
physical storage of data is independent of the
logical organization of data
Microsoft Office
Access 2013

Microsoft Access

19
Microsoft Office
Access 2013

Table Basics
• A table is a collection
of data about a
specific topic
• The data is organized
into rows and
columns
• Each column is a field
and each row is a
record

20
Microsoft Office
Access 2013

Planning Tables
• A well-planned database • Remember that a table
ensures that data is should store data about
complete, unique and one thing, and only one
accurate thing
• Be sure to have a clear
picture of how the
database will be used
• Begin by listing the data
you want to capture
– The subject matter
categories that are
generated in your list will
become the tables

21
Microsoft Office
Access 2013

Planning Fields
• Use the smallest fields that
make sense – this allows
you great flexibility for
sorting and searching the
data
• Remember that it is easier
to build in flexibility during
the design stage than it is to
redesign tables after records
have been added or queries
and reports have been
created based on table data

22
Microsoft Office
Access 2013

Naming Rules and Conventions

• A table name can be up to 64 characters long
• A field name can be up to 64 characters long
• Avoid using special characters (punctuation marks,
slashes, backslashes, etc.) in table names, field names or
other object names
• Embedding spaces into a table name or field name can
lead to complications in creating expressions
– Use camel case, or an underscore (_) to represent a space
• Use meaningful names
• Each table within a database must have a unique name
• Each field within a table must have a unique name

23
Microsoft Office
Access 2013

A Model of Table Structure

• The Information Engineering
(IE) model is a standard model
for representing table structure
• In the IE model, a table is
represented by a rectangle
– The name of the table appears
at the top of the rectangle
– Each field is listed beneath the
table name

24
Microsoft Office
Access 2013

Data Types
• The properties of a field describe the characteristics and
behavior of the data entered into that field
• A field’s data type determines the type of data the field
can store
• A field can contain only type of data
• You set the data type for a field in Table Design view

25
Microsoft Office
Access 2013

Data Types – Defining

• Use the upper section of the
Table Design view window to
specify field names, data
types and descriptions
• The field name and data type
are both required attributes
• Use the lower section of the
window (the Field Properties
pane) to specify additional
properties

26
Microsoft Office
Access 2013

Data Types in Access 2013

Short Text Stores up to 255 characters of text, a combination of text and
numbers or numbers that will not be used in calculations
Long Text Stores a block of text or a combination of text and numbers
(stored as text) longer than 255 characters
Number Stores a numeric value that is not a monetary value. Use this
type if you will perform calculations on the field
Date/Time Stores time-based data
Currency Stores monetary data. Data stored in this type of field is not
rounded off during calculations and the field is accurate up to
15 digits to the left of the decimal point and 4 digits to the right.
AutoNumber Automatically provides a unique numeric value for anew record
when it is added to a table. This data type is used in ID fields.
Yes/No Stores True or False values. A field with this data type can
contain only one of two values: Yes/No, True/False or On/Off.

27
Microsoft Office
Access 2013

Data Types in Access 2013

OLE Object Used to attach an Object Linking and Embedding (OLE) object
to a record. Use the Attachment data type to attach most types
of files that do not require OLE. OLE fields do not allow you to
attach multiple files to a single record.
Hyperlink Used to store a hyperlink (up to 2048 characters), such as an
e-mail address or a Web site URL.
Attachment Used to attach multiple files (such as images, sound files, Excel
spreadsheets or Word documents) to a record.
Calculated Performs calculations on fields within the table. A calculated
field cannot perform calculations that involve fields from other
tables.
Lookup Wizard Displays either a list of values that is retrieved from a table or
query, or a set of values you specify when you create the field.

28
Microsoft Office
Access 2013

Field Size Property

• Field properties control how a field’s
data is stored, handled or displayed
• The data type of the field
determines which field properties
are available for that field
• The Field Size property specifies the
maximum size for a field, and
directly affects the storage and
memory requirements for each
record
• The Field Size property is accessible
in the Field Properties pane of the
Table Design window

29
Microsoft Office
Access 2013

Field Size Property for a Text Field

• The default field size for a text field is 255 characters
• Specifying a field size for a text field limits the number of
characters that can be stored in the field
– If you know that a field will store only a limited number of
characters, for example, a State field would store only two
letters, change the field size for the State field to 2 to give
users a visual clue of what type of data is expected

30
Microsoft Office
Access 2013

Field Size Property for Number and

AutoNumber Fields
Byte Stores whole numbers for 0 to 255 in 1 byte of storage
Integer Stores whole numbers between -32,768 and 32,767 in 2 bytes
of storage
Long Integer Stores whole numbers between -2,147,483,648 and 2,147,483,647
in 4 bytes of storage
Single Stores numbers from -3.4 x 10308 to 3.4 x 10308 in 4 bytes of
storage. Allows fractions and provides decimal precision to 7
digits.
Double Stores numbers from -1.798 x 10308 to 1.798 x 10308 in 8 bytes of
storage. Allows fractions and provides decimal precision to 15
digits.
Replication ID A 16-byte field used to establish a unique identifier for replication
Decimal Stores numbers from -9.999 x 1027 to +9.999 x 1027 in 12 bytes
of storage

31
Microsoft Office
Access 2013

Table Views
• Datasheet view – used primarily for working with table
data (although you can create tables in Datasheet view).
This is the “open” view for a table
• Design view – used to create, view or edit a table’s
structure. You cannot see the data in Design view.

32
Microsoft Office
Access 2013

Creating Tables in Design View

• The Table Design view

window has an upper
and lower pane
• Type field names, data
types and an optional
description in the
upper pane
• Set field properties in
the Field Properties
pane (the lower pane)

33
Microsoft Office
Access 2013

Modifying Tables in Design View

• You can use Table Design view to change field names,
change field types and change field properties
• You can also add fields, delete fields, insert fields or
rearrange fields

34
Microsoft Office
Access 2013

Primary Keys
• A primary key is a field (or combination of fields) that
uniquely identifies each record stored in a table
• No two records in a table may be exact duplicates – two
records may, however, contain duplicate data other than
the primary key
• Primary key must be unique
• Primary key cannot contain a null value

35
Microsoft Office
Access 2013

Selecting a Primary Key

• A primary key should be
– A value that will never change
– A value that is extremely unlikely to be null
• Poor choices include: people’s names and social security numbers
• Good choices include unique identifiers issued by an organization or
an ID field
• When you use an ID field as a primary key, you can manually enter
the ID number or you can elect to have Access create ID numbers
automatically by setting the data type to AutoNumber
• To specify a field as a primary key select the field in Table Design
view, then click the Primary Key command button in the Ribbon

36
Microsoft Office
Access 2013

Importing Data
• You can import data to: • You can import data from:
– A new table – Access databases
– An existing table – SQL Server databases
– A linked table – Excel spreadsheets
– Text files
– XML files
– HTML documents
– Outlook folders
– SharePoint lists

37
Microsoft Office
Access 2013

Importing Tables from Other Databases

• Click the External Data tab
• In the Import & Link group, click Access to launch the
Get External Data Wizard
• Specify a source database file
• Select the database objects you want to import
• Click OK

38
Microsoft Office
Access 2013

Importing Data from Excel

• Click the External Data tab
• In the Import & Link group, click Excel to launch the Get
External Data Wizard
• Specify a source database file
• Specify whether you want to:
– Import source data to a new table
– Append a copy of the records to an existing table
– Create a linked table
• Follow the instructions on the remaining wizard screens

39
Microsoft Office
Access 2013

Importing Data to a New Table

• Access creates the new table during the import process
• If a table of the same name already exists, it will be
overwritten
• Imported data is copied from an outside source – it will
not be automatically updated if the source file is updated
• Changing data in the Access table does not affect the
source file

40
Microsoft Office
Access 2013

Appending Records to an Existing Table

• Use the Get External Data wizard
• The append operation does not overwrite data in the
existing table
• The structure and data types in both the source and
destination files must match

41
Microsoft Office
Access 2013

Importing Data as a Linked Table

• Each time the source file is updated, the linked table
automatically reflects the most recent changes
• In most scenarios, linking allows you to add and update
data either in the source file or in the linked table.
Exceptions are as follows:
– When you link to data in a text file, you can add new
records in Access, but not update records
– When you link to an Excel workbook or a Data Services file,
the linked table is a non-updatable recordset. If you want
to add, edit or delete data, you must make the changes in
the source file.

42
Microsoft Office
Access 2013

Working in Datasheet View

43
Microsoft Office
Access 2013

Working with Records – Navigating a Datasheet

44
Microsoft Office
Access 2013

Adding, Updating and Deleting Records

• Use the arrow keys, the Tab key and the mouse to move
from field to field
• As you enter new or revised data into a table record, a
pencil icon displays in the record selector to indicate that
the record contains unsaved changes
• When you reach the end of a record, you can press Tab
or Enter to automatically save the current record and
move the cursor to the next record
• You can also press Ctrl+S to save changes from
anywhere in the datasheet
• You must confirm deletions

45
Microsoft Office
Access 2013

Table Relationships

• A column in a table
that references the
primary key of
another table is
called a foreign key
• Foreign keys and
primary keys
represent data
relationships

46
Microsoft Office
Access 2013

Table Relationships
• A relationship is made by matching data in key fields
between two tables
• When creating relationships, remember:
– Related fields may or may not have the same name but
must have the same data type
– When related fields are Number fields, they must have the
same Field Size property setting
– To define a relationship using an AutoNumber primary key
field, the foreign key must be a Number field with a Field
Size property setting of Long Integer

47
Microsoft Office
Access 2013

Relationship Types
One-to-one A relationship in which each record in Table A can have only
one matching record in Table B, and vice versa.
One-to-many A relationship in which a record in Table A can have many
Parent-child matching records in Table B, but a record in Table B has
only one matching record in Table A. The relationship is
established only if the common field is the primary key in
Table A and the foreign key in Table B.

Many-to-many A relationship in which one record in either Table A or Table

B can relate to many matching records in the other table.
Relational databases cannot directly handle many-to-many
relationships, and these must be replaced by multiple one-
to-many relationships.

48
Microsoft Office
Access 2013

Representing table relationships

• In an IE diagram, you
represent relationships by
drawing lines between the
key fields
• The “one” side is indicated
by drawing a 1 beside the
parent key
• The “many” side is
indicated by drawing an
infinity symbol (∞) beside
the foreign key

49
Microsoft Office
Access 2013

Creating Relationships in a Database

• Open the Relationships
window
• Add tables
• Drag the primary key field
from the parent table
onto the foreign key field
in the related table to
open the Edit
Relationships dialog box

50
Microsoft Office
Access 2013

Table Relationships – Join Properties

• Option 1 is an INNER join.
This is the default join
type – it includes rows
where the joined fields
are equal
• Option 2 is a LEFT OUTER
join
• Option 3 is a RIGHT
OUTER join

51
Microsoft Office
Access 2013

Table Relationships – Join Properties

• Option 1 is an INNER join.
This is the default join
type – it includes rows
where the joined fields
are equal
• Option 2 is a LEFT OUTER
join
• Option 3 is a RIGHT
OUTER join

52
Microsoft Office
Access 2013

When referential integrity is enforced …

• You cannot add a record to a related table unless a
matching record already exists in the parent table
• You cannot change the value of the primary key in the
parent table if matching records exist in a related table
• You cannot delete a record from a parent table if
matching records exist in a related table

53
Microsoft Office
Access 2013

Viewing Related Data

• When you define relationships, you can see related data
in a sub-datasheet in Datasheet view

54
Microsoft Office
Access 2013

Review Questions
1. For a relationship between fields in two tables to be created, the
fields must:
a. have the same data type.
b. have the same name.
c. be stored in separate databases.
d. be defined as lookup fields.

2. Which of the following is the smallest Field Size setting that will
allow a number field to store the value 1,452,137.5?
a. Integer
b. Long Integer
c. Single
d. Double

55
Microsoft Office
Access 2013

Review Questions
3. What is the default Field Size setting for a Short Text field in
Access?
a. 12
b. Single
c. Double
d. 255

4. A primary key:
a. should use social security numbers whenever possible
b. cannot be a composite key.
c. should be null whenever possible.
d. should be a value that will never change.

56
Microsoft Office
Access 2013

Review Questions
5. Kevin needs to update a field in a table that is linked to data in an
Excel workbook. He should:
a. delete the source data file so that it does not interfere with the
linked table.
b. adjust the value directly in the linked table.
c. adjust the value in the source data file.
d. run the linked table update manager.

57