1.3.2 - databases / SQL

describe the following terms: database / relational database / entity / record / field / primary key / foreign key / secondary key

• database - organised collection of entities

• relational database - database that uses multiple tables to link entities

• entity - item that stores attributes about something

• record - row in a table

• field - column in a table

• primary key - unique identifier for each record

• foreign key - attribute that links 2 tables together

• secondary key - attribute that isn’t a unique identifier, but can be used to retrieve a record, e.g. someone’s name

describe how to perform database normalisation (from flat-file → 3NF)

• 1 - convert from flat-file → 1NF

  • all field names are unique

  • all values of a field should be of the same domain (type of data)

  • values should be atomic

  • all records are unique

    • done by giving each a primary key

• 2 - convert from 1NF → 2NF

  • remove partial dependencies

    • means making it so each table serves its own perpose i.e. each table stores info about 1 entity-type

    • done by splitting the info linked to each entity into seperate tables, linking everything bydefining/creating foreign keys in each table, linking the tables in a linking table

• 3 - converting from 2NF → 3NF

  • remove transitive dependencies

    • this is where you can infer the value of a space by using a value that isnt the primary key

    • done by making another table containing all transitive dependencies, along with deciding on the key

state and describe all the sql functions that need to be known

• SELECT - used to specify which field to select

• FROM - specifies which table(s) to retrieve

• WHERE - used to specify conditions upon which records are returned

• LIKE - used with WHERE to specify conditions involving wildcards. below are the 2 wildcards needed to know about:

  • “%” - represents 0 or more characters

  • “_” - represents a single character

• AND - …

• OR - …

• DELETE - removes records from a table

• INSERT INTO - adds a new record into table

  • syntax: INSERT INTO table1 (field1, field2) VALUES (value1, value2)

• DROP - deletes a awhole table

• JOIN - used to combine rows from multiple tables, using foreign keys, to return values from both fields

  • syntax: SELECT ... FROM table 1 JOIN table2 ON table1.key = table2.key WHERE ...

• * - used with SELECT to select all

…

…