Assignment Part I: Lab Questions
Problem 1: Consider the following statement:
SELECT 100 / 20 FROM DUAL
a) What is DUAL?
b) Why is it used?
c) What is displayed?
Problem 2: Describe a business process that requires more than one row in a table to be updated and /or inserted and / or deleted?
Problem 3: What is a database transaction?
Problem 4: Why is a transaction often referred to as a 'logical unit of work'?
Problem 5: Database transactions must be atomic. What does atomic mean?
Problem 6: What could happen if a transaction was not atomic?
Problem 7: What happens to a transaction when all statements in a transaction are successfully completed?
Problem 8: What happens to a transaction when at least one statement in a transaction is not successfully completed?
Problem 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?
Problem 10: How do the database needs of Business Analysts differ from the database needs of the same organization's Operational staff?
Problem 11: List 3 different corporate software applications that would use a database to store data. E.g. A payroll system.
Problem 12: What is a TPS system or application? How is a database involved?
Problem 13: Why are corporate software applications and databases often referred to as heterogeneous?
Problem 14: List 2 reasons why an organisation would want to copy data from their operational TPS database(s) to a Data Warehouse.
Problem 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?
Problem 16: Consider the following statement: SELECT Firstname ||"|| Surname
FROM Employee
WHERE INSTR(Firstname, 'm') > 0
What is displayed?
Assignment Part 2: Lab Questions
Problem 1: What is an Index?
Problem 2: Describe the contents of an Index based on the primary key of this table STUDENT TABLE
|
Row
|
StuNo
|
Firstname
|
Surname
|
Phone
|
Gender
|
|
1
|
7070707
|
Sue
|
Davis
|
0444 111 222
|
F
|
|
2
|
1010101
|
Jim
|
Black
|
0444 221 332
|
M
|
|
3
|
3330000
|
Jenny
|
Hill
|
0444 999 777
|
F
|
|
4
|
2666662
|
Helen
|
Black
|
0411 222 176
|
F
|
|
5
|
2020202
|
Emma
|
Glee
|
0424 111 774
|
F
|
|
6
|
1893220
|
Adam
|
Black
|
0404 333 141
|
M
|
Problem 3: How can an index improve the performance of a database system?
Problem 4: How can index be detrimental to the performance of a database system?
Problem 5: Write the code that would create an index on Surname in the above table
Problem 6: Write the code that would remove the index created in the above question.
Problem 7: Write the code that would create an index on Firstname & Surname in the above table
Problem 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?
Struggling to complete your assignments related to Database Design and Use? Database Design and Use Assignment Help service is one of the best online platforms for all the students to get their academic work completed with ease.
Tags: Database Design and Use Assignment Help, Database Design and Use Homework Help, Database Design and Use Coursework, Database Design and Use Solved Assignments, Database Transaction Assignment Help, Database Transaction Homework Help, Database System Assignment Help, Database System Homework Help