Flashcards
Databases and SQL - OCR Computer Science A Level
Cards in this deck (32)
- 1What is an Entity?A category of object, person, event, or thing of interest to an organisation, about which data is to be recorded
- 2What are Attributes and Records?> Attributes - columns of the whole table (entity)
> Records - rows of the whole table - 3What is a Flat File Database?> Consists of a single file
> Suitable to store one entity, e.g., a list of aname
> Repeated data makes them inefficient
> Unsuitable for more than one entity and their relationships - 4What is a Primary Key?> Identifyer required for each entity in a database
> Usually a numeric key for each record - 5What is a Secondary Key?Used as an alternative index that is easier to know, e.g., a surname rather than a number
- 6What are the 3 Types of Relationships between Entities?> One-to-One
> One-to-Many
> Many-to-One - 7What is a Foreign Key?> Attribute that joins two tables
> Common to both tables - primary key in one, foreign in another
> Allows for relationships between tables - 8How are Tables Linked in Many-to-Many Relationships?> Additional table is created, containing just both primary / foreign keys
> Both keys in the one table count as a "composite primary key" - 9What is Referential Integrity?> Keeping shared / required attributes in databases
> Shared attributes are not deleted on only one entity, in order to maintain the relationship - 10What is Normalisation?> Process to ensure the best possible design for a relational database
> Ensures that
- No data is unnecessarily duplicated
- Data is consistent throughout the database (e.g., not conflicting entries in different tables)
- The structure of each table is flexible and allows entering as many or as few items as requied
- The structure allows for complex queries - 11What is First Normal Form?> All field (attribute) names must be unique
> Values in fields (attributes) should be from the same domain
> Values in fields (attributes) should be atomic, i.e., they are either updated in their entirety or not at all
> No two records may be identical
> Every table must have a primary key - 12What is Second Normal Form?> All the requirements of 1NF
plus:
> Remove any partial dependencies (i.e., none of the attributes depend on composite keys / only part of the primary key)
> Fix and Many-to-Many relationships as a result of removing partial dependencies - 13What is Third Normal Form?> All the requirements of 2NF (and 1NF)
plus:
> Remove any transistive dependencies (i.e., ensure that non-key fields are not dependent on eachother) - 14Why is Normalisation Important?> Data integrity (i.e., no data redundancy) is maintained, meaning there is no conflicting or unnecessary duplication of data
> Faster searching, due to smaller tables with fewer fields (also saving storage space)
> Records cannot be accidentally deleted in one-to-many relationships - 15What is Capturing Data, and what Methods can be Used?> Data has to be inputted (either automatically or manually) before it is added to a database
> Manual methods include transcribing data from a form
> Automatic methods include: MICR (Magnetic Ink Character Recognition, used for cheques), OMR (Optical Mark Recognition), OCR (fuck 'em... i mean Optical Character Recognition), or NFC (Near Field Communication)
> Data may be selected / filtered, only capturing those that match certain criteria before being added to the database - 16How can Data be Transferred between Systems without the need for Human Intervention?> EDI - Electronic Data Interchange
> Documents that use standard formatting can be exchanged electronically
> Transaction software processes the information - 17What is a Transaction?A single, logical operation on data
- 18What is ACID?> A set of properties that guarentee transactions are reliably processed
> Atomicity - transaction is processed in its entirety or not at all (e.g., in powercuts or crashes, the transaction does not proceed, or only process part of it)
> Consistency - no transaction has violate the defined rules for maintaining referential integrity
> Isolation - a transation cannot be interrupted by another, and concurrent transations lead to the same results as transactions happening one after another
> Durability - similar to atomicity, once a transaction has been committed, it is irreversable, even if there is a system fail - 19What is Record Locking?> Prevents simultaneous access to objects in a database, so multiple people cannot update it inconsistently
> E.g., a record is locked when a user is editing it or retrieving data from it - 20What is Serialisation?> Ensures transactions do not overlap in time - i.e., they cannot interfere with eachother, or lead to updates being lost
- 21What is Timestamp Ordering?> Serialisation technique
> Objects in a database are given a read and write timestamp, updated when needed
> When a transaction starts, it reads data from a record, setting the read timestamp. Before data is written, it checks to see if the timestamp has updated since, if so, it does not continue as a transaction is currently taking place - 22What is Commitment Ordering?> Serialisation technique
> Ensures that transactions are not lost when more than one person is accessing the same database object
> Transactions are ordered in terms of their dependencies on eachother, as well as by time - 23What is SQL?> Structured Query Language
> Declaritave language used for updating tables in a relational database
> Uses statements to make, query, and modify databases - 24What do the Following Commands do? > SELECT > FROM > WHERE > ORDER BY> SELECT - lists the fields to be displayed
> FROM - lists the table(s) where data will be read from
> WHERE - lists data that matches the criteria
> ORDER BY - orders the results (default is ascending order) - 25How does the LIKE Command Work?> Condition that finds values with a wildcard
> LIKE "a%" finds values that begin with a
> LIKE "%a" finds values that end with a
> LIKE "%a%" finds values with a in any position
> LIKE "_a%" finds values with a in second position (more underscores = higher position)
> LIKE "a%b" finds values that begin with a and and with b - 26What does the JOIN Command do?> Combines rows from two or more tables, based on a common field between them
- 27How does the CREATE TABLE Command Work?CREATE TABLE table_name
(
Attribute1 DATA_TYPE, PRIMARY KEY
Attribute2 DATA_TYPE
etc.
)
For linked tables:
FOREIGN KEY Attribute3 REFERENCES OtherTable(Attribute3) - 28What Data Types can be used when Creating a Table?> CHAR(n) - string of fixed length n
> VARCHAR(n) - string of variable length, max n
> BOOLEAN - true or false, i mean you should know that one
> INTEGER / INT - ... an integer, what did you expect?
> FLOAT - a decimal (real) number
> DATE - a... a date. y'know, day, month year
> TIME - like hours, minutes, and seconds... it's pretty self explanatory
> CURRENCY - le cash money - 29How does the ALTER TABLE Command Work?> Used to add, delete, or modify columns
> ALTER TABLE table_name ADD attribute_name - adds the specified attribute
> ALTER TABLE table_name DROP COLUMN attribute_name - removes the specified attribute
> ALTER TABLE table_name MODIFY COLUMN attribute_name DATA_TYPE - changes the data type of the attribute - 30How does the INSERT INTO Command Work?> Used to insert a new record into a table
INSERT INTO table_name (column1, column2...)
VALUES (value1, value2...)
> If attributes are unknown, do not list them in the columns or values - 31How does the UPDATE Command Work?> Used to update a record in a database table
UPDATE table_name
SET column1 = value1, etc.
WHERE attribute = value - 32How does the DELETE Command Work?> it deletes a record. who'd've thunk it?
DELETE FROM table_name
WHERE attribute = value
Information
- Description
- A level Computer Science (Databases and SQL) Flashcards on Databases and SQL - OCR Computer Science A Level, created by Malachy Moran-Tun on 18/05/2022.
- Created by
- Malachy Moran-Tun
- Views
- 23
- Rating