Relationships

Explain the features of a relational database (P1)

A relationship is a defined connection between the rows of two tables. This connection is general determined by values in selected columns from the parent table that correspond to values in the child table.

One-To-Many

A one-to-many relationship is when a parent record in one table can potentially reference several child records in another table. In a one-to-many relationship, the parent is not required to have child records; therefore, the one-to-many relationship allows zero child records, a single child record or multiple child records (the child record cannot have more than on parent record).

One-To-One

A one-to-one relationship I when a record in one table is linked to only one other record in another table. A record cannot link to more than one record in another table, it must only link to one. The number of rows in Table A must equal the number of rows in Table B.

Many-To-Many

A many-to-many relationship is when on ore more rows in a table are associated with one or more rows in another table. An example of this I that a table of customers who can purchase many different products and a table of products that can be purchased by many different customers.

Normalisation

Normalisation is the process of organising data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Attributes

Businesses can use attributes to help them. This can be done by identifying the most important factors. For example, ‘Name, address, postcode etc.’ and then putting them in entities.

Data Types

Text/Alphanumeric

Text or alphanumeric data is made up of letters and/or numbers. Also symbols and spaces are also allowed into this data type.

Forename: Jack
Surname: Young
Address: 123 Sample Street

Number/Numeric

Number or Numeric data is made up of whole numbers or decimal numbers to be used. Only numbers can be entered and not letters or symbols.

Age: 18
Height (cm): 172.8

Currency

Currency data type automatically formats the data to have a £ or $ or € symbol in front of the data and also ensures there are two decimal places.

£5.75
$54.99
€0.80

Date/Time

The date and time data type restrict data entry to 1-31 for day and 1-12 for month. It checks if the date exists. It formats the data into long, medium or short date/time. If we wanted to input 29th February 2015 then it will be wrong. Yes, the day is in the requirements of 1-31 days, but the database is smartly configured to exclude dates like 29th February 2015 which does not exist. However, 29th February 2016 does exist. This will be handled automatically.

Long Date: 20 March 2015
Medium Date: 20-Mar-15
Short Date: 20/03/15
Long Time: 13:33:37
Medium Time: 1:33 PM
Short Time: 13:33

Boolean

Data is restricted to one of only two choices.

True/False
Yes/No
Male/Female
Hot/Cold

Validation Rules

In a database you can set rules which will allow the user to use options such as words or numbers. An example of a rule can be “Gender = Male + Female” is allowed. If wrong character is put into the database then an error message will be displayed. The user would then need to re-enter the characters in again until it matches Male or Female. This makes it so there are less errors in the database.

Entities

Explain referential integrity and the purpose of primary keys in building the relationships between tables (M1)

Entities can be related to each other through Primary Key and Foreign Key because, instead of typing the entities all over again, you can use a key on another part of a database and link the entities through it.

Primary Key

A primary key is a unique ID which can be used to identify a record in a database. When a table is created, one of the fields (usually the first field) is given a primary key like 1, 12, 67 etc. You can have as many records as you want, a primary key will still be assigned no matter the number. While the primary key is often a number, it may also be a text field as well or any other data type.

Foreign Key

A foreign key is a key that is a column of groups or a group of columns in a relational database table that provides a link between data in two tables. For example, you may link a primary key to another part of a database and then it will make a copy of that ID and will then be transferred as a foreign key into the other table.

Referential Integrity

Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.

Data Redundancy

The data that is used more than once in a table or in the whole database is called data redundancy. Sometimes data redundancy occurs by accident but is also done deliberately for backups. Data redundancy can be avoided by organising different fields and tables within the database, much like a relational database. The most common way of removing redundancy is to split all the data into tables which is correctly relational to the other tables.

Benefits of Relational Database

• Databases save time. Retrieving a single record can be done with just a few clicks in a database. On the other hand, trying to find a document in a paper-based system may take several minutes. Furthermore, making reports using relational databases is a lot quicker than making a report in a word document. You can produce all kind of reports according to the user’s needs This reduces the huge amount of time to do it in the paper form.
• Databases give you lots of different ways to look at your data. You can use query’s (database questions) to request a specific answer or list from your database. This can take just a minute or two to write initially into the database as opposed to hand-collating data records or sorting and resorting a spreadsheet.
• You can link entities together using primary and foreign keys, this means that you do not have to retype all the entities in another table.

How Potential Errors in the Design and Construction of a Database can be Avoided

Discuss how potential errors in the design and construction of a database can be avoided (D1)

Accidental Deletion of Fields

When a database is in use, sometimes it is easy to accidentally delete fields in the tables which could result in re-doing the data that has been lost. One way to avoid this would be to make sure to backups are made of the database, so whenever you delete fields by accident then it would not be a pain to replace. Having said this, it becomes a nuisance when the database is not backed up as it will take a lot of time to retype the data. To avoid accidental deletion in future, you can set permissions in the database so only the administrators can edit it. This means that anyone else who is not an administrator that is using the database have no authorisation to edit the database but can do other tasks like view it or create reports etc.

Incorrect Data Types

Incorrect data type can occur when a data type allocated is not matched with the data type used. For example, if there is a field called ‘Salary’ and you allocated this field to use a numerical data type, then putting a text data type like ‘Twenty One-Thousand’ would be wrong as it is not the correct data type to use (The correct value would be ‘21000’. This can be avoided as you can tell the database to only allow numerical values to be inputted within that field, so this error will not accrue again. The database will not allow anything inputted other than numbers.

Renaming Incorrectly

Renaming something incorrectly into a database can lead to errors in the near future. For example, if a bank put an extra 0 at the end of their balance then the customer would probably think that he/she has that money. This mostly occurs if editing a database manually instead of an automated process. Since typing in the database manually has higher chance of making a typo. This means if the error is not spotted as quickly as possible, then it could lead into legal problems or loss of profit. To reduce typo errors, the user that is editing the database should double check what they have done in order to be certain that the data has been inputted is correctly.

Validation

Validation is when the database checks a requirement set by the user to see if the inputted data matches with the correct requirement and becomes validates. For example, if I put in a requirement for the age field in my table whereby only people who are 18 or over can sign up to my website. The command would look something like this ‘>=18’, this means that anyone who is equal to 18 or greater are allowed to sign up to my website. When someone signs up to my website and they inputted information to say they are 17, then the sign-up process would not proceed as the validation checks are unsuccessful. Using this method in the database helps to cut down on unwanted data that you do not want, and it saves a lot of space.

Null Values

Null values in a database essentially means that there is ‘Nothing there’. This can lead into problems in a database as some parts of it may not be able to work because it is unable to recognise what the user wants it to do. If the database has not been setup correctly to deal with null values, then it would simply ignore it and move to the next step. This can be a problem, so, to prevent this from happening, you can use of a validation method explained above. The validation method will stop the null value from happening. For example, if an online application form where if no value were to be entered in an entry box (which is a requirement like email and username) then the user would be sent an error message to fill out the requirements of the application form like if they forgot to put a username in etc. This makes it so that the null values will be reduced, and the database would work correctly.