Purpose And Features Of A Relational Database Computer Science Essay

In the undermentioned study, I will be speaking about the chief grounds why we use databases, the benefits of utilizing them and a brief history of old database designs to the current database designs we have today. I will explicate some nomenclature about relational databases and the current properties within them. I will associate to Microsoft SQL Management Studio 2005 with some ocular screenshots and sentence structure quotation marks, these will be formatted consequently.

For the M1 standards, I will spread out on Primary Keys, remark on the benefits of referential unity and relevant information to maintain consistence and flow in a system.

For the M4 standard I will spread out on Cascading updates and supply grounds of using this into practise on my enforced database. Finally I will supply a decision summing up the chief points and giving my personal sentiment where I feel is necessary.

Background:

In the universe of hive awaying and recovering informations for organizational intents, a system called a 'Flat File Database ' was implemented.

Get quality help now

WriterBelle

Verified writer

Proficient in: Computer Science For Progress

4.7 (657)

“ Really polite, and a great writer! Task done as described and better, responded to all my questions promptly too! ”

+84 relevant experts are online

Hire writer

Herman Hollerith was a adult male in the seventiess that deliberately came up with an thought that informations could be stored and represented in a system, so level file systems was implemented. Flat file systems were built into IBMs runing systems and Microsoft NT during this clip.

Flat file systems are big groups of informations that is stored within a.txt file normally and has a really simple layout, normally dwelling of client informations separated with a | line. For illustration:

Michael Monaghan 17 Manchester Road Manchester M13 4GH | Anthony Monaghan 14 Fellow Close Manchester M13 4FG

The format of the level file system was non user friendly sing arranging, although it did carry through the right map for hive awaying client inside informations.

Taking into consideration of the Data Protection Act 1998 this would be accessed by administrative forces merely and would be encrypted when non in usage. If a database decision maker needed to seek the level file system for a particular record, they would normally utilize a 3rd party plan to question the information.

As engineering developed and new practises were put into topographic point, it was rapidly discovered that level file systems were non the most efficient manner to hive away and pull strings informations. There needed to be a more robust and advanced system available that would alter the data format of the database and be able to recover records with easiness and velocity. Relational databases were so created.

Relational databases:

The first relational design theoretical account was foremost proposed and implemented in 1969 by E.F. Codd. He came up with the thought of infixing informations into a centralized point and questioning informations based on qualities and characteristics of the information. This had so subsequently on, been developed to relational database designs and constructions that we see today.

The term relational, means to organize a relationship or relate to one another. In more specific footings, relational databases are two or more tabular arraies which have a connexion to each other by a field or value. These databases can incorporate a assortment of information sing what the company requires to hive away, such as on-line shopping. These can be linked so that, when a client purchases an point, the stock tabular array will so diminish its measure of that point etc.

Alongside the relational databases construct, an application is used to question the database, such as SQL Management Studio. SQL ( Structured Query Language ) is the linguistic communication in which we can pull strings and question the database/tables as specific or obscure as we need excessively.

SQL can be used to make databases, form relationships and restraints, modify tabular arraies and change single records when needed. In each tabular array it can incorporate many different Fieldss and columns, for illustration:

CustomerID, CompanyName, ContactName, ContactTitle etc. These are all used to organize the information into classs. Each of these columns is assigned informations types to maintain the referential unity of the database ( running with flexibleness ) . Data types are used to understate every bit much infinite as possible, without apportioning infinite that is non needed.

The above screenshot is exposing a tabular array being created utilizing SQL sentence structure in the direction studio. In the tabular array, each field is defined foremost ( CustomerID ) alongside the information type used ( VARCHAR ) .

Key Fields/Constraints/Referential Integrity:

Within a relational database, each record has to be alone and free from duplicated information. This is to salvage mistakes and possible jobs when mentioning to informations in the hereafter. For illustration, an employee might necessitate advising that they are required to manus in their hebdomad surrender notice. In consequence to this, the user is queried from the database and there is two names that have the same name therefore the surrender missive might travel out to the incorrect individual. In this scenario, cardinal Fieldss are made to be used.

Key Fieldss are made to unambiguously place a record by a figure, character or value that is non replicated throughout the database. So in the event of records holding the same family name, the decision maker can mention to its unique ID which is called a Primary Key.

A Primary Key is a tabular array restraint which affects the entireness of the tabular array it is working with. It allows each record to be alone and correspond to a alone figure. Repeated information does non supply a good standing point for referential unity for information to be queried. As a primary key is a table restraint, it will impact the complete tabular array.

Referential Integrity is the complete consistence of relationships between tabular arraies. Primary keys and foreign cardinal restraints provide referential unity throughout a database. If a Primary/Foreign key was removed, the unity of the relationship would be removed and destroyed between the tabular arraies. Tables need to hold Referential Integrity to halt repeated informations from being entered into the database, and conflicting with foreign cardinal restraints.

Referential Integrity needs to hold regulations to maintain informations consistent and robust. A primary cardinal restraint must be present within a tabular array to guarantee cogency of informations and a foreign cardinal restraint to associate relational tabular arraies with a suited relationship. It besides helps to halt N: N ( Many to Many Relationships ) from conflicted and destructing referential unity.

Many to many relationships cause repeated informations to be inserted within a tabular array, for illustration in a on-line library database, many books can cover many topics, and many topics can be listed in many books.

