Skip to content

prep-work.md

Latest commit

 

History

History
177 lines (109 loc) · 7.1 KB

prep-work.md

File metadata and controls

177 lines (109 loc) · 7.1 KB

Prep Work Week 2

Foreign Keys, Relationships

Foreign Key

A foreign key attribute is used to link two tables together. The values of the fields of this attirubte will be primary keys in another table. These references reflect relationships between entities in the database.

Extra resources:

Relationships

There are three specific types of relations(hips) that can exist between a pair of tables: one-to-one, one-to-many, and many-to-many. Let's look at them using our sample database ERD.

Chinook Sample Database

One-to-One (1-1)

A pair of tables bears a one-to-one relationship when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table.

  • Can you spot the one-to-one relationships in our sample database?

    Sorry, trick question! There is no one-to-one relationship in our sample database. But a good example could be the country-capital relationship mentioned in the previous link: a country has only one capital city, and a capital city is the capital of only one country.

Many-to-One (1-M)

A one-to-many relationship exists between two tables when a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table.

  • Can you spot the many-to-one relationships in our sample database?

    There's nine! An example: one Invoice can have many InvoiceLines. But an InvoiceLine can only be part of one Invoice. This relationship is implemented by putting by the foreign key InvoiceId attribute in InvoiceLine table.

Many-to-Many (M-M)

A pair of tables bears a many-to-many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table.

  • Can you spot the many-to-many relationships in our sample database?

    There's one: a Track can be part of many Playlists and vice versa a Playlist can consist of many Tracks. This relationship is implemented using an extra join table PlaylistTrack which consists of two foreign key attributes: PlaylistId and TrackId.

Extra resources:

Joins in SQL

An SQL JOIN clause combines attributes from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. There's different types of joins:

  • INNER JOIN
  • OUTER JOIN
    • LEFT (OUTER) JOIN
    • RIGHT (OUTER) JOIN
    • FULL (OUTER) JOIN
  • CROSS JOIN
  • Self-join

Note that for SQLite LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.

Extra resources:

Insert, Update and Delete Records in SQL

So far we've focussed on reading records using the SQL SELECT statement. But SQL can also be used to insert, update and delete data!

Insert

An SQL INSERT statement adds one or more records to a single table.

Let's try it out with an example using the sample database (again you can use the online SQLite viewer and upload our sample database file).

Add an artist 
INSERT INTO Artist (Name) VALUES ('John Doe');

SELECT
	*
FROM
	Artist
WHERE
	Name = 'John Doe';

Extra resources:

Update

An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.

Update the artist's name to something more catchy 
UPDATE
	Artist
SET
	Name = 'John Doe'
WHERE
	Name = 'DJ JD';

SELECT
	*
FROM
	Artist
WHERE
	Name = 'DJ JD';

Extra resources:

Delete

An SQL DELETE statement removes one or more records from a table.

Delete the artist again 
DELETE FROM Artist
WHERE Name = 'DJ JD';

SELECT
	*
FROM
	Artist
WHERE
	Name = 'DJ JD';

Extra resources:

Create, Alter and Drop tables in SQL

Similar to records, using SQL you can also create, alter and drop tables.

Create

Extra resources:

Alter

Extra resources:

Drop

Extra resources:

SQL Injection

Injection is considered the top web application security risk in 2020 by the Open Web Application Security Project. SQL injection specifically is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker or drop the database). A very recent example serves as a perfect anecdote.

The OWASP - SQL Injection Prevention Cheat Sheet comes in handy to understand which actions can be taken to prevent SQL injection flaws. The principal technique to avoid this problem is the usage of prepared statements or parameterized statements.

Extra resources: