Databases

Databases

1. A database is a systematic collection of related data which facilitates data management

   • It represents some aspects of the real world
     • But real world information is often not sufficiently clearly defined for a database therefore we have to:
       • Make assumptions about data
       • Categorise 'similar' data together
       • Decide on a finite number of possible values for data
   • Databases are designed, built, and populated with one or more specific purposes in mind
   • It may be generated and maintained manually or digitally

2. Database developement

   • First, the collection requirements and aims need to be set i.e. what we want to do with the information
     • Next, a conceptual schema needs to be created (?)
       • Lastly, the database can be implemented

3. Relational Database

   • Is a collection of data items organised as a set of tables from which a view of the data can be formed in many different ways without having to reorganise the database tables
   • This principle was formulated by E.F. Codd at IBM in 1970

4. Database Management System (DBMS)

   • Is a program or a set of programs which enables its users to access a database, manipulate data and helps in the representation of data as well as controlling access to the database by various users
     • In a database, data is structured for retrieval = DBMS is a program that lets users organise their data for retrieval
   • E.g. an online telephone directory which would use a database management system to store data linked to people's phone numbers and other contact details
   • E.g. Facebook which needs to store, manipulate, and present data related to members, their friends, activities, etc
   • Four major types of DBMS exist:
     • Navigational, SQL, object-oriented, and relational
       • Relational DBMS
         • Relational DBMS do not support many to many relationships
           • They usually have predefined data types they can support
             • Are the most popular DBMS type on the market
               • E.g. Microsoft Access: intuitive, good learning tool, self-contained, straightforward development of database applications
                 • DBMS' like Access come with a "front end" interface that makes managing records straightforward and fairly intuitive
                   • However, in order to work more intensely and effectively with a database SQL is required
               • E.g. MySQL: free open source database package, fast and scalable, used to store data for many websites e.g. Facebook, Uber, Netflix
               • Oracle: expensive, very robust, scalable, used for banking databases and databases with critical data
         • Issues of Relational DBMS
           • Restricting unauthorised access: a shared database is likely to have many users with different access permissions
             • E.g. some users can only view data while others can view and update
             • = Security and authorisation subsystems exist to assign accounts specific restrictions
             • Providing multiple user interfaces as users have different data access requirements
               • = A database can be accessed using a variety of interfaces to reflect this, output can be varied to suit user goals
               • Representing complex relationships among data: databases can contain numerous varieties of related data
                 • Effective queries allow us to visually understand these relationships quickly and easily
                 • Enforcing integrity constraints: multi-user systems need to be checked for consistency, vast quantities of related data that relies on the integrity of other data must be closely audited
                   • DBMS ensures that this is taken care of
                   • Providing backup and recovery
                     • E.g. if a computer fails during a complex update query then the DBMS is required to ensure that the data reverts back to its state prior to the execution of the update, referred to as 'rollback'
                     • = facilities are incorporated to recover from hardware or software failure
                     • Controlling redundancy: key problem with storing data multiple times, repeated entries in tables are a waste of time, effort, space
                       • Updates have to be performed multiple times = effort is multiplied
                       • Storage space is wasted which may be problematic for large databases
                       • Files that represent the same data may become inconsistent
                       • Increases possibility of errors and propagating errors
                       • Can slow down performances especially with complex queries
                       • Common method to reduce redundancy is to normalise database tables
   • Structured Query Language (SQL)
     • A standard computer language for accessing and manipulating relational databases
       • Helps optimising and maintaining databases
         • Used by relational databases such as MS Access, MySQL, Oracle
           • SQL syntaxes used in these databases are almost similar except that some databases use different syntaxes or even proprietary syntaxes
     • Allows for complex querying
     • SQL statement can be effectively used to insert, search, update, and delete database records
       • Can do things beyond this as well
     • Humans speak in a natural language which needs to be converted into code
     • Although there are various versions or 'dialects' of SQL that allow users to create own tags / words, in order to comply with the American National Standards Institute standard they must support the major keywords
       • These query and update commands form the Data Manipulation Language part of SQL:
         • SELECT: extracts data from a database table
           • SELECT *: selects all information
           • SELECT count: count number of items e.g. adding up population
           • SELECT distinct: count items once e..g. when items exist more than once
         • UPDATE: updates data in a database table
           • E.g. UPDATE Persons SET FirstName = 'Nina' WHERE LastName = 'Rasmussen'
         • DELETE: deletes data from a database table
           • DELETE FROM Persons WHERE LastName = 'Rasmussen'
         • INSERT: inserts new data into a database table e.g. new entries (rows), can specify the column for which we want to insert data
           • E.g. INSERT INTO Persons (c1, c2, ...), VALUES (v1, v2, ...);
   • Advantages of DBMS compared to traditional file storage
     • Their self-describing nature: DBMS contain not only the database itself but also a complete definition or description of the database structure and constraints
       • Program-data independence: structure of data files is separate from the programs that access them
         • Multiple views of data: allow users to view alternate combinations of data based on the user's requirements
           • Sharing of data
             • Multi-user transaction processing

5. Normalisation

   • A table with fields containing more than one kind of information are 'non-automatic' i.e. problematic
     • This is because having multiple values would make it difficult to associate one element with another as they wouldn't have individual fields
       • Searches for particular elements have to take into account the multiple entries such as searching in the same field for French History might bring results of French, History and French History
         • Changes have to be done in many places which would be difficult to find
     • Need to bring it down to an atomic level
   • A common method to reduce redundancy is to normalise database tables using the FIrst / Second / Third / Boyce-Codd Normal Form
     • A table is in First Normal Form if all underlying fields =/are (?) atomic and there are no repeating groups and fields
   • Problems caused by many to many relationships e.g. in a database with student records every student can have multiple departments but every department can have multiple students
     • = can be solved by creating separate tables

6. Modern Databases

   • Static web page
     • HTML document that resides on a web server as an individual file that doesn't change unless it is edited by someone
     • HTML behind it is relatively simple and a wide variety of authoring tools are available
   • Dynamic web pages
     • A web page that is generated in part or in whole each time it is requested and viewed by a user
       • Its content may vary each time it is requested depending on user input e.g. clicking on text or an image
     • One of the most common types are database driven web pages
       • Grabs information from a database that the web page is connected to and inserts the information into the web page each time it is loaded
         • If the information in the database changes so will the web page connected to the database automatically (=without human intervention)
         • Client sends a request for a page to the web server
           • who then sends SQL to the database
             • the web server used the results to create a page
               • which is then returned to the client
       • E.g. Online banking site
         • User logs in using username and password
           • Bank account information is stored in a database, has been connected to the web page through programming which allows the client to see their banking information
       • Discussion forums and blogs are almost always database driven

Imagens e arquivos do mapa