• Show understanding of the limitations of using a file-based approach for the storage and retrieval of data

• Describe the features of a relational database that address the limitations of a file-based approach

• Show understanding of and use the terminology associated with a relational database model

  • Including entity, table, record, field, tuple, attribute, primary key, candidate key, secondary key, foreign key, relationship (one-to-many, one-to-one, many-to-many), referential integrity, indexing

• Use an entity-relationship (E-R) diagram to document a database design

• Show understanding of the normalisation process

  • First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF)

• Explain why a given set of database tables are, or are not, in 3NF

• Produce a normalised database design for a description of a database, a given set of data, or a given set of tables

This section contains a lot of information to cover any possible CIE database question. However, prioritisation should be given to understanding:

• What is a database

• Why do we need databases

• benefits of a relational database over a flatfile database

• The key components of a DataBase Management System (DBMS) including schema, understanding the role of data dictionaries and data security. Much of this is covered in the bottom part of this section.

Introduction

Databases are everywhere! They are used to store details of pupils in your school, health records are increasingly held in a database and driving licence details are held by the DVLC. The police keep records of crimes and criminals, shops keep details of what they have for sale and what they have sold and the Government keeps details about its citizens, for example, for tax reasons. In fact, the more you look, the more you find databases in use. As they are so important and impact in our lives every day, it is reasonable that we should know all about them if we are doing a Computing course!

What is a database?

When you look inside any filing cabinet, you will find that it typically holds records e.g. patient records in a hospital. Each 'record' might be a single piece of paper or a folder of pieces of paper. Each record holds the data about a particular person you want to keep details about. The records are organised, sometimes in alphabetical order using each patient's surname from A - Z (ascending order) or perhaps Z - A (descending order) or perhaps by some other piece of data, such as who was born first or postcode or patient number and so on. Sometimes, you want to search through the database and get back a single record, or a set of records e.g. everyone who has had an operation in the last month. You might want to do other things to your filing cabinet of records, such as sort the records in a different order, or add, delete or amend a record.

Our filing cabinet of records is a database and for a long time, this was the only kind of database that could be used to store, organise and search data. There are problems with this kind of manual system. It is easy to lose a record by misfiling it in the wrong place. It is hard to take a million hospital records and put them in a different order, or search through all of the records for those that meet a certain set of criteria. When computers came along, so did software that allowed someone to make and use computerised databases. Suddenly, all the hard jobs associated with manual systems became easy any now anyone can make and use a database! You can set up a new database structure, define what records you want to keep and what data has to be in the records and then you can sort the records, search the records and do lots of other jobs using the software tools provided.

Persistent organised store of data

So we know that a database is an organised collection of data items and the links between the data items. What usually happens with databases is that you set up your database of data on the one hand, and then quite separately, use software applications that can access the data and sort and search it on the other hand. The data items are organised into a database structure, which is completely separate from the applications that use them. You might ask why you would do this. Well, with a bit of organising using a special piece of software known as a Database Management System (DBMS), different applications can get access to the same database by keeping it separate from the applications that use it.

We often use the word 'persistent' when talking about databases. Even though an application may not actually be searching through the data or doing something else with the data, the database is still there, it still exists.

Problems for the secretary

1. It takes a long time to find an individual member’s record because there are now over 5000 of them. This is especially true if you search using multiple search criteria.

2. It takes even longer to find a dog’s record, especially if a member’s details aren’t known for some reason.

3. Finding all dogs who meet certain criteria, e.g. all poodles born after a certain date takes a long time manually.

4. Occasionally, all of the records need to be sorted into a different order. This can take a long time.

5. When records are filled in, some details are recorded over and over again. For example, a spaniel, its origins and its life expectancy are recorded in a number of different records. This is clearly a waste of the secretary’s time. It would be far better if the details for each breed could be stored just once.

6. Details for personalised letters and address labels have to be manually transferred over to the letters and labels from the index cards. This is another time-consuming job.

7. When new records are entered, spelling mistakes or incorrect details are sometimes entered onto the cards.

Why a computerised database would help the secretary

1. When new records are entered into the database using a data input form, validation rules could trap some errors.

2. All records can be easily sorted by different fields and multiple fields. A ‘field’ is an attribute of a thing or person, a piece of information held in a record. In this case, fields include Record number, Initial, Surname, Breed, Origin of breed and so on.

3. The secretary can easily and quickly search the entire database for records that meet certain criteria.

4. The results of sorting and searching can be presented in well-designed reports.

