/ SQL, DATABASE

Learning SQL Part 3

The previous post was getting a little long, so here is the last of the notes from my freecodecamp course in SQL.

Triggers

A trigger is a block of SQL code that defines an action that should happen when a particular action is performed on a database. Like if something is deleted from the database, or something is inserted into a table.

Example:

DELIMITER $$
CREATE
    TRIGGER my_trigger BEFORE INSERT
    ON employee
    FOR EACH ROW BEGIN
    INSERT INTO trigger_test VALUES('added new employee');
    END $$

DELIMITER ;
INSERT INTO employee
VALUES(109, 'Oscar', 'Martinez', '1968-02-19', 'M', 69000, 106, 3);

/* SELECT * FROM trigger_test returns one row of 'added new employee' */

At the start we are setting the delimiter to $$. Normally the delimiter is a semicolon, but because we are using it already inside the trigger, we need to create a new one to denote the end of the code. It is changed back after we are done creating the trigger. This trigger is saying whenever we insert into the employee table, add a string to the trigger_test table that says ‘added new employee’.

Entity relationship diagrams

Database schema: all the tables and attributes on the tables

Entity relationship (ER) diagram: take data storage/business requirements and convert to a database schema, kind of a middle man

Entity: object we want to model and store information about

  • e.g. Student

Attributes: specific pieces of information about an entity

  • e.g. name, grade, gpa

Composite attributes: are attributes that can be broken up into sub-attributes

  • e.g. first name, last name

Multi-valued attribute: can have more than one value

  • e.g. a student could be in multiple clubs

Derived attribute: can be derived from other attributes

  • e.g. a particular gpa could mean someone has honours

Relationship attribute: an attribute about the relationship,stored on the relationship

  • e.g. a grade earned by taking a class

You define relationships between your entities

  • total participation: all members must participate in the relationship - e.g. all classes must be taken by at least one student
  • partial participation - e.g. not all students have to take a particular class

Relationship cardinality: the number of times an entity can be associated with the relation

  • e.g. a student can take many classes, a class can have many students

Weak entity: depends on another entity, not uniquely identified by its own attributes alone. Doesn’t exist on its own.

  • e.g. an exam must be associated with a class

Identifying relationship: relationship that identifies the weak entity

  • the relationship between a class and an exam