SLR 10 - DATABASES Flashcards
Define relational database
allows the user to specify information about multiple tables and the relationship between those tables
Define flat file
a database that allows the user to specify data attributes (columns, databases etc.) for only one table at a time, storing those attributes independently
Define primary key
a field that uniquely identifies a record in a table
Define foreign key
- the linking field in the foreign table formed when a relationship is made
- the foreign key becomes by default the primary key of the new table
Define composite primary key
when more than one field is added together to form a unique primary key for a table
What is indexing?
the process of creating an index of primary keys such that the location of any record can be retrieved given its primary key
Define secondary key
a key field which can be used to access a table in a different way
Define entity relationship modelling (ERM)
the process of producing a data model for describing the data or information aspects of a business domain or its process requirements, in an abstract way that lends itself to ultimately being implemented in a data such as a relation database
What is normalisation?
the formal process of optimally designing data tables by reducing data redundancy and repetition by converting them into normal forms
What is 1st normal form (1NF)?
- no repeating attributes/groups of attributes
- all attributes must be atomic (a single attribute can’t consist of 2 data items)
What is 2nd normal form (2NF)?
- table is in 1NF
- contains no partial dependencies
What is 3rd normal form (3NF)?
- table is in 2NF
- contains no non-key dependencies (all attributes are dependent on the key, the whole key and nothing but the key)
Advs of normalisation
- easier to maintain and change a normalised database
- no unnecessary duplication of data
- data integrity is maintained
- having smaller tables with fewer fields means faster searches and savings in storage
What is referential integrity?
the idea of keeping a database consistent by ensuring that any changes made to data or relationships associated with a table are accounted for in all the linked tables
What are the SQL commands/statements?
- SELECT
- FROM
- WHERE
- LIKE
- AND
- OR
- JOIN
- ON
- INSERT INTO
- UPDATE
- SET
- DELETE
How do you select everything from a section?
asterix (wildcard)
What are the possible data types when creating a new table?
- CHAR(n): this is a string of fixed length n
- VARCHAR(n): this is a string of variable length with
upper limit n - BOOLEAN: TRUE or FALSE values
- INTEGER/INT: integer
- FLOAT: number with a floating decimal point
- DATE: the date in the format Day/Month/Year (#dd/mm/yyyy#)
- TIME: the time in the format Hour/Minute/Second
- CURRENCY: sets the number as a monetary amount
How do you create a new table in SQL?
CREATE TABLE tblname
(
tblID CHAR(4) NOT NULL PRIMARY KEY
description VARCHAR(20)
)
How do you add a new column to a table?
ALTER TABLE tblname
ADD FieldName DATA_TYPE
How do you delete a column from a table?
ALTER TABLE tblname
DROP FieldName
How do you change the data type of a column in a table?
ALTER TABLE tblname
MODIFY COLUMN FieldName newDATA_TYPE
How do you insert a new record into a table?
INSERT INTO (column1, column2, …)
VALUES (value1, value2, …)
How do you update a record in a table?
UPDATE TableName
SET column1 = value1, column2 = value2 …
Where columnX = value
How do you delete a record from a table?
DELETE FROM TableName
WHERE columnX = value
What does a database management system (DBMS) do?
ensures that data stored in a database remains consistent
What is data integrity?
- the maintenance and consistency of data in a data store
- the data store must reflect the reality that it represents
What is transaction processing?
The idea of making sure that any logical operation or change in state of a database (transaction) conforms to ACID (Atomicity, Consistency, Isolation, Durability) rules for reliable processing
What does ACID stand for?
- Atomicity
- Consistency
- Isolation
- Durability
What does atomicity mean?
all transactions should either succeed or fail, and never only partially processed
What does consistency mean?
each transaction must obey the defined validation rules of the database to maintain referential integrity
What does isolation mean?
- no transaction should overwrite other transactions that are simultaneously occurring
- the same results must be obtained if transactions are concurrently or sequentially processed
How does the DBMS enforce isolation?
- implements a system of record locking
- record(s) affected by the transaction are locked effectively placing them in a read only state
- when the transaction is fully complete the lock will be removed
What does durability mean?
- once a transaction has begun, it must be completed under all circumstances
- as each part of the transaction is completed, its held in a buffer/cache on a disk
What are some methods of capturing data?
- paper based forms
- optical character recognition (OCR)
- optical mark recognition (OMR)
- barcodes
- QR codes
- sensors
How is data exchanged?
- electronic data interchange (EDI)
- computer-to-computer exchange for documents
- all docs must be in standard format
