# A-level Computing 2009/CIE/Theory Fundamentals/Database and data modelling
## File Based System

* Data stored in discrete files, stored on computer, and can be accessed, altered or removed by the user

### Disadvantages of File Based System

* No enforcing control on organization/structure of files
* Data repeated in different files; manually change each
* Sorting must be done manually or must write a program
* Data may be in different format; difficult to find and use
* Impossible for it to be multi-user; chaotic
* Security not sophisticated; users can access everything

## Database Management Systems (DBMS)

* Database: collection of non-redundant interrelated data
* DBMS: Software programs that allow databases to be defined, constructed and manipulated

### Features of a DBMS

* Data management: data stored in relational databases - tables stored in secondary storage
* Data dictionary contains:
** List of all files in database
** No. of records in each file
** Names & types of each field
* Data modeling: analysis of data objects used in database, identifying relationships among them
* Logical schema: overall view of entire database, includes: entities, attributes and relationships
* Data integrity: entire block copied to user’s area when being changed, saved back when done
* Data security: handles password allocation and verification, backups database automatically, controls what certain user’s view by access rights of individuals or groups of users

### Data change clash solutions

* Open entire database in exclusive mode – impractical with several users
* Lock all records in the table being modified – one user changing a table, others can only read table
* Lock record currently being edited – as someone changes something, others can only read record
* User specifies no locks – software warns user of simultaneous change, resolve manually
* Deadlock: 2 locks at the same time, DBMS must recognize, 1 user must abort task

### Tools in a DBMS

* Developer interface: allows creating and manipulating database in SQL rather than graphically
* Query processor: handles high-level queries. It parses, validates, optimizes, and compiles or interprets a query which results in the query plan.

## Relational Database Modelling

* Entity: object/event which can be distinctly identified
* Table: contains a group of related entities in rows and columns called an entity set
* Tuple: a row or a record in a relation
* Attribute: a field or column in a relation
* Primary key: attribute or combination of them that uniquely define each tuple in relation
* Candidate key: attribute that can potentially be a primary key
* Foreign key: attribute or combination of them that relates 2 different tables
* Referential integrity: prevents users or applications from entering inconsistent data
* Secondary key: candidate keys not chosen as the primary key
* Indexing: creating a secondary key on an attribute to provide fast access when searching on that attribute; indexing data must be updated when table data changes

## Normalization
### 1st Normal Form (1NF)

* Eliminate duplicative columns from the same table.
* Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key)

### 2nd Normal Form (2NF)

* Does not have a composite primary key. Meaning that the primary key can not be subdivided into separate logical entities.
* A row is in second normal form if, and only if, it is in first normal form and every non-key attribute is fully dependent on the key.
* 2NF eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.

### 3rd Normal Form (3NF)

* Functional dependencies on non-key fields are eliminated by putting them in a separate table. At this level, all non-key fields are dependent on the primary key.
* A row is in third normal form if and only if it is in second normal form and if attributes that do not contribute to a description of the primary key are move into a separate table. An example is creating look-up tables.

## Data Definition Language (DDL)

Creation/modification of the database structure using this language - written in SQL
* Creating a database:
* Creating a table:
* Changing a table:
* Adding a primary key:
* Adding a foreign key:

## Data Manipulation Language (DML)

Query and maintenance of data done using this language – written in SQL
* Creating a query:

### SQL Operators

| = | Equals to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| <> | Not equal to |
| IS NULL | Check for null values |
* Sort into ascending order:
* Arrange identical data into groups:
* Joining together fields of different tables:
* Adding data to table:
* Deleting a record:
* Updating a field in a table: