Database Concepts:

Traditional File Approach:

Ø Problems:

o Data redundancy (duplicated data)

o Data inconsistency (not all files updated as there are many copies)

o Program-data dependence

§ Each program has to specify exactly what fields constitute a record in a file being processed. i.e. has to be told all the details/structure and this must be updated when the structure changes.

o Lack of flexibility

§ When a new kind of report is needed a new program must be written etc.

o Data was not shareable

§ One department may make another field etc causing conflicts.

The Database Concepts:

Ø Database:

o “A collection of non-redundant data shareable between applications”

o All data belonging to an entire organisation would be centralised in a common pool of data accessible by all applications

Ø Problems:

o Unproductive maintenance (adding fields cause problems)

o Lack of security – All data in database, including confidential and commercially sensitive, visible by all.

DBMS – Database Management System

Ø Two essential features:

o Program-data independence – storage of structure of data is hidden from program and users.

o Restricted access to data – each user is given a limited view according to their needs.

Ø 3 levels of architecture in a DBMS – schema

o External / user schema – user’s view, can be different for each user.

o Conceptual / logical schema – overall view of database – attributes, relationships. Designed by database designer.

o Internal / storage schema – Describe how data will be stored

Ø Further functions of the DBMS

o Data storage, retrieval and updates

o Creation and maintenance of the data dictionary (which defines fields and lengths etc)

o Managing the facilities for sharing the database – eg 2 people updating the same record etc.

o Backup and recovery

o Security – handles allocation and checking passwords and “view” that a specific user has been allocated.

Multi access databases

Ø i.e. a Database on a file server on a network (LAN)

Ø more than one person can access the database at the same time

Ø users can work with databases which are stored on the shared drives as well as local tables, i.e. some on network, some on computer

Ø If it is configured appropriately, more than one person can update tables at a time.

o DBMS ensures integrity of data, eg avoids 2 people are trying to update the same record at the same time, so updates are not lost. This is done by locking.

Ø Record à RAM à save to file server

Locking

Ø At database level – whole database locked when 1 person views it. –Impractical but probably the fastest way of locking, as the DBMS has to do less conflict checks. Possibly useful for overnight report generation etc.

Ø At table level – lock all records in the table being modified

Ø User specifies no locks (DBMS only warns users of a possible conflict) – can be risky, only a few users would have this option.

Ø Open a table in read-only mode, i.e. others can only view it.

Deadlock

When user 1 wants record 1 but is looking at record 2, and user 2 wants record 2 but is looking at record 1, you would get a deadlock.

The DBMS takes action and aborts one of the users.

Software Protection Techniques

Control access to particular info

Ø Admin issues userid’s and passwords

Ø Users are allocated to groups with a set of permissions / privileges (view, modify, update, erase, execute etc) for example they may have access to only 1 subschema or bit of the database

Encryption – more security

Ø UserId’s and passwords only encrypted and saved separately

Open systems and ODBC (Open Database Connectivity)

Ø Open systems provide a standard to which applications may be written to allow portability to multiple systems.

Ø Portability is the key (to a good product)

Ø Converted / read using an ODBC driver, eg from Oracle ßàExcel