A-level Computing/CIE/Theory Fundamentals/Database and data modelling
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. It allows the user to enter search criteria and it returns/finds the data that matches the search criteria.
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
editNormalization
edit1st 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)
editCreation/modification of the database structure using this language - written in SQL
- Creating a database:
CREATE DATABASE <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)
editQuery 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>