Description
see attached
Unformatted Attachment Preview
CIS420 – Database Management
Systems
Final Project
Due Date: 11:59 pm EST, Friday of Unit 8
Points: 100
Overview:
The course culminates into a final project, in which you can demonstrate all that you
have learned about SQL throughout the course. The final project allows you to show
this course objective: Illustrate the theories and techniques in developing database
applications and be able to demonstrate the ability to build databases using enterprise
DBMS products such as Oracle or SQL Server.
Instructions:
You must provide a screenshot of your output for each table created and values
inserted using Oracle.
Q1: Create a table “employees” and insert values in that table.
create table EMPLOYEES
(
EMPLOYEE_ID
NUMBER(6) not null,
FIRST_NAME
VARCHAR2(20),
LAST_NAME
VARCHAR2(15),
EMAIL
VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE
DATE,
JOB_ID
VARCHAR2(10),
SALARY
NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID
NUMBER(6),
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
DEPARTMENT_ID NUMBER(4),
GRADUITY
INTEGER,
OUT_TIM
DATE,
IN_TIM
DATE
)
alter table EMPLOYEES
add constraint EMP_EMP_ID_PK primary key (EMPLOYEE_ID)
alter table EMPLOYEES
add constraint EMP_DEPT_FK foreign key (DEPARTMENT_ID)
references DEPARTMENTS (DEPARTMENT_ID);
alter table EMPLOYEES
add constraint EMP_JOB_FK foreign key (JOB_ID)
references JOBS (JOB_ID);
alter table EMPLOYEES
add constraint EMP_MANAGER_FK foreign key (MANAGER_ID)
references EMPLOYEES (EMPLOYEE_ID);
insert into
EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_i
d,salary,commission_pct,manager_id,department_id,graduity,out_tim,in_tim)
values(1,’JACK’,’JOHN’,’jack123′,’515.123.4567′,’01-jan-2020′,
‘AD_PRES’, 27500,null,null, 90,null,null,null),
(2,’Steven’,’King’,’King123′,’515.123.4567′,’05-jan-2020′,
‘AD_PRES’, 37500,null,null,90,null,null,null),
(3,’David’,’Pataballa’,’Pataballa32′,’590.423.4569′,’01-jan-2012′,
‘IT_PROG’, 17500,null,102,60,null,null,null),
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
(4,’Urman’,’Ismael’,’Ismael510′,’515.124.4369′,’05-jan-2013′,
‘FI_ACCOUNT’, 47500,null,108,100,null,null,null)
(5,’STOBIAS’,’Tobias’,’Tobias123′,’515.127.4564′,’01-jan-2018′,
‘PU_CLERK’,7500,null,114,30,null,null,null)
(6,’Himuro’,’GHIMURO’,’GHIMURO123′,’515.127.4565′,’01-dec-2017′,
‘PU_CLERK’,5500,null,114,30,null,null,null),
(7,’Weiss’,’MWEISS’,’MWEISS123′,’650.123.1234′,’01-dec-2010′,
‘ST_MAN’,15500,null,100,50,null,null,null)
(8,’Khoo’,’AKHOO’,’Khoo200′,’515.127.4562′,’01-dec-2005′,
‘PU_CLERK’,5500,null,114,30,null,null,null)
(9,’Popp’,’LPOPP’,’LPOPP00′,’515.124.4567′,’01-nov-2005′,
‘FI_ACCOUNT’,6500,null,108,70,null,null,null)
Q2: Create a table “departments” and insert values in that table.
create table DEPARTMENTS
(
DEPARTMENT_ID NUMBER(4) not null,
DEPARTMENT_NAME VARCHAR2(30),
MANAGER_ID
NUMBER(6),
LOCATION_ID
NUMBER(4)
)
alter table DEPARTMENTS
add constraint DEPT_ID_PK primary key (DEPARTMENT_ID);
alter table DEPARTMENTS
add constraint DEPT_LOC_FK foreign key (LOCATION_ID)
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
references LOCATIONS (LOCATION_ID);
alter table DEPARTMENTS
add constraint DEPT_MGR_FK foreign key (MANAGER_ID)
references EMPLOYEES (EMPLOYEE_ID);
insert into departments(department_id,department_name,manager_id,location_id)
values(10,’Administration’,200,1700),
values(30,’Purchasing’,114,1600),
values(50,’Shipping’,121,1500),
values(90,’Executive’,100,1700),
values(100,’Finance’,108,1700),
values(60,’IT’,103,1400),
values(70,’Public Relations’,204,2700),
values(120,’Treasury’,106,1700),
values(180,’Construction’,100,1700)
Q3: Create a table “locations” and insert values in that table.
create table LOCATIONS
(
LOCATION_ID
NUMBER(4) not null,
STREET_ADDRESS VARCHAR2(40),
POSTAL_CODE
CITY
VARCHAR2(12),
VARCHAR2(30),
STATE_PROVINCE VARCHAR2(25),
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
COUNTRY_ID
CHAR(2)
)
alter table LOCATIONS
add constraint LOC_ID_PK primary key (LOCATION_ID);
alter table LOCATIONS
add constraint LOC_C_ID_FK foreign key (COUNTRY_ID)
references COUNTRIES (COUNTRY_ID);
insert into
locations(location_id,street_address,postal_code,city,state_province,country_id)
values (1400,’2014 Jabberwocky Rd’,’26192′,’Southlake’,’Texas’,’US’),
(1500,’2011 Interiors Blvd’,’99236′,’South San Francisco’,’California’,’US’),
(1600,’2007 Zagora St
50090′,’South Brunswick’,’New Jersey’,’US’),
(1700,’2004 Charade Rd 98199′,’Seattle’,’Washington’,’US’),
(2700,’Schwanthalerstr. 7031′,’80925′,’Munich Bavaria’,’DE’),
(2800,’Rua Frei Caneca 1360′,’01307-002′,’Sao Paulo’,’Sao Paulo’,’BR’),
(2900,’20 Rue des Corps-Saints’,’1730′,’Geneva’,’Geneve’,’CH’);
Q4: Create a table “countries” and insert values in that table.
create table COUNTRIES
(
COUNTRY_ID CHAR(2),
COUNTRY_NAME VARCHAR2(40),
REGION_ID
NUMBER,
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
constraint COUNTRY_C_ID_PK primary key (COUNTRY_ID)
)
alter table COUNTRIES
add constraint COUN_ID_PK primary key (COUNTRY_ID);
alter table COUNTRIES
add constraint COUNTR_REG_FK foreign key (REGION_ID)
references REGIONS (REGION_ID);
insert into COUNTRIES(COUNTRY_ID,COUNTRY_NAME,REGION_ID)
values(‘US’,’United States of America’2),
(‘BR’,’Brazil’2),
(‘CA’,’Canada’2),
(‘CH’,’Switzerland’,1),
(‘CN’,’China’,3),
(‘DE’,’Germany’,1)
Q5: Create a table “regions” and insert values in that table.
create table REGIONS
(
REGION_ID NUMBER primary key,
REGION_NAME VARCHAR2(25)
)
insert into regions(region_id,region_name)
values(1,’Europe’),
(2,’Americas’),
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
(3,’Asia’),
(4,’Middle East and Africa’)
Q6: Create a table “jobs” and insert values in that table.
create table JOBS
(
JOB_ID
VARCHAR2(10) primary key,
JOB_TITLE VARCHAR2(35),
MIN_SALARY NUMBER(6),
MAX_SALARY NUMBER(6)
)
insert into jobs(job_id,job_title,min_salary,max_salary)
values(‘AD_PRES’,’President’,20000, 40000),
(‘AD_VP’,’Administration Vice President’,15000 ,30000),
(‘AD_ASST’,’Administration Assistant’,3000,6000),
(‘FI_MGR’, ‘Finance Manager’,8200,16000),
(‘FI_ACCOUNT’,’Accountant’,4200,9000),
(‘AC_MGR’, ‘Accounting Manager’,8200,16000),
(‘AC_ACCOUNT’,’ Public Accountant’,4200,9000),
(‘SA_MAN’, ‘Sales Manager’ 10000,20000),
(‘SA_REP’, ‘Sales Representative’,6000,12000),
(‘PU_MAN’, ‘Purchasing Manager’,8000,15000),
(‘PU_CLERK’, ‘Purchasing Clerk’2500,5500),
(‘ST_MAN’, ‘Stock Manager’,5500,8500),
(‘ST_CLERK’,’Stock Clerk’,2000,5000),
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
(‘SH_CLERK’,’Shipping Clerk’,2500,5500),
(‘IT_PROG’, ‘Programmer’,4000,10000),
(‘MK_MAN’,’Marketing Manager’,9000,15000),
(‘MK_REP’,’Marketing Representative’,4000,9000),
(‘HR_REP’,’Human Resources Representative’,4000,9000),
(‘PR_REP’,’Public Relations Representative’,4500,10500);
The next step will be to run queries from the tables you have created as follows:
Provide the screenshot of each output.
Q7: Create a query to display the last name, job code, hire date, and employee number
for each employee, with the employee number appearing first?
Q8: Create a query to display unique job codes from the EMPLOYEES table?
Q9: Name the column headings Emp #, Employee, Job, and Hire date, respectively?
Q10: Display the last name concatenated with the job ID, separated by a comma and
space, and name the column Employee and Title?
Q11: Create a query to display the data from the EMPLOYEES table. Separate each
column by a comma. Name the column THE_OUTPUT?
Q12: Create a query to display the last name and salary of employees earning more
than $12,000?
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
Q13: Create a query to display the employee’s last name and department number for
employee number 1?
Q14: display the last name and salary of all employees whose salary is not in
the range of $5,000 and $12,000?
Q15: Display the employee’s last name, job ID, and start date of employees hired
between February 20, 2008, and January 28, 2020. Order the query in ascending order
by start date.
Q16: Display the last name and department number of all employees in departments 20
and 50 in alphabetical order by name.
Q17: Display the last names of all employees where the third letter of the name is an a?
Q18: Display the last name of all employees with an a and an e in their last name?
Q19: Write a query to display the current date. Label the column Date.
Q20: Display the employee number, last_name, salary, and salary increased by 15%
and expressed as a whole number. Label the column New Salary.
Q21: Add a column that subtracts the old salary from the new salary. Label the column
Increase.
Q22: Write a query that displays the employee’s last names with the first letter
capitalized and all other letters lowercase and the name length for all employees whose
name starts with J, A, or M. Give each column an appropriate label. Sort the results by
the employees’ last names.
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
Q23: For each employee, display the employee’s last name, and calculate the number
of months between today and the date the employee was hired. Label the column
MONTHS_WORKED. Order your results by the number of months employed. Round
the number of months up to the closest whole number.
Q24: Write a query that produces the following for each employee:
earns monthly but wants . Label the column Dream Salaries.
Q25: Create a query to display all employees’ last names and salaries. Format the
salary to be 15 characters long, left-padded with $. Label the column SALARY.
Requirements:
•
Submit a Word document.
Be sure to read the criteria below by which your work will be evaluated before
you write and again after you write.
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
Evaluation Rubric for Final Project
CRITERIA
Accuracy
Completeness
Deficient
0 – 44 points
0-69%
accurately
answers the
questions.
0 – 14 points
Lab is not very
complete.
Answering a
few questions.
© 2022 Post University, Waterbury, CT
ALL RIGHTS RESERVED
Needs
Improvement
45 – 59 points
60-79%
accurately
answers some
questions.
15 – 19 points
Lab is
somewhat
complete.
Answering
some (at least
70%)
questions.
Proficient
Exemplary
60 – 74 points
80-99%
accurately
answers most
questions.
75 points
100% accurate
accurately
answers all
questions.
20 – 24 points
Lab is mostly
complete.
Answering
most (at least
90%)
questions.
25 points
Lab is
complete.
Answering all
(100%)
questions.
Purchase answer to see full
attachment
