10. Fundamentals Of Databases

Question 1

What is an entity ?

Answer 1

A thing about which data is to be stored eg. customer

Question 2

What are attributes ?

Answer 2

Characteristics of entities

Question 3

What are databases formed from ?

Answer 3

Tables

Question 4

What do tables store ?

Answer 4

Entities

Question 5

What must each entity have ?

Answer 5

A unique Identifier

Question 6

What are the three types of relationships between tables in a database ?

Answer 6

• One to One
• Many to Many
• One to Many

Question 7

What is a primary key ?

Answer 7

An attribute that provides an unique identifier for every entity in a database table .

Question 8

What condition must be met when tables are linked by shared attribute ?

Answer 8

The attribute must be a primary key in one table and be be called a Foreign Key in the other.

Question 9

What is a foreign key ?

Answer 9

An attribute which is a primary key in another related table

Question 10

What is a composite primary key ?

Answer 10

Combination of attributes to form a key where it’s not possible to form a primary key from one attribute

Question 11

What must be created when linking many to many relationships ?

Answer 11

A new table - Link table

Question 12

What is Normalisation ?

Answer 12

Ensuring databases contain no redundant or repeated data

Question 13

Why are databases normalised ?

Answer 13

Increase efficiency without any compromise to the integrity of their data

Question 14

What are the pros of normalisation ?

Answer 14

• Reduces the number of updates, insertion and deletion anomalies that occur
• Easier to maintain
• Faster searching and sorting

Question 15

What happens when a database conforms to the first normal form ?

Answer 15

There are no repeating attributes and is atomic (no single column contains more then one value )

Question 16

What happens when a database conforms to the second normal form ?

Answer 16

Must satisfy first normal form and partial key dependencies are removed

Question 17

What is a partial key dependency ?

Answer 17

Occurs in composite primary keys when a non-key attribute doesn’t depend on the whole composite key

Question 18

What happens when a database conforms to the third normal form ?

Answer 18

No non-key dependencies

Question 19

How is a database that meets the third normal form described ?

Answer 19

All non-key attributes depend on the key , the whole key and nothing but the key

Question 20

What are the four main SQL commands ?

Answer 20

SELECT, UPDATE , INSERT , DELETE

Question 21

What is the fixed length string data type in SQL ?

Answer 21

CHAR(Size)

Question 22

What is the variable length string data type in SQL ?

Answer 22

VARCHAR(Size)

Question 23

What is the integer data type in SQL ?

Answer 23

INT(Size)

Question 24

What is the Number with fractional part data type in SQL ?

Answer 24

FLOAT (Size, Precision )

Question 25

What is the Date data type in SQL ?

Answer 25

DATE

Question 26

What is the Date and time data type in SQL ?

Answer 26

DATETIME

Question 27

What is the time data type in SQL ?

Answer 27

TIME

Question 28

What is the year data type in SQL ?

Answer 28

YEAR

Question 29

What does a client server database provide ?

Answer 29

Simultaneous access to a database for multiple clients

Question 30

What is the problem of Concurrent Access ?

Answer 30

Different users attempt to access the same field at the same time

Question 31

How can the issue of Concurrent Access be solved ?

Answer 31

Record Locks, Serialisation, Timestamp ordering and commitment ordering

Question 32

What is the solution of Record Locks ?

Answer 32

When a record is accessed by a user it's locked to others until the user is finished with it .

Question 33

What is the solution of Serialisation ?

Answer 33

Rather then locking a field , requests from others are queued .

Question 34

What is the solution of Timestamp Ordering ?

Answer 34

When multiple commands are sent to the same field then each is assigned a timestamp and executed in order of their timestamps

Question 35

What is the solution of Commitment Ordering ?

Answer 35

An algorithm is used to work out an optimum order in which to execute commands for the same field. Algorithm takes into account the impact of commands on other parts of the database and attempt to minimise issues from occurring.