5. Details held in the database can be merged into a word processor to produce personalised letters and labels.

What the dog club needs to think about when setting up a database

1. Buying computer equipment is initially expensive. Savings to the dog club may not materialise for years.

2. Computer equipment can break down. Who will repair it? How will the dog club function without a computer?

3. The secretary may need to be trained and retrained.

4. The secretary may find using computers a stressful experience if he or she does not have an IT background.

5. How will software problems be dealt with? Does anyone in the dog club have the necessary skills?

6. Computerised equipment is attractive to thieves. Measures will need to be taken to protect the hardware and software.

7. The data in the system will be very valuable. A backup procedure will need to be put in place.

8. The Data Protection Act will need to be complied with.

A simple file-based database, otherwise known as a flat file database, has been created. A ‘file-based database’ simply means that all the records are held in one table. Each row in the table corresponds to one record. Each column in the table corresponds to a different field. Tables are two-dimensional structures. File-based databases are therefore also referred to as ‘flat’, hence the name ‘flat file’.

The good points about flat files

1. Flat files are relatively quick and easy to set up and use.

2. They are ideal for smaller databases.

3. They provide many of the sorting and searching tools commonly needed by users of the database.

The problems with flat files

1. You can see that in the flat file, every member has an ID number. This should be unique for each member and is known as the Primary Key. The problem is that some records (some rows) have the same ID number. This means that you cannot pick out one and only one record if you searched for a member by their membership number.

2. Dogs do not have their own unique identity number. It is therefore impossible to find an individual dog. Consider Gov in the above flat file. There are different two dogs, both called ‘Gov’. Their personal details just happen to be the same! You cannot tell them apart from the details held in the flat file.

3. Because dogs do not have their own ID number, you cannot enter in a ‘new’ breed until a member owns a dog of that breed.

4. Lots of details are held over and over again, for example, the origins and life expectancy of poodles, and the details about the member known as ‘A fish’. This is known as ‘data redundancy’ and is a waste of (hard disk) space. Data redundancy also contributes to larger files, which means longer search times. In addition, if you enter the same data over and over again in different places in the flat file, you are more likely to make a mistake when entering in one of the entries. This results in some of your data being inconsistent.

5. If ‘A Fish’ got married and changed her name to ‘A Haddock’, then the secretary would have to make four changes to the database rather than just one. This is clearly a waste of time. The secretary could also introduce some data inconsistencies if he or she changed the name in one of A Haddock’s records to A Hadock (with one ‘d’). This is referred to as an ‘amendment anomaly’.

6. If one member such as ‘Y Nott’ left the club, the secretary would have to delete five records (not just one). This is sometimes referred to as a ‘deletion anomaly’.

7. If one new member joined with more than one dog, you would have to store more than one record (each one holding details about the member) - even though only one member has joined! We will call this an ‘adding anomaly’.

To summarise, flat flies are easy and quick to set up. They are easy to use and are ideal for small databases. They have a series of problems. These relate to the repetition of data stored in the database (known as ‘data redundancy’) as well as problems to do with adding and removing records or changing the data in records. These are referred to as ‘adding, deleting and amendment anomalies’ and can result in the data in the records becoming inconsistent.

There are problems with flat file databases. However, there is something that can be done about it! Having two smaller tables is no different to having one big table, as long as we link them (or ‘relate’ them - hence the term ‘relational database’). Consider one record in a table. We can have one big record in one table, or split the big record into two parts and link them together (using a special link called a 'foreign key'). One way to split the table is to have a table for members and a table for dogs. We can draw a diagram of this (called an E-R diagram, or Entity-Relationship diagram). ‘Entity’ is just a name for a table, so this diagram shows the relationship between entities, or tables. In the diagram below, we have a ‘one-to-many relationship’.

It is a very good idea to put some real or made-up records into the tables you have identified as part of your E-R analysis. It really helps you to ‘visualise’ the problem. Note that when you split up a table into two or more smaller tables, the first thing you have to do is to check that each of the tables you have got has a primary key, a field whose value for every single record (i.e. every single row) will be unique - no other record will have the same value. The members’ table has such a field (ID) but the dogs’ table doesn’t have a suitable one. One simple solution (but not the only one) is to add an ID attribute to the dogs’ table and make this the primary key. We will call this new added field ‘DogID’. All attributes that could be considered primary keys are called candidate keys.

