2.5 Databases and distributed systems

Question 1

What is normalisation?

Answer 1

A staged process which removes repeated groups of data and inconsistencies.

Question 2

What is the difference between a primary and a foreign key?

Answer 2

A primary key is unique and used to identify a record whereas a foreign key is a field of one table which is also the primary key of another table (links tables together).

Question 3

What is the difference between a flat file and a relational database?

Answer 3

A flat file database is one table which holds all data however a relational database is a large, organised collection of data items stored in tables which link to other tables and can be accessed in many ways throughout the program.

Question 4

What are 5 advantages of relational databases?

Answer 4

Data Consistency
No Data Redundancy
Data Integrity
Data Independence
Better Security

Question 5

What is Data Consistency?

Answer 5

Data Consistency is the relationship between the input data, the processed data and the output data as well as other related data.

Changes made to data is controlled so that the specific data value is consistent throughout the whole database.

Question 6

What is Data Redundancy?

Answer 6

Occurs when duplicate data is available in multiple tables. This wastes storage and could potentially compromise data consistency.

Question 7

What is Data Integrity?

Answer 7

It is the correctness of data e.g. the extent to which it truthfully represents the original information.

Question 8

What is Data Independence?

Answer 8

Refers to the separation of information from the applications and programs that use it. New systems/programs can still develop/use this data without having to change it or modify the source code.

Question 9

What are three examples of security in a relational database?

Answer 9

Hierarchy of passwords - Limits users to certain parts of the program.
Access rights to parts of the program - Only certain users can access and change data.
Data is stored separately from the program.

Question 10

How can products in a supermarket have a One to One entity relationship to a barcode?

Answer 10

Every product in the supermarket will have a unique barcode number.

Question 11

How can a member of a library have a One to Many entity relationship with the books?

Answer 11

A member of a library can take home multiple books therefore, all the books will have the same, temporary owner.

Question 12

How can teachers have a Many to Many entity relationship with pupils in a school?

Answer 12

Teachers can teach many pupils across the day however, the pupil will also be taught by many teachers throughout their day.

Question 13

How can many-to-many relationships be improved?

Answer 13

They can be replaced by a one-many-one relationship.

Question 14

What is Big Data?

Answer 14

Data which involves very large, complex datasets therefore traditional databases are unable to process them under acceptable time frames.

Question 15

What are four examples of Big Data?

Answer 15

Transactional data from ecommerce.
Machine data from GPS.
RFID Readers.
Social Media interactions.

Question 16

What is a Data Warehouse?

Answer 16

A large collection of archived data used for decision making.

Question 17

Name six advantages of Data Warehousing which will benefit an organisation.

Answer 17

Allows an organisation to:
- Store information about every sale
- See who has bought items and when
- Use data mining
- Find the most popular product
- Target customers with special offers
- Plan future changes/developments within the business

Question 18

What is Data Mining?

Answer 18

The retrieval and analysis of large sets of data in data warehouses to identify trends and patterns e.g. market opportunities and health trends.

Question 19

What are 5 advantages of Data Mining which benefit an organisation?

Answer 19

Allows an organisation to create:
- A list of customers who are likely to buy a certain product
- Comparisons with competitors
- Predictions for future sales
- Analysis of best sites for shops
- Analysis of sales patterns

Question 20

What is Predictive Analysis?

Answer 20

A sub-set of data mining which is used to make predictions about future events, based on individual historic behaviour e.g. weather, insurance risk assessments and targeted marketing.

Question 21

How is predictive analysis implemented?

Answer 21

Predictive analysis assigns a probability for the likelihood that something, such as a customer, will behave in a certain way.

Question 22

How could a travel company receive data to undergo predictive analysis?

Answer 22

Comments on social media pages.
Sensors in smart devices such as location services.
Online questionnaires/satisfaction surveys.

Question 23

What is a distributed database?

Answer 23

Data is not stored in one location, but spread out across a network. The user would not be aware of this, it would still just appear as a large database.

Question 24

What are 6 advantages of distributed databases?

Answer 24

• Non-dependent on a central store of data.
• Reduces network traffic.
• If one server fails, the entire system does not go down.
• Easy to backup and copy from one server to another.
• If data is lost on the central site, it can be recovered from another local site.
• New locations can be added to a database without the need to rewrite the entire database.

Question 25

What are disadvantages of distributed databases?

Answer 25

• Heavy reliance on networks and communications.
• Security issues if sensitive data is transferred.
• If a link to a server fails, data may not be able to be obtained from that server.
• Increased costs for extra communication lines.
• Greater chance of data inconsistency.
• Harder to control the security of data spread.

Question 26

Write an SQL command to output the name and tutor group of all students in a table called STUDENTS.

Answer 26

SELECT Name, TutorGroup FROM STUDENTS

Question 27

Write an SQL command to output the names of students with tutor group 12.1 from a table named STUDENTS.

Answer 27

SELECT Name FROM STUDENTS WHERE TutorGroup = ‘12.1’

Question 28

Write an SQL command to change the tutor of students whose tutor is currently 12.1 to 12.2 from a table named STUDENTS.

Answer 28

UPDATE STUDENTS SET Tutor = ‘12.2’
WHERE Tutor = ‘12.1’

Question 29

Write an SQL command to output all names of students who have the same Tutor as Charlie Kelly from a table named STUDENTS.

Answer 29

SELECT Name FROM STUDENTS WHERE TutorGroup = (SELECT TutorGroup FROM STUDENTS WHERE Name = ‘Charlie Kelly)

Question 30

Write an SQL command to create a new table PHONES to store a phone number for each room number.

Answer 30

CREATE TABLE PHONES {
RoomNum Char(5) NOT NULL
PhoneNum Char(5) NOT NULL
}

Question 31

Write an SQL command to enter Room 113 with Phone 625 into a table named PHONES.

Answer 31

INSERT INTO PHONES VALUES (‘113’,’625’)

Question 32

Write an SQL command to output all student names with their date of births in descending order from a table named STUDENTS.

Answer 32

SELECT Name, DateBirth FROM STUDENTS
ORDER BY DateBirth DESCENDING

Question 33

What is the role of a Database Administrator?

Answer 33

The person in a company who is responsible for the structure, security and management of the database system.