Another illustration would be a Student to Tutor tabular array, the Student may hold more than one coach and the coach would hold many different pupils.

To work out this, a tabular array must be inserted between the tabular arraies called an intermediate tabular array. An intermediate tabular array contains the both of the primary keys in a separate tabular array, this would be used to fit each ID together to happen an indivuaul. For illustration.

Tutor_ID = 1

Student_ID = 4

__________________________

Tutor_ID: 1

Tutor_Name = 'Michael '

Tutor_Surname = 'Monaghan '

Class = 'Maths '

___________________________

Student_ID: 4

Student_Name = 'Anthony '

Student_Surname = 'Monaghan '

We can now understand that Tutor_ID = 1 and Student_ID = 4, therefore Mr Michael Monaghan will be learning Anthony Monaghan. This is put between the Many to Many relationship and references the relating tabular arraies e.g. Student and Tutor tabular arraies.

Candidate keys are used when a cardinal field that could be negotiated to be used as a primary key in a tabular array. The decision maker needs to take into consideration that numerical values would be best suited than character values for a primary key.

A Composite key is a table with two alone properties that make up one primary key. This would be used to fit two properties within a tabular array. An illustration would be, ISBN Number and Serial_Number. These natural naming keys are besides known as Surrogate keys. Surrogate keys are natural calling keys that provide a alone property to a tabular array, such as Serial_Code or Barcode_Number.

Foreign keys are used to associate two tables/databases together to organize a relational database. One column might be updated as other records are updated on the other tabular array by the restraint. Foreign keys are besides known as a table restraint. Once the foreign key has been executed within the tabular array, the tabular arraies will hold a relationship. A relationship could be set up so that Customer orders can be displayed and updated on client purchases on another tabular array etc.

Table restraints are a alteration to the tabular array which affects the running of the full tabular array. A Primary key would impact the tabular array from come ining the same inside informations and non doing it alone. A column restraint is a similar construct which merely affects a column, such as 'not nothing ' statement, which defines that nil can be left clean in that column.

Cascading Updates/Deletes:

Cascading updates is the procedure that relational databases can offer to implement Referential Integrity. It allows the user to update Fieldss within a tabular array and the information set will overrule the bing columns. Cascading deletes besides allows the user to cancel ( bead ) a record and with the unities with that information will besides be removed redemptive clip and infinite.

Cascading updates besides helps to maintain informations consistent and enforce referential unity throughout the database. It allows records that are being updated, to update along with altering the values in its relationships. It ensures referential unity in a database and deletes/updates relational links.

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

The sentence structure of cascading updates/deletes follows every bit, on updating or omission of a field or informations, the undermentioned action will happen throughout the tabular array. For illustration NO ACTION means to execute no action to update or cancel, this is the default.

Cascade performs the action to update or cancel all ties of informations that is related to the alteration of the tabular array, .

SET NULL will put all values to NULL value where necessary and tag them as 'NULL ' when being updated or deleted.

SET DEFAULT will put the mark tabular array into a default province which was antecedently set in the restraint. For illustration:

CREATE TABLE Students

(

Stu_ID int NOT NULL PRIMARY KEY,

Name varchar ( 20 ) ,

Course_ID int default 1 CONSTRAINT coursestuFK FOREIGN KEY

Mention Courses ( Course_ID )

ON DELETE SET DEFAULT

If a specific class name was removed from the classs table all Students related to the foreign key 's value will be set to '1 ' .

To enable Cascading updates within the database, it has to be enabled aboard a restraint to another tabular array. For illustration in my database, I am declaring a foreign cardinal restraint from Course_ID in my Student tabular array and citing the classs tabular array on Course_ID. The cascading update/delete statement can so be executed.

Second, I insert the values into the Students table. This pupil will be used as a trial to demo the Cascading Updates/Deletes are working efficaciously.

The information is entered right and the 'Anthony Monaghan ' record is completed with the Course_ID relating to the English class in the Courses tabular array.

I use the DELETE FROM statement which allows me to take Fieldss within tabular arraies based on a alone value. There is merely one class called English I wish to take, so I remove English.

When questioning the Courses tabular array once more, the English field is removed.

I use the statement SELECT * FROM Students and with the cascading delete option on, the Anthony Monaghan field was removed as the Course_ID English was removed.

Transaction Logs:

Transaction logs are used within SQL to turn over back any running statements which have encountered an mistake. If two statements were executed at the same clip and an mistake occurred, none of the statements would be executed. Here is an illustration of utilizing a dealing

BEGIN TRANSACTION

{

sql_statement | statement_block

}

END Transaction

The SQL statements would be contained within the BEGIN and END TRANS.

In decision to the undermentioned study, I have outlined the many different facets and characteristics that make an up relational database such as cascading updates, cardinal restraints e.g. primary, composite, campaigner and foreign keys. I have explained the definition of each and given relevant sentence structure mention where needed. I have besides used images to back up my statement when giving points on informations types and questioning from the relational database.

I have felt with the considerable sum of research and development I have experienced when working with SQL during this assignment, that It has broaden my cognition of how big and little companies organise their database and the different design attacks.

To sum the chief points in the assignment, relational databases have an exceeding advantage over level file systems as the direction capablenesss are much more elaborate with respects to arranging and easier to question information where needed.