Databases Flashcards

You may prefer our related Brainscape-certified flashcards:
1
Q

What is a database?

A

A database is a collection of data

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

What is an entity?

A

An ententity is an item of interest about which information is stored

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

What is a relational database?

A

A realtional database is a database which recognises the differences between entities by creating different tables for each entity

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

What are attributes?

A

Attributes are characteristics of entities, categories about which data is collected

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

What is a flat file?

A

A flat file is a database that consists of a single file that is more likely to be based around one single entity and its attributes

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

What is a primary key?

A

A primary key is a unique identifier for each record in the table, shown by underlining it (i.e. DoctorID, CarID, etc.)

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

What is a foreign key?

A

A foreign key is the attribute that links two tables, acting as a primary key in one table and a foreign key in another (i.e. DoctorID will be a foreign key to the Patient table)

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

What is a secondary key?

A

Secondary key allows a database to be searched quickly by setting up a secondary index on an attribute that it’s easier to remember.

A secondary key is a key field which can be used to access a table in a different way.

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

What are the different degrees of relationships that 2 databases possess when related?

A

When relating 2 databases there are different degrees of relationships they can possess:
I. One to one;
II. One to many;
III. Many to many;

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

How do you create a table using SQL code ? (Not on Spec but useful)

A

CREATE TABLE employee
(
EmpID INTEGER NOT NULL PRIMARY KEY,
EmpName VARCHAR(20) NOT NULL,
HireDate DATE,
Salary CURRENCY
);

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

How do you insert a new record in a database?

A

INSERT INTO tableName (column1, column2)
VALUES (value1, value2)

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

How do you update a record in a table?

A

UPDATE tableName
SET column1=value1, column2=vallue2
WHERE columnX = value

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

How do you delete a record in a table?

A

DELETE FROM tableName
WHERE column=value

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

What does ACID stand for

A

Atomicity
Consistency
Isolation
Durability

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

What is data redundancy

A

When there is unnecessary data repetition in a database i.e. having different students studying the same subject

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

Cons that follow data redundancy are

A

An unnecessary amount of data storage capacity required to store duplicated data

An increased risk of having inaccurate data

It is more time consuming and problematic to update the database so that’s it’s accurate

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

Data that is inconsistent lacks

A

Integrity

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

Data that lacks integrity is

A

Inconsistent

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

Using an entity relationship tables have some of the following pros

A

Less data redundancy and less chance of error leading to inconsistency in the data and less time spent updating the data as it has to be done only in one place

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

Each table in a entity relationship database represents

A

An entity

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

A composite primary key

A

A composite or concatenated primary key is when more than one field is combined to create a unique primary key for a table.

22
Q

Define Data integrity

A

Refers to the accuracy and reliability of data and is of prime importance in any computer system, as when data is inaccurate, information produced from systems will be discredited, hence why, validation and verification techniques are used to ensure data integrity

23
Q

Referential integrity

A

Is the process of ensuring consistency. This ensures the information is not removed if it is required elsewhere in a linked database

24
Q

What’s normalisation

A

The process of coming up with the best possible layout for a relational database.

25
Q

Normalisation tries to accomplish

A

No redundancy
Consistent data through linked tables
Records can be added and removed without issues
Complex queries can be carried out

26
Q

There are 3 types of normalisation

A

First Normal Form
Second Normal Form
Third Normal Form

27
Q

First Normal Form

A

There must be no attribute that contains more than a single value

28
Q

Second Normal Form

A

A database which doesn’t have partial dependencies and is in its NF1 can be said to be in is NF2. This means that no attributes can depend on par of a composite key

29
Q

Third Normal Form

A

If the database is in NF2 and contains no non-key dependencies then it’s in NF3.

30
Q

What’s a non key dependency

A

Means the attribute only depends on the value of the primary key and nothing else

31
Q

What’s indexing

A

Indexing is a method used to store the position of each record ordered by a certain attribute

32
Q

When selecting and managing data to reduce data input only…

A

Data that fits a certain criteria will be selected

33
Q

What does SQL stands for

A

Structured Query Language

34
Q

What’s one common way to exchange data

A

EDI as it doesn’t require human interaction and enables data transfer from one computer to another

35
Q

What does EDI stands for

A

Electronic Data Interchange

36
Q

The ORDER BY statement can order data in either

A

Ascending or descending order

37
Q

To order by descending order syntax

A

ORDER BY … Desc

38
Q

JOIN syntax explained

A

Provides a method of combining rows from multiple tables based on a common field between them

i.e. SELECT Movie.MovieTitle, Director.DirectorName, Movie.MovieCompany
FROM Movie
JOIN Director
ON Movie.DirectorName = Director.DirectorName

39
Q

Atomicity means

A

A transaction must be completed fully. Half completed change must not be saved back to the database

40
Q

Consistency means

A

Any changes in the database must retain the overall state of the database

41
Q

Isolation means

A

A transaction must not be interrupted by another transaction by locking values that are being accessed by one.

42
Q

Durability means

A

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

43
Q

How do you write nested selects

A

SELECT name FROM student
WHERE grade > (SELECT grade FROM student
WHERE name=”Smith”);

44
Q

How do you join two tables

A

SELECT student.name, course.name FROM student
JOIN course ON student.courseID=course.CourseID;

45
Q

What is a transaction

A

A change to the database

46
Q

What does DBMS stands for?

A

The Database Management System

47
Q

The Database Management System ensures that…

A

when a transaction takes place, the database changes from one consistent state to another.
For example when a transaction is taking a place a record may be locked to prevent another transaction from interfering with the first transaction.

48
Q

To get a table into Second Normal Form (2NF) you need to…

A
  1. Check data is already in 1NF
  2. Remove any partial dependencies, this means that one or more of the fields depends on only part of the primary key
  3. Fix any many-to-many relationships.
49
Q

What is the term, ‘error diagnostics’ ?

A

These are often messages from the compiler to the programmer indicating the location of syntax or possible logical errors, these message often reference the line number where the problem has occurred.

50
Q

Atomicity means ….

A

that a change in a database must be either completely performed or not performed at all.

51
Q

What is an advantage of a flat file database over a relational database?

A

they are simple to set up and ideal for very small databases.

52
Q

What are disadvantages of a flat file database over a relational database?

A

They are inefficient because they create data duplication which can also affect the integrity of data and therefore the accuracy of query results.