4 Consider the following relational schema, DDL statements and tables:
EMPLOYEE( EmployeeID, EmployeeName, SupervisorID, DepartmentID)
PROJECT (ProjectID, EmployeeID)
DEPARTMENT( Department ID, DepartmentName)
CREATE TABLE EMPLOYEE
( EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50) NOT NULL,
SupervisorID INT DEFAULT 9,
DepartmentID INT,
FOREIGN KEY (SupervisorID) REFERENCES EMPLOYEE (EmployeeID)
ON DELETE SET DEFAULT ),
FOREIGN KEY (DepartmentID) REFERENCES DEPARTMENT(DepartmentID)
ON UPDATE SET NULL);
CREATE TABLE PROJECT (
ProjectID INT PRIMARY KEY,
EmployeeID INT DEFAULT 9,
FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE (EmployeeID)
ON UPDATE CASCADE);
CREATE TABLE DEPARTMENT(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50),
);
EMPLOYEE
EmployeeID |
EmployeeName |
SupervisorID |
DepartmentID |
6 |
A |
7 |
6 |
7 |
B |
7 |
6 |
1 |
C |
8 |
7 |
8 |
D |
9 |
3 |
9 |
E |
NULL |
6 |
PROJECT
ProjectID |
EmployeeID |
1 |
6 |
2 |
7 |
3 |
1 |
4 |
8 |
5 |
7 |
6 |
1 |
DEPARTMENT
DepartmentID |
DepartmentName |
3 |
X |
6 |
Y |
7 |
Z |
Show the status of the above tables after each of the following operations:
a. Updating the EmployeeID from 1 to 11. If it is rejected, explain.
b. Insert a new project with ProjectID= 23 and EmployeeID= 17. If it is rejected, explain.
c. Deleting the employee whose ID= 8. If it is rejected, explain.
d. Changing the employee whose ID= 8 to 13.
4 Consider the following relational schema, DDL statements and tables: EMPLOYEE( EmployeeID, EmployeeName, SupervisorID, DepartmentID) PROJECT...
NEED HELP IN INSERT STATEMENTS FOR THE TABLES CAN YOU PLEASE ADD SOME INSERT STATEMENTS FOR A COMPANY SCHEMA SCRIPT. PLEASE ADN THANK YOU DROP TABLE dependent; DROP TABLE works_on; DROP TABLE project; DROP TABLE dept_locations; DROP TABLE department; DROP TABLE employee; CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary numeric(10,2), superssn char(9), dno numeric, primary key (ssn), foreign key (superssn) references employee(ssn) ); CREATE...
QUESTION B1. (7 marks) This question assumes the following relational schema Employee (employeeNumber, lastName, firstName, DOB, HireDate, Position Salary, Dept) Primary Key: employeeNumber Foreign key: Dept refers to DeptID in Department Department (DeptID, DeptName, DeptLocation) Primary Key: DeptID You have been given the following MySQL stored procedure: CREATE PROCEDURE Find_EmployeeName (IN employeeNo INT (11), OUT employeeName VARCHAR (60)) BEGIN SELECT concat(firstName, '', lastName) INTO employeeName FROM employees WHERE employeeNumber employeeNo; END (a) (2 marks) Name the two types of parameters...
MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects 2.List the project with the most hours SCRIPT TO BUILD DATABASE create table department ( dept_ID int not null , dept_name char(50) NOT NULL, manager_ID int not null, manager_start_date date not null, constraint Dept_PK primary key (dept_ID), constraint D_Name_AK unique (dept_name) ); insert into department values(1,'abc',1,'2019-01-08'); insert into department values(2,'abc2',2,'2019-01-08'); insert into department values(3,'abc3',2,'2019-01-08'); insert into department values(4,'abc4',2,'2019-01-08'); /*project table done*/ create table project...
these are all tables please see the tables and questions are mentioned below please see that all and I need answers asap please write proper answer it's an easy task and don't take much time please do it fast thanks in advance EMPLOYEE Remark Column Name EmployeeNumberINT Primary Key Yes No CHAR (25 CHAR (35 CHAR 25 NUMERIC INT CHAR (12 CHAR Name in the DEPARTMENT table Position No Number in the EMPLOYEE table Su OfficePhone EmailAddress No No No...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...
You will create the following 4 triggers: - trgEmployee: Will be placed on the Employee table and listens for Inserts, Deletes, and Updates - trgJob: Will be placed on the Job table and listens for Inserts, Deletes, and Updates - trgProject: Will be placed on the Project table that contains the projectId and projectName. - trgActivity: Will be placed on the Activity table that contains the activityId and activityName. Again, each trigger will write to its respective audit table: trgProject...
Database concepts Find the names and ids of the students who have taken at most one course in the Spring 2010 semester. Notice, at most one means one or zero. So, the answer should include students who did not take any course during that semester The data: create table classroom (building varchar(15), room_number varchar(7), capacity numeric(4,0), primary key (building, room_number) ); create table department (dept_name varchar(20), building varchar(15), budget numeric(12,2) check (budget > 0), primary key (dept_name) ); create...
database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...
Display all customers. If a customer has placed any orders, also display the highest..... Tables: CREATE TABLE Sales.Customers ( CustomerId INT NOT NULL IDENTITY, CustomerName NVARCHAR(50) NOT NULL, StreetAddress NVARCHAR(50) NULL, City NVARCHAR(20) NULL, [State] NVARCHAR(20) NULL, PostalCode NVARCHAR(10) NULL, Country NVARCHAR(20) NULL, Contact NVARCHAR(50) NULL, Email NVARCHAR(50) NULL, CONSTRAINT PK_Customers PRIMARY KEY(CustomerId) ); CREATE TABLE HR.Employees ( EmployeeId INT NOT NULL IDENTITY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BirthDate DATE NOT NULL, HireDate DATE NOT NULL, HomeAddress...