Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
1.3.2
| Question | Answer |
|---|---|
| Database | organised collection of data making it easier to retrieve, add, delete, update, modify, backup and make copies of data and they can be accessed by multiple people at the same time from different locations. |
| Table (database) | complete set of records |
| Record (database) | collection of fields composing an entry (the rows) |
| Field (record) | store a single piece of data with a specified data type (the columns) |
| Relational database | - Based on two or more tables - One field in each table exists in another - Each table is based on an entity - No rule about order of rows/columns |
| Entity (databases) | something we store data about |
| Flat-file database | - Made as lists in a single table often saved as a CSV file - Good for small amounts of data only - Really simple, quick to set up and require little expertise to maintain - Prone to data redundancy (repeated data) and are slow to query |
| CSV file | comma-separated values file |
| Keys (databases) | uniquely identify each record |
| Primary key | a field in a table that is unique for every record. It can be natural (e.g. car number plate) or a field designed for the purpose (e.g. ID number) |
| Foreign key | A field in a table that is linked to the primary key in the connected table, defining a relationship between the two |
| Secondary key | A field that is likely to be searched and indexed. Doesn’t have to be unique for each record |
| ERD | Entity relationship diagram: a diagram showing links between entities We use ‘crow’s foot notation’ |
| Crows foot notation | Entities/tables are represented in boxes Relationships are represented by lines with either a single line (one relationship) or a prong (many relationship) at the end Relationships: - 1:1 - 1:M - M:M |
| 1:1 (Crows foot notation) | One-to-one relationship: Each entity connects to a single instance of another |
| 1:M (Crows foot notation) | One-to-many relationship: Each single instance of one entity connects to many instances of another |
| M:M (Crows foot notation) | Many-to-many relationship: (not good practice for a database) Multiple instances of one entity connect to multiple instances of another That can be hard to use, so there is an alternative: |
| Indexing (database) | - An index of primary keys provides the location of each record - Take up extra space but speed up searching |
| Capturing data | Where information can be collected and stored in a database. Methods: - From paper form |
| Paper form (capturing data) | - Every part of form must be labelled - Complete the form in black pen + capital letters - Use of tick boxes - Squares for entering each letter separately |
| Papaer form getting data (capturing data) | - OMR - OCR - Many more methods as well e.g. sensors |
| OMR | optical mark recognition: identifies shaded areas |
| OCR | optical character recognition: converts text images into digitally encoded text that can be easily processed |
| Selecting/retrieving data | - SQL - QBE Both allow user to specify tables, fields, criteria, output sorting and use boolean expressions |
| SQL | Structured Query Language (declarative language) Used for communicating with databases when querying, manipulating, defining data or controlling access to it |
| QBE | query by example: gives graphical interface of table. Better for simple queries, and easy to learn |
| Managing/modifying data | Add, edit and delete data - DML - Built-in DBMS hides underlying data structure to ensure it is accurate by stopping duplicate primary keys, enforcing validation rules, secure access, encryption + program data independence + managing multiple users |
| DML | database manipulation language (e.g. SQL) |
| DBMS | database management system |
| Exchanging data | Formats data can be serialized to: - JSON - XML - CSV |
| JSON | Javascript object notation: human readable, hierarchical file format which data can be stored in. Things are represented as objects, where each record is an object that can contain other objects as fields. |
| XML | Extensible markup language: human-readable, hierarchical but is longer and less readable than JSON. Uses tags to designate different things within a document. A record could be enclosed in a tag, and a field within tags in these tags. |
| CSV | Comma-separated values: plaintext file format used to store tabular data, values separated by commas. Each line is a record. A value is a field. |
| Transmitting data | - Paper-based: small amounts with no-internet - Memory stick - Email - Optical drive - EDI |
| EDI | electronic data interchange: protocol between two systems to facilitate data exchange, so two databases can read and write directly from each other’s tables |
| Normalisation | the process of arranging data in databases to move it from 1NF-2NF-3NF |
| Redundant data | data that is unnecessarily repeated, wasting space. To get rid of this we use normalisation, of which we only need to know the first three stages: - 1NF - 2NF - 3NF - UNF/0NF |
| UNF/0NF (normalisation) | Data before any normalisation is in unnormalised form |
| 1NF requirements (normalisation) | 1. Field names are unique 2. Values in fields are from the same domain (‘thing’ eg all initials) 3. Values in fields are atomic 4. No two records are identical 5. Each table has a primary key or composite key |
| 1NF (normalisation) | Data after the first stage is in the first normal form |
| Atmoic | cannot be split up further |
| Composite key | combination of two or more fields used to uniquely identify each record |
| 2NF (normalisation) | Data after the second stage is in the second normal form |
| 2NF requirements (normalisation) | 1. Meets all requirements for 1NF/ is in 1NF 2. No partial dependencies (You may have to split a table. To work this out, go through each field and ask yourself if I can work out any other fields from just this information) |
| Partial dependencies | when one or more of the fields depend on only part of the primary key, eg if a composite key is used, it should be impossible to find any of the fields by looking at only one of the fields in the key. |
| 3NF (normalisation) | Data after the third stage is in the third normal form |
| 3NF requirements (normalisation) | 1. Meets all requirements for 2NF/ is in 2NF 2. No transitive dependencies (again, go through each field and check, and possibly split tables again) |
| Transitive dependencies | every field must depend on the primary key, the whole primary key and nothing but the primary key |
| Fixing a M:M relationship | - Create a linking table - Assign the primary keys from two initial tables as composite key for new linking table - Flip M:M crows feet relationship to two separate 1:M relationships joined by new table |
| SELECT (SQL) | retrieves certain fields. Can be nested allowing data to be selected based on previous selected data. Goes in to out |
| FROM (SQL) | specifies the table to retrieve data from |
| WHERE (SQL) | filters results, only returns fields that match a condition. Use >,<,<=,>=,!+ and = to compare |
| LIKE (SQL) | used with WHERE to select fields that match a given pattern |
| AND (SQL) | used with WHERE |
| OR (SQL) | used with WHERE |
| DELETE (SQL) | used to delete records DELETE FROM table WHERE field … condition |
| INSERT (SQL) | adds records INSERT INTO table (field, field, field) VALUES (... (in order of fields)) |
| DROP (SQL) | delete a whole table DROP TABLE table |
| JOIN (SQL) | selects records in different tables with the same values for a given field SELECT fields FROM table JOIN (or INNER JOIN) fields ON table.field = table.field |
| WILDCARDS (SQL) | - * - % |
| * (WILDCARDS) | used with SELECT, all fields returned in the selected records |
| % (WILDCARDS) | used with LIKE, represent characters that are unknown and may not exist |
| Referential integrity | accuracy and consistency of data within a relationship (possibly throughout linked tables); key technique for ensuring data integrity |
| Data integrity | process of maintaining consistency of a database |
| Multi-user databases | Users can be given different access rights: - Some can only query - Some can add and modify records - Some can delete records Due to lots of transactions, it is vital that they never cause corruption - The DBMS ensures the data remains consistent |
| Transaction processing | any information processing that is divided into individual, indivisible operations called transactions. Each transaction must succeed or fail, but fully complete either way |
| CRUD | create, read, update and delete data (the four basic operations that can be performed on a database) |
| ACID | ensures data integrity when transactions occur, keeping data valid - Atomocity - Consistency - Isolation - Durability |
| Atomocity (ACID) | half-finished changes must not be saved; all or none of instruction performed |
| Consistency (ACID) | databases have sets of rules (constraints) that must always be adhered to. Transactions should never leave a database in a state where the rules are broken. |
| Isolation (ACID) | Transactions can only run simultaneously if they don’t affect each other, a transaction should not be affected by another transaction. Record locking is used. |
| Durability (ACID) | transactions shouldn’t be lost. Changes should immediately be written to secondary storage |
| Record locking | prevents anyone accessing a record being used by someone else |
Created by:
FlashCardFun!