Databases

A method of abstractly describing the data tables and the relationships between them visually. They can be used to reduce redundancy and construct a relational database.

It is a thing about which data is to be stored, for example: a customer.

Attributes are characteristics or other information about entities, for example: the customer’s name or address.

It is a type of database that stores and provides access to data points that are related to one another.

A primary key is an attribute that provides an unique identifies for every entity in a database table.

If it is not possible to form a primary key from just one attribute. It is possible to combine attributes to form what is called a composite primary key

A foreign key is an attribute in a table which is the primary key in another, related, table.

Where one entity could be linked to only one of another entity

Where one field from a table is linked to several records from another table.

For example, a tutor might have multiple students and students might have multiple tutors. This type of relationship can become complicated when turned into a database because they cannot occur in real life; data cannot be transferred as it logically doesn’t make sense. This can be fixed using an association table. For example, a tutor can publish many papers and a paper can have many publishers so the association is Publication Issue Number; this is because it’s the simplest link.

Normalisation is the process of removing redundancy within a database. Doing so makes accessing and storing data easier and more efficient

• Select an initial key. This acts as a starter key. It needs to be unique so you can derive other values from it. If there is no suitable key, add one. • Transfer all identifiable attributes, ensuring each has their own relevant and unique name. • Look for repeating groups. These are a group of similar attributes that have multiple values for a single value of the initial key. Select a suitable initial key for the repeating group, surround them with brackets, and write them apart from the single-valued attributes

• Create new relations by separating all repeating groups select a new primary key for the new relation and propagate the initial key to form a new composite key. • All other single-values attributes remain with the initial key

The second stage of normalisation requires that all key attributes in a table are dependent on each other and that the table is in first normal form. Only then can you move on to creating a database that is in second normal form which will provide even more strength to the data structure. • Separate any attributes from keys formed in the previous step that are only dependent on one part of the composite key

The third step is the transition into third normal form. • Separate any attributes that are dependent on other non-key attributes; foreign keys are retained in the original relation • Check composite keys for redundant parts. If a part of a key can be derived from other attributes, demote key attribute to non-key

no redundancy, consistent data throughout linked tables, records can be added and removed without issues, complex queries can be carried out

Structured Query Language is a declarative language used for querying and updating tables in a relational database.

Data Definition Language, Data Manipulation Language

The data definition language is a language used to build the structure of a database, and consists of a variety of commands which allow the user to define the structure of the database by creating, altering or deleting tables. It also allowed the administrator to apply constraints to a database table.

The data manipulation language is the language used to populate and update the database after it has been created. DML allows the user to insert, modify, delete, and query a database table for the data values of the entire table, for a specific record or for records that meet criteria.

Variable length string of maximum n characters

Fixed length string of n characters

A date (there is also TIME, TIMESTAMP, and DATETIME)

Whole numbers (positive and negative)