The second thing we need to do is to check that each record in one table is related to a record in the other table. We can do this by using a ‘foreign key’; we copy the primary key from the one side (members) and put it in the many side (dogs). In other words, we copy the relevant ID from the member’s table and put in the relevant record for each dog. We now can view our improved database design

Don’t worry if you are still a little confused! E-R diagrams can be confusing at first. There is a much more detailed explanation of E-R diagrams given in Section 2.

How is the relational database an improvement over the flat file design?

• Each member’s details are now only stored once.

• Each dog is now identified by their own unique identity number.

• A new member now has their details entered into only one record, not multiple records.

• Changes to records need only be made in one place.

• Details that are to be deleted only need to be deleted from one record.

Can we improve the design even further?

We have solved some problems by splitting the original flat file into two related tables. We haven't solved all the problems, however. We still have to record breed details over and over again and we can't record a new breed in our database unless there is a real dog of that breed actually owned by someone! We could try splitting up the dogs’ table into two, like this:

Reading all of the relationships, we now have:

• Each member can own many dogs.

• Each dog can be owned by only one owner.

• Each dog can be only one particular breed. (This is a dog club for pedigree dogs only!)

• Each breed can appear in many different dog records.

As before, we can add some records to the tables to make ‘seeing’ what we have designed a little easier.

How is this relational database an improvement over the flat file design?

Not only can we now do the following:

• Each member’s details are now only stored once.

• Each dog is now identified by their own unique identity number.

• A new member now has their details entered into only one record, not multiple records.

• Changes to records need only be made in one place.

• Details that are to be deleted only need to be deleted from one record.

But also:

• We only need to store the details of each breed once.

• If we add, delete or amend a record, we only have to make the change in one place.

• We can add a new breed to our database, even if a member has not acquired a dog of this breed yet.

Summary

• What we have seen is that flat files have their uses for small databases.

• As the number of records in a flat file increases, however, jobs that used to be simple and quick start taking a lot longer.

• There comes a time when it is much better to consider breaking up one big file to produce smaller tables that are related.

• These types of databases are known as ‘relational databases’.

• A useful technique to help the designer achieve this is Entity-Relationship modelling.

• We talk about producing an E-R diagram for a particular database.

• E-R diagrams show a database designer what tables they need in a particular database and how those tables are related to each other.

• E-R diagrams do not in themselves show the designer what fields need to go in each table.

• (The designer will produce another document - a Data Dictionary - to record this information.)

• As with any new computerised system, an organisation needs to think carefully before it goes ahead and installs a computerised database.

Introduction

There is a lot of technical words in relational database modelling. These are explained in detail below.

Entities and attributes

An ‘entity’ is the term used to describe something we keep information about. Attributes are the pieces of information we keep about an entity. For example, we might have a pupil database. The entity here is 'Pupil' because that is what we keep information about. The attributes are the pieces of information about a pupil, such as their first name, surname, date of birth, form group and so on. In a library, you might keep information about books. 'Book' would be the entity and name of book, author, ISBN number and so on would be the attributes.

Tables

Once we have identified an entity, and we know what attributes we want to keep about each entity, we can then store the actual pieces of information about each entity. We can think of a database that holds entities and attributes as a table. Consider this example. It holds records about dogs.

Files and databases

Another name for a table of records is a 'file'. A database can be defined as one or more files. You can have a simple database, with all the information held in just one table.You could also have a bigger database, where all of the entities have been logically split up into different tables. For example, a school database might have one table for students, another for staff, another for all the information about different qualifications, a table for records about each room and the facilities in each room and so on.

Primary Keys

Each record in each table must have one field that is unique, to ensure that each record can be differentiated from the others, even if the other fields are the same. You all have a unique student number, a unique NI number, you may have a tax number, a driving licence number, a club membership number and so on. These numbers are always different for each record, even if e.g. someone else has the same name as you.

• • • Every entity must have one attribute that is also known as the primary key. If an entity of records does not have a primary key attribute, then you must add one!

    • A primary key is used to either find one record, or is used to sort a file in primary key order.

    • The primary key, usually an ID number of some kind, is unique, for each record. You cannot have two records with the same primary key value.

    • Many primary keys are made up of just one attribute. There are lots of circumstances, however, when the primary key is made up of 2, 3 or even more attributes. It is then known as a compound primary key. If you have normalised a database to 3NF, you will probably have seen an entity with a compound primary key.

Candidate Keys

