Question

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 DeptLocation_FK FOREIGN KEY(DNumber) REFERENCES Department(DNumber) CREATE TABLE Employee ( NOT NULL, * Employee number NOT NULL * Employee name Date of birth Home address /M-Male, F-Female Salary * Supervisor number */ /Department number * CHAR(5) VARCHAR(30) Date VARCHAR(50) CHAR DECIMAL(7,2), CHAR(5) DECIMAL(5) CONSTRAINT Employee_PK PRIMARY KEY(ENumber) ENumber Name DOB Address Sex Salary Supervisor DNumber CONSTRAINT Employee_FK1 FOREIGN KEY (Supervisor) REFERENCES Employee(ENumber) CONSTRAINT Employee_FK2 FOREIGN KEY (DNumber) REFERENCES Department (DNumber) CREATE TABLE Project PNumber NOT NULL NOT NULL DECIMAL(10) VARCHAR(30) VARCHAR(30), DECIMAL(5) DECIMAL(10,2) CONSTRAINT Project_PK PRIMARY KEY(PNumber) * Project number * Project title /*Project sponsor name */ * Department number */ * Project budget PTitle Sponsor DNumber Budget NOT NULL NOT NULL CONSTRAINT Project_FK FOREIGN KEY (DNumber) REFERENCES Department(DNumber), CONSTRAINT Project_CK UNIQUE (PTitle CREATE TABLE WorksOn NOT NULL ENumber PNumber * Employee number * Project number Working hours per week CHAR(5) DECIMAL(10) DECIMAL(3,1) NOT NULL NOT NULL Hours CONSTRAINT WorksOn_PK PRIMARY KEY(ENumber, PNumber) CONSTRAINT WorksOn_FK1 FOREIGN KEY(ENumber) REFERENCES Employee(ENumber), CONSTRAINT WorksOn_FK2 FOREIGN KEY(PNumber) REFERENCES Project(PNumber)

Answer 1

3)

1) ALTER TABLE Department ADD FOREIGN KEY (Manager) REFERENCES Employee(ENumber);

2) ALTER TABLE Employee ADD CHECK (Sex IN ('M', 'F'));

3) ALTER TABLE Employee ADD CHECK (Salary <= 999999.99);

4) ALTER TABLE Department ALTER COLUMN Manager CHAR(5) NULL;

NOTE: As per HOMEWORKLIB POLICY, I am allowed to answer only 4 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.