06 Database Systems

Question 1

What is a data warehouse?

Answer 1

A data warehouse is a large collection of archived data from multiple sources used for decision making. Huge quantities of data are stored in a consistent order to make interrogation more productive. In summary, a data warehouse is a huge database specifically structured for information access and reporting.

Question 2

What is a large collection of archived data from multiple sources used for decision making?

Answer 2

A data warehouse.

Question 3

What is data mining?

Answer 3

Data mining is the ‘interrogation’ of a data warehouse to help the organisation make decisions. It involves drilling down into the structure of the data to discover meaningful patterns. Users can then identify trends over time and can test theories for accuracy.

Question 4

What is the ‘interrogation’ of a data warehouse to help the organisation make decisions?

Answer 4

Data mining.

Question 5

Name advantages to a company of using a data warehouse? (Advantages are similar to data mining)

Answer 5

• Helps identify a list of customers likely to buy a certain product, which they can then use to target with a mail shot.
• The organisation can make comparisons with competitors.
• Can run ‘what if’ queries for modelling exercises.
• Can predict future sales
• Can find the best locations for new shops
• Can analyse sales patterns
• Test analysis of results for plausibility
• Can create reports to help decision making

Question 6

What is data independence?

Answer 6

Data should be independent of the underlying database structure, for example, if a business has an application for them without having to change the structure or relationships between data tables.

Question 7

What is data redundancy?

Answer 7

Data is redundant when it is duplicated unnecessarily. I.e. a customer’s address should only be stored in one place, so it can be updated easily.

Question 8

Data should be independent of the underlying database structure is known as what?

Answer 8

Data independence

Question 9

Data is redundant when it is duplicated unnecessarily is known as what?

Answer 9

Data redundancy

Question 10

What is data consistency?

Answer 10

Data must be consistence as it moves from input to processing to output, for instance, if the designer decides the date should be in the format 12/04/18, it shouldn’t sometimes appear as 12th April 2018.

Question 11

Data must be consistence as it moves from input to processing to output is known as what?

Answer 11

Data consistency

Question 12

What is data integrity?

Answer 12

Data integrity is the correctness of data. If the value is updated in one place, it should be the same updated value that appears in all the applications built on top of that database.

Question 13

What is the correctness of data?

Answer 13

Data integrity.

Question 14

A hospital unit uses a relational database. A patient is allocated to a ward and to a physiotherapist. One table in this database is structured as follows: tblWard (WardID, WardName, Capacity, FreeBeds) Name two other tables you could expect to see in this database.

Answer 14

Would be something like:

tblpatient (patientID[underlined], firstname, surname, dateofbirth, bloodtype, address, nextofkin, wardID, physioID)

tblphysio (psysioID[underlined], firstname, surname, phone)

Question 15

What is a distributed database?

Answer 15

A distributed database has data stored on a number of computers at different locations, but appears as one logical database to the user. Users can then access data as if it were held centrally in a single source.

Question 16

What has data stored on a number of computers at different locations, but appears as one logical database to the user?

Answer 16

A distributed database.

Question 17

List advantages of a relational database over a flat file spreadsheet approach.

Answer 17

• Data will be stored in one place and not be redundant. For example, the customers phone number will only be stored in one field of one table.
• Data will be independent of the database structure, for example, you could create an application for a shop for entering their sales using the same data tables they already use for marketing.
• Data will be consistent from input to processing to output, so dates should always remain in the same format like DD/MM/YY.
• Data is more secure because you can set different access levels for different users, so, a receptionist would be able to see some of the data, whereas a manager would be able to see all of it.

Question 18

Helps identify a list of customers likely to buy a certain product, which they can then use to target with a mail shot is an advantage of what?

Answer 18

A data warehouse.

Question 19

The organisation can make comparisons with competitors is an advantage of what?

Answer 19

A data warehouse.

Question 20

Can run ‘what if’ queries for modelling exercises is an advantage of what?

Answer 20

A data warehouse.

Question 21

Can predict future sales is an advantage of what?

Answer 21

A data warehouse.

Question 22

Can find the best locations for new shops is an advantage of what?

Answer 22

A data warehouse.

Question 23

