A-level Computing/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. It allows the user to enter search criteria and it returns/finds the data that matches the search criteria. 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 750px 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 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) 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>