14.05 Relational Databases + 14.06 Performing Searches

Question 1

What is a ‘relational database’?

Answer 1

A database that contains more than one table. However the tables are linked using their primary keys.

Question 2

Where might a ‘relational database’ be used?

Answer 2

For example in a vet’s where there could be 3 fields of information required, eg:

Table: Primary Key: Data Stored:

Owner # Name/address/phone/pet#

Pet # Name/breed/species/owner#

Appointment # Time/date/pet#

Question 3

Diagram to show how the tables are linked…

Answer 3

OWNER TABLE PET TABLE APPOINTMENT TABLE
Owner # ———–>Owner #—–>Pet #
Owner name Pet name Vet name
Owner address Pet bread Time
Owner phone Pet # Date

Question 4

Explanation of the diagram…

Answer 4

By looking up the owner by name or phone number etc we can find information about the pet and again via the primary key information can be found out about the appointment time.

Question 5

Advantages of Relational Databases…

Answer 5

• Data only needs to be entered once, and more information can be added about it, an owner already on the database can then ‘register’ another pet without needing to re enter information about themselves.
• Entering the data once means there will be less data errors made, if the information about the pet and owner had to be entered everytime there is an appointment, then it is lightly that errors would be made in entering this information each time.
• If data was entered incorrectly, if the pets name was entered incorrectly it only has to be corrected in one place, in a normal line database the pets name would have to be corrected under each appointment field.

Question 6

What is a ‘query’?

Answer 6

A method/way of searching a database

Question 7

What is ‘SQL’?

Answer 7

A specialised programming language for maintaining databases and generating queries

Question 8

What does ‘Query-by-example’ mean?

Answer 8

A method of searching a database by stating the criteria to be searched, eg by appointment time or pet bread.

The areas that could be searched for/in are:

• The table
• The field
• The criteria
• Any sorting that we want to apply
• If a particular field is required in the search results (hide the certain fields)

Question 9

In a table of students at a school, how could a query-by-example be used?

Answer 9

If there was a field of exam results that could be searched in, the query-by-example could be used to find groups of students by ability. The criteria could be to show students with more than 55% but less than 74% average score. This could be used with different results brackets to set classes for the next year/term based on results in previous exams.

Question 10

What is an operator?

Answer 10

An operator id used to search for values in the table . 
=         Equal to 
>         Greater than 
<         Less than 
>=       Greater than or equal to 
<=       Less than or equal to 
AND   Combines 2 criteria 
OR      Either one criteria or another
NOT   Excluding a criteria  

This could be used to find students who need to retake exams?

Re-sit = a result <50%

Criteria = ‘re-sit’ = yes?