Introduction to Databases

1. Introduction to Databases FdSc in ICT Module 107

2. Objectives BE ABLE TO IDENTIFY: • Some common uses of databases • Characteristics of file-based systems • Problems of file-based systems • the meaning of the term database • the major components of a DBMS • the advantages and disadvantages of a DBMS

3. Common Uses of Databases • Retail • Finance • Travel • Manufacturing • Utilities • Education • Research • Engineering • Everywhere

4. File Based Systems DEFINITION • A collection of application programs that perform services to end users • Each program defines and manages its own data

5. File Based Processing File handling Routines DataEntry & Reports File Definition Sales Files File handling Routines Data Entry & Reports File Definition Lease Files

6. Exercise • You have to develop a file based system for recording student names and addresses. • Decide on the record length for each of: • First name Surname • Address1 Address2 • Town County • Postcode Phone • Mobile phone Email

7. Limitations of File Based Systems • Separation & Isolation of Data • Data Dependence • Duplication of Data • Incompatible file formats

8. The Database Approach DEFINITION • A shared collection of logically related data designed to meet the information requirements of an organisation

9. Database Processing Data Entry & reports Sales Application Programs DBMS Database Data Entry & reports App. Programs Leases

10. Database Management System (DBMS) DEFINITION • A software system that enables users to define, create and maintain the database and which provides controlled access to the database

11. Facilities of a DBMS • Allows users to define the database (DDL – data definition language) • Allows users to insert, update, delete & retrieve data (DML- data manipulation language) • Provides controlled access • a security system • an integrity system • a concurrency control system • a recovery system • a user accessible catalogue

12. Components of a DBMS • Hardware • Software • Data • Schema (the structure of the database) • Metadata (data about the data) • Operational data • Procedures • People

13. Advantages • Minimal data redundancy • Consistency of data • Integration of data • Improved integrity • Consistent security • Standards • Increased productivity

14. Disadvantages • Complexity • Additional Hardware Costs • Size • Performance • Experts -Specialised Personnel • Potential organisational Conflict • Higher impact of failure

15. Database Models • Flat • Relational

16. Flat database model • a single, two-dimensional array of data elements • all members of a given column are assumed to be similar values • all members of a row are assumed to be related to one another • columns of the table often have a type associated with them, such as character data, date or time information, integers, or floating point number • this model is the basis of the spreadsheet.

17. Flat database model • A simple database with limited function • View • Format • Textual search • Edit • Add new records Column Row

18. Relational Database • Relational database system devised by Codd in 1970 • An attempt to devise a standard model with a sound mathematical basis • why does this differ to the previous systems? • Most successful database model • Most use the query language SQL • Examples include: • Oracle, Microsoft Access, FoxPro, MySql, SQLServer • SQLite (in Android)

19. Data Model • Data model: an integrated collection of concepts for describing data, relationships between data and constraints on the data • A data model comprises three components: – a structural part (describing how the database is to be constructed) – a manipulative part (defines the types of operations allowed on the data) – a set of integrity rules (ensures data accuracy) • A data model is used for unambiguous communication between developers and users

20. The Relational Data Model • Based on the mathematical concept of a relation(Usually referred to as an entity type) • Physically represented as a table with columns and rows • The only requirement is that the database is perceived by the user as tables

21. Example • An example of the Staff table/file/entity: • A relational database consists of tables that are appropriately structured (normalised) Column/field/attribute Row/record/tuple

22. Terminology • Relations (hold information about the objects we want to represent in the database • We represent relations (files) using tables – each row corresponds to an individual record – columns correspond to attributes • Attributes (columns) can appear in any order (the relation will still be the same relation) • Every attribute is associated with a domain, which is the set of allowable values for one or more attributes eg days of the week

23. Properties of Relational Tables • Each table has a unique name • Each cell of a table contains only one value • Each column has a distinct name in the table • The values of a column are all from the same domain • The order of columns has no significance • Each record is distinct (no duplicate records) • The order of records has no significance

24. Relational Keys • Each record in a table must be unique • A primary key uniquely identifies records within the table • A foreign key is a column or set of columns within a table that “is” the primary key of another table • A foreign key column itself may be not a key in its table

25. Schematic Representing Relational DBs • A relational database consists of one or more tables, conventionally represented as follows: Name of table followed by column names (attributes, fields) in parentheses and primary keys are underlined Branch (branchNo, street, city, state, zipCode, ) Staff (staffNo, name, position, salary, branchNo, mgr)

26. Relational Integrity • A set of integrity rules ensure that the data is accurate • There are domain constraints for each column that restrict the set of values allowed for each column. (Already discussed for foreign keys) • In addition, there are two very important relational integrity rules that apply to the database in general – Entity integrity – Referential integrity • We can also specify additional constraints as business rules

27. Relational Integrity (cont.) • A null represents a value for a column that is currently unknown or inapplicable to this record • A base table is a named table whose records are physically stored in the database (unlike query answer virtual tables) • Entity integrity: In a base table, no value of a primary key column can be null • Referential integrity: If a foreign key exists in a table, its value in the table must either coincide with the corresponding candidate key value of some record in the home table or be wholly null

28. Relational Database - Example • BRANCH relation • STAFF relation

29. SQL What is it? Structured Query Language • Used in ORACLE and other DB systems • Non-procedural - i.e. Specify what you want not how to get it • SQL - (also pronounced SEQUEL) directly related to the development of the RELATIONAL MODEL by E.F.Codd.

30. Example SQL Queries • select branchNo, city from branch; • select * from branch where branchNo = ‘B003’; • select branchNo, name from branch, staff where branch.branchNo = staff.branchNo;

31. Practical database • Use phpMyAdmin as a web browser client • Interacts with a MySQL database • Uses SQL commands with a GUI • Log in at http://hosting.computing.hct.ac.uk/phpmyadmin/ • If you do not have a login , register at: • http://hosting.computing.hct.ac.uk/login.php

32. Explore • Structure • Datatypes • Keys • Values • SQL statements