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

Relational Design of a System

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:

CREATE DATBASE <database-name>

• Creating a table:

CREATE TABLE <table-name> (…)

• Changing a table:

ALTER TABLE <table-name>
ADD <field-name><data-type>

• Adding a primary key:

PRIMARY KEY (field)

• Adding a foreign key:

FOREIGN KEY (field) REFERENCES <table>(field)

Data Manipulation Language (DML)

Query and maintenance of data done using this language – written in SQL

• Creating a query:

SELECT <field-name> 
FROM <table-name> 
WHERE <search-condition>

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:

 ORDER BY <field-name> 

• Arrange identical data into groups:

 GROUP BY <field-name> 

• Joining together fields of different tables:

 INNER JOIN

• Adding data to table:

INSERT INTO <table-name>(field1, field2, field3) 
VALUES (value1, value2, value3)

• Deleting a record:

DELETE FROM <table-name> 
WHERE <condition>

• Updating a field in a table:

UPDATE <table-name> 
SET <field-name> = <value> 
WHERE <condition>