When you look at any table, most of the time the primary key will stand out. Often, it will be called something like Order Number, Student ID, Member ID and so on and these clearly will be unique. However, it may be that more than one of the attributes (i.e. more than one of the columns) in a table are unique, or perhaps, more than one combination of attributes are unique (compound keys). All of these are together known as potential 'Candidate keys' for that table, as they could all ultimately be chosen as a suitable primary key.

Foreign Keys

Relational databases have more than one table. Records from each of the tables are combined to form the complete record of someone or something. Foreign keys are used to link the different records in different tables, so the database software knows which record in one table belongs to which record in another table.

• • • Foreign keys are used to link entities.

    • A foreign key in one table is a primary key in another table.

    • Although primary keys cannot have duplicate values in a table, foreign keys most definitely can. You have a situation where the same attribute cannot be duplicated in one table but can be duplicated in another!

    • When you have a one-to-many relationship between two entities, you will need to link them using a foreign key. To do this, always copy the primary key from the entity on the 'one' side of the relationship and put it in the table on the 'many' side. In the table on the 'one' side, it is known as a ‘primary key’. In the table on the 'many' side, it is known as a ‘foreign key’.

Tuple

This is generally considered another name for a row or record in relational database terms. It comprises of a dataset representing a single item (record).

Secondary keys

Very often, you may want to access a database by an attribute other than the primary key. Consider a textbook. Most of the time, a reader will want to access a particular topic in a book, so there exists a main contents page to allow them to do this. Sometimes, however, they want to look up specific things, so an index at the back of the book is provided. And sometimes, however, the reader may be more interested in looking at all the pictures! To enable this to happen, another index is provided - an index of pictures and where to find them! In this example, if a reader wanted to look at all the pictures, they could still go to the contents page and use that if they wanted to but it would take a long time, especially if it was a thick book. Now they have a secondary index file they can get back just the pictures whenever they want - and quickly.

Secondary keys, then, enable a user to access data using a different field much faster. The downside is that when secondary keys (indexes) are created, additional data is generated by the DBMS. For large databases, this can add significant overhead so designers have to choose which fields may need to be referenced in this way.

Another example of the use of secondary keys

For example, imagine a bus company that stores bus details. It might have in a typical record the bus code, departure and arrival points, times of arrival and departure, number of seats in the bus and drop-off points. You could easily get a list of all the buses, listed by the primary key (the bus code) from the main file. But when someone goes to the bus company to buy a ticket, they just want to know only when the buses leave from their town to London. Now this is a question that the bus company might get asked one hundred times a day! They could run a query. They would need to set one up and then check the record of every bus. If they had 20000 buses, checking each of them would be time consuming, especially if they had to do this so many times every single day! So instead, they set up a secondary index file, which only has buses from the hometown to London and the time of departure. It will be ordered according to a secondary key that has been set up. The result of using a secondary index file with a secondary key is faster access to the information the customer wants!

Referential integrity

Referential integrity is the term used to describe when all the links between tables using foreign keys are present and valid. If a record in one table refers to a record in another table, and that record is actually missing for some reason, then we talk about the lack of data integrity. Consider these two tables. The first table is a list of Dog Owners and the second table is a list of Dogs. The relationship between the two tables is a one-to-many:

• • • Each owner can own many dogs.

    • Each dog is owned by just one owner.

In the last column of the Dogs table, you can see the foreign key, ID. This foreign key is also a primary key in the Dog Owners table and the value tells you which record each dog links to in the Dog Owners table. In other words, the value tells you who the dog owner is.

Now imagine for some reason that record number 3, Mr D Lapidated, was deleted from the Dog Owners table. This may have been done by accident or may have been done deliberately. The problem that exists in the database now is that you have two records in the Dogs table (the dogs called Manic and Blip) that refer to a record in the Dog Owners table that doesn't exist. The integrity of the data has been broken and this can cause all kinds of errors in a database.

To prevent this from happening, you can often select an option when building a database to uphold all the referential integrity rules. In other words for the above example, if you did tell your database to uphold referential integrity, and you tried to delete record number 3 in the Dog Owners table, you would either not be allowed to do this or you would be given a warning message before being allowed to proceed and delete the record. If you really wanted to delete Mr D Lapidated's record from the Dog Owners table, you would normally be required to delete or modify the two Dog records for Manic and Blip first, so that no records referred to record number 3 in the Dog Owners table.

The key purpose of database normalisation is to represent and store data in a structured way that avoids:

• Redundant data

• Repeating data

