Databases

Question 1

What is SQL

Answer 1

Structured Query Language

Question 2

CREATE

Answer 2

Used to create a new table (or database)

Question 3

What is a database?

Answer 3

Is a persistent collection of related data stored in a structured way

Question 4

What are the 3 characteristics of a data base

Answer 4

-There are tools to query (search, sort, filter) data.

-Databases are made up of entities (Tables)

-Each able contains fields and records.

Question 5

What is a flat database

Answer 5

Stores all data in one table / entity

Question 6

What are the pros and cons of a flat file database

Answer 6

+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

Question 7

What is a secondary key?

Answer 7

A field that is an alternative Primary Key (also unique)

Question 8

What is a primary Key

Answer 8

A field that uniquely identifies a record

Question 9

What is a foreign key?

Answer 9

A primary key from another table, used to maintain a relationship

Question 10

What is data integrity?

Answer 10

When you can’t trust your data, there could be inconsistencies (tends to happen when there is redundant data)

Question 11

What is data redundancy?

Answer 11

When we stored duplicate data

Question 12

What are the types of relationships in an ER diagram

Answer 12

-One to one
-many to one
-many to many

Question 13

How do you delete a table or database

Answer 13

DROP

(DROP TABLE testTable;)

Question 14

How do you add a new record to a table?

Answer 14

INSERT

INSERT INTO testTable (custID, first_name)
VALUES (1, “tim”);

Question 15

How do you add a new record to a table?

Answer 15

INSERT

INSERT INTO testTable (custID, first_name)
VALUES (1, “tim”);

Question 16

How do you change the values inside of a table

Answer 16

UPDATE

UPDATE testTable
SET first_name = “Rehan”
WHERE custID = 2;

Question 17

How do you delete records from a table?

Answer 17

DELETE

DELETE custID =1;

Question 18

How do you extract data from the table?

Answer 18

SELECT

SELECT firstname, surname
FROM Customer
WHERE CustomerID > 10 AND CustomerID < 15

Question 19

What is a indexed field?

Answer 19

A field that contains an index for faster searching

Question 20

What is referential integrity?

Answer 20

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

Question 21

What is normalisation?

Answer 21

The process of arranging fields into different tables

Question 22

what is 1st normal form

Answer 22

-If every field stores 1 item of data (atomic)
-if groups of data are stored as separate entities (tables)
-if each table has PK

Question 23

2nd normal form

Answer 23

-if already 1NF
-no partial dependencies (this can only happen when there is a composite key)