create table locations(location_no int primary key,location_name varchar2(10),st

Question

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');

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 simple/complex views for end-users 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

Explanation / Answer

Couple of Simple View are:-

CREATE VIEW EMPLOYEE_VIEW AS SELECT * FROM EMPLOYEE WHERE GENDER = 'M';

CREATE VIEW PROJECT_VIEW AS SELECT * FROM PROJECT;

CREATE VIEW DEPARTMENT_VIEW AS SELECT * FROM DEPARTMENT WHERE DEPT_NAME IN ('CSE', 'ECE');

CREATE VIEW TITLES_VIEW AS SELECT * FROM TITLES;

Couple of Complex View are:-

CREATE VIEW EMP_TITLES AS SELECT emp_no, first_name, last_name, birth_date, title_no FROM EMPLOYEE JOIN TITLES ON EMPLOYEE.title_no= TITLES.title_no;

CREATE VIEW EMP_TITLES AS SELECT emp_no, first_name, last_name, birth_date, dept_no FROM EMPLOYEE JOIN TITLES ON EMPLOYEE.dept_no = DEPARTMENT.dept_no;

CREATE VIEW EMP_PROJECT AS SELECT * FROM empproject JOIN project ON empproject.proj_no = PROJECT.proj_no;

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.