Joseph de la Fuente
cs308 Homework #3
===========
Question #1
===========
(a) create view branchsimple as
select branch_name, assets
from branch;
View created.
BRANCH_NAME ASSETS
-------------------- ----------
Brooklyn Heights 200000000
Park Slope 150000000
East Village 300000000
Jamaica 180000000
SOHO 220000000
(b) insert into branchsimple values ('Queens Village', 150);
1 row created.
BRANCH_NAME ASSETS
-------------------- ----------
Brooklyn Heights 200000000
Park Slope 150000000
East Village 300000000
Jamaica 180000000
SOHO 220000000
Queens Village 150
BRANCH_NAME BRANCH_CITY ASSETS
-------------------- -------------------- ----------
Brooklyn Heights Brooklyn 200000000
Park Slope Brooklyn 150000000
East Village New York 300000000
Jamaica Jamaica 180000000
SOHO New York 220000000
Queens Village 150
this creates a new row in both the view branchsimple and table branch
where branch_name = 'Queens Village' and assets = 150 but leaves out a
value for branch_city in table branch
insert into branchsimple values ('Broadway', 'Manhatten', 175000);
ERROR at line 1:
ORA-00913: too many values
this creates an error because the view branchsimple only contains two
attributes(branch_name and assets) even though table branch contains
three attributes(branch_name, branch_city, assets)
an error would also occur for the delete and update functions if you
tried to delete something based on an attribute that does not exist in
the view branchsimple like
delete from branchsimple
where branch_city = 'Jamaica';
ERROR at line 2:
ORA-00904: "BRANCH_CITY": invalid identifier
update branchsimple
set assets = 1
where branch_city = 'Jamaica';
ERROR at line 3:
ORA-00904: "BRANCH_CITY": invalid identifier
otherwise, deleting or updating a tuple in table branch by using the
view branchsimple is possible
(c) create view customeraccount as
select depositor.customer_name, customer_street, depositor.account_number, balance
from customer, account, depositor
where customer.customer_name = depositor.customer_name
and account.account_number = depositor.account_number;
View created.
CUSTOMER_NAME CUSTOMER_STREET ACCOU BALANCE
-------------------- -------------------- ----- ----------
Susan Canal St A-101 500000
Adams Jay St A-102 200000
Joe Park Ave A-103 150000
Bob 112th St A-104 450000
Susan Canal St A-105 350000
Johnson Broadway A-106 50000
Susan Canal St A-107 100000
Bob 112th St A-108 220000
(d) insert into customeraccount values ('Xiljin', 'Lafayette', 'A-109', 150)
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
this creates an error because you cannot insert tuples into several of
the underlying three tables through a join view
update customeraccount
set balance = 75
where customer_name = 'Joe';
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non
key-preserved table
update customeraccount
set customer_name = 'Xiljin'
where customer_street = 'Canal St';
ERROR at line 1:
ORA-02291: integrity constraint (DBUSER.SYS_C003023) violated -
parent key not found
these create errors because the view customeraccount has no parent key,
just a bunch of foreign keys
none of these commands are possible
delete from customeraccount
where customer_name = 'Susan';
3 rows deleted.
CUSTOMER_NAME CUSTOMER_STREET ACCOU BALANCE
-------------------- -------------------- ----- ----------
Adams Jay St A-102 200000
Joe Park Ave A-103 150000
Bob 112th St A-104 450000
Johnson Broadway A-106 50000
Bob 112th St A-108 220000
ACCOU CUSTOMER_NAME
----- --------------------
A-102 Adams
A-103 Joe
A-104 Bob
A-106 Johnson
A-108 Bob
CUSTOMER_NAME CUSTOMER_STREET CUSTOMER_CITY
-------------------- -------------------- --------------------
Adams Jay St Brooklyn
Bob 112th St Jamaica
Christina 7th Ave Brooklyn
Johnson Broadway New York
Joe Park Ave New York
Susan Canal St New York
this query is possible and deletes the tuples in view customeraccount
and table depositor where customer_name = 'Susan' but not in table
customer
(e) create view customeraccount as
select customer.customer_name, customer_street, account.account_number, balance
from customer, account, depositor
where customer.customer_name = depositor.customer_name
and account.account_number = depositor.account_number;
View created.
CUSTOMER_NAME CUSTOMER_STREET ACCOU BALANCE
-------------------- -------------------- ----- ----------
Susan Canal St A-101 500000
Adams Jay St A-102 200000
Joe Park Ave A-103 150000
Bob 112th St A-104 450000
Susan Canal St A-105 350000
Johnson Broadway A-106 50000
Susan Canal St A-107 100000
Bob 112th St A-108 220000
insert into customeraccount values ('Xiljin', 'Lafayette', 'A-109', 150)
update customeraccount
set balance = 75
where customer_name = 'Joe';
update customeraccount
set customer_name = 'Xiljin'
where customer_street = 'Canal St';
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non
key-preserved table
the same queries used in (d) also resulted in errors, however each query
in (d) had their own specific error, but in (e) they all had the same
error which was that you cannot modify a non key-preserved table
delete from customeraccount
where customer_name = 'Susan';
3 rows deleted.
this query came up with the same results as in (d)
(f) create view customersavings as
select customer.customer_name, customer_street, sum(balance) as total_savings
from customer, account, depositor
where customer.customer_name = depositor.customer_name
and depositor.account_number = account.account_number
group by customer.customer_name, customer_street;
View created.
insert into customersavings values ('Susan', 'Canal St', 1);
update customersavings
set customer_street = 'Lafayette'
where customer_name = 'Bob';
delete from customersavings
where customer_name = 'Bob';
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
these queries(insert, update, delete) resulted in errors, where they are
being illegally used probably because they were not granted the rights
to perform such actions
===========
Question #2
===========
given:
(i) they will never lend more than two million dollars in a single loan
(ii) a customer can never borrow more than a million dollars more than
what he has deposited in all his accounts
(a)
for rule (i) the assertion function can be used like so
create assertion rule1constraint check
(not exists (select *
from loan
where loan.amount > 2000000));
for rule (ii) the assertion function can be used like so
create assertion rule2constraint check
(not exists (select *
from (select depositor.customer_name, sum(balance) as total_savings
from account, depositor
where depositor.account_number = account.account_number
group by depositor.customer_name) X
(select loan.customer_name, sum(amount) as total_loans
from loan
group by loan.customer_name) Y
where X.customer_name = Y.customer_name
and X.total_savings > Y.total_loans - 1000000));
however the "assertion" method cannot be applied in Oracle's SQL
(b) rule (i) can be implemented in oracle by using a check constraint in
the table loan
CREATE TABLE loan (
customer_name varchar2(20),
loan_number char(5),
branch_name varchar2(20),
amount number,
primary key (loan_number),
foreign key (customer_name) references customer,
foreign key (branch_name) references branch,
check (amount <= 2000000));
Table created.
trying to insert a value for amount greater than two million dollars
results in an oracle error where check constraint is violated
insert into loan values ('Xiljin', 'L-107', 'Park Slope', 5000000);
ERROR at line 1:
ORA-02290: check constraint (DBUSER.SYS_C003132) violated
(c) it seems that the "assertion" mechanism from the book is not actually
available in the system (Oracle 9i); when attempted to use it, an
error occurs stating "invalid create command"
it also seems that the check constraint used for rule (i) cannot be applied
to rule (ii) since subqueries required for rule (ii) are not allowed
in a check constraint found in a "create table" function
the only mechanism left is "trigger" which is used like so to implement rule (ii)
create view customerloans as
select loan.customer_name, sum(amount) as total_loans
from loan
group by loan.customer_name;
create trigger rule2constraint
before insert on loan
referencing new as newRow
for each row
when (customersavings.total_savings > newRow.amount + customerloans.total_loans - 1000000)
begin
newRow.amount = NULL;
end rule2constraint;
/