Transcript Databases powerpoint

Data Handling Software
Database Management Systems (DBMS)
Paper Databases
• A database is a
collection of data or
information which is
held together in an
organised or logical
way.
• Databases can either be
paper based or
computerised.
Computerised Databases
• You will come across
computerised databases in
every aspect of your life.
• Here are some examples
with which you will be
familiar:
– Search engines e.g. Google,
Ask, Bing
– School electronic registers
– Library database
– Shop stock control system
– DVLC - stores details of all
driver licences and car
registrations
Specification
• Candidates should be able to define a
database as a large collection of data
items and links between them,
structured in such a way that allows it to
be accessed by a number of different
applications programs.
What You Need to Learn From This Lesson
• Definition of a database
• Advantages and disadvantages of a database approach over
flat files
• Database security
• Hierarchy of passwords
• Storage of data separate to programs
Key Words
•
•
•
•
•
•
•
Fields
Table
Primary Key
Record
Relational Database Management System (RDMS)
Query
Report
Table
Fields
Field Names
Primary Key
Definition of files, records and fields
• A file
– is a set of related records
• e.g. a file of patients
• A record
– is a collection of data about a particular person, object or
concept
• e.g. the data about one patient
• A field
– is a subdivision of a record which holds a particular item
of data with a given data type
•
• e.g.
• Medicine quantity (data type is number)
• Patient name (data type is text)
Primary Key
– field(s) to uniquely identify a record
Records
Primary Key
A TABLE is made up of RECORDS
RECORDS are made up of FIELDS
FIELDS are made up of CHARACTERS
File, records & fields
Stock code:
SP2544
Stock File
Stock
code:Timber
RF4237
Stock
name:
door panel
PY3746
StockStock
name:code:
door panel
Material:
PineTimber
code:
TM1253
StockStock
name:
Timber
door panel
Pine
Size:Material:
600x600mm
Stock
name:
Pine Timber door panel
Size:Material:
Quantity:
25 600x600mm
Pine
Size: Material:
Quantity:
25 600x600mm
Size:
Quantity:
25 600x600mm
One
Quantity:
25
field
One
stock
record
Databases &
Database management software (DBMS)
• A database is a collection
– of tables and records
• DBMS - database (management) software
– Is a layer of software inserted between the different
applications (programs) and this data.
– It is responsible for the
creation/maintenance/manipulation of the database
Flat File Databases
• A flat-file database is a simple database that
stores all data in a single table in one file
• A flat-file database can be stored in a text file,
such as a a Spreadsheet, or in a database file
that contains one or more unrelated tables.
Flat File Advantages
• Useful for simple lists:
• Easy to create
Flat File Disadvantages
•Large amounts of duplicated data
•Difficult to update
•If entries change all instances have to be
updated
•Security
•All users have access to the same set of data
Relational Databases
• The problems associated with flat files can be
solved by moving some of the data out of the
main table and accessing it when required
• holds data in many
tables of records
which are linked by
relationships.
• A relationship is
implemented through
common fields held in
the two tables
What's the problem?
Relational Database Advantages
• Avoids data duplication (redundancy)
– data stored once
– linked by key fields
– all data available via relational links in key fields
• Ensures consistency of data of data to all users
Relational Database Advantages continued
• Increased security - What makes databases secure?
–
–
–
–
–
Hierarchy of passwords
ID
Authentication
Authorisation to files and processing
DBMS can check permissions Read only Write only etc.
Users only allowed to view the data they are entitled to, so
there is less risk of accidental or deliberate destruction.
• Data integrity
– It is possible to specify constraints on the data to ensure it
is in the correct format and range.
Disadvantages of relational database vs. flat files
– More complicated to design and create a
relational database than a flat file system
– More expensive because flat file system can by
created simply using spreadsheet software where
as a relational database needs to be created with
a DBMS
Database Security
– DBMS controls security access to different parts
of the database
– Each user is given appropriate access rights to the
database appropriate to the type of user they are
and their job
Database Software
Features – Ability to:
Produce Queries
Searching for data with
specific criteria.
e.g.: looking for customers
who all live in a certain
postcode to arrange delivery
of shopping.
Produce
Reports
Outputting information in
a printed format. e.g.:
Printing customer bills
with data from the
customer accounts.
Import/Export
Transferring data
between software
packages e.g.: Mail
merge where data is
exported from
Advantages/disadvantages of Database Software
Advantages of Relational Databases
Disadvantages of Relational Databases
Only have to enter data once
They are complex, difficult, and timeconsuming to design
Files and tables are linked, and so means if
the data is changed in one application, the
rest of the database will automatically be
updated for other applications
Damage to database affects virtually all of
the application programmes
It is quick and easy to access information
Search criteria can be use, saved and
modified
Initial training for programmers and users is
needed and can be costly
Everyone uses the same data, and so the
data is consistent
Validation checks can be performed
Ensures data integrity
The structure of the database can easily be
changed
Database Software
• A database is an organised collection of data or information.
• Computerised databases can import or export information.
• This needs to be done easily as different systems are often
used and the data must be read the same each time.
Query
• The whole point in storing data in a database is the fact that it
is easy to perform searches to extract data.
• One way of searching for that data would be performing a
search using the tables in the database.
• Another is using commands written in a language called
Structured Query Language (SQL).
Reports
• Databases can perform queries and produce the results in a
report.
• This is usually printed out and presented in such a way that it
is easily understood – for e.g. graphs, charts, tables, etc.