Database

Database

1. SQL stands for Structured Query Language and is the language used to create, drop and alter databases as well as manipulating the data within them. It is split into two branches.

   • DDL stands for Data Definition Language and is used to create a database structure and defines which attributes belong in which variables and grants privileges to the users.
     • Create Database:
     • Create Table:
       • Drop Table:
         • Alter Table: When altering a table you could do lots of different things like change the name of a variable, add a variable, change the data type of the variable and add a primary key. Here is an example.
     • Data Types: In the example above it only shows the use of INT which stores an 11 digit number but there are others, Char(size) stores a fixed length string that is exactly a certain size, VarChar(size) stores a string that is no longer than size, Decimal(size,d) stores the number of bits labelled by size and the number of those digits to the right of the decimal point is represented by the d. Date obviously a date and you can also have DateTime, Time and Year.
   • DML stands for Data Manipulation Language so is concerned with the manipulation of the data in the table. This is to: Insert, Update, Delete and Select.
     • Select (one table):
       • Select (multiple tables):
         • Insert:
           • Update:
             • Delete:

2. A database stores information about things and the things are called entities. The first thing when making a database is to come up with tables to store the information. Then within a table you have to come up with attributes which are the specific type of data stored in each table, such as forename and surname for information about a customer. Each record (row) in a table must have a unique identifier that is unique to that record and that record alone, this is called a primary key. If you were storing information about a person the primary key is something unique to them such as their NI number. In exams they can ask you to write out a table in short hand which is: table_name(Primary Key, Attributes) where the PK is underlined. There could not be a PK so you can do two things give it a unique number or make a composite key.

3. A composite key is a PK made up of two or more fields but it has to be unique to that record. An example is for a house you could combine the house number and postcode. A foreign key is a primary key from another table.

4. Entity Relationship Diagram: This is a diagram that shows relationships between the entities/tables. There are 3 types of relationships: one to one, one to many and a many to many. A one to one is like a country and a capital, one country has one capital and one capital belongs to one country. A one to many is like a customer and their orders one customer can have many orders but one order belongs to one customer. Finally a many to many is like an order and products, one order contains many products but one product can belong to many orders.

5. Normalisation is the process of creating a rational database and reduce redundancy. Redundancy is when data is repeated in the database or data is stored that could be calculated. Integrity should also be enforced to ensure the quality of the data stored. For example if there is a customer with id 5 there should not be any other customer with the same id, or if there is a rating system where you get a rating from one to 5 only numbers in that range should be stored. To do normalisation and reduce redundancy there are 3 steps you must follow 1st, 2nd and 3rd normal form.

   • 1st normal form is when one field contains one piece of data, the attributes are in groups called entities, there are no repeated attributes and there is a PK or composite key. In 2nd normal form there is no partial key dependencies, this means that when there is a composite key everything in the entity is dependent on all of the PK not just part of it if there is an attribute dependent on one part of the composite key we sat that the entity has failed 2NF and a new entity has to be created to change this. 3rd normal form is when all attributes in the entity are dependent on the PK and nothing but the PK.

Bilder und Dateien der Mindmap