Animal Project

Mapping E-R Model to Relational Animal :Animal(animal_id, name, height, weight, sci_name, age, life_span, color, gender) Domain Constraints: Attribute

Datatype

Contraint

Example

animal_id

number(8)

PRIMARY KEY

4

Name

varchar(30)

NOT NULL

White Tiger

height

varchar(6)

NOT NULL

5.5ft

weight

number(5)

NOT NULL

150

sci_name

varchar(30)

NOT NULL

Panthera tigris tigris

life_span

number(3)

NOT NULL

20

Age

number(3)

NOT NULL

15

Color

varchar(50)

NOT NULL

white

gender

varchar(6)

NOT NULL

male

Catagory :Catagory(category_id, category_name) Domain Constraints: Attribute

Datatype

Contraint

Example

category_id

number(1)

PRIMARY KEY

2

category_name

varchar(50)

NOT NULL

Least Concern

Sub-Category:sub_category(sub_category_id, sub_category_name) Domain Constraints: Attribute

Datatype

Contraint

Example

sub_category_id

number(1)

PRIMARY KEY

2

sub_category_name

varchar(50)

NOT NULL

Birds

Main-category :main_category(main_category_id, sub_category_id, category_id, animal_id) Domain Constraints: Attribute

Datatype

Contraint

Example

main_category_id

number(2)

PRIMARY KEY

2

sub_category_id

number(1)

NOT NULL

1

category_id

number(1)

NOT NULL

2

animal_id

number(4)

NOT NULL

34

Animal details :animal_details(animal_id, city_id, population) Domain Constraints: Attribute

Datatype

Contraint

Example

animal_id

number(8)

PRIMARY KEY

12

city_id

number (3)

NOT NULL

34

population

number(7)

NOT NULL

100

City :city(city_id ,city_name, state_id) Domain Constraints: Attribute

Datatype

Contraint

Example

city_id

number(3)

PRIMARY KEY

45

city_name

varchar(50)

NOT NULL

surat

state_id

number(2)

NOT NULL

23

State :state(state_id,state_name) Domain Constraints: Attribute

Datatype

Contraint

Example

state_id

number(3)

PRIMARY KEY

2

state_name

varchar(20)

NOT NULL

Gujarat

SQL Queries Create Database: CREATE DATABASE Animal Species Repository System WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'English_India.1252' LC_CTYPE = 'English_India.1252' TABLESPACE = pg_default CONNECTION LIMIT = -1;

Creating Tables: CREATE TABLE animal( animal_id number(8) PRIMARY KEY, name varchar(30) NOT NULL, height varchar(6) NOT NULL, weight number(5) NOT NULL, sci_name varchar(30) NOT NULL, life_span number(3) NOT NULL, age number(3) NOT NULL, color varchar(50) NOT NULL, gender varchar(6) NOT NULL );

CREATE TABLE category( category_id number(1) PRIMARY KEY, category_name varchar(50) NOT NULL );

CREATE TABLE sub_catagory( sub_category_id number(1) PRIMARY KEY, sub_category_name varchar(50) NOT NULL );

CREATE TABLE main_category( main_category_id number(2) PRIMARY KEY, sub_category_id number(1) REFERENCES sub_category(sub_category_id) ON DELETE RESTRICT NOT NULL, category_id number(1) REFERENCES category(category_id) ON DELETE RESTRICT NOT NULL, animal_id number(8) REFERENCES animal(animal_id) ON DELETE RESTRICT NOT NULL );

CREATE TABLE animal_details( animal_id number(8) REFERENCES animal(animal_id) ON DELETE RESTRICT NOT NULL , city_id number(3) REFERENCES city(city_id) ON DELETE RESTRICT NOT NULL , population number(7) NOT NULL );

CREATE TABLE state( state_id number(2) PRIMARY KEY, state_name varchar(50) NOT NULL );

CREATE TABLE city( city_id number(3) PRIMARY KEY, city_name varchar(50) NOT NULL, state_id number(2) REFERENCES state(state_id) ON DELETE RESTRICT NOT NULL );