Answer to Question #277142 in Databases | SQL | Oracle | MS Access for king

Question

a) Create the above two relations and impose the following constraints on that (10 marks) Attribute name Data type Constraints ID Number (7) Primary key Name Varchar (15) Not Null Deptname Char(3) Not Null Foreign key for instructor relation Primary key for department relation Budget Number (7) NOT NUll

b) Insert the following tuples to the department relation “MGBlock, CSE, 50L” & “SJTAnnex, CHE, 16L” – Present the output for each insertion. (5 marks) c) Form a new relation from with the following attributes – building, department, budget, number of instructors in each department. Name the new relation as Dept_Instcount - (10 marks) d) Update the tuple of Dept_Instcount – if instructor count is more than 2, increase the budget to 30L. (10 marks) e) Implement question – (b) as a trigger (10 marks) 

Answer 1

CREATE TABLE Persons (
    ID int PRIMARY KEY,
    Name varchar(15) NOT NULL 
); 

CREATE TABLE Department (
    DepartmentRelationBudget int PRIMARY KEY,
    Deptname char(3) FOREIGN KEY REFERENCES Persons(ID) NOT NULL
); 

INSERT INTO PERSONS(787293, 'Sam')
INSERT INTO PERSONS(3000000, 'Human Resources')