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

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 CREATE DATBASE  CREATE TABLE  (…) ALTER TABLE  ADD  PRIMARY KEY (field) FOREIGN KEY (field) REFERENCES (field)
 * 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  SELECT  FROM  WHERE 
 * Creating a query:

SQL Operators
ORDER BY  GROUP BY  INNER JOIN INSERT INTO (field1, field2, field3) VALUES (value1, value2, value3) DELETE FROM  WHERE UPDATE  SET  = WHERE
 * 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: