What is a "fat" client? What is a "thin" client?
Answer: Today, a "fat" client refers to a traditional client/server architecture
in which a client machine hosts a fairly large executable, required libraries, and
a middleware product. A "thin" client describes a client machine in which a
Web browser is used to execute an application, either via HTML or by
downloading Java applets as required.
Exercise
As I mentioned, this book uses a sample database for a small college. Start thinking about
what kinds of information you think the college will want to store and retrieve. Ask yourself
what kinds of questions a student, a professor, or an administrator might ask of the database. Jot
down how you might organize this information. You'll return to this information on Day
3, "Logical Database Design."
Answer: A college would certainly want to maintain information about:
- Students
- Departments
- Instructors
- Courses
- Facilities, such as buildings and equipment
Page 609
A student would want to know about which classes are available, where they will be
taught and by whom; the cost of each class.
An instructor would want to know about the classes that they are assigned to teach.
An instructor needs to be able to assign a grade to each student in a class.
An administrator would want to be able to assign each class to an appropriate location
(e.g. seating capacity or lab resources). An administrator might also want to be able to
compare one department to another in various ways—teaching load, instructor seniority, or
average grade given in classes.
Day 2, "Guidelines for Developing an Oracle Application"
Quiz
- True or false? If you use an application-development tool that supports
object-oriented development, there is a greater chance of implementing a
successful application.
Answer: False. An object-oriented development tool might reduce the effort
to develop and maintain the application. However, this doesn't imply that this
will result in a successful implementation; there are many other factors that
will influence the course of a project.
- Name three types of requirements that are needed when designing a system.
Answer: Data requirements, functional requirements, and performance
requirements.
- True or false? You shouldn't begin developing software until you have a
complete set of requirements.
Answer: False. You should wait to develop software until you have a large
number of requirements identified. However, if you wait until all the requirements
are identified, you'll be waiting a long time. Many requirements will emerge
when end-users see each prototype of the application. Also, requirements are not
static; they definitely will change over time.
Exercise
What types of risks might exist during the development of an Oracle application?
What factors could contribute to these risk categories? What steps could be taken to mitigate
these risk categories?
Page 610
Answer: You could categorize the risks during the development of an Oracle application
as follows:
- Schedule risk: The risk that the application fails to meet its development
milestones.
One obvious reason a project often fails to meet its development milestones
is because the milestones were unrealistic to begin with. Often, the pressure
on project management is simply too great; individuals are compelled to
underestimate the time and resources needed to complete a task.
Also, a project may appear to meet early milestones, such as requirements
analysis, but fail in achieving milestones that provide the actual application. What has
often happened on such projects is that the early milestones
appeared to have been met but careful scrutiny would have uncovered problems. Therefore, on a large
project, it is prudent for the customer to engage an independent audit of the project
for each early milestone.
- Budget risk: The risk that the application fails to meet the budget that has
been allocated to it.
Often, if a project fails to meet its schedule, it also fails to meet its budget.
The reason is simple: the largest component of a project's budget is usually for labor.
If the estimate for the labor required to complete a task was low, the
corresponding budget estimate will also be low. One exception to this is a project that is late
in acquiring the people that are needed; in this case, the budget is within its limit
but the schedule isn't met.
- Technical risks: The technical risks on a project could have many sources.
New products or technology may have been selected without careful analysis.
Developers may not have received enough training or have sufficient experience to build
a reliable application. The project designers may have underestimated the
server computing resources (for example, CPU speed,
storage requirements) or networking resources needed for the production environment. Each of these elements
require careful thought. Often, it's worth the time and money to conduct a simple
benchmark to characterize the application performance in a production environment.
Day 3, "Logical Database Design"
Quiz
- True or false? If an attribute that isn't part of the primary key is a foreign key,
it must be mandatory; it cannot allow a null value.
Answer: False. Sometimes a foreign key is mandatory and sometimes it isn't.
It really depends on the business rules that are being modeled.
Page 611
- Will the Student Schedule table handle the situation in which a student needs
to repeat a course? Why or why not?
Answer: Yes. Each class is uniquely identified by class ID. But each offering of
the same course will have a different class ID. For instance, suppose a student
takes Biology 101 during the Fall 1997 semester and fails the course—which is class
ID 109230. In Spring 1997, he repeats the course—which is now class ID
110330. Because the two classes have unique IDs, a row can be inserted into the
Student Schedule for both classes.
- What characteristic of a relational database will prevent a class from being
deleted from the Class table if there are rows in the Student Schedule table that contain
the Class ID to be deleted?
Answer: Referential integrity.
- From an entity-relationship perspective, there is a relationship between the
Class and Instructor tables. Is it identifying or non-identifying?
Answer: Non-identifying. Instructor
ID is not part of the primary key in the Class table.
- True or false? If you create the proper index for a table, its rows will always
be retrieved in the ascending order of the indexed columns.
Answer: False. There is no implied order to the rows in a table. To guarantee
that rows are retrieved in a particular order, you must specify that order in a
SQL statement.
Exercises
- Suppose you want to identify the instructor who is the head of a department.
Can you think of at least two ways of doing this? What are the strengths and
weaknesses of each approach?
Answer: There are two obvious choices. One choice is to add an attribute to
the Department entity that identifies the instructor who is the head of the
department. Of course, this attribute would be a foreign key to the Instructor entity. So,
the Department entity would consist of three attributes:
- Department ID
- Department Name
- Department Head
The other choice is to add an attribute to the Instructor entity that
indicates whether or not an instructor is the head of the department. You could also
name this attribute Department Head and allow it to have the value Y if the
instructor was the head of the department (it could be null if the instructor was not
the
Page 612
department head). The problem with this approach is that this information is
a characteristic of a department; therefore, it makes more sense to incorporate it
in the Department entity.
- The Student Schedule table currently holds both current classes and
previous classes that a student has taken. Propose an alternate design using two
tables—one table contains the current schedule and the second table contains classes that
the student has previously taken. Identify each attribute in the two tables. What are
the advantages of this design?
Answer: The current definition of the Student_Schedule entity is:
- Student ID (foreign key to Student table)
- Class ID (foreign key to Class table)
- Grade
- Date Grade Assigned
Primary key: Student ID, Class ID
As an alternative, the Student_Schedule entity can be broken into two
entities: Current Student Schedule and Student Class History. The student's
current schedule would be stored in Current Student Schedule; it would not include
the student's grade. As soon as a student's grades were assigned, records would
be added to the Student Class History entity. At the beginning of each semester,
the contents of Current Student Schedule would be deleted.
The definition for Current Student Schedule could be this:
- Student ID (foreign key to Student table)
- Class ID (foreign key to Class table)
The definition for Student Class History could be this:
- Student ID (foreign key to Student table)
- Class ID (foreign key to Class table)
- Grade
- Date Grade Assigned
For a large university, this design makes more sense. For example, suppose
a university has twenty thousand enrolled students. Suppose that each student
is enrolled in an average of 3.5 classes per semester. At the beginning of each
semester, the Current Student Schedule entity would therefore contain
20,000¥3.5 = 70,000 records. At the end of each semester, another 70,000 records would
be added to the Student Class History. If all of these records were kept in a
single entity, Student_Schedule, the performance of the system would degrade over
time. For example, in about fourteen years, the
Student_Schedule entity would contain almost a million rows, which could really slow down the process of class enrollment.
Page 613
Day 4, "Implementing Your Logical Model: Physical Database Design"
Quiz
- What is wrong with this statement?
CREATE TABLE new_table (
first_col number,
second_col date
third_col number default sysdate);
Answer: There are two problems with the statement. First, a comma is needed
at the end of the definition for second_col. Second,
third_col is a number and cannot have a default
value—sysdate—that is a date datatype.
- Describe an SQL statement that might result
in the following Oracle error message?
ORA-02266: unique/primary keys in table referenced by
Âenabled foreign keys
Answer: You will see this error if you try to drop a table whose primary key
is referenced by another table's foreign key.
- What is the difference between a column and table check constraint?
Answer: A column CHECK constraint is defined at the column level and
cannot reference any other columns in the table. A table
CHECK constraint is defined at the table level and can reference any of the table's columns.
Exercise
The Instructor table has a column named Position. In the current design of this table,
there is a CHECK constraint on the Position column that restricts the value to
ASSISTANT PROFESSOR, ASSOCIATE PROFESSOR, and FULL
PROFESSOR. Modify the database design so that an
additional table, Instructor_Position, is used to specify legal values for instructor position.
Answer: Create a table named Instructor_Position:
create table Instructor_Position
(Position varchar2(25),
constraint PK_Instructor_Position Primary Key (Position));
Redefine the Instructor table as follows:
create table Instructor
(Instructor_ID varchar2(20),
Department_ID varchar2(20)
constraint NN_Instructor_Dept_ID NOT NULL,
Last_Name varchar2(25)
constraint NN_Instructor_Last_Name NOT NULL,
First_Name varchar2(25),
MI varchar2(1),
Page 614
Position varchar2(25),
Telephone varchar2(10),
Fax varchar2(10),
Email varchar2(100),
constraint PK_Instructor Primary Key (Instructor_ID),
constraint FK_Instructor_Department_ID
Foreign Key (Department_ID) references Department (Department_ID),
constraint FK_Instructor_Position
Foreign Key (Position) references Instructor_Position (Position));
Day 5, "Introduction to Structured Query Language (SQL)"
Quiz
- True or false? You must include a column in the select list if you want to sort
the rows returned by the SELECT statement by that column.
Answer: False. You can specify a column in the
ORDER BY clause that is not in the select list.
- What is wrong with this statement:
select First_Name
from Student
order by Last_Name
where
Last_Name like `%IN%';
Answer: The ORDER BY clause occurs before the
WHERE clause.
- True or false? A column must be indexed before it can be specified in the
ORDER BY clause.
Answer: False. Oracle and other relational databases don't require that a column
be indexed before it can be used to sort the rows retrieved from a table.
Exercise
Using the COURSE table that was discussed in this lesson, construct a
SELECT statement that will return the Department ID, Course ID, and Course Title, sorted by Department ID
and Course ID, for any course whose description contains the phrase
introduc, regardless of capitalization.
Answer: Here is the SELECT statement that will accomplish this. Note that the
lower function is used to convert all course descriptions to lowercase so that a consistent comparison can
be made with introduc.
SQL> select Department_ID, Course_ID, Title
2 from Course
3 where
4 lower(Description) like `%introduc%'
5 order by Department_ID, Course_ID;
Page 615
DEPARTMENT_ID COURS TITLE
-------------------- ----- ----------------------------------------
ANTHRO 101 INTRO TO ANTHROPOLOGY
BIO 101 INTRO TO BIOLOGY
ECON 101 INTRO TO ECONOMICS
ENG 101 INTRO TO STRUCTURES
ENG 102 INTRO TO CIRCUIT THEORY
ENG 103 INTRO TO DYNAMICS
ENGL 101 INTRO TO ENGLISH LIT
ENGL 193 SEMINAR ON THEME ANALYSIS
MATH 101 GENERAL CALCULUS
MATH 189 NUMBER THEORY
PHILO 101 INTRO TO PHILOSOPHY
PSYCH 101 INTRO TO PSYCHOLOGY
12 rows selected.
Day 6, "Using SQL to Modify Data"
Quiz
- Construct an SQL statement that adds a course with the following
characteristics: Department ID = BIO, Course ID = 137, Title = INSECT BEHAVIOR,
Description = In-depth study of insect societies and their behavior patterns, Units = 3,
no additional fees.
Answer: Here is the INSERT statement:
insert into Course
(Department_ID, Course_ID, Title, Description, Units)
values
(`BIO', `137', `INSECT BEHAVIOR',
`In-depth study of insect societies and their behavior patterns', 3);
- Construct an SQL statement that charges $50 in additional fees for all courses
in the philosophy department.
Answer: Here is the UPDATE statement:
update Course
set Additional_Fees = 50
where
Department_ID = `PHILO';
- Construct an SQL statement that eliminates a scheduled class if it is offered by
the English department or is going to be held in Flugle Hall.
Answer: Here is the DELETE statement:
delete from Class
where
Department_ID = `ENG' or
Class_Building = `FLUGLE HALL';
Page 616
Exercise
Several of the instructors at Flugle College have decided to create a new department
called Integrated Studies. As a result, the English, History, and Philosophy departments will
merge to become the Integrated Studies department. The department ID for this new
department will be INTSTD. In the database, create the Integrated Studies department (without
deleting the existing departments). Also, modify the contents of the Instructor table so that
instructors in the English, History, and Philosophy departments are now associated with the
Integrated Studies department.
Answer: You can determine that six instructors are associated with the English, History,
and Philosophy departments with this query:
SQL> select Instructor_ID
2 from Instructor
3 where
4 Department_ID in (`ENGL', `HIST', `PHILO');
INSTRUCTOR_ID
--------------------
G331
L391
E491
T149
D944
B331
6 rows selected.
First, create the new department:
SQL> insert into Department
2 (Department_ID, Department_Name)
3 values
4 (`INTSTD', `Integrated Studies');
1 row created.
Next, update the Instructor table so that any instructor in the English, History,
and Philosophy departments is now associated with Integrated Studies:
SQL> update Instructor
2 set
3 Department_ID = `INTSTD'
4 where
5 Department_ID in (`ENGL', `HIST', `PHILO');
6 rows updated.
If you query the Instructor table, you will see that the same six instructors now belong to
the Integrated Studies department:
SQL> select Instructor_ID
2 from Instructor
3 where
4 Department_ID = `INTSTD';
Page 617
INSTRUCTOR_ID
--------------------
G331
L391
E491
T149
D944
B331
6 rows selected.
Finally, do a ROLLBACK so that the changes aren't permanent.
SQL> rollback;
Rollback complete.
Day 7, "Taking Advantage of SQL Built-In Functions"
Quiz
- Construct an SQL statement that will retrieve each row from the Instructor table
as shown in this example:
Professor Parker
Answer: The SELECT statement is
SQL> select initcap(position || ` ` || last_name)
2 from instructor;
- Construct an SQL statement that will retrieve the instructor whose last
name appears first in an alphabetic order.
Answer: The SELECT statement is
SQL> select min(Last_Name)
2 from Instructor;
MIN(LAST_NAME)
------------------------
ANGELO
Exercise
Create a table, named NEW_CLASS, using a CREATE TABLE <xyz> AS
... statement based on a join of the Class, Schedule_Type, and Schedule_Type_Details tables that contains
the following columns:
- Class_ID
- Department_ID
- Course_ID
Page 618
- Day of the week spelled out
- Time spelled out (for example, 11:00 a.m.)
Answer:
SQL> create table New_Class as
2 select Class_ID, Department_ID, Course_ID,
3 decode(day,1,'Sunday',2,'Monday',3,'Tuesday',4,'Wednesday',
4 5,'Thursday',6,'Friday',7,'Saturday') Day_of_Week,
5 to_char(Starting_Time,'HH:MI PM') Starting_Time
6 from Class, Schedule_Type_Details
7 where
8 Class.Schedule_ID = Schedule_Type_Details.Schedule_ID;
Table created.
SQL> select Class_ID, Department_ID, Course_ID, Day_of_Week, Starting_Ti
2 from New_Class
3 order by Class_ID;
CLASS_ID DEPARTMENT_ID COURS DAY_OF_WE STARTING_TIME
-------------------- -------------------- ----- --------- --------------
103400 PSYCH 183 Monday 01:00 PM
103400 PSYCH 183 Wednesday 01:00 PM
103400 PSYCH 183 Friday 01:00 PM
103600 MATH 50 Monday 03:00 PM
103600 MATH 50 Friday 03:00 PM
103600 MATH 50 Wednesday 03:00 PM
104200 PHILO 198 Friday 09:00 AM
104500 HIST 184 Monday 03:00 PM
104500 HIST 184 Wednesday 03:00 PM
104500 HIST 184 Friday 03:00 PM
108300 ENGL 101 Monday 10:00 AM
108300 ENGL 101 Wednesday 10:00 AM
108300 ENGL 101 Friday 10:00 AM
108400 HIST 115 Tuesday 09:00 AM
108400 HIST 115 Thursday 09:00 AM
108600 PSYCH 183 Monday 10:00 AM
108600 PSYCH 183 Wednesday 10:00 AM
108600 PSYCH 183 Friday 10:00 AM
109100 BIO 101 Monday 03:00 PM
109100 BIO 101 Wednesday 03:00 PM
109100 BIO 101 Friday 03:00 PM
110300 ENG 199 Friday 09:00 AM
120200 ECON 199 Tuesday 09:00 AM
120200 ECON 199 Thursday 09:00 AM
24 rows selected.
Page 619
Day 8, "More Sophisticated Queries
with SQL"
Quiz
- Construct an SQL statement that retrieves the last name of an instructor who
is teaching a course with additional fees greater than $50.
Answer: Here is a three-table join that returns the information:
SQL> select Last_Name
2 from Class CL, Instructor I, Course CO
3 where
4 CL.Instructor_ID = I.Instructor_ID and
5 CL.Department_ID = CO.Department_ID and
6 CL.Course_ID = CO.Course_ID and
7 Additional_Fees > 50;
LAST_NAME
-------------------------
WEISS
- Construct an SQL statement that retrieves a list of cities in which students
reside and the number of students that reside in each city.
Answer: Here is the SELECT statement that produces the desired results:
SQL> select City, count(*)
2 from Student
3 group by City;
CITY COUNT(*)
-------------------------- --------
DOVER 14
SPRINGFIELD 17
- Create a view that lists each class—its Class_ID, Department_ID,
and Course_ID—for those classes that meet on Mondays.
Answer: Here is the CREATE VIEW statement that performs the requested
action. Before you actually try to create the view, you should experiment until you
have defined the SELECT statement on which the view will be based:
SQL> select Class_ID, Department_ID, Course_ID
2 from Class CL, Schedule_Type ST, Schedule_Type_Details STD
3 where
4 CL.Schedule_ID = ST.Schedule_ID and
5 ST.Schedule_ID = STD.Schedule_ID and
6 STD.Day = 2;
Page 620
CLASS_ID DEPARTMENT_ID COURS
-------------------- -------------------- -----
104500 HIST 184
109100 BIO 101
108300 ENGL 101
108600 PSYCH 183
103400 PSYCH 183
103600 MATH 50
6 rows selected.
Then, you can create the view:
SQL> create view Classes_on_Monday as
2 select Class_ID, Department_ID, Course_ID
3 from Class CL, Schedule_Type ST, Schedule_Type_Details STD
4 where
5 CL.Schedule_ID = ST.Schedule_ID and
6 ST.Schedule_ID = STD.Schedule_ID and
7 STD.Day = 2;
View created.
SQL> select Class_ID, Department_ID, Course_ID
2 from Classes_on_Monday;
CLASS_ID DEPARTMENT_ID COURS
-------------------- -------------------- -----
104500 HIST 184
109100 BIO 101
108300 ENGL 101
108600 PSYCH 183
103400 PSYCH 183
103600 MATH 50
6 rows selected.
Exercise
The number of instructors is 18. The number of classes being offered is 10. However,
the number of distinct Instructor_IDs in the Class table is 9. Using these tables and SQL,
provide a complete explanation.
Answer: There are 10 classes being offered as you can see:
SQL> select count(*) from Class;
COUNT(*)
---------
10
There are nine instructors for these classes:
SQL> select distinct Instructor_ID from Class;
Page 621
INSTRUCTOR_ID
--------------------
D944
E405
E491
G331
J505
R983
S131
T149
W490
9 rows selected.
Let's see if any of the instructors are teaching more than one class:
SQL> select Instructor_ID from Class
2 having count(*) > 1
3 group by Instructor_ID;
INSTRUCTOR_ID
--------------------
J505
Instructor J505, who happens to be Jerrold Jason, is teaching more than one class:
SQL> select Class_ID from Class
2 where Instructor_ID = `J505';
CLASS_ID
--------------------
108600
103400
Day 9, "Programming an Oracle Database with PL/SQL"
Quiz
- Name the three sections of a PL/SQL subprogram.
Answer: The three sections of a PL/SQL subprogram are the declaration,
executable, and exception sections.
- True or false? A PL/SQL variable that stores a column value must have the
same name as the column.
Answer: False. A PL/SQL variable that stores a column value may have any
legal name.
Page 622
- Why is it a good practice to use
%TYPE when declaring variables?
Answer: It is a good practice to use
%TYPE when declaring a variable because it reduces the effort needed to maintain PL/SQL code. If you declare a
variable without using %TYPE and the corresponding column's definition changes, you
will have to modify the variable declaration. If you use
%TYPE, you won't have to modify the variable declaration.
Exercise
Construct an anonymous block that declares a procedure that will set the additional fees
for a course to $50 if there are no current additional fees. The procedure should have only
one argument—Department_ID—and should perform this action only for the value
of Department_ID supplied to the procedure.
Answer: Let's interpret "no current additional fees" as meaning that the additional fees
are either zero or null. Let's look at a possible solution.
As you can see in the following query, the math department has three courses
without additional fees: Course IDs 50, 101, and 189.
SQL> select Course_ID, Additional_Fees
2 from Course
3 where
4 Department_ID = `MATH';
COURS ADDITIONAL_FEES
----- ---------------
50 0
101 0
189 0
51 10
Here is the anonymous block that contains a procedure (see line 2) that is invoked on
line 13. As specified, the procedure contains a single argument: Department_ID.
SQL> declare
2
2 procedure Set_Default_Course_Fees (arg_Department_ID varchar2) is
3
3 begin
4
4 update Course
5 set Additional_Fees = 50
6 where
7 Department_ID = arg_Department_ID and
8 Additional_Fees is null or
9 Additional_Fees = 0;
10
10 end;
11
11 -- Main block.
12
Page 623
12 begin
13
13 Set_Default_Course_Fees (`MATH');
14
14 end;
15 /
PL/SQL procedure successfully completed.
You can see from the following query that the procedure has set the additional fees to
$50 for the three courses:
SQL> select Course_ID, Additional_Fees
2 from Course
3 where
4 Department_ID = `MATH';
COURS ADDITIONAL_FEES
----- ---------------
50 50
101 50
189 50
51 10
Day 10, "Program Development
with PL/SQL"
Quiz
- True or false? A stored procedure can call a stored function but a stored
function cannot call a stored procedure.
Answer: False. A stored procedure can call other procedures or functions. A
stored function also can call other procedures and functions.
- Name three reasons for using stored procedures, functions, and packages in
an application.
Answer: Efficiency, reusability, and portability.
- What tools can be used to develop PL/SQL subprograms?
Answer: SQL*Plus, SQL Worksheet, Procedure Builder, and other
third-party tools. Procedure Builder makes the task of PL/SQL development much easier
than using SQL*Plus or SQL Worksheet.
- If "x > 32" is assigned to a PL/SQL variable, what is the datatype of the variable?
Answer: Boolean. Only a Boolean expression can be assigned to a Boolean variable.
Page 624
Exercises
- Create an anonymous PL/SQL block that will call the
Assign_Grade procedure in the Flugle package and assign a B to Anna Anastatia for Biology 101.
Answer: If you query the Student and Class tables, you'll find the following:
- Anna Anastatia's student ID is 10231324.
- The class ID for Biology 101 is 109100.
As you can see in the following query, Anna has not yet received a grade
for Biology 101:
SQL> select * from Student_Schedule;
STUDENT_ID CLASS_ID GR DATE_GRAD
-------------------- -------------------- -- ---------
10231311 104200 B 02-JUN-97
10231311 104500 B- 03-JUN-97
10231324 109100
10231311 109100
Here is an anonymous block that will invoke the Assign_Grade procedure in
the Flugle package:
SQL> set serveroutput on
SQL>
SQL> declare
2
2 Status number;
3
3 -- Main block.
4
4 begin
5
5 dbms_output.enable;
6
6 Flugle.Assign_Grade(`10231324','109100','B',status);
7
7 dbms_output.put_line(`status: ` || to_char(status));
8
8 end;
9 /
status: 0
PL/SQL procedure successfully completed.
Once again, if you inspect the Student_Schedule table, you'll see that Anna
has now been assigned a B for the class:
SQL> select * from Student_Schedule;
STUDENT_ID CLASS_ID GR DATE_GRAD
-------------------- -------------------- -- ---------
10231311 104200 B 02-JUN-97
10231311 104500 B- 03-JUN-97
10231324 109100 B 04-JUN-97
10231311 109100
Page 625
- A student, Jackson Smythe, has created a stored function named
Change_My_Grade with two arguments: Student ID and Class ID. The function changes the grade
for any specified student and class to an A+. Write out the statements that will
create this function.
Here is the function that will perform this dastardly deed:
SQL> create or replace function
2 change_My_Grade(arg_student_ID IN varchar2,
3 arg_class_ID IN varchar2)
4 return number is
5
5 counter number;
6 status number;
7
7 normal CONSTANT number := 0;
8 unsuccessful CONSTANT number := -1;
9
9 not_registered exception;
10
10 begin
11
11 status := normal;
12
12 -- Determine if the student is registered for this class.
13
13 select count(*) into counter
14 from student_schedule
15 where
16 student_id = arg_student_id and
17 class_id = arg_class_id;
18
18 if counter = 0 then
19 --
20 -- The student is not taking this class.
21 --
22 raise not_registered;
23 status := unsuccessful;
24 else
25 --
26 -- Assign the grade for this class.
27 --
28 update student_schedule
29 set
30 grade = `A+',
31 date_grade_assigned = sysdate
32 where
33 student_id = arg_student_id and
34 class_id = arg_class_id;
35 end if;
36
36 return status;
37
37 exception
38 when not_registered then
39 raise_application_error (-21003, `Student not registered for
Âclass');
Page 626
40 when others then
41 null;
42
42 end;
43 /
Function created.
To test the function, create an anonymous block that will
invoke Change_My_Grade for Paul Fernandez's grade in History 184.
SQL> declare
2
2 Status number;
3
3 -- Main block.
4
4 begin
5
5 dbms_output.enable;
6
6 status := Change_My_Grade(`10231311','104500');
7
7 dbms_output.put_line(`status: ` || to_char(status));
8
8 end;
9 /
status: 0
PL/SQL procedure successfully completed.
As you can see from the following query, the function works as advertised:
SQL> select * from Student_Schedule;
STUDENT_ID CLASS_ID GR DATE_GRAD
-------------------- -------------------- -- ---------
10231311 104200 B 02-JUN-97
10231311 104500 A+ 04-JUN-97
10231324 109100
10231311 109100
Day 11, "More Programming Techniques with PL/SQL"
Quiz
- How are SQLCODE and SQLERRM used in PL/SQL subprograms?
Answer: SQLCODE returns the Oracle error that resulted from the last SQL
operation. An error code of 0 indicates the normal completion of the operation.
SQLERRM returns the text of the Oracle error message that corresponds to the value
in SQLCODE.
Page 627
- True or false? A PL/SQL subprogram may have multiple cursors open at the
same time.
Answer: True. In a PL/SQL subprogram, there is no restriction on having
more than one cursor open at the same time.
- What are the benefits of using database triggers?
Answer: Triggers serve several purposes. A trigger can perform complex
validation when a row is added or modified. A trigger can modify a column's value before it
is stored in the table. A trigger in one table can be used to modify the contents of
a different table.
- What kind of database trigger would you use if you wanted to modify the value
to be stored in a column when a new row is added to a table?
Answer: A before-insert trigger which will fire for each row.
Exercises
- Create a stored function,
Teaching_Load, that has a single argument—a Department_ID.
Teaching_Load should return the average number of courses
that instructors in the specified department are currently teaching.
First, you need to create the stored function:
SQL> create or replace function Teaching_Load(arg_Department_ID
IN varchar2)
2
2 return number is
3
3 number_of_instructors number;
4 number_of_classes number;
5 avg_teaching_load number;
6
6 begin
7
7 -- Get the number of instructors for this department.
8
8 select count(*) into number_of_instructors
9 from Instructor
10 where
11 Department_ID = arg_Department_ID;
12
12
12 -- Get the number of classes for this department.
13
13 select count(*) into number_of_classes
14 from Class
15 where
16 Department_ID = arg_Department_ID;
17
17 avg_teaching_load := number_of_classes / number_of_instructors;
18
18 return avg_teaching_load;
19
19 exception
Page 628
20 when others then
21 null;
22
22 end;
23 /
Function created.
To test the function, you can invoke it in a
SELECT statement that references the DUAL table. As you can see, the instructors in the Biology department are
teaching an average of 0.5 classes:
SQL> select Teaching_Load(`BIO') from dual;
TEACHING_LOAD(`BIO')
--------------------
.5
- Create a stored procedure,
Suitable_Locations, that has a single argument—seating capacity.
Suitable_Locations should print out the first three buildings
and rooms whose seating capacity exceeds the specified seating capacity.
Here is a stored procedure that will satisfy the requirement:
SQL> create or replace procedure Suitable_Locations
2 (arg_Seating_Capacity IN number) is
3
3 cursor Get_Locations is
4 select class_building, class_room, seating_capacity
5 from Class_Location
6 where
7 Seating_Capacity >= arg_Seating_Capacity
8 order by Seating_Capacity desc;
9
9 begin
10
10 --
11
11 dbms_output.enable;
12
12 for Get_Locations_Rec in Get_Locations loop
13
13 exit when Get_Locations%rowcount > 3;
14 dbms_output.put_line(`Building: ` ||
ÂGet_Locations_Rec.Class_Building ||
15 ` Room: ` || Get_Locations_Rec.Class_Room || ` Capacity: ` ||
16 to_char(Get_Locations_Rec.Seating_Capacity));
17
17 end loop;
18
18 exception
19 when others then
20 null;
21
21 end;
22 /
Procedure created.
Page 629
To test the stored procedure, you can use an anonymous block that invokes it:
SQL> declare
2
2 Status number;
3
3 -- Main block.
4
4 begin
5
5 dbms_output.enable;
6
6 Suitable_Locations (30);
7
7 end;
8 /
Building: FLUGLE HALL Room: 100 Capacity: 200
Building: FLUGLE HALL Room: 150 Capacity: 120
Building: NARROW HALL Room: 200 Capacity: 90
PL/SQL procedure successfully completed.
- Write a before-delete trigger on the Instructor table that will raise an exception
if an instructor is scheduled to teach a class.
Here is the script for creating the trigger. In line 7, a query is used to
determine how many classes the instructor is teaching; that value is stored in
counter. In line 11, counter is evaluated; if it is greater than zero, the trigger raises an
application error, preventing the deletion of the instructor:
SQL> create or replace trigger Instructor_BD
2 before delete on Instructor
3 for each row
4
4 declare
5
5 counter number;
6
6 begin
7
7 select count(*) into counter
8 from Class
9 where
10 Instructor_ID = :old.Instructor_ID;
11
11 if counter > 0 then
12 raise_application_error (-20800,
13 `Instructor is scheduled to teach a
Âclass');
14 end if;
15
15
15 end;
16 /
Trigger created.
Page 630
To test the trigger, you can try deleting an instructor who is currently teaching
a class. As you can see, the trigger raises the application
error—20800—preventing the DELETE statement from successful completion.
SQL> delete from Instructor
2 where
3 Instructor_ID = `E405';
delete from Instructor
*
ERROR at line 1:
ORA-20800: Instructor is scheduled to teach a class
ORA-06512: at line 9
ORA-04088: error during execution of trigger `FLUGLE.INSTRUCTOR_BD'
However, if you try to delete an instructor who isn't teaching a class, the
trigger will not raise the application error:
SQL> delete from Instructor
2 where
3 Instructor_ID = `P331';
1 row deleted.
Day 12, "Developer/2000: Introduction to Oracle Forms"
Quiz
- True or false? Oracle Forms Runtime will only execute an
.fmb file.
Answer: False. Oracle Forms Runtime only executes an
.fmx file, not an .fmb file.
- Name the three types of modules that the Forms Designer can create.
Answer: The three module types are forms, menus, and libraries.
- True or false? The Forms Designer can only save a form to an Oracle database if
it contains a base table block.
Answer: False. The Forms Designer can save a form to either the database or
the file system, regardless of the form's contents.
- True or false? You can create a block based on the contents of a table owned
by another Oracle user.
Answer: True. You can create a block based on a table owned by another
Oracle user as long as you have been granted the appropriate privileges for that table.
Page 631
Exercise
Create a form for the Instructor table. Specify an
ORDER BY clause so that the records retrieved by a query are ordered by department ID, instructor last name, first name, and middle
initial. Test the form.
Answer: Please refer to the Instructor.fmb file in the Day 13 folder on the CD-ROM.
Day 13, "Developer/2000: Developing a User Interface with Oracle Forms"
Quiz
- True or false? A master block cannot be based on a view; it must be a table.
Answer: False. A master block can be based on a view.
- True or false? You can modify display characteristics of an item with the
Layout Editor or by modifying the item's properties.
Answer: True. You can modify the display characteristics of an item—for
instance, its position or font—by selecting the item with the Object Navigator and
right-clicking Properties.
- How can the tab order of a group of items be changed?
Answer: You can change the tab order of a group of items by changing their
order with the Object Navigator.
Exercises
- Create a master-detail window for entering Schedule_Type
and Schedule_Type_Details.
Answer: Please refer to the
Schedule_Type.fmb file in the Day 14 folder on the
CD-ROM.
- In the Department_Class form, create an LOV for Schedule_ID that will
validate user input.
Answer: Please refer to the
Department_Class.fmb file in the Day 14 folder on
the CD-ROM.
Page 632
Day 14, "Developer/2000: Application Development with Oracle Forms"
Quiz
- True or false? An item-level trigger in one block cannot refer to items in a
different block.
Answer: False. There are no restrictions on the ability of an item-level trigger
to reference items in a different block.
- How are block items referenced in a trigger?
Answer: To reference a block item in a trigger, use the nomenclature
:block-name.item-name.
- If you create an item-level trigger to validate user input, what exception should
be raised if the user enters an invalid value?
Answer: FORM_TRIGGER_FAILURE.
Exercises
- Modify the Instructor form so that it has the same width and height as
the MDI_Frame form.
Answer: Please refer to the
Instructor.fmb file in the Day 15 folder on the
CD-ROM.
- Create a menu item for the Browse menu that will invoke a form that
displays Instructors in a tabular format, ordered by Instructor last name, first name,
and middle initial.
Answer: Please refer to the
menu15.mmb file in the Day 15 folder on the CD-ROM.
Day 15, "Developer/2000: Developing Reports with Oracle Reports"
Quiz
- True or false? You can store your RDF files in a source code control system such
as PVCS.
Answer: False. An RDF file is a binary file. Instead of trying to maintain
version control on your RDF files, you should generate REX files, which are text files
that can be kept under version control.
- Name the four elements of a report layout.
Answer: Header, footer, body, and margin. Almost every report has a body layout.
Page 633
- True or false? A report cannot have more that two queries linked together.
Answer: False. You can have multiple queries that are related. For instance,
you could have a report based on three queries: a query of departments, a query
of instructors in each department, and a query of the classes that each instructor
is teaching.
Exercise
Create a master-detail report that lists each classroom and the classes that are scheduled
for that room, regardless of meeting time (for example, all of the classes that are scheduled to
use a particular room).
Answer: Please refer to the Classroom.fmb file in the Day 16 folder on the CD-ROM.
Day 16, "Developer/2000: Using Oracle Graphics and Procedure Builder"
Quiz
- What are the two file types used by Oracle Graphics?
Answer: An .ogd file—which stands for Oracle Graphics Designer
file—contains the definition of a chart or display. Using the Graphics Designer, you generate
an .ogr file (Oracle Graphics Runtime) from an
.ogd file.
- Name three chart types that can be specified with the Graphics Designer.
Answer: Line chart, bar chart, and pie chart.
- True or false? You can use the Stored Program Unit Editor to modify a
package specification but not a package body.
Answer: False. The Stored Program Unit Editor can be used to modify either
a package specification or package body.
Exercises
- Create a bar chart that displays the number of courses offered by each
department at Flugle College.
Answer: Please refer to the
Courses_by_Department.ogd file in the Day 17 folder
on the CD-ROM.
- Modify the trigger on the Student_Schedule table so that it only records changes
to the Student_Schedule table if the value of the Grade column is A, B, or C.
Answer: Please refer to the
Student_Schedule.sql file in the Day 17 folder on
the CD-ROM.
Page 634
Day 17, "Introduction to Oracle Power Objects"
Quiz
- True or false? A Power Objects application connects to an Oracle database
through an application transaction object.
Answer: False. A Power Objects application connects to an Oracle database
by specifying a database session object.
- Name three types of objects that you can place on a form.
Answer: A text field, a radio button, and a repeater.
- Give an example of a container object.
Answer: An example of a container object is a repeater. A repeater may contain
one or more text fields.
Exercises
- Build a form for displaying and modifying information about each
instructor. Display the number of classes, if any, that the instructor is scheduled to teach.
- Modify the Student form to display the GPA of the current student.
Answer: Please refer to the
Flugle.poa file in the Day 18 folder on the CD-ROM.
Day 18, "Developing an Application with Oracle Power Objects"
Quiz
- True or false? A report's
RecordSource property must reference a single table
or view; it cannot join two or more tables.
Answer: True. The RecordSource property is limited to a single table or view.
To construct a master-detail report, you must either create a view or add a subform
to a report which contains a detail report that can be linked to the master report.
- True or false? Power Objects supports a maximum of three levels of inheritance.
In other words, you can create class A, add an instance of class A on class B, and
add an instance of class B on class C.
Answer: False. Power Objects will support many levels of inheritance.
However, you should be judicious about how many levels you construct because too
many levels of inheritance can have an adverse effect on performance.
Page 635
- If you can validate user input in a Power Objects application with the
Validate method, why would you need to use database constraints and triggers to
enforce data integrity?
Answer: You'll always want to create database constraints and triggers to
enforce data integrity, even if the same business rules are enforced in a Power
Objects application. The reason is that database constraints and triggers represent the
first line of defense for data integrity. Because a single database could support
many different applications, each constructed with a different tool or by a
different developer, you can't assume that all applications implement the full set of
business rules.
Exercises
- Power Objects includes a number of predefined classes. If you examine the
contents of the Classes folder, you'll see a class named clsMeter. Add an instance of
it to MyForm. See if you can figure out how to link the meter to the X position
of the mouse on the form.
- Use Power Objects to build a report which contains a list of the instructors
that belong to each department. Add a lookup field to each instructor line
which indicates if the instructor is not teaching any classes for the semester.
Answer: Please refer to the
Flugle.poa file in the Day 19 folder on the CD-ROM.
Day 19, "An Overview of Oracle Database Security and Tuning"
Quiz
- True or false? A database role can be granted to both users and other database roles.
Answer: True. You may want to create certain generic roles that have some
basic privileges and grant those roles to more specific roles.
- True or false? Adding an index to a table always improves the performance of
an application.
Answer: False. A non-unique index should only be added if the indexed
column will be frequently used in queries of the table. Also, if the contents of the table
are frequently changed, the index could degrade overall performance.
- True or false? Any Oracle user can create a public synonym as long as the
synonym points to a table that is owned by that user.
Answer: False. The creation of a public synonym requires either the
DBA role or the CREATE PUBLIC SYNONYM system privilege.
Page 636
Exercise
You can answer this question descriptively, without using SQL statements: If you were
the DBA at Flugle College, what database roles would you create? What privileges would
you grant to those roles?
Answer: You might want to create these roles:
- A role for student
- A role for instructor
- A role for department chairperson
- A role for college administrator
Object privileges are commonly referred to as Create (C), Read (R), Update (U), Delete
(D), or CRUD. A matrix that defines all these privileges for each role is called a CRUD matrix.
Privileges for student role:
Class: R
Class_Location: R
Course: R
Department: R
Instructor: R
Schedule_Type: R
Schedule_Type_Details: R
Student: R (only for self)
Student_Schedule: CRUD (only for self)
Privileges for instructor role:
Class: R
Class_Location: R
Course: R
Department: R
Instructor: RU (only for self)
Schedule_Type: R
Schedule_Type_Details: R
Student: R (only for self)
Student_Schedule: RU
Privileges for department chairperson role:
Class: CRUD
Class_Location: R
Course: CRUD
Page 637
Department: R
Instructor: CRUD
Schedule_Type: R
Schedule_Type_Details: R
Student: R
Student_Schedule: RU
Privileges for college adminstrator role:
Class: CRUD
Class_Location: CRUD
Course: CRUD
Department: CRUD
Instructor: CRUD
Schedule_Type: CRUD
Schedule_Type_Details: CRUD
Student: CRUD
Day 20, "Using Oracle Database Designer and Oracle Designer/2000
in Application Development"
Quiz
- True or false? One drawback to Database Designer is that it supports only
Oracle databases.
Answer: False. Database Designer will allow you to generate an RDB, DB2,
or SQL Server database from your database design.
- Name three advantages of using Database Designer instead of a text editor to
create the DDL statements that implement a database design.
Answer: Some of the advantages of using Database Designer include the following:
- Far fewer errors due to "typos," such as misspelling a column name.
- Capability to capture table and column definitions in the same file.
- Capability to pass information to Designer/2000, such as display formats
for columns.
- A diagram is more easily understood than a set of SQL statements and can
be presented to other project members for review/feedback.
Page 638
Chapter 21, "Oracle: The Next Generation"
Quiz
- What is the role of the htp and htf PL/SQL packages?
Answer: The htp and htf PL/SQL packages are used to dynamically
generate HTML source code.
- True or false? The Network Computing Architecture is available only for
Sun Solaris and Microsoft Windows NT Server.
Answer: False. The Network Computing Architecture is a strategy that
will eventually support a wide range of platforms.
Previous | Table of Contents |