Purpose And Features Of A Relational Database Computer Science Essay

In the following report, I will be talking about the main reasons why we use databases, the benefits of using them and a brief history of old database designs to the current database designs we have today. I will explain some terminology about relational databases and the current attributes within them. I will relate to Microsoft SQL Management Studio 2005 with some visual screenshots and syntax quotes, these will be formatted accordingly.

For the M1 criteria, I will expand on Primary Keys, comment on the benefits of referential integrity and relevant information to keep consistency and flow in a system. For the M4 criteria I will expand on Cascading updates and provide evidence of applying this into practise on my implemented database. Finally I will provide a conclusion summing up the main points and giving my personal opinion where I feel is necessary.

Background:

In the world of storing and retrieving data for organisational purposes, a system called a ‘Flat File Database’ was implemented. Herman Hollerith was a man in the 1970s that intentionally came up with an idea that data could be stored and represented in a system, so flat file systems was implemented. Flat file systems were built into IBMs operating systems and Microsoft NT during this time.

Flat file systems are large groups of data that is stored within a .txt file usually and has a very simple layout, usually consisting of customer data separated with a | line. For example:

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

The format of the flat file system was not user friendly regarding formatting, although it did fulfil the correct function for storing customer details. Taking into consideration of the Data Protection Act 1998 this would be accessed by administrative personnel only and would be encrypted when not in use. If a database administrator needed to search the flat file system for a specific record, they would usually use a third party program to query the information.

As technology developed and new practises were put into place, it was quickly discovered that flat file systems were not the most efficient way to store and manipulate data. There needed to be a more robust and advanced system available that would change the formatting of the database and be able to retrieve records with ease and speed. Relational databases were then created.

Relational databases:

The first relational design model was first proposed and implemented in 1969 by E.F. Codd. He came up with the idea of inserting data into a centralised point and querying data based on qualities and features of the data. This had then later on, been developed to relational database designs and structures that we see today.

The term relational, means to form a relationship or relate to one another. In more specific terms, relational databases are two or more tables which have a connection to each other by a field or value. These databases can contain a variety of information regarding what the company requires to store, such as online shopping. These can be linked so that, when a customer purchases an item, the stock table will then decrease its quantity of that item etc.

Alongside the relational databases concept, an application is used to query the database, such as SQL Management Studio. SQL (Structured Query Language) is the language in which we can manipulate and query the database/tables as specific or vague as we need too.

SQL can be used to create databases, form relationships and constraints, modify tables and change individual records when needed. In each table it can contain many different fields and columns, for example:

CustomerID, CompanyName, ContactName, ContactTitle etc. These are all used to organise the data into categories. Each of these columns is assigned data types to keep the referential integrity of the database (running with flexibility). Data types are used to minimise as much space as possible, without allocating space that is not needed.

The above screenshot is displaying a table being created using SQL syntax in the management studio. In the table, each field is defined first (CustomerID) alongside the data type used (VARCHAR).

Key Fields/Constraints/Referential Integrity:

Within a relational database, each record has to be unique and free from duplicated data. This is to save errors and potential problems when referring to data in the future. For example, an employee might need notifying that they are required to hand in their week resignation notice. In result to this, the user is queried from the database and there is two names that have the same name thus the resignation letter might go out to the wrong person. In this scenario, key fields are made to be used.

Key fields are made to uniquely identify a record by a number, character or value that is not replicated throughout the database. So in the event of records having the same surname, the administrator can refer to its unique ID which is called a Primary Key.

A Primary Key is a table constraint which affects the entirety of the table it is working with. It allows each record to be unique and correspond to a unique number. Repeated data does not provide a good standing point for referential integrity for information to be queried. As a primary key is a table constraint, it will affect the complete table.

Referential Integrity is the complete consistency of relationships between tables. Primary keys and foreign key constraints provide referential integrity throughout a database. If a Primary/Foreign key was removed, the integrity of the relationship would be removed and destroyed between the tables. Tables need to have Referential Integrity to stop repeated data from being entered into the database, and conflicting with foreign key constraints.

Referential Integrity needs to have rules to keep data consistent and robust. A primary key constraint must be present within a table to ensure validity of data and a foreign key constraint to link relational tables with a suitable relationship. It also helps to stop N:N (Many to Many Relationships) from conflicted and destroying referential integrity.

