Databases Flashcards
What is SQL
Structured Query Language
CREATE
Used to create a new table (or database)
What is a database?
Is a persistent collection of related data stored in a structured way
What are the 3 characteristics of a data base
-There are tools to query (search, sort, filter) data.
-Databases are made up of entities (Tables)
-Each able contains fields and records.
What is a flat database
Stores all data in one table / entity
What are the pros and cons of a flat file database
+Use database tools to filter / sort
-we might encounter data integrity issues (when you cant trust your data)
-This could happen as you store something in different ways more then once (when you store things more then once its called data redundancy)
-data redundancy might lead a larger database then necessary
What is a secondary key?
A field that is an alternative Primary Key (also unique)
What is a primary Key
A field that uniquely identifies a record
What is a foreign key?
A primary key from another table, used to maintain a relationship
What is data integrity?
When you can’t trust your data, there could be inconsistencies (tends to happen when there is redundant data)
What is data redundancy?
When we stored duplicate data
What are the types of relationships in an ER diagram
-One to one
-many to one
-many to many
How do you delete a table or database
DROP
(DROP TABLE testTable;)
How do you add a new record to a table?
INSERT
INSERT INTO testTable (custID, first_name)
VALUES (1, “tim”);
How do you add a new record to a table?
INSERT
INSERT INTO testTable (custID, first_name)
VALUES (1, “tim”);
How do you change the values inside of a table
UPDATE
UPDATE testTable
SET first_name = “Rehan”
WHERE custID = 2;
How do you delete records from a table?
DELETE
DELETE custID =1;
How do you extract data from the table?
SELECT
SELECT firstname, surname
FROM Customer
WHERE CustomerID > 10 AND CustomerID < 15
What is a indexed field?
A field that contains an index for faster searching
What is referential integrity?
A related record is required in another table (always on the Foreign Key, there must be a related record in the table where it is the Primary Key
For example, you can’t book a roomNo that doesn’t exist
What is normalisation?
The process of arranging fields into different tables
what is 1st normal form
-If every field stores 1 item of data (atomic)
-if groups of data are stored as separate entities (tables)
-if each table has PK
2nd normal form
-if already 1NF
-no partial dependencies (this can only happen when there is a composite key)
