Relational Databases What is a relational database What

Relational Databases • What is a relational database? • What would we use one for? • What do they look like? • How can we describe them? • How can you create one?

Relational Database • A relational database is a collection of data items organised as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganise the database tables (whatis. com definition) • Or, in other words, a relational database is a database that consists of multiple tables joined together by relationships

Meaningful Information • Data are stored in databases so that they can be sorted and searched – you could just store data in Word! • You can use the data in the database to derive meaningful information – for example, there isn’t any data in a library database to tell us which books are overdue, but we can create a query to tell us • Relational databases also allow things called aggregate functions that work on groups of records and can be used for statistical analysis

Entities and Tables • When you are designing a database for a particular situation, you need to think about what entities are involved • Entities are usually things, but could also be an action or process, such as a loan. • Information for each entity will be stored in its own table in the database • A table is made up of fields, and each field will have a data type • Each table also has a primary key – something about the entity that is unique, such as registration number for a car or National Insurance number for a person

Standard Database Notation There is a standard form for describing tables: • The NAME of the table appears in upper case • A list of the fields, separated by commas, appears in brackets after the table name • The key fields are underlined For example: • CAR (registration, make, model, colour)

Relationships • Tables are joined together by relationships • A field that is linked to another table is called a foreign key • Relationships have a degree: • One-to-One • One-to-Many • Many-to-Many • We can show these on an entity-relationship diagram. . .

Entity-Relationship Diagrams ER diagrams show entities and relationships: • The entities are shown as rectangles. They are named, and will probably represent the tables you will have in your database • The relationships are shown as lines, with text above them to describe the relationship • The degree of the relationship is indicated by the end of the line – plain for “ 1” and forked for “many”.

Entity-Relationship Diagrams For example: Book ISBN title author publisher Loan borrower book date taken date due date returned Borrower borrower_id forename surname address telephone date of birth

Advantages of Relational Databases • Data about each of the entities is only entered once: – There is therefore less duplication (also known as redundancy) of data – There will be no inconsistencies across the duplicates (as they won’t exist!) – Less disc space will be required for storing the data • It can actually make searching easier.

Database Management Systems A databases management system, such as Access should be able to: • Store, retrieve and update information in the database in a transparent manner and display it to multiple users • Implement file and/or record locking to prevent conflicts when data are changed. • Look after security - at field, table, form, query, etc. , level, to prevent unauthorised access

DBMS continued… The DBMS will: • Perform back-up and recovery procedures - e. g. repairing or copying database structure • Allow control of the user interface through forms, reports, buttons and macros • Queries - “query by example” (e. g. Access queries) or “query language” (e. g. SQL structured query language)