P1:What is Relational Database?Relational databases work to eliminate the duplication of data.
Rather than holding every piece of associated information on a single table, the table would be split into data sections or segments. As an example of this, a college might build a table comprising student names, ages and addresses held in a primary field, i.e. student ID number. This field would be linked to a further table used as a new student “enrols” onto a “course”, this in turn would be connected to another table headed “tutor”, which would identify the “tutor” and “study room” allocated, according to the learning programme. Once a table is created, it is necessary for it to be linked with other relevant tables and built into a database; the database helps to maintain accuracy, availability, usability, resilience and organisation; also it serves to avoid duplication by sieving out multiple pieces of identical data. In addition, the database can be used for storing, managing and retrieving relevant information.
The main features:When it comes to databases they contain a number of main features, which comprise customisable templates, calculated fields for powerful database application, integrated search, permission and access, mail merge, import/export between databases and apps, relational databases capability, customisable views and print reports, field types and integration. The features listed are highly beneficial to a database system because they enable the speedy access to data. There are also other main features that are very important to any database, specifically these include:Entities/tables:Data is stored by using tables, thereby enabling the information contained to be accessed for viewing whenever it is required. The data can be stored in countless volumes of tables, and all databases can store vast numbers of tables. Attributes/fields:A field refers to a piece of data/information contained inside a record, and sometimes will also be referred to as a “record”.Records:Records relate to singular pieces of different types of information, which might be first/last name, are, age and gender etc.
; this type of information being known as a field. Forms:Forms within a database relate to where and how input data is placed. A benefit of using forms is that restrictive criteria can be applied to ensure sure that only valid/relevant data is entered into the form.Queries:Queries significantly ease the way to the finding and researching of data, and are used to locate a set of data as defined by the user.The examples above set out a number of ways, in which a database can store, receive, send and search for data within specific parameters to enhance the speedy retrieval by the user.
Attributes play a major role in strengthening the storage and retrieval aspect of data held within any database. Data types:Databases are permanently maintained to ensure that the data is reliably available and efficiently accessible. Data can normally be classified into certain ‘data types’. The main data types are:Text/alphanumeric:This refers to the data that is made up of both letters and numbers; this may also facilitate the incorporation of symbols and spaces.
For example:Forename: JohnSurname: BlogAddress: 73, Horsham RoadPostcode: RH13 5PBNumber/ numeric: This allows for whole or decimal numbers to be used. Only numbers can be entered; no letters or symbols.For example:15 (whole)21.35 (decimal)Currency:This automatically formats the data, preceded by £ or $ or Euro symbol, and also allows for two decimal places.
For example:£5.75$54.99Date/ time:This restricts data entry to 1-31 for days, and 1-12 for months.
Legitimacy and existence of the date is verified, and formats the data into long, medium or short date/time.For example:Long Date: 20 February 2006Medium Date: 20-Feb-06Short Date: 20/02/06Long Time: 18:21:35Medium Time: 06:21 PMShort Time: 18:21Boolean:Data is restricted to one of only two choices.For example:Yes/NoMale/FemaleHot/ColdRelationships:Relationships within a database are important as they keep tables connected to allow the database to work. The data is split into sensible data groups; for instance student ID data, enrol data, course data, tutor data and room data. This data will then be separated into a table where each of the categories will split into groups; once the tables are setup then the relationship can be progressed linking them together. With relationships, one primary benefit for the database relates to the elimination of double entry data and duplication. In practice, when a student enrols on to a course then a completely new record will appear on the course table, which will aid the identification of that student.
By reducing data duplication it lowers the risk of errors being made, every mistake means a re-type for each inaccurate or piece of lost/missed data, whilst increasing the further risk of misspelling when re-entering the information onto the database.Data redundancy:The same item of information that is available and used from more than one source within the database table can lead to data redundancy. It is a condition that is created within a database or data storage where the same piece of data is held in two different places. This can occur by accident, but is also done deliberately for backup and recovery purposes. Data redundancy can be avoided by developing and organising different fields and tables within the database. To do this, large tables can be split into smaller ones with the relationships between them being defined. The object of this is to separate the data, which means specific deletion of data, modifications and additions to fields.M1:When tables are linked together and a primary key is found in another table, it is referred to as a foreign key.
Referential integrity keeps the relationships between tables that are linked together remain consistent. For example, where there are two tables such as a booking table and a payment table and passenger IDs (primary keys) are assigned to both tables, referential integrity would make sure that all records between those two tables will correspond. If a user attempts to add any records in the foreign key, this will not be possible as it already contains a corresponding record. Also, if any records in either the booking or payment table were deleted, (being linked), referential integrity will ensure the linked tables are updated. When tables are linked and a foreign key is in existence, referential integrity does not allow the deletion of records from the primary key where its records are already being used in another table that it is linked to. For example, in a table that has details of flights, it would not be possible to delete a flight ID, this being due to the fact that it will be linked to other tables, such as a booking table and other passengers that have booked that particular flight.Primary keys are unique sets of numbers used to identify someone or something in a table.
In a database, primary keys will be given to each individual on the table so that they can all be identified when required, and if mixed into other tables. For example, in retail, when ordering any item at certain retailers such as Argos or Screwfix, the item identification number would be the primary key to the staff at the checkout; they will check the number against their stock before purchase. Without the primary key it would require a time consuming search through their database just to find the item. By using primary keys it speeds up the process of product location and being able to find items in store. Relationships between tables can be singular or many multiples, for example in a one-to-many relationship, one primary key could link with only one table, where others may link to a number of tables.
For example in a one-to-many relationship, if a customer has booked a flight, the primary key containing their record would link to a booking table, and a flight table and also a payment table.D1:Discuss how potential errors in the design and construction of a database can be avoided.When working on databases, a common issue met with is the accidental deletion of fields in a table. For example, if a user were checking over their database and editing some fields, it would be easy to accidentally over-write a number of fields without noticing the mistake.
Once discovered, the error may be difficult to retrieve everything lost. Likewise, where intentionally deleting data and where the user deletes the wrong field, saving the database upon closing; the data would be lost, having been permanently deleted. A way to avoid the deletion of fields is to firstly, ensure a check is made over the spreadsheet of what, if any, changes have been made during the session before saving and exiting the database; this will enable unwarranted deletions can be retracted. Another way is to ensure the creation of more than one backup file of the database, so that in the event, any unplanned action can always be reversed, and the status quo retrieved. This enables any deleted data ensuring can always be pasted into the currently updated database being worked on. A more secure may of preventing deletion is to lock the database so that only the original user is able to use it.
For example, by doing this were another user to try and access the database and make changes to it, this would not be possible because only the original user (i.e. creator of the database) has permission to use, edit and save the database.
An error that may occur, which can also be frustrating, is when entering “nil values”. When fields don’t always require any value entered; for example, where a customer were to insert their personal details into an application form and leaving a field blank. This may cause errors in the database, as it will be unable to function effectively with data fields incomplete. A solution to the problem is to suggest a nil value in order for the field left blank can be filled in.
To prevent this error from presenting itself, the validation rule should be employed. A validation rule would need to be set with an error message appearing, that instructs the applicant to fill in all fields available, which will then allow the applicant to submit their form, advising the user that the primary key field cannot be left empty; this would be avoid the nil value issue occurring.Validation is used to check that data entered is valid. Within a database, validation rules will be set relating to the entry of information onto forms, and notification to the user where data is missing or invalid. Without validation, incorrect or false information would be used by a database resulting in malfunction or a fraudulent outcome. For example, social networking sites such as Twitter and Facebook stipulate that users must be over the age of 13 in order to create an account.
Any person completing the online application would be subject to validation rules that only permit people that are aged 13 and over to click continue and successfully create an account on their website. Where a younger person mistakenly endeavours to continue creating an account, an error message occurs, notifying them that they are below the age required to hold an account. In this circumstance, by having validation rules, it ensures accuracy of data and validity of honest customers.Another common error that can occur in databases is the entering of incorrect data types.
For example, in the image below, in the column with the field name “DOB” the database requires only numbers to be used when entering the information, and where text is entered, this will not allow the user to submit data unless entered in numeric terms. The main solution to this error is for input masks to be set up so that when entering data into these fields, only numbers are accepted. Another way of preventing this error where a user tries to input text into this field, an error message appears advising that the information type entered is invalid, and that they should insert the correct data type in order to proceed. Another way of avoiding this error would be the use of drop down lists, in this case for the DOB a drop down list would be needed for each section in the DOB (the date, the month and the year), this would lessen the chance for errors that could occur should the wrong data types be entered into this field.Test Type What is expected in the test What happened in the test Error Message Name entry I can enter my name in the box I entered my name and it worked N/AValidation I can’t move on unless the boxes are all filled I tried to miss the box for every box and it prompted me to fill it with a custom message depending on with box needed filling Error message – “You must enter a value in the student. Date of birth field” Drop Downs (look up wizards) The look up wizard should create a drop down of titles and courses I clicked on the drop downs and they worked N/APost code You can only enter a post code in the box I tried to enter a mixture of letters and numbers but it would only allow a post code format and also wouldn’t allow dates higher than present day Phone number So you can only enter phone numbers in the box Wouldn’t let me add anything other than a number