{"id":1209,"date":"2023-04-13T15:07:55","date_gmt":"2023-04-13T14:07:55","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/?page_id=1209"},"modified":"2023-04-18T13:18:49","modified_gmt":"2023-04-18T12:18:49","slug":"databases","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/data-modelling\/databases\/","title":{"rendered":"Databases"},"content":{"rendered":"\n

The following notes are to assist you if your work placement does not provide their own instruction. Continue to do your own research and ask colleagues in the work place for advice. <\/p>\n\n\n\n

Flat file databases<\/h2>\n\n\n\n

A flat file database is simply a table of data that stores all the information about a given object in a row.<\/p>\n\n\n\n

\"Example
Example table<\/figcaption><\/figure><\/div>\n\n\n\n

You can see that some information has been added in multiple places. This will increase the size of the database and can lead to the following issues:<\/p>\n\n\n\n

  • Insert anomalies<\/li>
  • Delete anomalies<\/li>
  • Update anomalies<\/li><\/ul>\n\n\n\n

    An insert anomaly is where you can\u2019t add new information to the table because you are lacking part of the row. In the above example you can\u2019t add a new department until you have the teacher\u2019s details.<\/p>\n\n\n\n

    A delete anomaly is where deleting the information about a given object also removes all the information about a related object. In the above example Marie Macleod could retire. But if we remove her entry then we lose all the data about the maths department.<\/p>\n\n\n\n

    An update anomaly happens when you update a row with new information but fail to update a row that shares the information. In the above example we could update the telephone number for the English department on Donal Morrison\u2019s row but forget to make the update to Julie Stevens\u2019. Now there are two telephone numbers listed for the English department.<\/p>\n\n\n\n

    \n\n\n\n

    Relational databases<\/h2>\n\n\n\n
    • AKA Linked Databases<\/li>
    • R<\/strong>elational D<\/strong>ataB<\/strong>ase M<\/strong>anagement S<\/strong>ystem(RDBMS<\/strong>)<\/li>
    • MS Access, MySQL, MariaSQL, Microsoft SQL Server, Oracle<\/li><\/ul>\n\n\n\n

      A relational database splits the data into multiple tables. The aim being to only have data about an object stored in a single place. These data objects are then linked with relations<\/strong>.<\/p>\n\n\n\n

      The data in the previous example can be split into two tables. One stores the data about the department, and one stores the data about the teacher including an identifier stating in which department they work.<\/p>\n\n\n\n

      \n
      \n
      \"Teacher
      Teacher table<\/figcaption><\/figure><\/div>\n<\/div>\n\n\n\n
      \n
      \"Department
      Department table<\/figcaption><\/figure><\/div>\n<\/div>\n<\/div>\n\n\n\n

      We\u2019ve now split the data into two tables.<\/p>\n\n\n\n

      Each table contains data about a single object type: Teachers and Departments.<\/p>\n\n\n\n

      By assigning a Department ID to each teacher we can look up all the information about that department.<\/p>\n\n\n\n

      This has fixed all the anomalies. Any change should now only have to alter a single row.<\/p>\n\n\n\n

      In an RDBMS an entity set is represented by a table.<\/p>\n\n\n\n

      \n\n\n\n

      Entities (rows)<\/h2>\n\n\n\n

      In an RDBMS an entity is represented as a row in a table. A row stores all the data about a given object. This row of data can also be called an Entity<\/strong>. An entity stores information about an object or something we want to record.<\/p>\n\n\n\n

      An entity is made up of multiple attributes or fields.<\/p>\n\n\n\n

      An entity should only store data relevant to that object or a key to relate it to another table.<\/p>\n\n\n\n

      For example a garage might make a database for their customer details. But it would not make sense to store the vehicle details in the same table as the customer details. A customer might have multiple vehicles.<\/p>\n\n\n\n

      Strong entities<\/strong> \u2013 An entity that has an independent existence, i.e. doesn\u2019t rely on another entity.<\/p>\n\n\n\n

      Weak entities<\/strong> \u2013 An entity that relies on another entity to exist.<\/p>\n\n\n\n

      Example. A table containing staff details could hold strong entities, but a table containing next of kin details would be weak as the entities within are only needed based on a staff entity.<\/p>\n\n\n\n

      Attributes<\/h2>\n\n\n\n

      Entities<\/strong> are made up of attributes<\/strong>. These store the individual properties of data about an entity.<\/p>\n\n\n\n

      There are various types of attribute:<\/p>\n\n\n\n