center850008549640By Vishal Solanki 1000000By Vishal Solanki
TOC o “1-3” h z u TASK 1 PAGEREF _Toc507054632 h 2Introduction PAGEREF _Toc507054633 h 2Benefits of Relational database PAGEREF _Toc507054634 h 3How relational Database can decrease data redundancy PAGEREF _Toc507054635 h 5Referential integrity PAGEREF _Toc507054636 h 9Range of common errors their impact and possible avoidance PAGEREF _Toc507054637 h 10Conclusion PAGEREF _Toc507054638 h 11References PAGEREF _Toc507054639 h 12
TASK 1IntroductionRelational database – was created by Edgar Codd (of IBM Research) around 1969. It has since become the dominant for database model for commercial applications (in comparison with other database models such as hierarchical, network and object models). Now, there are many commercial Relational Database Management System (RDBMS), such as Oracle, IBM DB2 and Microsoft SQL Server. There are also many free and open-source RDBMS, such as MySQL, mSQL (mini-SQL) and the embedded JavaDB (Apache Derby).
A relational database organizes data in tables (or relations). A table is combined up of rows and columns. A row is also called a record / tuple. A column is also called a field / attribute. A database table is similar to a spreadsheet. However, the relationships that can be created among the tables enable a relational database to efficiently store huge amount of data, and effectively retrieve selected data. A language called SQL (Structured Query Language) was developed to work with relational databases. The relational database model is based on a mathematical concept where relations are interpreted as tables.
Benefits of Relational database1. Data is only stored once. the city data was gathered into one table so now there is only one record per city. The advantages of this are
No multiple record changes needed
More efficient storage
Simple to delete or modify details.
All records in other tables having a link to that entry will show the change.
2. Complex queries can be carried out. A language called SQL has been developed to allow programmers to ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’ table records. These actions are further refined by a ‘Where’ clause. For example
SELECT * FROM Customer WHERE ID = 2
This SQL statement will extract record number 2 from the Customer table. Far more complicated queries can be written that can extract data from many tables at once.
3. Better security. By splitting data into tables, certain tables can be made confidential. When a person logs on with their username and password, the system can then limit access only to those tables whose records they are authorized to view. For example, a receptionist would be able to view employee location and contact details but not their salary. A salesman may see his team’s sales performance but not competing teams.
4. Cater for future requirements. By having data held in separate tables, it is simple to add records that are not yet needed but may be in the future. For example, the city table could be expanded to include every city and town in the country, even though no other records are using them all as yet. A flat file database cannot do this.
How relational Database can decrease data redundancyData Redundancy
Data should not be redundant, which means that the duplication of data should be kept to a minimum for several reasons. For example, it is unnecessary to store an employee’s home address in more than one table. With duplicate data, unnecessary space is used. Confusion is always a threat when, for instance, an address for an employee in one table does not match the address of the same employee in another table. Which table is correct? Do you have documentation to verify the employee’s current address? As if data management were not difficult enough, redundancy of data could prove to be a disaster.
Primary key-foreign key relationship
defines a one-to-many relationship between two tables in a relational database. A foreign key is a column or a set of columns in one table that references the primary key columns in another table. The primary key is defined as a column (or set of columns) where each value is unique and identifies a single row of the table.
This is an automatic check to ensure that the data entered is sensible and feasible. Validation cannot ensure data is actually accurate.
There are different types of validation checks a database can run:
Validation type How it works Example
Check digit The last couple of digits can be used as a ‘check sum’ that can detect if errors have occurred Bar code readers in shops
Type check Checks the data is in the right format Numbers in currency cell must be a monetary value with two decimal points
Length check Checks the data is an acceptable length A PIN for online banking needs to be four (or six) characters long
Lookup table Checks that the value provided matches an item in a set list A limited set of values, such as the seven days of the week
Presence check Checks that data has been entered into a field In most databases a key field cannot be left blank
Range check Checks that a value falls within the specified range An online gift certificate purchase must be more than or equal to £5 but less than or equal to £50
Spell check Looks up words in a dictionary A search engine often recommends a correct spelling if a word is spelt wrong
Input mask Checks that data has been entered with the correct amount of characters and/or numbers National insurance numbers need to be in the format: YY XX XX XX Y, where Y = letter and X = number
Duplicate Checks that a value has not been repeated A primary key value can only be entered once
The computer will use these rules to check whether the data entered is correct. If it is invalid an error message will be created.
This is the most common type of integrity constraint. This is used to manage the relationships between primary and foreign keys.
Referential integrity is best illustrated by an example.
Let’s assume the department and employee entities have been implemented as tables in a relational database system.
When entering a new employee, the department in which they work needs to be specified. Department number is the foreign key in the employee table and the primary key in the department table.
In order to preserve the integrity of the data in the database there are a set of rules that need to be observed:
If inserting an employee in the employee table, the insertion should only be allowed if their department number exists in the department table
If deleting a department in the department table, the deletion should only be allowed if there are no employees working in that department
If changing the value of a department number in the department table, the update should only be allowed if there are no employees working in the department whose number is being changed
If changing the value of a department number in the employee table, the update should only be allowed if the new value exists in the department table
If any of the above is allowed to happen then we have data in an inconsistent state. The integrity of the data is compromised – the data does not make sense.
Oracle, and other relational database management systems, will allow enforcement of referential integrity rules. If implemented, and a user tries to break any of the rules, an error message will be given and the change will not take place.
if the primary key value of a row in a referenced table is updated, all rows in the referencing table with a foreign key value equal to the primary key value of this row, should also be updated to the new value. Using our previous example, when the value of a department number in the department table is updated, any department numbers in the employee table equal to the department number being updated are also changed to the new value.
if a row in the referenced table is deleted, then all rows in the referencing table with a foreign key value equal to the primary key value of the row should also be deleted. Using our previous example, when a department is deleted from the department table, any rows in the employee table that reference the department number are also deleted.
Range of common errors their impact and possible avoidance
References BIBLIOGRAPHY Authority, S. Q., 2008. https://www.sqa.org.. Online Available at: https://www.sqa.org.uk/e-learning/SoftDevRDS03CD/page_10.htmAccessed 10 february 2018.
Authority, S. Q., 2008. https://www.sqa.org.uk. Online Available at: https://www.sqa.org.uk/e-learning/SoftDevRDS03CD/page_11.htmAccessed 11 february 2018.
BBC, 2018. http://www.bbc.com/education. Online Available at: https://www.bbc.co.uk/education/guides/zdvrd2p/revision/1Accessed 12 february 2018.
Hock-Chua, C., 2010. https://www.ntu.edu.sg. Online Available at: https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.htmlAccessed 11 february 2018.
Jones, C., 2016. www.teach-ict.com. Online Available at: http://www.teach-ict.com/as_as_computing/ocr/H447/F453/3_3_9/database_design/miniweb/pg8.htmAccessed 11 february 2018.
stephens, R. p. a. R., 2003. http://www.informit.com. Online Available at: http://www.informit.com/articles/article.aspx?p=30646Accessed 10 february 2018.