3.10 Fundamentals of Databases

Question 1

entity definition

Answer 1

category of object about which data is to be recorded, Entity1 (Attribute1, Attribute2…)

Question 2

attribute definition

Answer 2

characteristics about entities

Question 3

entity identifier

Answer 3

each identity has a unique identifier, an attribute that is unique within that table, underlined

Question 4

relationships between entities

Answer 4

one-to-one, one-to-many, many-to-many

Question 5

foreign key

Answer 5

attribute that creates join between two tables, attribute that is common in the tables, called primary key in one and foreign in other

Question 6

first normal form

Answer 6

contains no repeating attributes or groups of attributes, split repeating attributes so it becomes 1nf

Question 7

second normal form

Answer 7

database must satisfy first normal form, partial key dependencies are removed

Question 8

partial key dependency meaning

Answer 8

one or more attributes depends on only part of a primary key

Question 9

third normal form

Answer 9

conforms to second normal form, database must have no non-key dependencies, all attributes depend on the key, the whole key and nothing but the key

Question 10

importance of normalisation

Answer 10

easier to maintain and change, no unnecessary duplication of data, faster sorting and searching, saves storage space as data is only held once

Question 11

normalisation

Answer 11

no data is unnecessarily duplicated, data is consistent throughout database automatically, structure of table is flexible

Question 12

SQL as declarative language

Answer 12

programmer describes the result that’s required

Question 13

SELECT command SQL

Answer 13

used for retrieving, SELECT <attribute> FROM <table> WHERE <condition> (ORDER BY <ASC/DESC>)</condition></attribute>

Question 14

UPDATE command SQL

Answer 14

modifies attributes of already existing entity, UPDATE <table> SET <attribute> = <value> WHERE <attribute> = <value></value></attribute></value></attribute>

Question 15

DELETE command SQL

Answer 15

removes entities from database, DELETE FROM <table> WHERE <condition></condition>

Question 16

INSERT command SQL

Answer 16

adds new records to existing table, INSERT INTO <table> VALUES (<value1> <value2>)</value2></value1>

Question 17

wildcards

Answer 17

any possible value, can return all values

Question 18

defining table with SQL

Answer 18

CREATE TABLE tablename (attribute datatype, …)

Question 19

client server database

Answer 19

provides simultaneous access to database for multiple clients, concurrent access occurs when same field is being accessed at same time by different users

Question 20

how is concurrent access managed

Answer 20

record locks, serialisation, timestamp ordering, commitment ordering

Question 21

record locks

Answer 21

when a record is accessed by one user, it is immediately locked to others until user is finished, blocked from accessing or modifying

Question 22

serialisation

Answer 22

requests from other users are placed in a queue

Question 23

timestamp ordering

Answer 23

each command is assigned a timestamp and commands are carried out in order of timestamps

Question 24

commitment ordering

Answer 24

algorithm is used to work out optimum order in which to execute commands

Question 25

client server database advantages

Answer 25

consistency of database is maintained because only on copy of data held rather than a copy at each workstation, resource can be made available to large number of users, access rights and security managed centrally, backup and recovery managed centrally