Q1: Does our C program have to create the tables and enter the values in them
or is that done manually ? In the sample input file, it says that the
program only deals with authenticating and so on ... So I would like to
know if we have to create tables in our program and enter values in the
table.
A1: Yes, your program should create the tables automatically from the
input file provided. Your program should take as command line input a
file name. This file will contain the database, followed by the input
commands, as shown in the ProjectDB.txt file.
Q2: Do we need to create tables or are they provide for us?
A2: See A1 above.
Q3: Should we use the code in CH. 12 or write our own code?
A3: You may use the code but you'll have to convert it to C.
Q4: Is the database provide to us? And if it is, how do we accesses it?
A4: As I said you have to create your own tables. You will connect to
the main oracle server exactly as in studio 2. You then issue create
table statements, and then populate the database by using insert
statements in your code. Finally you execute the input commands.
Q5: What exactly are we to implement the OLAP queries?
A5: You implement the OLAP queries as dynamic SQL. Actually calling
these OLAP is a misnomer. The requirement documentation says that you
must support select statements.
Q6: How do we get our database schema into the Oracle system, and how
should we go about populating our database for testing?
A6: see A4 above.
Q7: How is the date supposed to be represented in our schema and
ER-diagram? It seems to have no logical connection to any other table or
entity, but we're not sure if it's legal to have an entity containing
Semester and Year that's not connected to any other entity
A7: A separate table with the current semester and year will work.
Q8: In the input file, for the COURSEINFO section, there are many
parameters that have nothing to do with Course. For example,
InstructorId, CurrentClassId, ClassTime, all relate to the Class
information, not Course. For the DISPLAY sub-function of COURSEINFO, are
we supposed to just print the first ClassTime (for example) found with the
given CrsCode, or are we printing a list of all classes ever offered with
that CrsCode?
A8: This is teaching information. Your actual tables may not
correspond to the information I give. You have to take all this
information and populate your DB according to your schema. This means
that you may have to pull information from different parts to populate
your tables.
The CrsCode for DISPLAY only applies a current course or one that will
be offered next semester. If a course is offered in both semesters
(current and next) then both rows should be displayed.
Q9: For the REGISTER function, how are we to know what section the student
is registering for? This is needed both for a field in the transcript
table, and to know which class's enrollment field to update.
A9: There are no sections anymore, so this problem goes away.
Q10: when we connect to the oracle server in our main function our exec sql
statements do not work in other functions but only in that main function.
how can we solve this problem as it would be impractical to write everything
in the main function.
A10: just move the EXEC SQL BEGIN/END DECLARE section to the global
space instead of main(), i.e., in a header file or beginning of the
main file. Also the EXEC SQL DECLARE DB_NAME DATABASE should be global
in scope.
Q11: can we use c++ with embedded sql. we tried to write some code in
c++ but it does not compile properly. is there a way to compile c++ or
should we just do it in c?
A11: On windows you can use C++ by adding
code=cpp to the custom build step for the precompiler (i.e, set this
in the project settings). Click on program.pc (or whatever name you
have). Click on the project tab and choose settings. Then under custom
build add code=cpp. Add code=cpp right after the C:\PATH\Proc (note
that PATH may be different for different machines).
A makefile forunix is now available. See the directory makedir and copy all files in the dir. Replace
your target in place of cppdemo1 and then type "make cppsamples".
Q12: I have a question about the term project. When I tried to create
assertions to set up integrity constraints in the Oracle database
system, I always got this error message: invalid CREATE command. So I
am just wondering whether Oracle support Assertion or not. And I asked
one of class TAs about the same question, he said that maybe what I
guessed is correct since he couldn't find Assertion in the Oracle
document. So could you let me know the answer for that? Then our team
can find a correct way to deal with those constraints. Thanks a lot
for your time.
A12: It turns out that oracle does not support create assertion
statements. You should use triggers instead. Chapter 9 in the book
talks about triggers. You can also look at the Oracle DBMS page
to find out some more information about triggers. Click on "The
Oracle PL/SQL language".
Q13: I'm not quite clear on what information the DISPLAY portion of
COURSEINFO is supposed to give. None of the properties specified make
mention of section number at all, so for the properties that are
likely stored in the Class table (enrolled, max-enroll, classtime,
instructorId, CurrentClassId, NextSemClassId), what are we supposed to
print out? The first one of each found in the table? Or a complete
history of every class ever offered with the specified Course Code?
Or something else entirely?
Q13: See A9 above. Sections have been removed.
Q14: In your sample input for Authentication, you used last name then
first name instead of user's id. I'm sure the book says to us an ID
number. Please tell me which you will use when testing our project.
A14: This has been fixed. The input will be ID and password.
Q15: On careful reading of chapter 3 of the text, I find no mention at
all of classes having sections. Would we be correct in assuming there
is no treatment of sections in this project?
A15: Yes, sections have been dropped.
Q16: Are we correct in assuming all courses meet for 2 hours? (So
registering for classes at 9 and 10 should not be allowed.)
A16: See the new ProjectDB.TXT file for the domain of class
times. Each course will be for 2 hrs.
Q17: For the function setting the student's grade for a course, am I
correct in assuming that if the student gets a grade of "I" that it
does NOT mean I need to de-register him from future courses for which
he is registered for which the current course is a pre-requisite?
(Only an actual D or F will trigger de-registration.)
A17: The student should not be allowed to register in this case, since
(s)he has not really completed the course yet!
Q18: In your sample input you have called functions by specific names such
as SETGRADE. I was planning to name that function StudentGrade, as it is
given in the textbook. Will your input be more flexible to handle that
kind of variability?
A18: You may name the function whatever you want in your code. The
input command will be called SETGRADE.
Q19: Should we hardcode the current semester and current year, or should we
expect you to give us that at the beginning of your input file, or should we
find it out using the Date function in SQL?
A19: You should extract the current year and month from the sql
commands: select to_char (sysdate,'YYYY') into XXX from dual;
select to_char (sysdate, 'MM') into XXX from dual;
XXX is some char array to hold the value.
Then you can find out which semester it is by checking the month. For
spring the month will be from 1-5 and for Fall 8-12.
Q20: How can we set up our database schema (i.e. our CreateTable statements) in
Oracle and have them be "saved" so that all we'll have to do is input your
data in the future to these tables?
A20: See A1 and A4.
Q21: is the domain of departments closed or open
A21: It is open, but restricted to any 4 letter combination. The
values will be as specified in COURSEINFO or PROFESSORS (see
ProjectDB.TXT)
Q22: i believe you need to specifiy the valid days and times for classes.
on the spec, Sec III, C, 5, it says : The possible values should be selected
from a fixed list of weekly slots.
A22: see A16 above.
Q23: May we please have the fixed list of weekly time slots for classes to be
taught, or do we assume all classes have a duration of 2 hours and are taught
MR and TF only? This is mentioned on page 32, Section IIIC5. Your sample
input has classes starting at 9, 10, 12, and 2. We assume someone could not
register for courses starting at 9 and 10 on the same days. Is this correct?
A23: See A16 above.
Q24: I was wondering how the input file was going to be passed in to
the program. Are you going to use it as a command line argument, a
prompt at the start of the program, or through a pipe (program.exe <
input.txt)? Thank you!
A24: You should take the input file and the first input parameter. The
code will be run as "progam.exe inputfile".
Q25: do we have to normalize every create table function?
A25: NO. Use whatever design you feel comfortable with.
Q26: how do I connect to the vcmr server in the .pc file. I was having
trouble getting it to successfully connect.
A26: The PC labs are already set to connect to the server
automatically. If you use the IBM machines, they are also set to
connect to the server. If you installed the oracle client on your
machine, then you will have to cinfigure the net8 client too. See the
oracle page link from the course web page to see the settings to use.
Q27: Professor, the schema says the "system shall contain the identity
of the current and next semesters". What type of container should we
use to store those variables? Our group came up with using a 1 row
table to store the data in, but it seems a little odd to have a 1 row
relation. Is there a way to store global variables in an SQL table?
A27: see A19 above.
Q28: Can you tell you what fuction ENDSEMESTER does? We need a
clarification on it.
A28: See section 3.2.V.J in the book for details.
Q29: About USERINFO DELETE, when you use UserInfo delete, does this
mean that you delete the specific student from the table?
A29: Yes
Q30: Finally, OLAP function, we would to know the usage of this
function? What will be the INPUT COMMANDs?
A30: Any select statement is allowed on the tables you create. Of
course, since each group may have different names for tables, we will
modify the OLAP query to use the correct table names that you will
specify in your report. The sample input file has an example of the
OLAP query. For the select statement you should check if it is a
" select " to begin with. If not it will be an error. If it is a
select statement then simply execute it after preparing the statement
using dynamic SQL. Then use the SQL data descriptor area to find how
many fields were returned, etc. to do the output.
Q31: For GETGRADE function, we know that if a Faculty uses this
function, the function requires one Student's ID. What if a Student
logged in, and accidentally, enter his/her Id to retrieve the grade,
Should this be an error? Because you don't need to enter an ID to
retrieve your OWN GRADE?
A31: If the student supplies the ID it should not be an error. You can
simply ignore the Id and show the grades only for the student logged
in.
Q32:I was wondering what could be the different kinds of input query in OLAP
that you are expecting:
"SELECT * FROM TABLENAME" /
"SELECT FIELD1, FIELD2(any no. of fields) FROM TABLENAME" /
"SELECT FIELD1, FIELD2(any no. of fields) FROM TABLENAME WHERE FIELD3 =
'xx'"/
"SELECT FIELD1, FIELD2(any no. of fields) FROM TABLENAME WHERE FIELD3 = ?
AND FIELD4 = ?"/
Joins :"SELECT .... FROM T1, T2 WHERE....."/
Nested :"SELECT.....FROM....WHERE FIELD1 IN (SELECT....)"
If variable no. of inputs are to be accepted, would you please recommend
me some material from where I can learn more about the Input Descriptor?
How to use SetDescriptor and execute a query ?
A32: The following are be issued:
"SELECT * FROM TABLENAME" /
"SELECT FIELD1, FIELD2(any no. of fields) FROM TABLENAME" /
"SELECT FIELD1, FIELD2(any no. of fields) FROM TABLENAME WHERE FIELD3 =
'xx'"/
Joins :"SELECT .... FROM T1, T2 WHERE....."/
The following will not be issued:
"SELECT FIELD1, FIELD2(any no. of fields) FROM TABLENAME WHERE FIELD3 = ?
AND FIELD4 = ?"/
Nested :"SELECT.....FROM....WHERE FIELD1 IN (SELECT....)"
In general, you should simply execute whatever select "string" I pass
to you, so there will be no input binding, but the output number of
fields can be different (say from a join). For this you need to use
the output data descriptor area.
Q33: Is the duration of a class same for all classes ? Your sample
records have TIME as MR9AM, MR10AM....in order to check whether there
is a time-conflict when a student tries to register for a new course,
I need the duration of each class....
A33: see A16.
Q34: From where do we get the value for the current semester and year ? Do we
hardcode it (as SPRING and 2002) or you can test using a different set ?
A34: see A19 above.
Q35: How to check whether a cursor is NOT NULL ? for eg, I open a
cursor on "SELECT S.Id FROM Student WHERE S.Id = 999999999"...If such
a student does not exist, then the cursor would return nothing....how
do we test that condition ?
A35: use the SQLSTATE variable to check the output of the select. See
A42 to find out the possible codes.
Q36: Do we need another command like "Quit" to quit our program? The
reason is that the command "ENDSESSION" only log off the current user, but
the program should still work to accept another user login.
A36: You must quit when you reach end-of-file of the input file.
Q37: How do we initial the database? Before user is able to input a
command, there should already be a database. Do we just put the initial
database, described in "ProjectDB.TXT", in our program, or we need to
implement an interface to initialize the database?
A37: see A1, A4, and A24
Q38: I am confused about the sample database that was given. Has that
already been created, so we dont have to design our own ? If so, how
do we connect to it? I dont know what the usernames and passwords for
it are. Please let me know how the sample database works because I am
totally confused.
A38: See A1, A4, A24, and A26
Q39: I figured it out in Windows, but I still can't get the makefile
correct for Unix. This would help since it would allow everyone in my
group to work on the program from off campus. Would it be possible to
post a sample makefile for the ProC compiler where the program is
written in C++?
A39: See A11 above.
Q40: How do I connect to the database from my laptop? The website
gives the address and port, but does not explain how to use this data,
is it ODBC? Do we set up some other part?
A40: See A26. You have to setup the Net8 client from the oracle tools.
See me if you need help to configure it.
Q41: Parts 1 and 2 seem to be in the text book, do we need to do
anything or can we just use the books answer? More then that, do we
need to use the refinement with textbooks?
A41: You can use the schema from the book. Any refinement you do is up
to you. For the textbooks you may assume that a course has only one
book per semester. But different books may be used for the same course
in different semesters.
Q42: What are the possible values for SQLSTATE?
A42: Check out the ProC/C++ precompiler programmer's guide on the
oracle information link:
http://desktop.msfc.nasa.gov/oracle815doc/server.815/index.htm
------------------------
NEW QUESTIONS:
Q43: I met some problems when i parse and insert the data from the
projectDB.txt which you provided.I found the sequence of the data to
insert was very important.If I parse the data as the the file,there
always violate integrity constraints(because their parent key can not
be found).In order to sloved the problem,I have to set buffer to
storage the data after parsing,and insert them follow my sequence.I
think it's not good,because I don't sure how big the buffer I should
malloc.I thought it for a long time,maybe the best way is to change
the projectDB.txt data sequence.
BTY,sping and fall ,the upcase and lower case also influence the
reference.
A43: I have fixed the up and low case problem. You can expect
SPRING/FALL/BOTH in uppercase.
In general you will have to hold the data in temporary buffers before
inserting it into your tables. How big to make the buffers? The best
is to keep it dynamic (use STL vectors or lists).
Another approach suggested by Robert Bachman:
Another way to parse the input file, without using buffers, is to
use file indexing.
Declare long variables like so
long transcriptIndex, ...;
Then when the program determines that you are at the transcript
section
of the file, the code
transcriptIndex = infile.tellg();
will store the absolute index in the infile where transcript starts
then when you want to go back to that location in the infile use the
command
infile.seekg(transcriptIndex);
I think this is much more time efficient as well as storage efficient.
The data storage can be done using at most 2 passes through the file
(one pass for the indexing and one pass to enter data in any order you
wish). And its much more easily adapted to any set of tables and
foreign key constraints.
HEre is a summary of what you can do:
MEthod 1: you make multipe passes of the file. Read the file once and
get the info about users, courses and then make a second pass to add
transcript, etc.
Method 2: You keep temporary buffers and do not insert into the DB
until the record is complete.
Method 3: You scan the input file and keep offset pointers to the
beginning of each table. Then you jump ahead to courses to fill
that info and then jump back in the file to the transcript, etc.
Method 4: use a perl script to parse the input and then make a script
file that first runs the perl script and next your c++ code.
Q44: In ProjectDB.txt, textbooks roll over onto multiple lines in the
Courses table. Should it all be contained on one line, as mentioned
in class, or do we need to take this rollover into account in
populating the databases?
A44: This has been fixed. There will be one line per record.
Q45: How are triggers created using proC? Whenever I try to create a
trigger using proC, I get a list of compile errors about 70 errors
long. I looked pretty deeply into the documentation found online at:
http://desktop.msfc.nasa.gov/oracle815doc/server.815/index.htm
as suggested on the class website, and I did not find any mention of
triggers anywhere in the manual that I looked.
A45: See answer A12 above and go the site to see an example of a trigger.
Q46: In the Get Grade History query (query D), the third part asks for
"List of courses completed with grade and instructor grouped by
semester." There are two problems that I see with this:
a). In the sample database, the teaching table only has records for
classes taught in the spring and fall 2002 semesters. How can the
user get the instructor's name for classes taught in any semester
taught before 2002? Either rows need to be added to the teaching
table for all courses that are referenced in the transcript table, for
every year and semester, or the request for the instructor's name
being part of the result must be dropped.
b). When the auther wrote "grouped by" in the sentence, they must have
intended to say "ordered by," because "grouped by" does not make much
sense.
A46: Part a) has been fixed. See A56. You are correct for part b). The
"grouped by" is meant in colloquial sense, not DB sense of GROUPBY.
Q47: Is the database uniform, meaning are all spaces/tabs between
columns/fields, going to be standard? Does this also apply to the
input in between tables (always 1 newline? assuming no extra spaces
where there shouldn't be).
A47: The number of spaces is not standard and you should not hard code
that. However there will be whitespace between columns. The only
exception to this is the name field which has one whitespace after the
comma (Last, First), the text book field, and the course name
field. You should know the beginning of a piece of information by the
table name on a line by itself. This will be followed by the column
names and the records. A blank line means end of that table of info.
Q48: How do we interface ProC with C++. FAQ Question 11 is not
clear. it says to add code=cpp to the custom build step for the
precompiler. We looked in many places, and do not understand what this
means and how to implement it.
A48: see A11 above.
Q49: Can we assume that the input that you will test our program with,
is perfect/(has no errors). Moreso, can we assume that the input
follows all appropriate constraints. If the input follows all
constraints, then is it necessary to add all constraints to our code?
But if the input DOES NOT follow all constraints, then how do we
report constraint errors?
A49: The input database will be consistent as far as past and current
information is current, but the input command lines that request some
action can cause violations of contraints. Thats is what you have to
check for.
Q50: I think implement command No. 9 is quite complicated in a general
matter. There are too too many situations we need to consider. Can you
classified it into different parts?
A50: I think DISPLAY and EDIT are intuitive. i.e., EDIT corresponds to
an update transaction while DISPLAY to a select. ADD corresponds to an
insert, and it may specify only partial information (but must specify
the primary key). Then EDIT can be used to specify the rest of the
fields. Also see Q55 below.
Q51: I think the textbook is attribute of Class but not Course. So what do
you mean by adding textbook to a Course? If add to particular class,
which class to add? And what if there is more than one textbook?
A51: See the new updated ProjectDB.txt file, where there is
information about all course offerings. So one can ask to add a
textbook for a given course in a given semester.
Q52: As a wild hope, is there any chance at all that we could do this
assignment using Perl instead of C/C++? I would happily take on the
responsibility of learning how to connect to the database within Perl
in order to take advantage of Perl's text manipulation abilities...
A52: If you want you can use perl's text manipulation facilities to
parse the input and transform it into your table format, but the main
program should be in C/C++. If you choose the perl option then you
MUST provide a script that will automatically invoke your perl program
and then the C/C++ exec file. The command line format should be:
scriptfile inputfile.
Q53: When computing a student's GPA, we need the list of completed courses,
the grade for each course, and the credit-hours for each course. In
your input file, the credit-hours is in the TEACHING table, and is
there only for 2002 classes. Do we assume that past classes of a given
course have the same credit-hour value as current classes ?
A53: See the upated input file that has a record for every course taught.
Q54: Will the database file you use to test our program be set up exactly
as the sample? i.e., the "USERS" table first, followed by the
"TRANSCRIPT" table, etc etc?
A54: The order will be exactly as in the input, only the data will be different.
Q55: For the ADD subfunction of COURSEINFO, since sections no longer exist,
what is this actually adding? Your file says that it can take any
subset of the given parameters, but if it doesn't give all the
parameters necessary to create a new class for the given semester,
should we just allow it to create a partial class listing?
A55: Depending on your table fields, you should allow partial class
listing as long as the primary key is provided for your table. For
instance, when adding a new course the instructor or textbook may not
be known. So an add should succeed even if this information is not
provided in the beginning. Of course when the current semester
advances then this information should have been specified.
Q56: in the database, there are many entries in the transcript table
that do not have a corresponding entry in the TEACHING, COURSES or
COURSEINFO tables. That makes it hard to do any integrity constraints
on the transcript table. Is that on purpose ?
A56: This has been fixed.
Q57: I am not sure how to handle some of the less obvious integrity
constraints. For instance, the "CourseInfo ADD/EDIT/DISPLAY CrsCode"
interaction has many possibilities for breaking integrity constraints,
such as changing the max enrollment might make it so that there are
too many students enrolled, changing the credit hours might make some
student be over 20 credit hours, changing to a classroom which is too
small for all those enrolled, etc.... Should any of the above actions
simply not be allowed (cause a FAILURE) or do we need to come up with
cascading strategies to allow the task to go through?
A57: If any change causes violation of a constriant the action should
not be permitted.
Q58: We are using C++ for embedded SQL. As we know we should add
code = cpp to the custom build step for the precompiler. But we can't find
this code variable!! Could you please advise us.
A58: See A11 above.
Q59: We got an error when we compiled the code including Descriptor
area. In Windows, it gave an error statement like 'usage is only valid
when Dynamic = ANSI'....so, we added the flag, dynamic = ANSI after
code = CPP and it compiled. How should we change the unix makefile to
specify that dynamic = ANSI ?
A59: in the makefile add dynamic=ANSI to CPPFLAGS
Q60: We are trying to implement the constraints of the database and cannot
do it. We were informed that CREATE ASSERTION is not supported in
Oracle and we are now trying to use a subquery within the CHECK
statement as in the book:
CREATE TABLE EMPLOYEE (
ID INTEGER,
Name CHAR(20),
Salary INTEGER,
DepartmentId CHAR(4),
MngrId INTEGER,
CHECK ( 0 < (SELECT COUNT(*) FROM EMPLOYEE) ) );
But when we try to run this we recieve the following:
ERROR at line 7:
ORA-02251: subquery not allowed here
Our group has no idea how we are supposed to implement integrity
constraints if CREATE ASSERTIONS are not supported, and from what it
looks like, subqueries in CHECK statements are not allowed either.
A60: Assertions are not supported in Oracle and neither are subqueries
in check statement. You will have to use triggers to get the job done.
See A12 for more info on triggers.
Q61: Can we assume that a course will only have 0, 1, or 2 prerequisites
and no more as shown in your sample input file?
A61: You can assume a max of 10 prereqs per course.
Q62: I have a quick question about triggers in the database systems
project. If we create triggers on certain conditions, does this mean
that we don't have to worry about checking the tables in our code?
for example, if I create the trigger
CREATE Trigger CheckIfClassIsFull
BEFORE INSERT ON TRANSCRIPT
FOR EACH ROW
WHEN (Count>=Max)
BEGIN
Show errors trigger CheckIfClassIsFull;
END
Do I still have to check and see in the code if the class is full?
Can our error checking be in the triggers and constraints only, or do
we have to double everything in our code?
A62: You still have to make sure that the transaction will not go
through. In your action block after WHEN, all you do is print out an
error, but the transaction will still go ahead and insert the
tuple. You need to prevent this and abort the transaction. You can do
the processing in the block or set a flag and do it outside.
Q63: I realize that the input from your input file in the project may
violate constraints and such... But can we be assured that the data
you enter will be legal data (i.e. Id numbers will be exactly 9
digits in length, and stuff like that)?
A63: Yes, it will be legal.
Q64: The SetGrade function on your input does not take a year
parameter. Is this an error? Or are we just supposed to assume the
year is the current year? If that's so, do we take that to mean that
no grades can ever be corrected once the year has ended?
A64: It is not an error. The specs in the book says that one is
allowed to change a grade for either the current or the previous
semester. The semester can span years.
Q65: I'm working on the COURSEINFO option and I was wondering if you
will be giving a statement like this:
COURSEINFO ADD CrsCode Prereq=p1 Prereq=p2 etc....
meaning, will it be possible to have multiple Prereqs defined in the
ADD version of COURSEINFO, or does the user have to use the EDIT
version of COURSEINFO for every time he/she wants to add a prereq?
A65: No, there will always be one instance of an option. So I'll issue
multiple lines of edit if I need to add different prereqs for instance.
Q66: For the past week we have been trying to figure out why our
create triggers dont work in our program. We have tried all
possibilities and used the format found on the website.
A66: It turns out that ProC/c++ is not recognizing the triggers for
some reason. I found that enclosing the trigger as a string and doing
a dynamic call works.
For example I used the following commands and it worked:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
EXEC SQL AT DB_NAME
CREATE TABLE t6 (id int, name char(6), primary key (id));
EXEC SQL AT DB_NAME CREATE TABLE t2 (val int);
EXEC SQL AT DB_NAME EXECUTE IMMEDIATE
"CREATE TRIGGER Trig after insert on t6 referencing new as N for
each row when (N.id < 3) begin insert into t2 values (10); end;";
EXEC SQL AT DB_NAME INSERT INTO t6 values (10, 'zkkk');
EXEC SQL AT DB_NAME INSERT INTO t6 values (2, 'zaaa');
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The trigger trig inserts a tuple with value 10 whenever we insert any
row in t6 with an id value less than 3.
The output as seen form SQLPLUS is as follows:
SQL> select * from t6;
ID NAME
---------- ------
10 zkkk
2 zaaa
SQL> select * from t2;
VAL
----------
10
This confirms that the trigger works (it inserted the 10 into t2
automatically).
Note that it is possible to copy the trigger statement to a varchar
variable, say trig1string, and then call
EXEC SQL AT DB_NAME EXECUTE IMMEDIATE :trig1string
Q67: I've been trying to get constraint checking working for our
project, and I'm convinced it can't be done according to the spec in
the book. In the FAQ, in question 60, you say that we should use
trigers to implement the functionality we would have implemented using
assertions otherwise. The problem is, in the version of Oracle that
we're using, you cannot have a SELECT statement in the WHEN condition
of a trigger. As such, there's no way to compare an inserted row
against all rows in another table. Neither triggers nor CHECK
constraints have that capability. For example, I can't figure out any
way to check the constraint that a class can't have a higher max
enrollment than the room it's in can support, because I can't compare
a newly inserted class to the entries in the Classroom table to ensure
that the requested classroom can support the class size. How are we
supposed to implement conditions like this?
A67: It is possible to do subqueries in PL/SQL. Keep in mind that the
WHEN clause can be completely omitted, which means that the condition
will always be true. Then one can have a PL/SQL block right after the
trigger where one can check any complex constraint.
I'll use an example to illustrate this. Assume that tables t6 and t2
are as defined and instantitated in A66 above.
Now I issue the following statement:
EXEC SQL AT DB_NAME EXECUTE IMMEDIATE
"create trigger trig2 before insert on t6 for each row \
DECLARE \
cnt NUMBER; \
BEGIN \
select count(*) into cnt from t6; \
IF (3 < cnt) THEN \
insert into t2 values (5); \
ELSE \
insert into t2 values (2); \
END IF; \
END;"
Comment: the \ allows one to span a string on multiple lines.
See how I read the current count of rows into the PL/SQL variable cnt
and then compare the value to see if cnt (BEFORE the insert!) is
greater than 3. If so I will insert 5 into t2, else I will insert 2
into t2.
See what happens when I issue the following SQL statements. Keep in
mind that now both trig (from A66) and trig2 are active.
SQL> insert into t6 values (5, 'kkk');
1 row created.
SQL> select * from t6;
ID NAME
---------- ------
10 zkkk
2 zaaa
5 kkk
SQL> select * from t2;
VAL
----------
10
2
SQL> insert into t6 values (19, 'LLL');
1 row created.
SQL> select * from t6;
ID NAME
---------- ------
10 zkkk
2 zaaa
5 kkk
19 LLL
SQL> select * from t2;
VAL
----------
10
2
2
SQL> insert into t6 values (1, 'ggg');
1 row created.
SQL> select * from t6;
ID NAME
---------- ------
10 zkkk
2 zaaa
5 kkk
19 LLL
1 ggg
SQL> select * from t2;
VAL
----------
10
2
2
5
10
Notice how both trig and trig2 fired for the last select statement!
So in general you can do complex checks inside the PL/SQL begin-end
block and you can declare variables in the declare section, and either
omit the WHEN clause (true by default in this case), or do simple
checks in the WHEN clause that do not involve a query.
More details on the PL/SQL can be obtained from the oracle PL/SQL
guide. Take a look at Jeff Ullman's page on PL/SQL for a quick start.
http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html
Q68:What's the meaning of "For ADD or EDIT any subset of the fields
may be specified" in the list of commands.
Suppose ADD CrsCode Prereq=123,do other fields also be specified?
What's the currentclassid and nextsemclassid,I have no these attr in
my table.
A68: Yes, other fields may be specified in addition.
currentclassid is the class room id for this semester.
nextsemclassid is the class id for next semester.
See also A65 above.
Q69: When using a trigger such as:
1 CREATE OR REPLACE TRIGGER CheckGrade
2 BEFORE INSERT ON TRANSCRIPT
3 REFERENCING NEW AS N
4 FOR EACH ROW
5 WHEN(N.Grade NOT IN('A', 'B', 'C', 'D', 'F', 'I'))
6 BEGIN
9* End CheckGrade;
What command do I use in line 7 to abort the insert? Since as i have
found out, rollback doesn't work with triggers.
A69: One way is to insert a value into some other table and then check
the content of that table after each insert or whatever action you
were trying to do. If there is a value in the table that gives an
error conditoin then you can rollback outside the trigger using
embedded SQL.
For example I can create a global table like
exec sql at db_name create table errtable (errnum int);
then in the trigger body after line 6 above I can do the following:
begin
insert into errtable (-1);
end checkgrade;
Now in my embedded C code I can check
exec sql at db_name savepoint mysave1;
exec sql at db_name STUFF CALLING INSERT, etc.
exec sql at db_name
select count(*) into :err from errtable E where E.errnum=-1;
if (err >= 1){
exec sql at db_name rollback to mysave1;
}
As you can see before the insert I did a savepoint and then later I
rollback to that point, effectively undo-ing the insert. Note also
that it undoes the inserts etc. in the trigger itself, i.e., this
restores the table to the state it was in before the insert was called
(including any subsequent action taken in the trigger, etc.)
Q70: After reviewing you modifications to the COURSEINFO command, I
think I have found a potential problem. If you use the Case 1,
COURSEINFO ADD, you will be creating a record that can never be used
and you can never modify. This is because all future edits require a
ClassId, but one is not specified in this case. I see no way of not
specifying a ClassId and having this function do anything of value.
Also, the parameters enrolled and registered are not used in any of
the cases.
A70: First, enrolled and registered are not used as input for any
case, but you should display these fields for either a current
semester's or next semester's class.
Let's add a new course via
COURSEINFO ADD CS7777 CrsName="new course" DeptID=CS
SemesterOffered=BOTH CreditHrs=3
Now I can say
COURSEINFO ADD CS7777 NextSemClassId=153 MaxEnrollment=40
This will add a new class to next semester with room=153 and max
enrollment=40
I can edit info by saying
COURSEINFO EDIT NextSemClassId=153 MaxEnrollment=50 Textbook="Some text, zaki"
I can't change the room though!
Then at some later point someone says ENDSEMESTER, so the next sem
becomes the current semester.
Now I can say
COURSEINFO EDIT CurrentClassId=153 Textbook="some other text, zaki"
So the only problem with this format is that one cannot change a room
once it is assigned, but other than that things work out.
Q71: What happens if someone deletes a student or a prof?
A71: ONe solution is to allow a "soft" delete. i.e., keep a flag in
the users table which says that the user is no longer active. So that
the user cannot register or authenticate even. It doesn't make sense
to delete information about past courses or transcript. Only the
current semester and next semester's information should be
modified. if a faculty is dropped and she/he was supposed to teach a
course next sem you can set prof id to null. if the prof is active in
the current sem then you should not allow the delete. To do the delete
one must issue an edit command first to change profid and then do the
delete.