Many to many relationships cause repeated data to be inserted within a table, for example in a online library database, many books can cover many subjects, and many subjects can be listed in many books.

Another example would be a Student to Tutor table, the Student may have more than one tutor and the tutor would have many different students.

To solve this, a table must be inserted between the tables called an intermediate table. An intermediate table contains the both of the primary keys in a separate table, this would be used to match each ID together to find an indivuaul. For example.

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, thus Mr Michael Monaghan will be teaching Anthony Monaghan. This is put between the Many to Many relationship and references the relating tables e.g. Student and Tutor tables.

Candidate keys are used when a key field that could be negotiated to be used as a primary key in a table. The administrator 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 unique attributes that make up one primary key. This would be used to match two attributes within a table. An example would be, ISBN Number and Serial_Number. These natural naming keys are also known as Surrogate keys. Surrogate keys are natural naming keys that provide a unique attribute to a table, such as Serial_Code or Barcode_Number.

Foreign keys are used to link two tables/databases together to form a relational database. One column might be updated as other records are updated on the other table by the constraint. Foreign keys are also known as a table constraint. Once the foreign key has been executed within the table, the tables will have a relationship. A relationship could be set up so that Customer orders can be displayed and updated on customer purchases on another table etc.

Table constraints are a change to the table which affects the running of the entire table. A Primary key would affect the table from entering the same details and not making it unique. A column constraint is a similar concept which only affects a column, such as ‘not null’ statement, which defines that nothing can be left blank in that column.

Cascading Updates/Deletes:

Cascading updates is the process that relational databases can offer to enforce Referential Integrity. It allows the user to update fields within a table and the information set will override the existing columns. Cascading deletes also allows the user to delete (drop) a record and with the integrities with that data will also be removed saving time and space.

Cascading updates also helps to keep data consistent and enforce referential integrity throughout the database. It allows records that are being updated, to update along with changing the values in its relationships. It ensures referential integrity 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 syntax of cascading updates/deletes follows as, on updating or deletion of a field or data, the following action will occur throughout the table. For example NO ACTION means to perform no action to update or delete, this is the default.

CASCADE performs the action to update or delete all ties of data that is related to the modification of the table,.

SET NULL will set all values to NULL value where necessary and mark them as ‘NULL’ when being updated or deleted.

SET DEFAULT will place the target table into a default state which was previously set in the constraint. For example:

CREATE TABLE Students

(

Stu_ID int NOT NULL PRIMARY KEY,

Name varchar (20),

Course_ID int default 1 CONSTRAINT coursestuFK FOREIGN KEY

REFERENCES Courses (Course_ID)

ON DELETE SET DEFAULT

If a specific course name was removed from the courses 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 alongside a constraint to another table. For example in my database, I am declaring a foreign key constraint from Course_ID in my Student table and referencing the courses table on Course_ID. The cascading update/delete statement can then be executed.

Secondly, I insert the values into the Students table. This student will be used as a test to show the Cascading Updates/Deletes are working effectively.

The data is entered correctly and the ‘Anthony Monaghan’ record is completed with the Course_ID relating to the English course in the Courses table.

I use the DELETE FROM statement which allows me to remove fields within tables based on a unique value. There is only one course called English I wish to remove, so I remove English.

When querying the Courses table again, 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 roll back any running statements which have encountered an error. If two statements were executed at the same time and an error occurred, none of the statements would be executed. Here is an example of using a transaction

BEGIN TRANSACTION

     {

        sql_statement | statement_block

     }

END TRANSACTION

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

In conclusion to the following report, I have outlined the many different aspects and features that make an up relational database such as cascading updates, key constraints e.g. primary, composite, candidate and foreign keys. I have explained the definition of each and given relevant syntax reference where needed. I have also used images to support my statement when giving points on data types and querying from the relational database.

I have felt with the considerable amount of research and development I have experienced when working with SQL during this assignment, that It has broaden my knowledge of how large and small companies organise their database and the different design approaches.

To sum the main points in the assignment, relational databases have an exceptional advantage over flat file systems as the management capabilities are much more detailed with regards to formatting and easier to query information where needed.