Can analyse sales patterns is an advantage of what?

Answer 23

A data warehouse.

Question 24

Test analysis of results for plausibility is an advantage of what?

Answer 24

A data warehouse.

Question 25

Can create reports to help decision making is an advantage of what?

Answer 25

A data warehouse.

Question 26

Advantages of data mining to an organisation?

Answer 26

• Identify a list of customers likely to buy a certain product, which they can then use to target with a mail shot.
• Make comparisons with competitors
• Run ‘what if’ queries for modelling exercises
• Predict future sales
• Find best locations for new shops
• Analyse sales patterns
• Test analysis of results for plausibility
• Create reports to help decision making

Question 27

Identifying a list of customers likely to buy a certain product is an advantage of what?

Answer 27

Data mining to an organisation

Question 28

Make comparisons with competitors is an advantage of what?

Answer 28

Data mining to an organisation

Question 29

Run ‘what if’ queries for modelling exercises is an advantage of what?

Answer 29

Data mining to an organisation

Question 30

Predict future sales is an advantage of what?

Answer 30

Data mining to an organisation

Question 31

Find best locations for new shops is an advantage of what?

Answer 31

Data mining to an organisation

Question 32

Analyse sales patterns is an advantage of what?

Answer 32

Data mining to an organisation

Question 33

Test analysis of results for plausibility is an advantage of what?

Answer 33

Data mining to an organisation

Question 34

Create reports to help decision making is an advantage of what?

Answer 34

Data mining to an organisation

Question 35

Advantages to a large company of having a distributed database?

Answer 35

• Less network traffic. Data is held closer to where it is processed. This means there is less chance of bottlenecks in the network and data queries will run more quickly.
• There is no ‘single point of failure’. If a network connection is lost, it is likely that data will still be available to users
• There is improved security because data is not all held in one place that could be vulnerable to attack or accidental loss.
• Resilience - some data is replicate, so applications will continue to run if one copy of the data is lost.

Question 36

Less network traffic is an advantage of what?

Answer 36

A large company having a distributed database

Question 37

No ‘single point of failure’ is an advantage of what?

Answer 37

A large company having a distributed database

Question 38

Improved security is an advantage of what?

Answer 38

A large company having a distributed database

Question 39

Resilience is an advantage of what?

Answer 39

A large company having a distributed database

Question 40

Disadvantages of using distributed databases?

Answer 40

• They’re more complex. So they require experienced staff to manage and maintain.
• Data must be synchronised across multiple locations. This can lead to corruption, especially when trying to restore after a crash.
• Transfer of data across the network could be vulnerable to attack or loss, unless encryption is used.
• Security passwords must be enforced at all locations if an employee leaves, their account must be deleted on all systems.
• All locations must be protected from viruses with antivirus software and an employee code of conduct.
• Data may become inaccessible if the critical server fails.

Question 41

What are more complex and require experienced staff to manage and maintain?

Answer 41

Distributed databases.

Question 42

Data must be synchronised across multiple locations which can lead to corruption. This is a disadvantage of what?

Answer 42

Distributed databases.

Question 43

Transferring data across the network could be vulnerable to attack or loss is a disadvantage of what?

Answer 43

Distributed databases.

Question 44

Security passwords must be enforced at all locations if an employee leaves is a disadvantage of what?

Answer 44

Distributed databases.

Question 45

All locations must be protected from viruses with antivirus software and an employee code of conduct is a disadvantage of what?

Answer 45

Distributed databases.

Question 46

Data possibly becoming inaccessible if the critical server fails is a disadvantage of what?

Answer 46

Distributed databases.

Question 47

What is data normalisation?

Answer 47

A staged mathematical process that removes repeated groups of data.

A staged mathematical process that removes data duplication and inconsistencies

Question 48

A staged mathematical process that removes repeated groups of data is known as what?

Answer 48

Data normalisation.

Question 49

Name security weaknesses associated with a distributed database and list solutions.

Answer 49

• Data is vulnerable to hacking while it is transferred between locations over a network. A solution to prevent this is encryption
• Data is stored in multiple locations and some local sites may not be as secure as central headquarters. Therefore, usernames and passwords should always be required and passwords should be changed regularly.