ICT Databases Terminology and Notation WJEC Applied ICT

ICT@ Databases – Terminology and Notation WJEC Applied ICT DEFINITION A database is a collection of data or information which is held together in an organised or logical way. Paper based databases These can be as simple as a notebook which contains addresses sorted by surname or a birthday book with birthday reminders by month. Other paper based databases can be much larger, for example, the Yellow Pages directory. The directory is organised by business type e. g. architects builders florists plumbers taxies

Databases – Terminology and Notation ICT@ WJEC Applied ICT Under each category are listed all of the local builders, sorted again alphabetically e. g. Bob the Builder Daffy Duck's driveways Mickey Mouse extensions Road Runner and son. 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 Task: can you think of at least three other examples?

Databases – Terminology and Notation ICT@ WJEC Applied ICT Why use a database? Vast amounts of information can be stored. Queries can be run to search for specific records or groups of records. Reports can be produced from the data stored or queries run Information can be extracted from the database and exported into a word processing package for mail merging Validation can be used to reduce errors

Databases – Terminology and Notation ICT@ WJEC Applied ICT Tables Databases store data or information in tables, just like the one below: The table allows you to see all of the records stored in the database. Tables can store many records, from a few dozen for a small database up to millions for a large company database. A database can contain many tables.

Databases – Terminology and Notation ICT@ WJEC Applied ICT 5. Record DEFINITION: A record is all of the data or information about one person or one thing. In the table below, all of the information about each cartoon character is stored in a 'row' or 'record'.

Databases – Terminology and Notation 5. Record ICT@ WJEC Applied ICT Each table can contain anything from a few up to millions of records A record is made up of lots of individual pieces of information. For example, look at Wonder Woman's record. It stores her first name, her last name, her address, city and her age. Each of these individual pieces of information in a record is called a 'field'

Databases – Terminology and Notation Fields ICT@ WJEC Applied ICT Each table contains a lot of records. A record is made up of lots of individual pieces of information. Look at Wonder Woman's record: it stores her first name, last name, address, city and age. Each of these individual pieces of information in a record is called a 'field'. DEFINITION: a 'field' is one piece of data or information about a person or thing.

Databases – Terminology and Notation ICT@ WJEC Applied ICT Flat file database When you first set up your database, you can choose to make a 'flat-file' database or a 'relational' database. With a flat file database, all of your data is stored in one large table. Take a database that a vet might use. In our example below, there is data about the owner of the pet i. e. name, address, phone number, there is data about the pet - name, type of animal, date of birth, and there is also data about any appointments the pet has. This might seem pretty logical at first. But think aboutit, is it really as good as it seems? Every single time the pet has an appointment, the customer's title, surname, street, town, county, and phone number have to be entered. Also, the pet's name, type and d. o. b. also have to be entered. That would get fairly tedious having to enter so much data each time and there would be a great risk of making a mistake - remember GIGO. Repeating data unnecessarily like this is called 'data duplication'.

Databases – Terminology and Notation ICT@ Relational databases WJEC Applied ICT On the previous slide, we saw that a flat file database wasn't always the best choice as it causes a lot of data duplication. The answer to this is called a relational database. In the database below, the data is split up into sensible groups i. e. customer data, pets data andappointments data. Then a separate table is made for each group. Customer Table Pet Table Appointment Table

Databases – Terminology and Notation ICT@ Relational databases WJEC Applied ICT Once the tables have been set up, a relationship can be created to link them together – as shown by the lines linking the tables below. This is known as a 'relational database'. The main benefit of a relational database is that data doesn't have to be duplicated. When a customer books an appointment for their pet, a new record is created on the 'appointment's table' and the relevant Customer and Pet IDs are chosen. Reducing data duplication reduces the amount of data which needs to be stored, thus making the database smaller. It also reduces the risk of mistakes, because every time you have to type the same data in, there is a risk you could mis-spell it.

Databases – Terminology and Notation ICT@ Primary Key WJEC Applied ICT DEFINITION: A primary key is a field in a table which is unique and enables you to identify every record in that table.

Databases – Terminology and Notation ICT@ Primary Key WJEC Applied ICT There are two types of primary keys: 1. Simple 2. Compound or composite Simple primary key DEFINITION: A simple primary key is made up of a single field only. For example, 'customer ID' in the example below would be classed as a 'simple primary key'.

Databases – Terminology and Notation ICT@ Primary Key WJEC Applied ICT DEFINITION: A compound / composite primary key combines more than one field to make a unique value. Example 1 Sometimes, a single field is not enough to uniquely identify a record. Consider a pack of cards. There are four suits, hearts, diamonds, clubs and spades. A single primary key would not uniquely identify a particular card based just upon the suit. There also thirteen values from the Ace right through to the King. Again, this would not be sufficient to uniquely identify a card. But, if you made a primary key out of the suit and the value, then every card in the pack could be uniquely identified.

Databases – Terminology and Notation ICT@ Secondary Key WJEC Applied ICT DEFINITION: A field that can be indexed to speed up the searching of large databases. Usually the fields that are most searched on in a query – for instance the Surname in a customer database.

Databases – Terminology and Notation ICT@ Foreign Key WJEC Applied ICT DEFINITION: A foreign key is used to link tables together and create a relationship. It is a field in one table that is linked to the primary key in another table. In this database there are three tables: Artists Recordings Genre The primary key in the Artists table is: Artist. ID The primary key in the Genre table is: Genre. ID These primary keys link to identically named fields in the Recordings table. Each of those identically named fields are known as a 'foreign key'

Databases – Terminology and Notation ICT@ Referential integrity WJEC Applied ICT DEFINITION: Every foreign key value has a matching value in the corresponding primary key. Referential integrity uses these to ensure that there are no orphan records i. e. it prevents you from deleting related records. Referential integrity can also alert you if you try to delete a record which is related to another one. It can also be used to cascade changes made to the database.

Databases – Terminology and Notation ICT@ 2. Correct database notation WJEC Applied ICT How should table definitions be correctly written? Here are the rules which you should follow: The table name should be in capital letters The attributes (field names) should be placed in brackets following the table name. All of the attributes are listed inside the brackets. The attributes should be seperated by a comma The primary key should be underlined The foreign key should be overlined If an attribute contains two or more words, they should be joined together and the start of each word should have a capital letter e. g. telephone number would become Telephone. Number

Databases – Terminology and Notation ICT@ 2. Correct database notation WJEC Applied ICT The following table definition therefore be written as follows Customer ID Title Forename Surname Address line 1 Address line 2 Town County Postcode Telephone number email CUSTOMER (Customer. ID, Title, Forename, Surname, Address. Line 1, Address. Line 2, Town, County, Postcode, Telephone. Number, email)

Databases – Terminology and Notation ICT@ 3. Database notation example WJEC Applied ICT Following the rules from the previous slide , see if you can write the correct database notation for the three tables following PK = primary key FK = foreign key

Databases – Terminology and Notation ICT@ 3. Database notation example WJEC Applied ICT Employee table Department Table Qualifications Table Employee ID (PK) Department ID (PK) Qualification ID (PK) Surname Department Description Qualification Description Forename Date of Birth Address Line 1 Address Line 2 Town Postcode Contact Number Department ID (FK) Qualification ID (FK) Date hired