A-level Computing 2009/CIE/Theory Fundamentals/Database and data modelling

From Wikibooks, open books for an open world
Jump to navigation Jump to search

File Based System[edit]

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

Disadvantages of File Based System[edit]

  • 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)[edit]

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

Features of a DBMS[edit]

  • 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[edit]

  • 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[edit]

  • 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[edit]

  • 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[edit]

Database Relationships.png

Normalization[edit]

1st Normal Form (1NF)[edit]

  • 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)[edit]

  • 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)[edit]

  • 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)[edit]

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)[edit]

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[edit]

= 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>