Unfortunately, the most confusing aspect of normalisation is the process of delivering a set of entities in first normal form (1NF). The most confusion surrounds the concept of 'repeating groups' which has become very confused in recent years. The situation has become so bad that it is no longer worth the hassle to justify that a repeating group was only ever meant to refer to multiple data items in a single attribute, or tuple. This article explains the confusion and from where it stems.

These videos are intended to get all pupils to fully understand the AS/A2 Computing topic of database normalisation, an often misunderstood topic that pupils frequently find difficult and confusing.

There are two different videos for normalisation with each host producing 3 videos. Use whichever one you prefer to watch.

Kevin Drumm Normalisation Videos

Mr B's Computer Academy Normalisation Videos

Series of 3 videos breaking down normalisation.

Final, alternative video (suggested by a pupil)

This video covers the key concepts from a hands-on perspective, but the content covered is still relevant.

This section has been taken from the databasedesign-resource site. Links have been left in for further information, etc. It is unedited and taken directly from the site. Additionally, due to time, the content has not been checked by a Mill Hill School CS teacher.

A database glossary at your disposal.

The words used in database design MAY be confusing, at times: That is why I provide this database glossary for you: If you don't find the term you are uncertain about here, use my Contact page for a message, and I will include the term here.

This is a "live" glossary: Whenever there exists relevant information about a term on some other page around here, the term appears as a direct link to that page. Some of the pages may be articles, short or long, others may lead you to a free eBook, and others again may offer you a payable eBook, if the term is a complex and/or lengthy one.

If I do not have a relevant page for a given term, you can blame it on me: Not writing fast enough. I will, however, put the term in bold and give a short explanation.

Alternate (candidate) Key

A column or combination of columns, other than the primary key column(s), which may be used to uniquely identify a row in a table. The primary key is chosen from one of the candidate keys. As you can read from the article behind this term; if you have many candidate keys, you have a problem of selecting the best fit.

Attribute

An attribute is a part of the description of the entity. The entity itself is described by one or more attributes; together, they describe all things of importance about the entity. Example: Typhical attributes for a customer would be name, address, telephone, etc.

Business Rules

Specific business-related information that is associated with database objects. The information can be business restrictions (allowable values), facts, or calculation rules for given business situations, e.g VAT shall be added to all products. Business rules should be applied in the completed database, either as triggers/stored procedures, or (absolutely worst case) implemented in the application code. However, if you do that, you will make your business dependent on a specific application...

Candidate keys

Each entity should have at least one candidate (that is: unique) key defined. This is in order to being able to identify each row in the table at hand. Read the above link about candidate keys: It will teach you the importance of the issue.

Cardinality

The cardinality of an entity indicates the number of instances (zero or many) of an entity.

Many describe the cardinality through relationships: One-to-one, One-to-many, Many-to-many. In addition, each end of a relationship may be optional or mandatory. The cardinality term is not restricted to relationships, however: We also try to distinguish between low and high cardinality on attributes: an attribute for GENDER will have a low cardinality (2); Male or Female. This may inflict on how we design indexes in the database.

Another meaning of cardinality is to describe how many rows you have in a specific table.

Column

The physical equivalence to an attribute: In a database table, the table consists of one or more columns; together they describe all we want to know about each occurrence (record) in the table.

Data Definition Language (DDL)

The language used to define objects in a database: CREATE TABLE, CREATE INDEX, and so on. DDL provides the semantics for administering all the physical objects in your database. It is based on a given standard, but may deviate some from vendor to vendor. All of the objects created together, form the 'schema' of a given application.

Data Manipulation Language (DML)

The language used to manipulate objects in a relational database: We only have four statements in DML: INSERT, SELECT, UPDATE and DELETE. DML provides the semantics for manipulating one or more occurrences (record) in a table in our database. It is based on a given standard, but may deviate some from vendor to vendor, especially through ‘extensions’ found valuable by the given vendor.

Data model

A data model is not the model (conceptual/logical/physical) of your future or existing database: It is a generic model which you base your analysis and design upon. The relational model is one such data model. To my knowledge, there are no other data models that reflect the reality as we have to deal with it. I can not provide you with an OO-oriented data model, or an XML data model; such complete models do not exist, although some vendors are trying to make you think otherwise.

Database model

A Database model is the logical conversion of an Entity Relationship model. While an ER model reflects the business case, the Database model visualizes the logical model, which in turn is the basis for the physical implementation in the form of database tables, indexes and other mechanisms in the database, necessary to construct the database in question.

