Databases

What is a Database?

• A database is an organsiedcollection of information that is stored and accessed electronically.
• Examples of databases include school databases that may have information about students like their addresses, emergency contacts etc.
CRUD:
• C: Create New Data items.
• R: Read Existing Data Items.
• U: Update Existing Data Items.
• D: Delete Data Items That Are No Longer Relevant.

Examples:

How Are Data Bases Organised?

1. Entities: These are the things that the database is made to discuss like Teachers, Pupils, Employees etc.
2. Records: These are the rows of a table that describe a single entity.
3. Fields: These are the columns of a table that describe a specific aspect of all of the entities.
4. Facts: These are each of the record-field pair.

Datbases VS Spreadsheets:

Relational VS Non-Relational

Relational:

• To store data in seperate tables.

Non-Relational:

• To store data in a table.

SQL Commands

INSERT:

- The most common way to enter information to a table is using the insert command.

- The usual layout of an insert command would be:

INSERT INTO (Table Name)(Fields that the new information will be added to) VALUES (The values to be added)

SELECT:

- The select command is a very versatile command and can be used in many ways.

- The select command can be used to gather an entire table or a specific field from a table.

- If we want to select a whole table, we would write:

SELECT * FROM (Table Name)

- Or, if we want to select a specific field - This is called slicing:

SELECT (Field Name) FROM (Table Name)

UPDATE:

- The update command is used to change the value of a fact based on a specified field that will identify the record of a specific entity.

The usual layout of an update command would be:

UPDATE (Table Name) SET (Field that contains the fact to be updated) WHERE (Field that contains unique value)(Unique value to the entity to be changed)

DELETE:

- Deleting is commonly used to delete records from a table in a database. For example, when a student leaves a school.

- The usual layout of a delete command would be:

DELETE FROM (Table Name) WHERE (Field Name) = (Unique Value of the record to be deleted)

SORTING:

- Sorting is done to order a specified field in a specific order.

- The key command in sorting is the ORDER BY command which specifies the order of the records in the field. The order is automatically ascending but if you want the order to be descending you must write it: [DESC].

- The usual layout of a sorting command would be:

SELECT (The Fields Wanted) FROM (Table Name) ORDER BY (Field name (Optionally: [DESC]))

For Example:

Primary and Foreign Keys

Primary Key:

• A primary Key is a field that is used to uniquely identify a record. Like an ID number for example.
• A primary Key can be either an existing field or a generted ID field

Foreign Key:

• A foreign key is a field that is present in 2 or more tables in order to link them.
• This common field is always the primary key.