1.3.2 - databases Flashcards

4

1
Q

entity

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

flat file database

A

Consists of a single file.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

relational database

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

foreign key

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

composite key

A

A primary key that consists on more than one attribute.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Basic structure of an SQL query

A

SELECT
FROM
WHERE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SQL query to select all fields from a table

A

SELECT *

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SQL query to select all records starting with ‘A’

A

WHERE…LIKE “A%”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

SQL query to insert new records

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

SQL query to delete an entity

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

SQL query to update an record

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

SQL query to delete a table

A

DROP TABLE <table name>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

SQL join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

SQL query to add a field

A

ALTER TABLE Employee
ADD Department VARCHAR(IO)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

SQL query to change the data type of a column

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

SQL query to delete a field

A

ALTER TABLE Employee
DROP COLUMN HireDate

17
Q

Database management system (DBMS)

A

Ensures that the data stored in the database remains consistent.

18
Q

data integrity

A

The process of maintaining the consistency of the database.

19
Q

ACID

A

Atomicity
Consistency
Isolation
Durability

20
Q

atomicity in databases

A

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

21
Q

consistency in databases

A

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

22
Q

isolation in databases

A

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

23
Q

recording locking

A

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

24
Q

durability in databases

A

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

25
Q

referential integrity

A

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

26
Q

1st normal form

A

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

27
Q

2nd normal form

A

Any partial dependencies have been removed.

28
Q

partial dependency

A

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

29
Q

3rd normal form

A

Any transitive dependencies have been removed.

30
Q

transitive dependencies

A

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

31
Q

advantages of normalisation

A
  • no data redundancy
  • maintaining and modifying the database
  • faster sorting and searching
  • deleting records
32
Q

features of a flat file database

A
  • may have redundant data
  • flat file harder to update
  • no specialist knowledge needed to operate
33
Q

features of a relational database

A
  • data integrity
  • linked tables
  • easier to change format
  • provides security features
34
Q

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

A

Completed transactions stored in secondary storage