Databases 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
Normalisation tries to accomplish
No redundancy Consistent data through linked tables Records can be added and removed without issues Complex queries can be carried out
26
There are 3 types of normalisation
First Normal Form Second Normal Form Third Normal Form
27
First Normal Form
There must be no attribute that contains more than a single value
28
Second Normal Form
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
Third Normal Form
If the database is in NF2 and contains no non-key dependencies then it’s in NF3.
30
What’s a non key dependency
Means the attribute only depends on the value of the primary key and nothing else
31
What’s indexing
Indexing is a method used to store the position of each record ordered by a certain attribute
32
When selecting and managing data to reduce data input only…
Data that fits a certain criteria will be selected
33
What does SQL stands for
Structured Query Language
34
What’s one common way to exchange data
EDI as it doesn’t require human interaction and enables data transfer from one computer to another
35
What does EDI stands for
Electronic Data Interchange
36
The ORDER BY statement can order data in either
Ascending or descending order
37
To order by descending order syntax
ORDER BY … Desc
38
JOIN syntax explained
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
Atomicity means
A transaction must be completed fully. Half completed change must not be saved back to the database
40
Consistency means
Any changes in the database must retain the overall state of the database
41
Isolation means
A transaction must not be interrupted by another transaction by locking values that are being accessed by one.
42
Durability means
Once a change has been made to a database it must not be lost due to a system failure
43
How do you write nested selects
SELECT name FROM student WHERE grade > (SELECT grade FROM student WHERE name="Smith");
44
How do you join two tables
SELECT student.name, course.name FROM student JOIN course ON student.courseID=course.CourseID;
45
What is a transaction
A change to the database
46
What does DBMS stands for?
The Database Management System
47
The Database Management System ensures that...
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
To get a table into Second Normal Form (2NF) you need to...
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
What is the term, 'error diagnostics' ?
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
Atomicity means ....
that a change in a database must be either completely performed or not performed at all.
51
What is an advantage of a flat file database over a relational database?
they are simple to set up and ideal for very small databases.
52
What are disadvantages of a flat file database over a relational database?
They are inefficient because they create data duplication which can also affect the integrity of data and therefore the accuracy of query results.