Databases

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

What is a data base?

An organised collection of data, possibly containing different types of infromation (e.g. students, lessons, teachers etc.)

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

• Improves the integrity of data…

• …reducing the amount of unnecessarily duplicated data…

• …making it quicker and easier to keep data up to date

• Allows data to be combined 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 a group of entities of the same type (e.g. students)

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

Any set of one or more fields that uniquely identifies each record in the table. As such it could be used as a primary key.

Primary key

The (set of) field(s) that uniquely identifies each record in the table. Each 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 difference between a primary key and a foreign key?

• A primary key will only appear once in a table/is a unique identifier 

• A foreign key may appear multiple times a table/may not be unique

Secondary key

• A field that allows a database to be searched quickly

• Isn’t necessarily unique

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 an attribute that is not the primary key 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 non-key dependencies

Transitive / Non-key dependency

When a non-primary attribute depends on another attribute that is also not the primary key or part of the composite primary key

Functional dependency

When the value of one attributes depends on the value of another attribute

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

• Ensures data is ___

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

• Prevents 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

• Two or more tables linked using key fields

• A different table is used 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

• It holds all the related fields/information about that one entry (e.g. information about a student)

• One entry (row) of a table

Field

• One item of data in a record

• A particular piece of information about the entry (e.g. date of birth of a student)

What is meant by the term referential integrity?

Database/relationships are consistent - each foreign key links to a valid primary key 

What is an example of how referential integrity can be broken?

If primary key is deleted/updated, foreign keys become invalid. As such referential integrity is lost.

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 date 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

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

• Not conatained in quotation marks

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

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?

SELECT first_name, fav_num, date_of_birth
FROM table_name
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

Use * wildcard

SELECT *
FROM my_table

FROM statement

Specifies which table is being accessed or modified

WHERE statment

Used to specify 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

INSERT INTO table_name 
(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

UPDATE TableName
SET column1 = value1, column2 = value2, ... 
WHERE <condition e.g. columnX = some_value>

UPDATE statment

Used to update a record in a database table

How to delete records from a table

DELETE FROM TableName
WHERE <condition e.g. columnX = some_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 datatype

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;