Font size
WorksheetsDatabase Design
Total questions: 70
Worksheet time: 2hrs 18mins
Identify the roles of database in the present business world.
i. Handling multifiles data access
ii. Controlling data security
iii. Equitable use
iv. Validating data
i and ii
ii and iii
i, ii and iii
i, ii and iv
Define the property of database that ensures the evolvement in order to satisfy the changes in user requirements.
flexibility
efficiency
usability
completeness
Identify the relation key that uniquely defines each row in a table.
Foreign Key
Candidate Key
Primary Key
Composite Key
Choose the correct symbol of PROJECT in the Relational Algebra.
˄
×
π
σ
In relational data model, the total number of values can be identified as ______________________ .
Cardinality
Tuple
Attribute
Entity
Choose the symbol of entity in the Entity Relationship Diagram.
Indicate the term for number of entities to which another entity can be associated via a relationship set.
Schema
Attributes
Cardinality
Entity
Identify the relational algebra operator that is applied to tables of Employees and Department to produce the following result as in the table.
An outer join operator
The cross product operator
The project operator
The natural join operator
A relation is in _______, if and only if, every determinant is a candidate key.
First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization works through a series called normal forms. Identify stages that are as high as needed to go in the normalization process for most purpose in business database design.
One
Two
Three
Unnormalised
Choose the SQL command that is used to change a table's storage characteristics.
CHANGE TABLE
EDIT TABLE
MODIFY TABLE
ALTER TABLE
Identify which of the following relation would be produced by the relational algebra statement below.
Compute the inner join between the DEPARTMENT and EMPLOYEE relations.
Compute the natural join between the DEPARTMENT and EMPLOYEE relations.
Compute the set difference between the DEPARTMENT and EMPLOYEE relations.
Compute the cross product between the DEPARTMENT and EMPLOYEE relations.
Select the relational algebra statement to find employee tuples who work in Accounting Department called Sandra.
σ dept = ‘IT’ ˄ name = ‘Sandra’(EMPLOYEE)
σ dept = ‘IT’ ˅ name = ‘Sandra’(EMPLOYEE)
π dept = ‘IT’ ˄ name = ‘Sandra’(EMPLOYEE)
π dept = ‘IT’ ˅ name = ‘Sandra’(EMPLOYEE)
Choose the syntax for output in Table
σ staffID, name, department (π staffID=2211)(STAFF)
π staffID, name, department (σ staffID=2211)(STAFF)
π name, staffID, department (σ staffID=2211)(STAFF)
σ name, staffID, department (π staffID=2211)(STAFF)
Write the relational algebra to list employee’s number and employee’s name whose salary is greater than RM3000 in the table.
σ WorkerId, WorkerName (π Salary > 3000)(WORKER)
π WorkerId, WorkerName (σ Salary = 3000)(WORKER)
π WorkerId, WorkerName(σ Salary > 3000)(WORKER)
σ WorkerId, WorkerName (π Salary > 3000)(WORKER)
Entities can be associated with one another in Entity Relationship Diagram (ERD) using _____________.
relationship
cardinality
attributes
primary key
An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. Based on the statement, choose the relationship that has been applied.
one-to-many
one-to-one
many-to-many
many-to-one
Choose the denotation in a relational database which has the functional dependency between attributes of StudentID, StudentName and Course.
StudentID --> StudentName, Course
StudentName -->Course
StudentName, Course -->StudentID
Course <--StudentID, StudentName
Based on the relational schema in Figure, select one of the following that would violate the 2NF property.
Register_No, Name
Course_No, Course_Name
Register_No, Phone
Phone, Branch
Determine the SQL statement that were used to drop table in a database.
DROP DATABASE TABLE
DROP RELATION
DROP TABLE
DROP DATABASE RELATION
Determine the result based on the SQL statement in Figure.
A listing of all customers in the customer table
A listing of the owner of the customer table
A listing of all owner and customer of customer table
A listing of the owner of the customer table as well as customers
Analyze the result based on SQL statement in Figure.
Changes the length of the Unit_Price field to 775
Changes the price of a unit called Product_T to 7
Changes the unit price of Product 7 to 775
Updates the Product_T table to have a unit price of 7
Identify the INCORRECT statement while using LIKE comparison operator.
WHERE CustomerName LIKE ‘ad%’;
WHERE CustomerName LIKE ‘%ad’;
WHERE CustomerName LIKE ‘_ad%’;
WHERE CustomerName LIKE = ‘ad%’;
Choose the SQL statement to display the entire record from the Table with the names of students sorted out in descending order.
SELECT * FROM Student ORDER BY NAME;
SELECT * ORDER BY NAME FROM Student;
SELECT * FROM Student ORDER BY NAME DESC;
SELECT * ORDER BY NAME DESC FROM Student;
Identify the output when SQL command in Figure is executed.
The highest salary for all employees
The highest salary in each department
The employees with the highest salaries
The employees with the highest salary for each department
_________is a person, place, object, event or concept in the user environment about which company wishes to maintain the data
attribute
entity
relationship
primary key
Which attribute is the DERIVED ATTRIBUTE?
Name
Address
Age
Salary
Each entity is described by __________.
Attribute
Key
Entity
Relationship
This shape is symbol for ?
Entity
Attribute
Primary Key
Foreign Key
Which attribute is the COMPOSITE ATTRIBUTE?
Employee
Birthdate
Name
Address
A attribute in a relation is a foreign key if the _____ from one relation is used as an attribute in that relation.
Record
Field
Foreign Key
Primary Key
Every student can take many subject AND every subject can have many students
This statement above referring to...
Many to Many (M:N)
One to One (1:1)
One to Many (1:M)
______________is a relationship set where two entity sets participate in a relationship set.
Unary Relationtiop
Binary Relatioship
Ternary Relationship
Key Relationship
An attribute that holds multiple values for a single entity.
Single Valued Attributes
Multi Valued Attributes
Composite Attributes
Derived Attributes
What is the degree relationship that shows in the picture?
Ternary Relationship
Binary Relationship
Unary Relationship
Primary Relationship
The cardinalities for the picture is
One-to-one (1:1)
One-to-many (1:M)
Many-to-many (M:N)
This represents which type relationship
One-to-One
One-to-Many
Many-to-Many
Subtype
This represents what type relationship
One-to-One
One-to-Many
Many-to-Many
Subtype
An identifying relationship means
The foreign key created by the relationship is part of the primary key of the table
The primary keys of the two tables are the same
The foreign key created by the relationship is not part of the primary key of the table
The relationship is identified by a name attribute
What type relationship exists between these 2 tables
There is no Relationship
One-to-Many Non-Identifying Relationship
Many-to-Many Relationship
One-To-Many Identifying Relationship
What type relationship exists between these 2 tables
No Relationship
One-to-Many Non-Identifying Relationship
Many-to-Many Identifying Relationship
One-to-Many Identifying Relationship
In the individual table, customer_id is a
primary key
non-key column
foreign key
none of the other options
You are designing a medical system database and have doctors and patients. What type relationship would you expect to exist between doctor and patient
One-to-One
One-to-Many
Many-to-Many
No Relationship
When you have a Many-to-Many relationship in a database, you will end up with how many tables to store that relationship?
One
Two
Three
Four
In designing an eCommerce site, what type relationship would you expect exists between a customer and an order?
One-to-One
One-to-Many
Many-to-Many
No Relationship
Given the following diagram, if you want to delete a customer and retain referential integrity what order do you delete from the tables?
order_details
order
customer
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 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.
THIS QUESTIONS NEEDS TO BE FIXED SORRY!!!
CustomerID + Date + Time
CLICK HERE TO GET THE MARK
StaffID + CustomerID
Date + Time
StaffID + CustomerID + Date
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
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" 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.
