4.10 Fundamentals of Databases

Question 1

Primary Keys (Definition)

Answer 1

A primary key is the unique identifier for each row.

Question 2

Simple Primary Keys

Answer 2

Contain only one field to make a unique value

Question 3

Composite Primary Keys

Answer 3

Contains more than one field to make a unique value

Question 4

Foreign Keys

Answer 4

A field in one table that is linked to the primary key in another table

Question 5

Entity

Answer 5

Table

Question 6

Record

Answer 6

Row

Question 7

Attribute

Answer 7

Field

Question 8

Entity Definition

Answer 8

TableName (Primary Key, field1, field2)

• underline primary key

Question 9

Relationships: One to One

Answer 9

x -|——|- y

Question 10

Relationships: One to Many

Answer 10

x -|——-<- y

Question 11

Relationships: Many to Many

Answer 11

x -|——<- xy ->——| y

middle box contains foreign keys of other boxes to make primary key

Question 12

Relationship Diagrams are called …

Answer 12

Entity-Relationship Diagrams (ERDs)

Question 13

Normalisation Rules help …
(3)

Answer 13

• organise data efficiently
• eliminate redundant data
• ensure only related data stored in table

Question 14

First Normal Form (4)

Answer 14

• no columns with repeated or similar data
• each data item cannot be broken down any further (atomic)
• each row is unique (it has a primary key)
• each field has a unique name

Question 15

Second Normal Form (2)

Answer 15

• must already be in 1NF
• non-key attributes must depend on every part of the primary key

Question 16

Third Normal Form (2)

Answer 16

• must already be in 2NF
• there are no non-key attributes that depend on another non-key attribute

Question 17

Benefits of Normalisation (5)

Answer 17

• no redundant data = smaller database size = less £ on storage
• less search data = faster query
• no duplication = better integrity, less mistake risk
• no duplication = less chance of storing 2+ different copies of data
• instant change cascade

Question 18

Problems of Normalisation (5)

Answer 18

• be careful with splitting data (atomic)
• more tables
• more tables/more complex = slower queries
• more tables = must assign more relationships
• more tables = more complex queries

Question 19

Insert: every field

Answer 19

INSERT INTO table VALUES (value1, value2);

Question 20

Insert: selected fields

Answer 20

INSERT INTO table (field1, field2) VALUES (value1);

Question 21

Update

Answer 21

UPDATE table SET fieldtochange = newfieldvalue WHERE condition;

Question 22

Delete

Answer 22

DELETE FROM table WHERE condition;

Question 23

Select

Answer 23

SELECT *
FROM table
WHERE condition;

Question 24

Select - Order By

Answer 24

ORDER BY Asc/Dsc

Question 25

Select - 2 Tables

Answer 25

SELECT *
FROM table1, table2
WHERE table1.PrimaryKey = table2.ForeignKey AND condition;

Question 26

DDL - Database Definition Language

Answer 26

CREATE TABLE tableName (
Id integer NOT NULL PRIMARY KEY,
field1 varchar(50),
field2 date/time);

Question 27

Different Data Types (6)

Answer 27

• VARCHAR (x)
• INT/INTEGER
• FLOAT
• REAL
• BOOL
• DATE/TIME