Transcript Database_Session One_P1

Unit 18: Database Modelling
Session One Part One
1
Aims:
Discuss unit content and assessment
Discuss what is a database
Research who uses a database and why?
Discuss purpose and features of a relational database
Learn some terminology focused around databases
2
What is covered?
Understand the features of relational databases
Features: entities; attributes; relationships; benefits
Entities: key fields eg primary keys, foreign keys; referential integrity; auto incremented keys; field
attributes; data redundancy
Attributes: field properties eg data types, size, validation rules
Relationships: one-to-many; one-to-one; many-to-many; normalisation
Benefits: reduced data redundancy; other eg reduced data storage, faster access, efficient updating,
searching, sorting, reporting
3
What is covered?
Be able to design, create and populate a relational database
Design: relationships; tables; queries; data entry forms; reports; design documentation eg DFds, ERDs,
data dictionaries, structured English
Creating relationships: normalisation (first, second and third normal forms); modifying; cascading updates;
cascading deletes
Query design: selection of data types; use of logical operators eg AND, NOR, NOT
Data entry forms: verification routines; validation routines eg input masking, checks for completeness, data
consistency; data redundancy; visual prompts; dropdown; combo boxes
Populate: data entry; importing data from external sources eg other databases, spreadsheets, text files
Exporting data: query results; report results; destination eg spreadsheet, database
Advanced features: creating styles for fields, tables, forms, reports; creating styles to match user need;
consistency eg using tool box; customising: menus and toolbars eg use show/hide functionality, add
buttons; automated functions eg macros, scripts, program code
Errors: reasons eg different data types, poor design, inconsistent normalisation; rectification
4
What is covered?
Be able to test a relational database
Testing: test plan; functionality; against user requirements; customer acceptance
Evaluation criteria: fit for purpose; justification of features used; suggestions for improvements
5
How will you be assessed?
One Assignment
Practical observations
6
What is a database?
Store information efficiently and reliably so that the information can be retrieved on demand in the
form and structure required.
A database is a structured collection of records or data that is stored in a computer system. The
structure is achieved by organizing the data according to a database model.
http://en.wikipedia.org/wiki/Database
A collection of information organized in such a way that a computer program can quickly select
desired pieces of data. You can think of a database as an electronic filing system.
http://www.webopedia.com/TERM/d/database.html
7
Who uses a database?

When a database holds details about people, it's likely to include their first name, surname and
their date of birth. In addition to this, specialist information is stored depending on the database's
intended use.
 The police have details of all known criminals in a database, e.g. crimes they've
committed.
 Schools use a database to store details about their pupils, e.g. how many days they've
been off school sick.
 A hospital will store details of all its patients in a database, e.g. a history of their health
issues.
 The Government uses a database to store records of people's income tax payments.
 A database is used to keep track of all the drivers in central London who have (or
haven't) paid the Congestion Charge.

In the case of the Congestion Charge database, if someone hasn't paid the congestion charge, a
fine will be issued. The database would play an integral part in automating the process.

Information stored in the other databases listed above could be used in similar ways.
8
Why use a database?

Databases can store very large numbers of records efficiently (they take up little space).

It is very quick and easy to find information.

It is easy to add new data and to edit or delete old data.

Data can be searched easily, eg. 'find all Ford cars‘

Data can be sorted easily, for example into 'date first registered' order.

Data can be imported into other applications, for example a mail-merge letter to a customer saying that an MOT
test is due.

More than one person can access the same database at the same time - multi-access

Security may be better than in paper files.
http://www.bbc.co.uk/schools/gcsebitesize/ict/databases/2databasesrev4.shtml
9
Why use a database?

Minimise unnecessary data redundancy (duplications)

Ease of modification

Simple design

Keep data consistent, data is only stored once

Can represent real world entities
10
What is a DBMS?
Database Management System
 The DBMS is a software provided by the database vendor.

Software products such as Ms Access, Oracle, MS SQL Server; are all DBMS

The DBMS provides all the basic services required to organise and maintain the database, these
include:
 Moving data to and from data files as needed
 Managing data when access is required by multiple users
 Managing transactions so that each transaction’s database change are an all- or- nothing
unit of work
 Support for a query language in order to retrieve data
 Provisions for backing up the database and recovering from failures
 Security to prevent unauthorised data access and modification
11
How does it work?
Database Management System
 By using layers of data abstraction databases have the unique capability of presenting multiple
users of the data with their own distinct view, while storing the underlying data only once.

Users can then access an application with the purpose of storing and retrieving data.
 The Physical Layer – contains the data files, e.g. the tables in access
 The Logical Layer – also called the schema. It transforms the data in the data files into a
common structure
 The External Layer – the user’s views.
12
Layers of data abstraction
External
Layer
View 1
Logical
Layer
View 2
View 3
Internal Schema
Physical Layer
Database file
1
Database file
2
Database file
3
Database file
4
13
Types of database
 Using the internet find out about the following types of
databases:
 Flat file –
A flat file database is a database designed around a
single table.
 Relational database –
Incorporates multiple tables that are related together
using keys.
10 minutes
14
Common Terms
 Using the internet find out what the following terms mean?









Field
Column
Row
Primary Key
Foreign Key
Atomic
Entity
Table
Data redundancy
25 minutes
Extra Task: Normalisation
15
Terminology
Column
Table
The data values held are
stored in the tables
A table has rows and
columns.
Row
Each column represents
a particular data value,
e.g. Author.
Each row represents a
record of a “thing”, e.g.
Order
Order ID
Customer
ID
OrderDate
0001
C1
01/11/2010 Yes
0002
C45
01/11/2010 No
0003
C45
05/11/2010 No
CustomerID
FirstName
Paid
Surname
C1
Mike
Lee
C45
Eva
Johnson
C53
Mohammed
Ahmed
16
Terminology
Entity
An entity is a name of some part of the
data chosen so that it can be referenced by
a name.
In this example the entities are the same
as the tables.
For example: Customer, Order, Item etc.
17
Terminology
Attribute / Field
An item of data relating to an entity e.g. FirstName.
CustomerID
FirstName
Surname
C1
Mike
Lee
C45
Eva
Johnson
C45
Mohammed
Armed
What are the other attributes in this example?
18
Conclusion:
Discussed unit content and assessment
Discussed what is a database
Researched who uses a database and why?
Discussed purpose and features of a relational database
Learnt some terminology focused around databases
19