1.3.2 - databases

Question 1

entity

Answer 1

A category of things/people of interest to a business that data is to be recorded about.

Question 2

flat file database

Answer 2

Consists of a single file.

Question 3

relational database

Answer 3

A separate table is created for each entity identified in the system.

Question 4

foreign key

Answer 4

A field that links two tables, links to a primary key in a second table.

Question 5

composite key

Answer 5

A primary key that consists on more than one attribute.

Question 6

Basic structure of an SQL query

Answer 6

SELECT
FROM
WHERE

Question 7

SQL query to select all fields from a table

Answer 7

SELECT *

Question 8

SQL query to select all records starting with ‘A’

Answer 8

WHERE…LIKE “A%”

Question 9

SQL query to insert new records

Answer 9

INSERT INTO <table name> (<field1>, <field2>)
VALUES (<value1>, <value 2>)</value1></field2></field1>

Question 10

SQL query to delete an entity

Answer 10

DELETE FROM <table name>
WHERE <field> = <condition></condition></field>

Question 11

SQL query to update an record

Answer 11

UPDATE <table name>
SET <field> = <value>
WHERE <field> = <condition></condition></field></value></field>

Question 12

SQL query to delete a table

Answer 12

DROP TABLE <table name>

Question 13

SQL join

Answer 13

SELECT Song. SongTitle,, Artist .ArtistName, Song. MusicType
FROM Song
JOIN Artist
ON Song.ArtistID = Artist.ArtistID
WHERE Song.MusicType = ’’Art Pop”

Question 14

SQL query to add a field

Answer 14

ALTER TABLE Employee
ADD Department VARCHAR(IO)

Question 15

SQL query to change the data type of a column

Answer 15

ALTER TABLE Employee
MODIFY COLUMN EmpName VARCHAR(30)NOT NULL

Question 16

SQL query to delete a field

Answer 16

ALTER TABLE Employee
DROP COLUMN HireDate

Question 17

Database management system (DBMS)

Answer 17

Ensures that the data stored in the database remains consistent.

Question 18

data integrity

Answer 18

The process of maintaining the consistency of the database.

Question 19

ACID

Answer 19

Atomicity
Consistency
Isolation
Durability

Question 20

atomicity in databases

Answer 20

A change to a database is either completely performed or not at all.

Question 21

consistency in databases

Answer 21

Any change in the database must retain the overall state of the database.

Question 22

isolation in databases

Answer 22

A transaction must not be interrupted by another transaction. Other users or processes shouldn’t be able to access the data concerned.

Question 23

recording locking

Answer 23

The records being affected by the transaction are locked, effectively placing them in a read-only state.

Question 24

durability in databases

Answer 24

Once a change has been made to a database, it must not be lost due to a system failure.

Question 25

referential integrity

Answer 25

Ensuring that changes are consistent across a database. If a record is removed, all references of it are removed.

Question 26

1st normal form

Answer 26

1- all field names must be unique 2- values in fields should be from the same domain 3- values in fields should be atomic 4- no two records can be identical 5- each table needs a primary key

Question 27

2nd normal form

Answer 27

Any partial dependencies have been removed.

Question 28

partial dependency

Answer 28

One or more attributes depends on only part of the primary key.

Question 29

3rd normal form

Answer 29

Any transitive dependencies have been removed.

Question 30

transitive dependencies

Answer 30

When the value of the field is determined by the value of another field that is not part of the primary key.

Question 31

advantages of normalisation

Answer 31

- no data redundancy - maintaining and modifying the database - faster sorting and searching - deleting records

Question 32

features of a flat file database

Answer 32

- may have redundant data - flat file harder to update - no specialist knowledge needed to operate

Question 33

features of a relational database

Answer 33

- data integrity - linked tables - easier to change format - provides security features

Question 34

Give one way that durability can be achieved for a completed transaction. [1]

Answer 34

Completed transactions stored in secondary storage