Knowt
Chapter 18
Describe the characteristics of a primary key and a foreign key.
(a) Primary key
It is unique
It cannot be a null value
Identifies a record
(b) Foreign key
Is needed to act as a link in relationships
It is only present in relational databases
It is a primary key in another table
Part of the database is shown.
Write down the field name which is the most appropriate for the data type, based on the data shown in the table. Your answers must be different in each case
Boolean Gender
Numeric: integer Exam_mark
Numeric: decimal Average_mark
Alphanumeric Student_name
A club has many members. The secretary of the club wants to keep details of the club members in a database.
(a) Describe the benefits and drawbacks of storing membership details on a computer database.
Benefits
Membership details can be searched very quickly as there are many members
Membership details can be quickly edited/updated
The data in the database can be backed up easily
Easier to create an archive of past members
More secure with a valid reason
Can be used as a source for mail merging
Less errors as data does not have to be re-written
Reduces the time as data does not have to be re-written
Drawbacks
Need IT skills to use the software/computer
Need to have to additional equipment which is expensive
Can take up a lot of storage space/memory in the computer
Expensive to buy software
Expensive to secure the system as it contains personal data
Training staff on how to use the software will be time consuming
Training could be expensive for the club
(b) The secretary collects data from the members on paper-based forms. Describe the features of a well-designed paper-based data capture form.
Adequate space for response//Individual character boxes
Fonts/font size should be easy to read
Instructions how to complete form
Logical order of questions
Questions spaced out
Appropriate white space
Questions should be clear and understandable
The manager of a car garage has asked a systems analyst to create a new database system for the checking of cars making sure they are safe to drive. The checking of cars is known as a service.
Each car has a unique licence plate.
The cars can either be powered by electric or petrol.
Cars are serviced every 10000 km, for example 10000 km, 20000 km.
The minimum cost of the service is 1500 rupees and the maximum is 5000 rupees depending on the model of car.
Different models of car are serviced.
The date of the service is agreed
(a) Complete this table, filling in the field names and the most appropriate data types to create the database using the information given above. For any numeric field, specify the type of number.
(b) The systems analyst is planning to create a relational database. Discuss the advantages and disadvantages of creating a relational database rather than a flat file database.
Advantages
Service details only need to be entered once into the database
Mistakes are less likely to happen when entering data if it already exists
Uses data redundancy
Data can be accessed using key fields
Uses data flexibility
Uses data integrity
Uses data consistency
Better security due to user level access control
Caters for future requirements
Disadvantages
Relational databases can be more complex
Requires training to set up
Harder to set up
Requires a data administrator therefore increasing the costs
A newsagent has created a database with fields to store details of each newspaper and magazine he sells. Part of the database is shown.
(a) Tick (✓) the field name that would be the most appropriate primary key for the database.
A Product_number
B Name_of_publication
C Selling_price
D Frequency
E Publisher
(b) The newsagent has added a field called Bar_code to store the bar code number of the product. Explain why a presence check is not a suitable validation check for the Bar_code field
Not every publication has a bar code
(c) The newsagent could use a relational database rather than a flat file database. Describe the advantages of using a relational database rather than a flat file database.
Uses data integrity which means no discrepancies in the data
Less memory used in the computer
Data is not unnecessarily duplicated
Queries can be created using many tables/Complex queries can be created
Reports can be created using many tables/Complex reports can be created
Only have to input the data once into a relational database therefore it saves time
(d) From the data shown in the part of the database, identify the most appropriate data type for the:
(i) Selling_price
Currency
(ii) Name_of_publication
Text
A teacher is setting up a school shop. The teacher is setting up a spreadsheet to keep the accounts of the shop
(a) Fig. 3.1 shows a box containing 50 Choco Bars to sell in the shop. The information on the box will be entered into the spreadsheet.
A Text
B Number/Integer
C Date
D Currency
(b) Each box delivered to the school shop includes a QR code. Explain why QR codes are used.
They can be read from many angles
The physical size of the QR can be small therefore takes up less space on the box
Can be used to track stock
Can contain links to more information about the products
It contains information about the product
Can be read by any device with a camera and suitable software
A systems analyst has created a new computer system to keep records in a medical centre. She has created a relational database to store the medical records of patients. The database uses primary and foreign keys.
Describe what is meant by a relational database.
Contains more than one table
Tables are linked
It uses relationships
Removes redundancy of data
Saves storage space
Explain the difference between a primary key and a foreign key
Primary key holds unique data
Primary key identifies the record
Primary key can be automatically indexed
Each table has one primary key whereas a table can contain a number of foreign keys
Foreign key is used to link with the primary key of another table
A dentist works at the medical centre. The dentist stores the medical records of his patients. Describe four other uses of the database software that the dentist can use to help him in his work.
Searches/queries can be used to search details of patients
Reports can be created about treatment carried out
Formulas can be created to calculate the cost of treatment
Charts can be created showing missed appointments by patients
Images are stored of the x-rays to identify the patient/determine treatment
Mail merge appointments that have been missed
Two types of database are relational and flat file.
(a) Compare and contrast relational databases and flat file databases. Your answer must include similarities and differences.
Similarity
Both use a primary key
Both can create searches/reports
Both store data in records and fields in the table
Both use indexes
Differences
Flat file database is a plain text file
Flat file is a simple structure
Flat file uses one table
Relational database has linked tables/relationships
Relational database allows searches/reports to be created over multiple tables
Relational database allows cross referencing between tables
Relational database records are easier to add
Relational databases are more powerful
Relational database reduces duplicate data
Relational database uses foreign keys
To gain full marks there must be correct answers for both similarities and differences
(b) Give, using an example, one reason why flat file databases are used in certain applications rather than relational databases.
One from:
The data is in a list
There is only a small amount of data
A single table is to be used
One mark:
Example of a list such as name, address/CSV files
Petr has set up a database for a science project on planets in the solar system. Part of the database is shown.
(a) When Petr was designing his database, he had to set the data types for each field. Complete the design table below by filling in the data types for each named field. Use the most appropriate data type. Each data type must be different. For any numeric field, specify the type of number
(b) Operators such as AND, OR, NOT, LIKE, >, >=, <, <=, =, <> can be used to search Petr’s planet database. The search criteria for all the gas giant planets with more than 20 moons would look like this:
Type_of_planet = ''Gas Giant'' AND Number_of_moons > 20
Write down the search criteria that will produce a list of planets that are not gas giants but have at least one moon and were first observed after 1800.
Type_of_planet= NOT "Gas Giant" AND Number_of_moons >=1 AND First_observed >1800
(c) Write down the names of the planets that are not gas giants but have at least one moon and were first observed after 1800.
Pluto
Haumea
(d) Petr has copied the data from the Orbital_period field into a spreadsheet. The data for the orbital period for Mars, 1.88, is stored in cell A2. In cell B2 he has entered a function of: ROUND(A2,0) Explain in detail the function used in cell B2.
It would take the value of A2 – 1 mark
Changes the value into an integer/zero decimal places – 1 mark
Aimi works for a shop that sells toys. She is setting up a relational database of the stock in the shop. She has started designing the database. Aimi has written down the questions that are asked about each toy and needs to produce appropriate field names from them. An example of the type of data that is to be stored is shown.
What is the toy? Tawara Doll
Who manufactured the toy? Tinky
What year was it manufactured? 2020
What price was it bought for? $12.99
(a) Complete the design table below by filling in an appropriate field name for each question. Each field name must be different. Field names must not include spaces.
(b) When Aimi created the whole database one of the fields was set as integer. However, as data is entered Aimi realises that the field should have been set to two decimal places.
Describe the steps that need to be taken, by Aimi, to change the field from an integer to a decimal. This field will be set to two decimal places
Open the table
Select Design View
Select the field
Select field type
Select from the drop down list – double
Click decimal places
Type in 2
(c) Aimi created a relational database but could have created a flat file database. Discuss the advantages and disadvantages of using a relational database rather than a flat file database.
Advantages
Data does not need to be entered a second time as tables are used
Records cannot be duplicated but a flat file cannot stop this
Saves time when entering data
More efficient storage as data is only stored once
Simpler to delete/modify details
Complex queries can be carried out
Complex reports can be created
Better security as some tables can be made confidential
Avoids inconsistent records
More data independence
Less inconsistency of data
More ability to cater for future requirements
Disadvantages
More complex than a flat file database as more tables are required
Takes more time to set up
More of a reduction in performance if many tables are needed
Slower extraction of data
Less robust due to broken keys and records//Each table requires a key field
and relationships to other tables
Needs specialist personnel to setup the database
More processing power needed for complex queries
A relational database is more complex to understand that a flat file database
To gain full marks there needs to be advantages and disadvantages
The owner of a bookshop has a database of all the books she has in stock. As some of the data is being entered into the database it is checked using validation checks. 11 An example of some of the records in the database is shown.
Explain, using a field name and examples of the data stored in that field in the database, why validation checks must be appropriate to the data that is being checked
Type of book
Can use length/lookup check as only one letter is input for example E or H
Cannot use a type check as only one letter used
ISBN
Can use check digit as a way of checking that digits are not transposed
Cannot use a length check as they are different lengths
Date of purchase
Can use a format check as all dates are the same format.
Number of copies
Can use a range/limit check as the lowest number is 1
Can use a type check as only numbers are accepted
Name of book/Name of Book
Cannot use a length check as names can be any length
Cannot use a type check as any character can be used
Name of author
Cannot use a length check as names can be any length
A new database system has been set up for a bookshop. Part of a record from the database system is shown. Each field has a validation check which needs to be tested.
(a) For each field identify the most appropriate validation check. Each check must be different. The validation checks for two fields have already been identified; these types of validation check must not be used for the other fields
(b) The Purchase_price field uses a range check. The prices of books range from 1 to 100. Identify items of test data which could be used with the Purchase_price field, giving reasons for your choice. The reasons must be different in each case.
Any number in the range 1–100
This is normal data and should work//data that is within the acceptable range
Any number outside the range or a word/character/symbol
This would check the range check boundaries/trap incorrect data types/abnormal data//data that is outside the acceptable range
1
This would check the lower boundary of the range check/extreme data//on the edge of acceptable range
100
This would check the upper boundary of the range check/extreme data//on the edge of acceptable range
A student is creating a database for her geography project to show all the earthquakes that occurred in 2019. She has produced part of the database which is shown.
When she has completed her database, she needs to carry out some searches on the data. To search for all the earthquakes with a depth of more than 20km she will need to type the following search criteria.
Depth(km) >20
(a) Write the search criteria to find all the earthquakes of a magnitude greater than or equal to 6.5, with a depth less than 15km.
Magnitude>=6.5 AND Depth(km)<15
(b) Give the name of the country that satisfies the search criteria in part (a).
Japan