Font size
WorksheetsN5CS: Database Design and Development Test (SCHOLAR)
Total questions: 28
Worksheet time: 20mins
Nadheera runs a food take-away service. A company called "Let's Just Eat" signs her up to be a supplier on their mobile app that allows people to order food and have it delivered to their homes.
Who is the end user of the mobile app?
People ordering food using the app.
Nadheera and her take-away service.
The "Lets Just Eat" company.
The driver that delivers the food.
What does the term functional requirements mean when creating a database system?
A list of the hardware a system should run on.
Performance requirements for the system.
A definition of exactly what the system should do.
A specific instruction for the structure of the database.
What is an entity in a relational database model?
How two things are related to each other.
A form used with a database.
A person, object or thing we store data about.
A definition of the attributes of a database.
Live Core operate a search engine for web sites. They store the IP address of each user, the words they used in a search, and any link the user clicked on in the search results. These are stored as columns ipaddress, keyword and openedlink in a database.
What do we call ipaddress, keyword and openedlink in the database?
Entities.
Attributes.
Relationships.
Calculated values.
An attribute in a database holds values that are either true or false. What data type should be used for this attribute?
Text.
Number.
Boolean.
Date/Time.
What is the most appropriate data type for each of the attributes in this entity?
appointmentid
visitorname
telephonenumber
A presence check in a database system:
ensures that a user is present to operate the system.
requires that a value is entered for an attribute.
checks that there are two entities associated with a relationship.
checks that the values entered for an attribute are only those allowed.
A text field allows the user to enter a single text character with this validation rule: > A AND <= D.
Give an example of a letter that would be accepted as valid input?
(a)
Restricting a username to more than six characters and less than twelve is an example of:
field length validation.
data entry security.
restricted choice validation.
a range check.
The two entities staff and carservice exist for a database system. Part of the data dictionary for this system is: Entity name: carservice.
What would be the best type of validation for the attribute servicedby?
Restricted choice
Presence check
No validation required
Range check 55-57
Complete this sentence. A (a) exists when the primary key of one entity appears as a foreign key in another entity.
Two database tables are shown.
Referential integrity requires that if the courseid with the value of "3R2" in course is changed to "4R1", then the rows in the student table with the value of "3R2" would:
be deleted.
have a null (empty) value entered to replace "3R2".
have the value updated to "4R1".
not be affected by this change.
Enforcing referential integrity means that a foreign key value cannot be used ...
without a primary key of the same value in a related table.
without it being validated against a predefined rule.
without a Boolean data type for the key.
unless it is also the primary key of the same table.
What is type of relationship between musician and instrument in this entity-relationship diagram?
Many-to-many
One-to-one
Many-to-many
One-to-many
This entity relationship diagram represents a database. It has been implemented with referential integrity with cascade updates and cascade deletes.
If a row in the car table is deleted, what will happen in the carcollector table?
The row with the related primary key value will be deleted.
The primary key in carcollector with the same value will be set to empty.
Nothing will happen.
A row will be added to include the deleted value.
An art gallery holds a database of artists and the artworks they have created. For each artist, they hold an artist id, his/her first name, last name and email address. For each artwork, they hold the artist id, the unique name of the artwork and its current value.
Which of these is the correct entity relationship diagram for this system?
Diagram a)
Diagram b)
Diagram c)
Diagram d)
Create the query design to match the answer table.
tour
type
minclimbers
tourbooked
type = "Prep and climb"
minclimbers DESC
Which query design will show the studentno, email and courseid for students enrolled on 'Artificial Intelligence' courses.
A query is required to display the name and number of goals scored for a football player. The generated answer table is shown.
Is the query "fit for purpose"?
Yes, because only the information required is shown.
Yes, because although other data is also included, the information required is shown.
No, because it provides extra information that is not required.
No, because it does not provide the information required.
A database has the following two tables. Which SQL statement will produce the answer table.
Select the SQL statement that will add the requested data.
INSERT INTO product VALUES (5099, "Vegan brownie", "Gluten free brownie", 1.75);
INSERT INTO product SET productid = 5099, productname = "Vegan brownie", base = "Gluten free brownie", cost = 1.75;
INSERT INTO product (5099, "Vegan brownie", "Gluten free brownie", 1.75) TO (productid, productname, base, cost);
UPDATE product INSERT productid = 5099, productname = "Vegan brownie", base = "Gluten free brownie", cost = 1.75;
What will this SQL command do to the database? INSERT INTO flavour VALUES (74, "Banana", 1.50, "No", 4003) (75, "Mint choc", 1.75, "No", 4003);
The command contains an error and will fail when run.
It inserts a single row into the flavour table (for "Banana") - the second row is not added.
It inserts two rows into the flavour table.
It inserts two rows into the flavour table and updates the primary key value for 4003 in the product table.
The two highlighted rows in italics need to be deleted.
Which SQL statement will do it?
DELETE FROM flavour, product WHERE product.productcode = 3152 OR product.productcode = 4003;
DELETE FROM flavour WHERE flavourcode = 60 OR flavourcode = 61;
DELETE FROM flavour, product WHERE product.productcode = flavour.productcode AND (flavourcode = 60 OR flavourcode = 61);
DELETE FROM flavour WHERE flavourcode = 60 AND flavourcode = 61;
Write the SQL command that will correctly update the database. The cost of "Dairy ice-cream" based products is to be updated to 2.75.
How many records would be affected by this query?
UPDATE product
SET cost = 2.75
WHERE base = "Dairy ice-cream";
(a)
Which of the following is NOT a requirement of the General Data Protection Regulation (GDPR)?
Data must be held securely.
Data is only used for the purpose for which it was collected.
Data is shared publicly without consent.
Data must not be kept for longer than required.
Which SQL statement is used to remove rows from a database table without deleting the table structure?
DROP TABLE
DELETE FROM
REMOVE ROWS
TRUNCATE TABLE
What is the purpose of a primary key in a database table?
To lock the database table for editing.
To identify a column that can contain null values.
To uniquely identify each row in a table.
To mark columns that should be indexed.
