Employee Details Employee Salary Employee Dept. Title Qualifications Employee ID
ID: 3805538 • Letter: E
Question
Employee Details
Employee Salary
Employee Dept.
Title
Qualifications
Employee ID
Employee ID
DeptID
TitleID
QualID
Personal ID
SalaryID
DeptDate
EmployeeID
EmployeeID
FirstName
Currency
From Date
Experience
Middle Name
Monthly Salary
ToDate
LastName
From date
To Date
To Date
Gender
Date ofBirth
Hire Date
Instructions
Based on the data above,
Create the objects (attributes, entities & relationships etc) as defined using SQL DDL statements which will include the following:
Drop statements for all objects in the lab project (drop existing objects first so that you can rerun your script without error).
Create statements for all tables and keys of your project.
Create indexes on natural key columns, foreign keys, and other columns that will be frequently used as query filters (i.e., Columns in the “WHERE” clause).
Create at least two views.
Create at least two sequences.
Create at least two triggers.
Check content of the catalog/data dictionary.
Item 7 (above) should contain SQL SELECT statements to query DBMS catalog/data dictionary (such as user_objects or user_tables) to demonstrate all objects are created successfully.
The deliverable would be two separate files: one plain text file (.txt or .sql file) with your SQL statements only; and the other document (doc/docx/pdf) include both your SQL statements and the output (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.
Employee Details
Employee Salary
Employee Dept.
Title
Qualifications
Employee ID
Employee ID
DeptID
TitleID
QualID
Personal ID
SalaryID
DeptDate
EmployeeID
EmployeeID
FirstName
Currency
From Date
Experience
Middle Name
Monthly Salary
ToDate
LastName
From date
To Date
To Date
Gender
Date ofBirth
Hire Date
Explanation / Answer
create database test;
use test;
drop view Employee_Monthly_Salary_Details;
drop view Employee_Experience;
drop table Employee_Details;
drop table Employee_Salary;
drop table Employee_Dept;
drop table Title;
drop table Qualifications;
drop trigger make_10pc_hike;
drop trigger give_25pc_hike;
create table Employee_Details(
Employee_ID int primary key,
Personal_ID int,
First_Name varchar(200),
Middle_Name varchar(200),
Last_Name varchar(200),
ToDate date;
Gender varchar(10),
DOB date,
Hire_Date date
);
create table Employee_Salary(
Employee_ID int primary key,
Salary_ID int,
Currency varchar(50),
Monthly_Salary int,
From_Date date,
ToDate date
);
create table Employee_Dept(
DeptID int primary key,
DeptDate date);
create table Title(
Title_ID int AUTO_INCREMENT primary key,
Employee_ID int,
From_Date date,
ToDate date);
create table Qualifications(
QualID int AUTO_INCREMENT primary key,
Employee_ID int,
Experience float);
show tables;
create view Employee_Monthly_Salary_Details as
select t1.First_Name,t1.Employee_ID,t2.Monthly_Salary
from Employee_Details as t1 inner join Employee_Salary as t2
on t1.Employee_ID=t2.Employee_ID;
create view Employee_Experience as
select t1.First_Name,t1.Employee_ID,t2.Experience
from Employee_Details as t1 inner join Qualifications as t2
on t1.Employee_ID=t2.Employee_ID;
CREATE TRIGGER give_25pc_hike After INSERT ON Employee_Details
FOR EACH ROW SET @Monthly_Salary = @Monthly_Salary+@Monthly_Salary*0.25;
END;
CREATE TRIGGER make_10pc_hike BEFORE INSERT ON Employee_Details
FOR EACH ROW SET @Monthly_Salary = @Monthly_Salary+@Monthly_Salary*0.10;
END;
show triggers;
select table_name,table_type from information_schema.tables where table_schema='test' order by table_name;
select distinct INDEX_NAME from information_schema.STATISTICS where TABLE_SCHEMA = 'test';
select trigger_name,action_statement from information_schema.triggers where trigger_schema='test';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.