Database Management Systems (DBMS)

1. Data Handling Software Database Management Systems (DBMS)

2. 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.

4. 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

5. 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.

6. 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

7. Key Words • Fields • Table • Primary Key • Record • Relational Database Management System (RDMS) • Query • Report

8. Table Fields Field Names

9. Primary Key

10. 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

11. Records

12. Primary Key

13. A TABLE is made up of RECORDS RECORDS are made up of FIELDS FIELDS are made up of CHARACTERS

14. File, records & fields Stock File Stock code: SP2544 Stock name: Timber door panel Material: Pine Size: 600x600mm Quantity: 25 Stock code: RF4237 Stock name: Timber door panel Material: Pine Size: 600x600mm Quantity: 25 Stock code: PY3746 Stock name: Timber door panel Material: Pine Size: 600x600mm Quantity: 25 One stock record Stock code: TM1253 Stock name: Timber door panel Material: Pine Size: 600x600mm Quantity: 25 One field

15. 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

16. Flat File Databases • A flat-file database is a simple database that stores all datain a single tablein 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.

17. Flat File Advantages • Useful for simple lists: • Easy to create

18. 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

19. 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 recordswhich are linked by relationships. • A relationship is implemented throughcommon fields held in the two tables What's the problem?

20. 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

21. 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.

22. 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

23. 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

24. Database Software

25. Advantages/disadvantages of Database Software

26. 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.

27. 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).

28. 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.