The File approach can be analysed as a business in which several separate departments are storing data in their own formats, without consideration of congruous information, leading to overlapping pockets of data. These crossover points create duplication which in turn generates inconsistencies; in an instance where the data is updated at one location but not others. The lack of uniformity between departments in the organisation of data also makes it more challenging for the relevant information to be shared, particularly when differing notations are used to denominate the same data categories.
A Database is a set of related information that can be shared across a group, giving different users the ability to utilise the data through separate applications. The database lacks the inefficiency akin to the duplication occurring within a file based approach, as well as giving better security and integrity of information.
In 1970 a paper titled 'A Relational Model of Data for Large Shared Data Banks' was published by Dr. E.F.Codd who was working for IBMs research laboratory. The idea behind the paper was that information be orgainised into tables, and the tables would be linked together by redundant data.
Each table in a relational database must include a form of data that uniquely identifies a row in that table, this is known as a primary key. It is important to consider carefully what information to assign as the primary key, as it cannot be repeated, often a persons name is not good enough; as it is not uncommon for two people to have the same name. The clearest solution is to allocate each entity with a numerical value, creating an individual identifier.
A foreign key is equally important to a successful database, as it is used to navigate between tables, it is a column of redundant data acting as an index.
The beginnings of a database grow from an ER Model, in which you would decide the entities you wish to store on your database and the relationships between them. This model can then be interpreted into a database, but in order for it to work correctly there are protocols to adhere to, most importantly each of your tables must only represent one type of thing
SQL or SEQUEL is the language used for manipulating data in relational tables. In the computer lab session we used SQL to pose queries to a database called Biblio. The language is logical but the syntax needs to be performed accurately or you may end up with no results, or far, far too many.
Some useful commands:
SELECT Determines which columns to include in the query's result set
FROM Identifies the tables from which to draw data
WHERE Filters out unwanted data
ORDER BY Sorts the rows of the final result set by one or more columns
LIKE To be used when filtering with wildcards
* Select all
F% Strings beginning with F
%T Strings ending with T
%bas% Strings containing the substring 'bas'
= Equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
10. The name of the author who wrote "A Beginner's Guide to Basic" listing also, the ISBN and name of this book.
ReplyDeleteSELECT author, titles.isbn, title
FROM authors, titles, title-author
WHERE title= 'A Beginner's Guide to Basics'
AND titles.isbn = title_author.isbn
AND title_author.au_id = authors.au_id