create table locations(location_no int primary key,location_name varchar2(10),street_address varchar2(20),postal_code int,city varchar2(10),state_province varchar2(10));

create table department(dept_no int primary key,dept_name varchar2(10),location_no int, foreign key(location_no) references locations(location_no));

create table titles(title_no int primary key,title varchar2(10));

create table employees(emp_no int primary key,first_name varchar2(10),last_name varchar2(10),birth_date date,gender char(2),dept_no int,title_no int,foreign key(dept_no) references department(dept_no), foreign key(title_no) references titles(title_no));

create table project (proj_no int primary key,proj_name varchar2(10));

create table empproject (emp_proj_no int primmary key,proj_no int,emp_no int,start_date date,hours_on_project int,status varchar2(10),foreign key(proj_no) references project (proj_no),foreign key(emp_no) references employees(emp_no));

Inserting data to the tables:-

insert into locations values(1,'Canada','1st street toronto',10112,'Toronto','Canada');

insert into locations values(2,'US','MI Mishigan',12112,'Novi','US');

insert into department values(11,'CSE',1);

insert into department values(12,'ECE',2);

insert into titles(101,'PHD');

insert into titles(102,'Associate');

insert into employees values(201,'John','David','12-02-1985','M',11,101);

insert into employees values(202,'David','Jones','15-05-1980','M',12,101);

insert into project values(301,'Text Clustering');

insert into project values(302,'Communication Systems');

insert into empproject values(401,301,201,'25-05-2015',50,'On going');

insert into empproject values(402,302,202,'20-04-2016',25,'Final Review');

Question:

: Create tables DDL queries. Add appropriate data to each table.

: Create simple/complex views for the end-users.

: Design data retrieval queries using various joins, functions, set operators and nested queries.

: Utilize various database objects like synonym, view, sequence and index.

: Implement user access control for different object.

: Use PL/SQL blocks(procedure/ function/package/trigger) for data manipulation.

Locations location no (PK) location name street address postal code city state_province department dept no (PK) dept name location no (FK) employees emp_no (PK) first name last name birth date gender dep no (FK) title no (FK) EmpProject emp proj.no (PK) proj no (FK) emp no (FK) start_date hours on project status titles title_no(PK) title Project proj.no (PK) proj name