Allow easy access and addition
A data base will frequently need, to be changed added to, taken from, and found. Databases will most of the time have a form to do all this. This means that a company can not only keep track of details, but also add, modify and remove them, in a user friendly, easy to use manor. This also prevents data redundancy and lost/ disorganised data, because everything is completed in real time.
Increase company operations rate
If the details for a company are stored in a database they can be searched for and pulled up quickly and easily. This means that accessing data doesn’t become a time killing chore. It instead becomes a quick job that, takes little or no time to do and the work on the actual data can be started quicker.
Discover Data Discrepancy
A database has validation rules in its tables. This means that data will correct, but also two records can be pulled up next to each other and double checked, in case of fraud and incorrect details. Also if duplicate records are taken for whatever reason, these can be detected and eradicated, or adequately marked as duplicate. If the database is designed correctly these records can be automatically pulled out.
Features
Tables, Records & Fields
The information stored in a database is recorded into tables. Similar to a spreadsheet, a database will store a small amount of data in each space. Each table will have a certain number of fields. Each field defines a specific piece of data about each record. A record is a set of data about one specific item. For example an onine shop might have a customer table. This would have one customer per record, and each piece of data defined about said customer would be defined by the field. Here is an example. Each Row is a record (except the top one which are the field identifyers.
This setup in some form or another is used in every kind of datbase. The above table, is a good but very small, example of a database. Most databases will have many more tables and they will be linked to eachother through Relationships.
Relationships
Relationships can initially seem useless, but they are used for linking up tables within a database, to add organisation and integrity. Data can be correlated, and if there is a field that needs to be repeated in another table the two fields can be linked. This means that if the Customer table had a Customer ID field and the Order table also had the customer ID field; the Order table would always have valid Customer ID , and if the end user needed to view who the order belonged to, the customers details could be pulled up instantly by using the relagtionship, instead of maunally rooting through the customer table. It is a powerful and valid function within a database and is often a useful step towards Normalisation. Sometimes a database can be connected in a different way- Normally it will be a one to one relationship (linking two fields from two different tables together), however sometimes a one-to-many relationship is required- for example if a store has many customers- a one to many relationship will be required so that each store will have every required detail about the customer linked to it. Also Many-to-Many Database Relationships exist, for example if many customers have a Merlin Pass; they will go to many parks, and in this case many to many relationships could be needed however they can mostly be overcome by creating another table that can hold bridge the gap so that only one to many relationships have to be used. Especially seeing as how some databases do not allow many-to-many, or cannot support them very well.
Normalisation
This process has four goals
- Arrange the data into easy to access and logical groups
- Minimising the amount of duplicated data
- Creating a database in which you can easily and quickly change and access the data- without compromising structural integrity
- Linking the tables up in such a way that if the data needs to be changed, and is duplicated throughout the databases , when it is changed these changes are also applied
It means that the database will conform to similar standards with that of other normalised databases, but also the database will be smaller and therefore faster and easier to access and manage
Field Properties
Each Field which represents a piece of data about each record. Some records may not use each data field, but each field will always have at lease one record using it or else it would become redundant. A Field property defines the kind of data that will be stored in the field for each record. These properties will not be directly visible when viewing the form, however it will make itself evident, by the data which is allowed to be entered, and the type of data each field contains. The field properties can be changed in the form edit mode. The properties allowed can be seen in the above screen shot. Sometimes the properties of a field are essential to relationships, integrity and table linkage.
Key Fields
There are several types of Key Field possible in a database.
-SuperKey
-Candidate Key
-Primary Key
-Foreign Keys
The BOLD keys are most commonly used. Other keys are not as important and will not be discussed here.
Primary Keys
- Detail the order or the records
- Allow every record to be unique
- Provide a method of linking tables together.
(A SuperKey is a collection or a single field that identifies the records within that table uniquely. A candidate key will be one or more of the Super keys which will only contain the utmost required fields in order to uniquely identify it. Only one candidate key can become the primary key. Sometimes a new unique ID field will be made and no candidate keys will be used)
Foreign Keys
-Allow relationships between tables.
-Identical to the Primary Key in another field
-Used for cross referencing between tables within a database, for example- if a customer has a complaint, the customers ID who made the complaint can be stored in the complaint table as the foreign ID.
Referential Integrity
When two tables are linked via a primary and foreign key, sometimes if the primary key is deleted there will be redundant values in tables where the foreign key exists, that have nothing to link to, errors will appear and problems with the database will occur. This can be prevented in one of two ways- one if a primary key is deleted and the foreign keys linked to it are not, an error will occur and prevent redundancies- this works through Errors. The other method is of course to set up a Macro that will delete all foreign key records attached to it, when deleting a record from the primary key table in question. If deleting a foreign key this integrity does not need to be maintained. This ensures the integrity of the database after records are deleted
Relationship Settings
In some databases options can be set to maintain referential integrity when creating a relationship that links said two tables together. There are three options in Microsoft Access with other programs offering similar options. Option One. “Enforce referential Integrity” This means that if a value is entered in one assigned field that doesn’t correspond with data in the linked table, an error will occur and the change will be prevented. The other two options are “Cascade Update Related Fields” and “Cascade Delete Related Fields”. When a user changes or deletes the data in a relationship between two tables the data will be updated or deleted in the other table too, according to what action was performed and which boxes were ticked. These ensure stability and cleanliness of the file
Errors
Errors are an essential part or building and maintaining a database. Annoying as they are, errors maintain integrity, working tables and forms, and do not allow you to save or continue until the problem has been resolved.
Queries
A database query is simply the process of requesting specific information from a database in a newly generated table (temporary), and because the data in a query is subject to change, every time it is requested a new page of results is made. A Query is simply based on a series of values that grab the required data from any table into a new and condensed as required table. Very often new queries need to be made to get data in new ways, however this can also be achieved through revision of older ones.
Reports
A report is simply a way of displaying specific data, most of the time from queries, and presenting it with custom text and pictures. For information on selecting the data for a report - see Queries. These can be printed off or sent in email, for easy reading. These are intended for presentation and display mostly.
Macros
Macros perform mundane and often forgotten functions quickly and simply without using too many resources, be they human or computer. They are a simple set of instructions the computer must follow in order to perform tasks that have to be done repeatedly, for example, deleting redundant records and keeping the database clean.
Databases are large and complex, both in theory and in implementation. I hope this report has helped you to understand the purpose and features of a database.
Nick Hutchins – P1 – M1 – M4
