DBMS - Assignment 3

 Assignment 3 

 Objectives : To create one or more tables with Check constraint , Unique constraint, Not null constraint , in addition to the first two constraints (PK & FK)

The following is the list of additional integrity constraints.

1 NULL: Specifies that the column can contain null values.

2 NOT NULL: Specifies that the column can not contain null values.

3 UNIQUE: Forces the column to have unique values.

4 CHECK: Specifies a condition that each row in the table must satisfy. Condition is specified as a logical expression that evaluates either TRUE or FALSE.

Set A

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

Machine(Machine_id int Machine_name NOT NULL, Machine_type varchar(10),Machine_price float, Machine_cost float)

Constraints: 

1. Machine_name should be in uppercase.

2.Machine_type can be ( ‘drilling’, ‘milling’, ‘lathe’, ‘turning’, ‘grinding’).

3.Machine_price should be greater than zero.

Table level constraint: Machine_cost less than Machine_price.

Query:-

Create table Machine(Machine_id int primary key ,

 Machine_name NOT NULL CHECK (Machine_name = upper (Machine_name ), 

Machine_type varchar(10) CKECK (Machine_type IN ( ‘drilling’, ‘milling’, ‘lathe’, ‘turning’, ‘grinding’ ) ,Machine_price float CHECK (Machine_price >0 ),  Machine_cost float CHECK ( Machine_cost <Machine_price  ));

2.Policy(Policy_no int , Policy_name varchar(20) NOT NULL,Policy_type varchar(10), Policy_sale_date date, Policy_intro_date date)

Constraints: 

1. Policy_name should be in lowercase.

2. Policy_type can be (‘life’, ‘vehicle’, accident’).

Table level constraint : Policy_sale_date should be greater than Policy_intro_date.

Query:-

Create table Policy(Policy_no int Primary key , Policy_name varchar(20) NOT NULL CHECK (Policy_name = lower(Policy_name ) ,Policy_type varchar(10) CHECK (Policy_type IN (‘life’, ‘vehicle’, accident’) ), Policy_sale_date date, Policy_intro_date date , Constraint policy_dat_chk CHECK (Policy_sale_date  >Policy_intro_date  ));

3.Consider the relations Person (pnumber, pname, birthdate, income), Area( aname,area_type). An area can have one or more person living in it , but a person belongs to exactly one area. The attribute ‘area_type’ can  have values as either urban or rural

Query:-

Create Table Area ( aname varchar(50) primary key , area_type varchar(50) CHECK (area_type IN ( 'urban', 'rural'));

Create table Person (pno int primary key , pname varchar(50), birthdate date, income float , aname references area(aname));

Set B

1. Employee(Employee_id int, Employee_name varchar(20) NOT NULL, Employee_desig varchar(10),

Employee_sal float, Employee_uid text Unique)

Constraints:

1. Employee_name should be in uppercase.

2.Employee_desg can be (‘Manager’, ‘staff’, ‘worker’).

3.Employee_sal should be greater than zero.

Table level constraint :Employee_uid not equal to Employee_id

Query :-

Create Table Employee  (Employee_id int primary key ,
Employee_name varchar(20) NOT NULL CHECK (Employee_name = upper (Employee_name )), Employee_desig varchar(10) CHECK (Employee_desig IN ('Manager', 'staff', 'worker')),Employee_sal float CHECK (Employee_sal >0),  Employee_uid int Unique , Constraint Emp_id CHECK(Employee_uid <> Employee_id ));

2. Consider the following database of bank. A bank maintains the customer details ,account details and loan

details .It has the branch information also. Create the tables , in 3NF, as per the information given below.

Account ( acct_no int ,acct_type varchar(20),balance int)

Loan(loan_no int,loan_amt int ,no_of_years int)

Branch(branch_no int,branch_name varchar(20),branch_city varchar(20)

Customer(cust_no int ,cust_name varchar(20),cust_address text,customer_city varchar(20)

Relationship : 

Customer-Account : M-M 

Customer –Loan : M-M 

Branch –Loan : 1-M 

Branch –Account : 1-M

Constraints: 

1. branch_name should be not null.

2. acct_type should be(‘saving’,’cuurent’)

Queries :-

• Create table Branch(branch_no int primary key ,branch_name varchar(20) NOT NULL ,branch_city varchar(20) );

• Create table Customer(cust_no int primary key  ,cust_name varchar(20), cust_address text, customer_city varchar(20) );

• Create table Loan(loan_no int Primary key ,loan_amt int ,no_of_years int , branch_no int references Branch(branch_no) );

• Create table Account ( acct_no int rimary key ,acct_type varchar(20) CHECK (acct_type IN (‘saving’,’cuurent’) ),balance int ,branch_no int references Branch(branch_no));

• Create table Cust_Acc( cust_no int references Customer(cust_no), acct_no int references Account (acct_no) );

• Create table Cust _Loan(cust_no int references Customer(cust_no) , loan_no int references Loan(loan_no));