Reference no: EM132424197

HS2021- Database Design and Use - Holmes Institute

Part 1

Lab Questions

Question 1. Consider the following statement:
SELECT 100 / 20 FROM DUAL
a) What is DUAL?
b) Why is it used?
c) What is displayed?

Question 2. Describe a business process that requires more than one row in a table to be updated and /or inserted and / or deleted?

Question 3. What is a database transaction?

Question 4. Why is a transaction often referred to as a 'logical unit of work'?

Question 5. Database transactions must be atomic. What does atomic mean?

Question 6. What could happen if a transaction was not atomic?

Question 7. What happens to a transaction when all statements in a transaction are successfully completed?

Question 8. What happens to a transaction when at least one statement in a transaction is not successfully completed?

Question 9. Assume that this statement has been executed:
• INSERT INTO Customer (Custld, Date, Balance) VALUES (*CV, '1-June', 0);
Sometime later, the following two statements are executed:
• INSERT INTO banktrans (TransType, Date, Custld, Amt) VALUES ('Deposit',1-Junes,t1', 1000);
• UPDATE Customer
SET Balance = Balance
♦ 1000 WHERE Custld
= 'C1';
a) Why is it important that both of the above statements are executed?
b) What are the repercussions if the first statement is executed but the second statement is not?
Question 10. How do the database needs of Business Analysts differ from the database needs of the same organisation's Operational staff?

Question 11. List 3 different corporate software applications that would use a database to store data. E.g. A payroll system.

Question 12. What is a TPS system or application? How is a database involved?

Question 13. Why are corporate software applications and databases often referred to as heterogeneous?

Question 14. List 2 reasons why an organisation would want to copy data from their operational TPS database(s) to a Data Warehouse.

Question 15. Assume that the table EMP has the following data
EmpNo Surname Firstname
1 Smith Fred
2 Jay Emma
2 Phelps Mark
Consider the following statement: SELECT Firstname ||"|| Surname
FROM Employee
What is displayed?

Question 16. Consider the following statement: SELECT Firstname ||"|| Surname
FROM Employee
WHERE INSTR(Firstname, 'm') > 0
What is displayed?

Part 2

Lab Questions

Indexes:

Question 1. What is an Index?

Question 2. Describe the contents of an Index based on the primary key of this table STUDENT TABLE
Stu No Number Primary Key
FirstName Varchar(30)
, SurName Vardiar(30)
, Phone Varchar(12)
, Gender Varchar(1));

Question 3. How can an index improve the performance of a database system?

Question  4. How can index be detrimental to the performance of a database system?

Question  5. Write the code that would create an index on Surname in the above table

Question 6. Write the code that would remove the index created in the above question.

Question 7. Write the code that would create an index on Firstname & Surname in the above table

Question 8. Consider the data stored in this table. STUDENT(Stuld, Firstname, Surname, Gender, TutGrp) Note: (The Rowld is not a column in the table. It is merely used as a way of referencing each row)

a) Which of the following insert

statements would cause a duplicate primary key constraint error?
INSERT INTO student
VALUES (1, 'Fred, 'Smith', 'M', 4);
INSERT INTO student
VALUES (2856 'Emma', 'Jones', 'F', 2);
INSERT INTO student
VALUES (2912 'Davis', 'Sue, 'F', 1);

b) How many rows do you need to look at to list the firstname and tutgrp of all the students whose surname is Cortez?

c) How many rows had the surname of Cortez?

Note: Need 1st and 2nd question only in Part 1 and 2.