OCR_Computing_GCSE_Databases Flashcards
PUPIL and CLASS are two entities used in a database.
Explain the term entity.
A real world object about which data is stored in a database. Corresponds to tables in the database.
The data for the first four pupils in the PUPIL table is shown below.
State the primary key for the PUPIL table and explain your answer.
PupilNumber
It is a unique identifier. Two pupils cannot have the same PupilNumber but they can have the same surname, firstname or ClassCode.
The database also contains a CLASS table. The primary key for the CLASS table is ClassCode. Explain why ClassCode has also been included in the PUPIL table.
ClassCode is used here as a foreign key to link CLASS and PUPIL using the ClassCode, all the class details can be retrieved from the Class table otherwise the class details will have to be rewritten everytime/to avoid data redundancy
A television set top box contains a database of television channels and programmes.
Describe what is meant by a database.
A set of data organised as a set of records in one or more files.
Data about television channels are stored in the CHANNEL table. Part of this table is shown below.
State the primary key for the CHANNEL table and give a reason for your choice.
Primary key: ChannelID.
It is a unique identifier. Two channels can have the same Channel Name but not the same Channel ID.
A television set top box contains a database of television channels and programmes
Data about programmes that will be broadcast are stored in the PROGRAMME table. The data about each programme includes the channel on which it would be broadcast.
Explain how a foreign key can be used to connect the PROGRAMME table to the CHANNEL table (see image below).
Using ChannelID (the primary key of the Channel table) is stored in the PROGRAMME table where it is a foreign key.
A television set top box contains a database of television channels and programmes
Data about television channels are stored in the CHANNEL table
Data about programmes that will be broadcast are stored in the PROGRAMME table. The data about each programme includes the channel on which it would be broadcast.
Explain why the programme data is stored in a separate table from the channel data.
Less data entry required because programme and channel details are stored once. Avoids redundancy/don’t have to repeat channel details for every programme on that channel. Avoids inconsistency (when channel data changes)
A dentist uses a database to store the details of patients and their appointments.
A database management system (DBMS) is used. What does a DBMS typically include. Think input, process and output!
Input
Forms
Process
Queries
Output
reports.
A dentist uses a database to store the details of patients and their appointments.
A database management system (DBMS) is used which includes forms, queries and reports.
What (DBMS) feature is used to print out all the appointments that the dentist has booked ?
Reports
A dentist uses a database to store the details of patients and their appointments.
A database management system (DBMS) is used which includes forms, queries and reports.
What (DBMS) feature is used to enter a patients details when the patient registers with the dentist ?
FORM
A dentist uses a database to store the details of patients and their appointments.
A database management system (DBMS) is used which includes forms, queries and reports.
What (DBMS) feature is used to find out all the the appointments that a certain patient has made ?
QUERIES
When a patient makes an appointment, the start time of the appointment needs to be validated.
State the validation checks which can be carried out on the start time of an appointment.
The time is in the correct format hh:mm.
The time is within the dentist’s working day.
The hours are in the range 1-12 / 0-24.
The minutes are in the range 0-59.
A dentist uses a database to store the details of patients and their appointments.
Justify the use of separate entities to store the patient and appointment data.
The patient’s data does not have to be repeated for each appointment as the patient ID can be stored with the appointment to link the two entities.
Allows the patient (and appointment) data to be manipulated independently e.g. if the name of the patient changes.
Avoids the possibility of the patient data becoming inconsistent due to being stored multiple times
A grocery shop uses a database with a DBMS to keep records of its stock.
Explain what is meant by a DBMS.
A Database Management System. Used to manage the database. Provides facilities for
creating tables, inserting data, editing/deleting data,
running queries (questions).
and producing reports
A grocery shop uses a database with a DBMS to keep records of its stock.
The database uses forms and reports.
Describe each of these and give one example of how it would be used in the shop’s database.
FORM
An input screen, allowing chosen data items to be displayed and edited. Uses text boxes/drop down lists/checkboxes etc. When edited the changes are updated in the database. The grocery shop could use a form to enter new products into the database.
REPORT
An output of the data in a database. A snapshot of the data at a given time/when printed of specified fields (based on a query) and laid out in a specified format. Aggregates may be calculated and displayed. The grocery shop could use a report to display weekly sales.
A grocery shop uses a database with a DBMS to keep records of its stock.
Below is some data from the supermarket’s database.
State the ProductID of the products in the above sample which fit the following criteria.
Supplier = Killey’s
0003
0006
A grocery shop uses a database with a DBMS to keep records of its stock.
Below is some data from the supermarket’s database.
State the ProductID of the products in the above sample which fit the following criteria.
Price > £1.00 OR Supplier = Hill Farm
0001
0002
0004
0008
A grocery shop uses a database with a DBMS to keep records of its stock.
Below is some data from the supermarket’s database.
Write the criteria which can be used to select all products which are not discontinued and where the QuantityLeft is lower than the ReorderLevel.
A* Question!
(Discontinued = False) AND (QuantityLeft < ReorderLevel)
Mrs Smith uses a query to select jobs using the following criteria:
(Time = “Afternoon”) OR (Time = “Evening”)
List the JobNumbers of the jobs that will be selected from the extract shown.
36
37
39
Mrs Smith wants to use database management software to create a report of all the jobs that she needs to perform on any given day, using data from the DOG and JOB tables.
- The table DOG stores the following data about each dog: DogID, name, sex, weight, date of arrival, date of departure.
- The table JOB stores the daily jobs that she needs to do with each dog.
Design a layout for the report. 7 Marks for drawing this!
There is a date.
There is a title.
Jobs are grouped/sorted appropriately (eg by dog, time or job type).
Each job includes the Dog Name.
Each job includes the Dog ID.
Each Job includes the Job type.
Each job includes details.
Mrs Smith runs a dog sitting service that looks after dogs whose owners are going away on holiday.
Mrs Smith uses a database with two tables:
• The table DOG stores the following data about each dog: DogID, name, sex, weight, date of arrival, date of departure.
• The table JOB stores the daily jobs that she needs to do with each dog.
An extract of the JOB table is shown below:
Explain why DogID has been included in this table.
It is used as a foreign key (in this table)… the primary key of the DOG table to link the two tables and allows us to find the details of the Dog to which each job relates.
