Example-5

Consider the following tables and integrity constraints given and create the tables accordingly:

1Machine(Mid,MName NOT NULL, MType, MPrice , MCost)

Constraints:
I.MName should be in uppercase.
II.MType can be (‘drilling’, ‘milling’, ‘lathe’, ‘turning’, ‘grinding’).
III.MPrice should be greater than zero.

Table level constraint: MCost less than MPrice.

Create table Machine (M_Id int primary key, M_Name varchar(15) not null, M_Typevarchar(10),M_Price int, M_Cost int, check (M_Name= upper(M_Name)),
check(M_Type IN (‘drilling’, ‘milling’, ‘lathe’, ‘turning’, ‘grinding’)),
check (M_Price> 0), check (M_Cost< M_Price));





2.Policy(No,Name NOT NULL, Type ,Sale_Date, Intro_date )

Constraints:

I.Name should be in lowercase.

II.Type can be (‘life’, ‘vehicle’, ‘accident’)



Tablel evel constraint: Sale_date should be greater than Intro_date.

Create table Policy (P_No int primary key, P_Name varchar(15) not null, P_Type

varchar(10),Sale_Date varchar(15), Intro_Date varchar(15), check (P_Name=

lower(P_Name)),check (P_Type IN (‘Life’, ‘Vehicle’, ‘Accident’)), check

(Sale_Date> Intro_Date));

3.Employee (EmpNo, Emp_Name NOT NULL,Emp_desig, Emp_sal , Emp_uid)

Constraints

I.Emp_name should be in uppercase.

II.Emp_desg can be (‘Manager’, ‘staff’, ‘worker’).

III.Emp_sal should be greater than zero.



Table level constraint: Emp_uid not equal to Emp_id



Createtable Employee (Emp_No int primary key, Emp_Name varchar(15) not null,

Emp_Designation varchar(10), Emp_Sal int, Emp_UId int, check (Emp_Name=

upper(Emp_Name)),check (Emp_Designation IN (‘Manager’, ‘Staff’, ‘Worker’)),

check(Emp_Sal> 0));

 4.Room(room_no, type, price);

Constraints:

I.Room type must be one of single, double, family.

II.Price must be between Rs.500/- and 1000/-.

III.Room no must be between 1 and 100.



Create table Room (Room_No int primary key, Room_Type varchar(10), Price int, check

(Room_Type IN (‘Single’, ‘Double’, ‘Family’)), check (Price between 500 and

1000),check (Room_No between 1 and 100));

CREATE TABLE student_info( no NUMBER(3) PRIMARY KEY,  

stu_code VARCHAR(10), name VARCHAR(30),  city VARCHAR(30),   scholarship

NUMBER(5), CHECK(stu_code like 'j%'), CHECK(name = upper(name)), CHECK

(city IN ('Houston','San Antonio','Boston','Miami')),CHECK(scholarship BETWEEN 5000 AND 20000));