Database Normalization

Database Normalization is the process of analyzing your database model to ensure that information is stored only in one place in the database, and that there are no derivates of the information within the database. A perhaps more understandable explanation of the 5 Normal Forms can be found in my eBook on Database Normalization

Datatype

Identifies the kind of information that an Attribute/column in an entity/table on a specific database platform represents. These are actual physical representations and are dependent on the actual RDBMS’s. The datatype should be given by the domain definition for that attribute/column.

Domain

A standardized definition which applies to many attributes/columns.

For example, the domain MONEY may be specified as NUMBER, 15 digits long, with 2 decimals, not allowed to have a value of 0.00, and so on. Applying domain definitions to every attribute/column, eases the implementation of changes, and assures that the same kind of information is treated equally throughout the system.

Entity

Any kind of information of importance to the business: Customers, Orders, Products, or whatever information we need in order to perform a certain task.Used in the building of the conseptial (business) model

Foreign Key

Column or combination of columns in a table, whose values are related to a primary key in another table.

Index

An index is a physical mechanism applied to one (or a combination of) column(s). The purpose of the index is for the database system to use the index as a look-up mechanism instead of reading the whole row. Indexes are a prime resource for optimalization (and thereby increasing speed) of searches in the database.

Join Relationship

A join relationship is a collection of information from two or more tables. The join is performed by relating columns which are foreign key columns in one table with equivalent columns which are primary key columns in the other table.

MetaData

'Data about Data'. This is the documentation stored in the database repository, and which holds information about your database objects. In Oracle, for example, the table USER_TABLES holds vital information about your tables.

NULL values

The concept of NULL, which means "unknown value", (not the digit zero) is not a part of the relational model, but has been introduced by the different vendors. It can create unexpected problems when accessing the database (read the linked article).

Occurrence

Each row in a database table. If you have 100 customers in your CUSTOMERS table, the table has 100 occurrences.

Open DataBase Connectivity (ODBC)

A general interface for communication with different vendor-specific Relational Database Systems.

Primary Key

A column (or combination of columns) whose value(s) uniquely identify a row in a table. This has been a central consept in Relational Theory, and crucial to both identification and performance. Lately, the concept has been questioned, saying that a table should have one or more candidate keys only; however, for all practical reasons, a table should never be created before its (unique) primary key has been determined.

Ref cursor

A ref cursor is a pointing feature for use in a procedural language. The use of a ref cursor, explicit, gives you the power to navigate a result set from a SELECT statement one record at a time. It is a basic concept in Oracle, but can also be used from other procedural languages such as Java.

Referential Integrity

Referential integrity deals with the governing of data consistency. We mostly think of it as keeping the relations between tables valid; that is, an order may not have a customer id that does not exist; a transaction can not be posted for an illegal (non-existent) account.

Relationship

A relationship is an association between two entities. For example, the relationship between the ORDERS and CUSTOMERS tables will normally be that an order is placed by ONE AND ONLY ONE customer, while a given customer MAY HAVE placed ONE OR MORE orders. This will create a link between orders and customers, and thereby generating a business rule inside the database to enforce that relationship.

Structured Query Language (SQL)

SQL is the industry standard language for designing and communicating with relational databases.

Stored Function

A stored function is SQL (and procedural code, in most cases), placed in the database itself. It masks the business logic from the programmer. In addition, stored functions return only one value to the calling program, and can be used as part of DML statements and within calculations and conditional statements as a parameter.

Stored Procedure

A stored procedure is SQL (and procedural code, in most cases), placed in the database itself. It masks the business logic from the programmer. In addition, stored procedures represent a powerful tool to let all programmers have a generic interface to different access mechanisms to each table in the database.

Table

The physical implementation of an entity. This is where the actual data is stored as customers, orders, products, or whatever. Each table consists of one or more columns.

Trigger

A trigger is a stored procedure assigned to a given table. It ‘fires’ whenever you do an operation on that table (BEFORE/AFTER INSERT/UPDATE/DELETE etc.) Triggers are powerful, performance-enhancing mechanisms in the database.

Unique Identifier

The equivalent of a primary key. It may also be a candidate for creating unique indexes in addition to the primary key.

View

An imaginary table: A view may be constructed to give the user/programmer access to a limited resultset from one or more tables. It is often used for security reasons; restricting access through views.

However; it may also be a signal of insufficient design: Making a view to solve a problem with a query which is difficult to do in a single SELECT.