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
