Categories
Databases

Discuss and define Entity Relationship Diagram.

Learning Goal: I’m working on a databases project and need an explanation and answer to help me learn.
Develop an Entity Relationship Diagram based on the Data Stores represented in the Data Flow Diagram created in Assignment-1.
You can include any additional Data Stores needed to complete the ERD.
You can add a Primary Key to each Table (Usually an Integer).
In Access Database you can specify this as “Auto Increment”. (You don’t have to use Access!).
All relationships in the ERD must be complete and consistent with the Data Stores shown in DFD.
Upload your ERD as a MS Word Document.
ERD- Videos
————-
ERD Using Lucid Chart:
Part-1
Part-2
ERD Using Access:
Table Relationships:
Referential Integrity:
Data Dictionary- Access DB

Categories
Databases

Define the trigger.

1) Connect to your Pluggable Database
2)
// Create Tables
create table department (
dep_id int primary key,
name varchar2(30)
);
create table employee (
dep_id references department,
name varchar2(30)
);
create table department_secrets (
dep_id references department,
secret varchar2(30)
);
3)
// FILL IN THE TABLES
insert into department values (1, ‘Research and Development’);
insert into department values (2, ‘Sales’ );
insert into department values (3, ‘Human Resources’ );
insert into employee values (2, ‘Peter’);
insert into employee values (3, ‘Julia’);
insert into employee values (3, ‘Sandy’);
insert into employee values (1, ‘Frank’);
insert into employee values (2, ‘Eric’ );
insert into employee values (1, ‘Joel’ );
insert into department_secrets values (1, ‘R+D Secret #1’ );
insert into department_secrets values (1, ‘R+D Secret #2’ );
insert into department_secrets values (2, ‘Sales Secret #1’);
insert into department_secrets values (2, ‘Sales Secret #2’);
insert into department_secrets values (3, ‘HR Secret #1’ );
insert into department_secrets values (3, ‘HR Secret #2’ );
// Allow any employee to see all secrets in their own department, but not any in any other departments.
4)
// Start by creating a package.
create or replace package pck_vpd
as
p_dep_id department.dep_id%type;
procedure set_dep_id(v_dep_id department.dep_id%type);
function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/
create or replace package body pck_vpd as

procedure set_dep_id(v_dep_id department.dep_id%type) is
begin
p_dep_id := v_dep_id;
end set_dep_id;
function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return ‘dep_id = ‘ || p_dep_id;
end predicate;

end pck_vpd;
/
5)
// Define the trigger.
create or replace trigger trg_vpd
after logon on database
declare
v_dep_id department.dep_id%type;
begin
select dep_id into v_dep_id
from employee where upper(name) = user;
pck_vpd.set_dep_id(v_dep_id);
end;
/
QUESTION: When is this code triggered? What does it do when it is triggered?
6)
//Define the policy. The policy states which procedure is used to add a where clause part to the where clause if someone executes a select statement.
BEGIN
SYS.DBMS_RLS.ADD_POLICY(
object_schema => ‘SYSTEM’,
object_name => ‘department_secrets’,
policy_name => ’emp_vpd_policy’,
function_schema => ‘SYSTEM’,
policy_function => ‘pck_vpd.predicate’,
statement_types => ‘select,update,delete’
);
END;
/
7)
//Create some users.
create user frank identified by frank
create user peter identified by peter
create user julia identified by julia
//Grant the required privileges
grant all on department_secrets to frank;
grant all on department_secrets to peter;
grant all on department_secrets to julia;
grant create session to frank;
grant create session to peter;
grant create session to julia;
//Create a public synonym
create public synonym department_secrets for department_secrets;
8)
connect as frank and
select * from department_secrets;
QUESTION: What happened? Why?
connect as peter
select * from department_secrets;
QUESTION: What happened? Why?