0. Manufacturing Company The company has offices in three cities: Boston, New Yo

Question

0. Manufacturing Company The company has offices in three cities: Boston, New York, and Cleveland. The database has to support data about departments and employees of the company. For the departments, the company needs to keep their codes, names (names are unique), locations, and types; for the employees-IDs, names, and types (full-time part-time, or consultant). Each employee is assigned to one department. In addition, each employee has a title, which defines the employee's salary; salaries cannot exceed S100,000. Departments of the company are located in three cities. The logical model of this database is the following (primary keys are underlined; foreign keys are in italic): Title (titleCode titleDescription, salary) Department (deptcode, deptName, location, deptType) Employee (ID, emplName, emplType, deptCode, titlecode) You may use the following data in the tables of your database:

Explanation / Answer

Answer 1:

********************************************

CREATE DATABASE ManufacturingCompany;

CREATE TABLE IF NOT EXISTS Title

(titleCode varchar(255) NOT NULL AUTO_INCREMENT,   

titleDescription varchar(255),

salary decimal(6,2),

CHECK(salary<100000) ,   

PRIMARY KEY (titleCode)

);

CREATE TABLE IF NOT EXISTS Title

(

titleCode varchar(10) NOT NULL UNIQUE,   

titleDescription varchar(255) UNIQUE,

salary decimal(6,2),

CHECK(salary<100000) ,   

PRIMARY KEY (titleCode)

);

CREATE TABLE IF NOT EXISTS Department

(

deptCode int(10) NOT NULL UNIQUE,   

deptName varchar(255) UNIQUE,

location varchar(255) NOT NULL CHECK (location IN ('Boston','New York','Cleveland')),

deptType varchar(255),   

PRIMARY KEY (deptCode)

);

CREATE TABLE IF NOT EXISTS Employee

(

ID int(10) NOT NULL UNIQUE,

emplName varchar(255),

emplType varchar(255),

deptCode int(10),

titleCode varchar(10),

PRIMARY KEY (ID),

FOREIGN KEY (deptCode) REFERENCES Department(deptCode),

FOREIGN KEY (titleCode) REFERENCES Title(titleCode)

);

*************************************************************

Answer: 2

****************************************************************************************************

INSERT INTO Title (titleCode, titleDescription, salary) VALUES ('T1','Accountant',10000);

INSERT INTO Title (titleCode, titleDescription, salary) VALUES ('T2','Analyst',20000);

INSERT INTO Title (titleCode, titleDescription, salary) VALUES ('T3','Proffessor',30000);

INSERT INTO Title (titleCode, titleDescription, salary) VALUES ('T4','Engineer',90000);

INSERT INTO Title (titleCode, titleDescription, salary) VALUES ('T5','Doctor',60000);

INSERT INTO Department (deptCode, deptName, location,deptType) VALUES (001,'Computer Center','Boston','IT');

INSERT INTO Department (deptCode, deptName, location,deptType) VALUES (002,'Analyst','New York','Resource');

INSERT INTO Department (deptCode, deptName, location,deptType) VALUES (003,'Marketing','Cleveland','Marketing');

INSERT INTO Department (deptCode, deptName, location,deptType) VALUES (004,'Human Resource','New York','HR');

INSERT INTO Department (deptCode, deptName, location,deptType) VALUES (005,'Finance','Boston','Finance');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (1,'Mike','FullTime',001,'T1');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (2,'Jane','PartTime',002,'T2');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (3,'Cindy','FullTime',003,'T3');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (4,'John','FullTime',002,'T1');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (5,'Vinay','PartTime',001,'T2');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (6,'David','Controller',003,'T5');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (7,'Priyanka','FullTime',002,'T4');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (8,'Shaktiman','FullTime',003,'T1');

INSERT INTO Employee (ID, emplName, emplType,deptCode,titleCode) VALUES (9,'Spiderman','PartTime',004,'T2');

****************************************************************************************************

Answer:3

*********************************************************************************************************

ALTER TABLE Employee ADD DateOfBirth DATE;

UPDATE Employee SET DateOfBirth = '1990-04-12' WHERE ID=1

UPDATE Employee SET DateOfBirth = '1990-04-13' WHERE ID=2

UPDATE Employee SET DateOfBirth = '1990-04-16' WHERE ID=3

UPDATE Employee SET DateOfBirth = '1990-06-16' WHERE ID=4

UPDATE Employee SET DateOfBirth = '1993-09-16' WHERE ID=5

UPDATE Employee SET DateOfBirth = '1990-01-16' WHERE ID=6

UPDATE Employee SET DateOfBirth = '1990-04-11' WHERE ID=7

UPDATE Employee SET DateOfBirth = '1990-04-13' WHERE ID=8

UPDATE Employee SET DateOfBirth = '1996-07-09' WHERE ID=9

*********************************************************************************************************

SELECT ON SINGLE TABLE

1) Assume X=30000

SELECT * FROM Title where salary>30000

2) Assume X=30000 and Y=65000

SELECT * FROM Title where salary between 30000 and 65000

3) Assume X=1 and Y=3

SELECT emplName FROM Employee WHERE deptCode In(1,3)

4) Name starts with is not very clearly visible ,So I assumed that name starts with letter 'S'.

SELECT emplName FROM Employee WHERE emplName like 'S%'

Related Questions

Navigate

• Browse (All)
• Browse
• Subjects

---

---

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