Databases

Made from Ada comp. sci. and PMT comp. sci. notes

What is a data base?

An organised collection of data

What is a flat file data base?

A data base in which all the required data is stored in a single file

Benefits of databases

• Improving the integrity of data

• Reducing the amount of unnecessarily duplicated data

• Making it quicker and easier to keep data up to date

• Being able to combine data in different ways to produce useful information

What is DBMS the abreviation of?

Abreviation of database management system

What is a database management system?

A software application that allows a database administrator to maintain one or more relational databases

Typical features of a database management system

Provides a graphical user interface that allows the user to:

• modify the database structure

• create indexes

• optimise the database

• browse data

Whata are database applications?

Software programs designed to retrieve, manage, distribute, and present information effectively

What is a table?

A collection of related data that represents an entity

What is an entity?

An item/object of interest about which information is stored

Non-prime attribute

Attribute that is not part of an candidate key

Candidate key

F

Primary key

A field that uniquely identifies each record in the table. A table can only have one _____

Composite key

Combination of 2 or more fields that uniqualy identify each record in a table

Foreign key

• An attribute in a table that appears as the primary key in another table.

• It links two tables together

What is the purpose of a secondary key?

To allow a database to be searched quickly

What is an attribute?

A characteristic of an entity

What are the three main category of relationships?

• One-to-one

• One-to-many

• Many-to-many

One-to-one relationship

When one instance of an entity is linked to only one instance of another entity

One-to-many relationship

When one instance of an entity is linked with multiple instances of another entity.

Many-to-many relationship

When multiple instances of an entity are linked with multiple instances of another entity. Not allowed in 3NF

What is ER diagram the abreviation of?

Abreviation of entity-relationship diagram

Entity-relationship diagrams

• Entities represented as boxes

• Relationships represented by lines, which show how many of one entity can be linked to another entity

• Straight line at one end means one

• Splayed line (crows foot) means many

• Attributes of an entity can be shown in ovals connected to the entity by a line

• Relationship can by labelled

one-to-one ER diagram

one-to-many ER diagram

many-to-many ER diagram

Pupose of a link/junction table

Allows many-to-many relationships to be implemented in a relational database by breaking this relationship into a one-to-many relationship between each of the tow entities and the _____ table.

First normal form (1NF)

A database in which:

• All cells are atomic (contains one value)

• Has a pimary key - every record is uniquely identified

• Each field only holds a 1 type of information

• No Repeating fields/data

Second normal form (2NF)

• Database that is in the 1NF

• Database that has no partial dependencies - every field is dependent on the primary key. 

Partial dependancy

When a non-prime attribute is functionally dependant on only part of a composite key

Third normal form (3NF)

• Database that is in the 2NF

• Database has no transitive dependencies / no-key dependencies

Non-key dependency

When an attribute depends on value(s) of fields that are not the primary key or part of the primary key

Dependency

A value that varies in line with another value.

What does normalisation aim to achieve?

• No redundancy (unnecessary duplicates).

• Consistent data throughout linked tables.

• Allow records to be added and removed without issues.

• Allow complex queries can be carried out

Normalisation

Process of coming up with the best possible layout for a relational database

Data consistency

When a database transaction can only change data in acceptable ways - ways that follow a pre-defined set of rules

Data redundancy

When there is duplication of data or unnecessary data is kept in the database

Data integrity

The reliability of data in terms of its accuracy, completeness, and consistency

Transaction

A single logical unit of work. Can be made up of multiple steps. Example is transferring money from a current bank account to a svaings account.

Serialisation

Making sure that when two or more transactions are executed concurrently, the effect should be the same as if they had been executed serially (in order, one after the other)

Record locking

Process of preventing simultaneous access to records in a database. Prevents data inconsistencies or a loss of updates.

ACID test

Set of properties of database transactions that will guarantee the integrity of data:

• Atomicity - components of a transaction are atomic, i.e. indivisible. The whole transaction must succeed or fail.

• Consistency - ensures that an illegal transaction is rejected so that the integrity of the database is upheld.

• Isolation - ensures that each transaction will be isolated and dealt with in a way that does not affect others.

• Durability - ensures that data is saved once a transaction has been completed. Even if there is a hardware failure immediately after a transaction, the data will be safe.

Indexing

Method used to store the position of each record ordered by a certain attribute (normally secondary key). This is used to look up and access data quickly.

