To start off, I will confess now that databases fascinate me. In every job I have worked, from being on the customer service desk in a busy supermarket to managing the IP portfolio for a multinational drinks producer, I have used a Database Management System (DBMS) to assist me in my vocation. The three main components of a database are the data-set, the users requiring access to that data, and the systems applications and processes which permit the user to access that data. A balance of three is required although I would suggest that a database without users loses its sense of identity and purpose and reverts back to being simply information. If users are the valuers of the data; the database is the facilitator of data access.
DBMS manage 'structured' data, that is, data which we have carefully selected and stored in a specific form, to be accessed for a particular purpose, on any number of occasions, potentially by a number of different users. Its acts as the user interface to access this information, and imposes security controls to restrict access according to the data/user type. The efficient management of large amounts of data is crucial, because we are only likely to need to access small amounts of data subjectively relevant to our needs at any particular time, such as when we make a query on an individual component of any data-set (i.e. we may only want to find out two pieces of data such as name, location, salary of a specified employee - not every employee in the same building or department). Every database user is likely to have a different informational need that such a query seeks to satisfy, and the ability of a DBMS to sift through and filter data in accordance with our individual requirements is fundamental in achieving this.
DBMS facilitate and permit access to a core set of data. This eliminates the need for duplicate entries, and thus promotes user efficiency and improves data integrity. In order to provide better access to the data, relationships need to be established between pieces of data which draw upon the logical process of user enquiry. In its simplest form, a database can be represented as a table consisting of rows and columns: each row stands as a single entry under a data-field (column) (i.e. a person, company, item), each row has a unique identifier, 'a primary key', which identifies the data in that row to be different from all other rows. Where data in the table is duplicated or diluted (i.e. it is not focused on the user), data from one column is removed and is replaced by a 'foreign key' in the first table, which links to a second separate table which contains the now removed data-field. Each row in the second table relates to the specified 'foreign key' for data contained in the first table, thus creating links between the two tables. This linking is the essence of database construction. With the links now in place, we can now search for data between the available tables to create a 'database'.
Creating a simple database requires the use of precise, uniform language to retrieve data from a number of tables. This is commonly known as SQL (Structured Query Language, in full). I'll refer to basic examples given in the lecture notes, as follows:
"To create a table, we can insert the following SQL commands:
create table tablename ( column1, column2, ... columnN );
...where column is the column name, followed by the column data type,
possibly followed by modifiers like 'primary key.
To populate a table you use the insert into command as follows...
insert into Department values ( 1, 'Sales', 'London' );"
Once we have created and populated the table, we can now query it.
To query a database, we need to SELECT a data field (i.e. name, location, salary) FROM a specified table/s (i.e. user table, location table) WHERE certain conditions are met (i.e. = equals, a "precise item", > is greater etc.) AND where a second condition is required.
An example of an advanced SQL command is:
SELECT Fname, Lname, Dept_Name
FROM Employee, Department
WHERE Dept = 2
AND Dept = Dept_No
Our practical lab session involved us interrogating a database containing bibliographic data for a number of publication, using a variety of increasingly complex SQL commands to retrieve specific information from the database. Getting the SQL commands correct and retrieving something resembling 'useful' information was an uphill struggle to begin with, but improved as I became more fluent in the language. Like HTML, it is essential that the instructions you give are fully realised and executed with precision, as you are given no leeway if a single character is wrong or is out of place!
The clear and correct use of commands and connectors is imperative for effective querying.
I have never thought of the "science" behind databases, and this session gave me a great insight and appreciation of the DNA of a simple relational database. One day (technical ability abiding), I would love to be be able to write a database, but until I get the hang of SQL and querying other users' databases, it may be a long day coming!!!
No comments:
Post a Comment