Font size
WorksheetsHigher Database Design & Development
Total questions: 25
Worksheet time: 24mins
A relational database is developed for a hospital. State which of the following are end-user requirements.
The nurses must be able to count how many patients should be in each room.
The doctors must be able to change the diagnosis of a patients.
The database must store information about the staff, patients, rooms and appointments.
A simple update query will be used to change the room that a patient occupying.
A complex search query will be used to show the details of a patient's appointment by searching using their patientID and appointment date and appointment time.
A relational database is developed for a hospital. State which of the following are functional requirements.
The nurses must be able to to enter a new patients into the database.
The receptionists must be able to add a new appointment for the patient and assign it to a doctor and room.
The database must allow the patients current medication to be updated using a simple update query.
A simple search query will be used with wildcards to allow the user to search for a patient using just the first letter of their surname.
A complex search query will be used to show the details of a patient's appointment by searching using their patientID and appointment date and appointment time.
State the cardinality between the two table shown in the Entity Occurrence Diagram
One to one
One to Many
Many to Many
State the cardinality between the two table shown in the Entity Occurrence Diagram
One to one
One to Many
Many to Many
State the cardinality between the two table shown in the Entity Occurrence Diagram
One to one
One to Many
Many to Many
State which of the following Entity Occurrence Diagrams correctly demonstrate some or all of the relationships between the entities in the Entity Relationship Diagram shown.
State the purpose of a compound key.
To join multiple fields together to create a unique combination of data that can be used in place of a primary key.
An aggregate function that is used to calculate compounding interest in a financial database.
Used in conjunction with the primary key to create a unique identifier for each record.
Used in encryption to decrypt and encrypt plain text messages.
Using the image provided, state a suitable compound key.
StudentID + CourseID
CourseID + EnrollmentDate
StudentID + CourseID + ExamDate
ExamDate + ExamTime
The data dictionary shown is used to store the details of when an employee is working in a shop. State the errors with this data dictionary.
Wrong Primary Key
Should use a Compound Key
Validation missing condition on Department field
Incorrect data types
Sizes are incorrect
State any errors that are present in the data dictionary shown.
Birthday should have a length check
The entity should have a compound key
The field EmployeeID should not have a presence check
StoreID should have referential integrity applied to it in the validation
There are no errors
State which of the following is an example of a length check.
len([userID]) = 10
= 10
userID = len(10)
== 10
In a data dictionary, describe the purpose of the attribute size column.
The attribute size column is used to identify the maximum number of characters allow for a textual value.
The attribute size column is used to define the exact length of data stored in a text data type must be.
The attribute size column is used to state the maximum size of each field being stored in an entity.
The attribute size column is used to define the actual storage, in bytes, that will be used to store any data stored in the field per record.
Describe the purpose of a wildcard.
A wildcard is used in the WHERE clause to create a condition that allows placeholder letters.
A wildcard let's the use enter any letter or number to fill in a space of the data.
As the user types in some data the computer using wildcards will display some possible options to choose from.
The wildcard is a special type of query that allows the user to change zero, one or many letters in a text value.
State which of the following WHERE clauses would display records which contain the word "and" (not as part of any other word) in their category.
WHERE category LIKE "* and *"
WHERE category LIKE "% and %"
WHERE category LIKE "?and?"
WHERE category LIKE "_and_"
WHERE category CONTAINS "and"
State which of the following are not an aggregate function.
AVG()
price * quantity
MIN() AS [Minimum Price]
LIKE "A*"
GROUP BY itemCODE
State which of the following are an aggregate function.
MAX(price) AS [Maxiumum Price]
price * quantity
COUNT(*) AS [Number of Items]
surname LIKE "A*"
GROUP BY itemCODE
Describe purpose of calculated values.
To carry out arithmetic on the data stored in fields to be displayed in the results table.
To carry out arithmetic on the data stored in fields to update the values that are stored in the database entity.
To calculate the result of an aggregate function.
To calculate string operations on the values stored in the database records.
Describe the purpose of an alias.
An alias is used to give a name to or rename a column in the results table of an SQL query.
An alias is used to change the name of a field in the database table.
An alias is used to change the name of any values that are used in the GROUP BY clause.
An alias is used to add a heading to the whole results table so the user knows what the results are for.
Describe the purpose of the GROUP BY clause.
GROUP BY groups records together that have the same value in the given field. This allows an aggregate function to be used on subsets of data individually rather than the whole set of data.
The GROUP BY let's the user specify where to find the data in the database.
The GROUP BY clause allows the user to categorise the records in the results table.
A GROUP BY is used when the user is using non-aggregate functions to allow them to be displayed even though they don't have an aggregate function.
State whether this SQL query will execute correctly.
SELECT category, COUNT(category) AS [Number of Items]
FROM Items
Yes
No
Describe the expected output of the following SQL Query.
SELECT DirectorName, AVG(duration) AS [Average Movie Duration]
FROM Movies
GROUP BY DirectorName
ORDER BY AVG(duration) DESC
It will display a list of directors with the average length of their movies. The directors will be shown from largest average length to smallest average length.
It will display the average duration of all the movies with the director who's average was the closest to the total average of all the movies. The directors will be displayed from largest total average to smallest total average.
It will display the average duration of the specified director. The directors will be shown from largest average length to smallest average length.
It will display only the directors names from smallest duration to largest duration.
State which of the following SQL queries will display the year that each directors first movie was released.
SELECT name, MIN(yearReleased) AS [Year of First Movie]
FROM Movie
GROUP BY name
SELECT name, MAX(yearReleased) AS [Year of First Movie]
FROM Movie
GROUP BY name
SELECT name, MIN(yearReleased) AS [Year of First Movie]
FROM Movie
GROUP BY yearReleased
SELECT name, MAX(yearReleased) AS [Year of First Movie]
FROM Movie
GROUP BY yearReleased
Using the sample data shown, Select the output of the following SQL Query:
SELECT Type, MIN(price) AS ‘Least expensive item’
FROM Electronics
WHERE Quantity >= 10
GROUP BY Type;
State which of the following SQL Statements would produce the results shown for all full time employees.
SELECT Department, SUM(salary) AS [Salary Cost]
FROM Staff
WHERE partTime = false
GROUP BY Department;
SELECT Department, COUNT(salary) AS [Salary Cost]
FROM Staff
WHERE partTime = false
GROUP BY Department;
SELECT Department, SUM(salary)
FROM Staff
WHERE partTime = false
GROUP BY Department;
SELECT Department, SUM(salary)
FROM Staff
ORDER BY SUM(salary) DESC
GROUP BY Department;
Select of the following results would be generated from the SQL Queries shown.