Relational database

A database which recognises the difference between entities and uses different tables for each entity.

What is capturing data?

The process of getting the information that will be stored in the database.

What method do banks use to capture data from cheques?

Magnetic Ink Character Recognition (MICR) is used for all of the details apart from the amount which must be entered manually.

Selecting data

The process of removing excess information to extract only the data you require.

Managing data

Manipulating the information collected in any type of way such as through sorting through it or selecting certain parts using SQL

What is the most common language used to manipulate data in databases?

SQL

What is SQL an abreviation for

Abreviation of Structured Query Language

Structured Query Language (SQL)

• A declarative language used to manipulate databases.

• It enables the creating, removing and updating of databases

Record

• One row of the table

• It holds all the related fields of data about that one entry

Field

One item of data in a record

Is SQL case sensitive?

____ is not case sensitive

What case are SQL keywords written in, and why?

• ___ keywords written in upercase

• This helps with readability

What do SQL statments always end with?

___ statements are terminated with a semicolon

5 SQL data types you need to know

• Text

• Date fields

• Time fields

• Numeric fields

• Boolean fields

Format of text in SQL

• A string value contained in single quotation marks

• Example: ‘Some random text’

Format of data field in SQL

• Contained in single quotation marks and usually written in the form ‘YYYY-MM-DD’

• Example: ‘1912-06-23’

Format of time fields in SQL

• Contained in single quotation marks and commonly written in the 24h fromat ‘hh:mm:ss’

• Example: ‘21:12:12’

Format of numeric fields in SQL

• Not conatained in quotation marks

• Stored as pure numbers (so no characters like currency symbols or other formating characters)

Format of boolean fields in SQL

• Either TRUE or FALSE

• Used without quotation marks

Rules for identifiers in SQL

• Names must begin with a letter

• Names can only consist of letters, numbers, and underscores

• Names cannot be SQL keywords (watch out for ORDER)

• Names should not include spaces

How to create table in SQL

Use the CREATE keyword. For example:

CREATE TABLE TableName
(
    Attribute1 INTEGER NOT NULL, PRIMARY KEY,
    Attribute2 VARCHAR(20) NOT NULL,
    …
);

What must be specified for each attribute when createing a table in SQL?

• Whether it is the primary key

• Its data type

• Whether it must be filled in (‘Not Null’)

How to retrieve fields from a given table in SQL?

• Use a SELECT statement

• SELECT, FROM, WHERE, ORDER BY:

SELECT first_name, fav_num, date_of_birth
FROM some_info
WHERE first_name = 'John' AND fav_num > 12 AND date_of_birth < '2012-12-12'
ORDER BY fav_num DESC;

How to get all available fields from the table my_table in SQL

SELECT *
FROM my_table

FROM statement

Specifies which table is being accessed or modified

WHERE statment

Specifies a criteria used to select which records will be accessed or modified

ORDER BY statement

• Specifies how to sort results

• Used with ASC or DESC for ascending and descending respectively

JOIN statement

Provides a method of combining rows from multiple tables based on a common field between them. For example:

SELECT Movie.MovieTitle, Director.DirectorName, Movie.MovieCompany
FROM Movie
JOIN Director
ON Movie.DirectorName = Director.DirectorName;

How to add new record to a table

Use INSERT INTO statement. For example:

INSERT INTO (column1, column2, ...)
VALUES (value1, value2, ...)

INSERT INTO statment

Used to insert a new record into a database table

How to update records in a table

Use and UPDATE, SET, WHERE. For example:

UPDATE TableName
SET column1 = value1, column2 = value2, ... 
Where columnX = value

UPDATE statment

Used to update a record in a database table

How to delete records from a table

Use DELETE, WHERE. For example:

DELETE FROM TableName
WHERE columnX = value

DELETE statement

Used to delete records from a table

How to add a column to a table using SQL

ALTER TABLE TableName
ADD AttributeX and their dataTypes

How to delete a column of a table using SQL

ALTER TABLE TableName
DROP COLUMN AttributeX

How to change the data type of a column using SQL

ALTER TABLE TableName
MODIFY COLUMN AttributeX NewDataType

ALTER statement

Used to add, delete or modify the columns in a table

How to delete a table using SQL

DROP TABLE table_name;

How to delete a databse using SQL

DROP DATABASE DB_name;