Notes

/*
ASSIGNMENT:Database sem4
*/

--creatin address type
CREATE OR REPLACE TYPE address_type AS OBJECT (
street VARCHAR2(25),
city VARCHAR2(25),
country VARCHAR2(20))
/

CREATE TABLE addresses OF address_type;


--creating type
CREATE OR REPLACE TYPE contact_type AS OBJECT(
location VARCHAR2(15),
house_no VARCHAR2(15),
phone_no VARCHAR2(15),
town VARCHAR2(15),
postcode VARCHAR2(15))
/

--creating varray type
CREATE TYPE contact_varray_type AS VARRAY(30) OF contact_type;
/

--creating table for staff
CREATE TABLE staffs(
staff_id NUMBER(15) NOT NULL,
staff_fname VARCHAR2(30) NOT NULL,
staff_lname VARCHAR2(30) NOT NULL,
staff_fullname VARCHAR2(30),
staff_salary NUMBER(15),
date_of_birth DATE,
gender CHAR,
email VARCHAR2(30),
Contacts contact_varray_type,
address REF address_type SCOPE IS addresses
);

--creating table for festival_staff
CREATE TABLE festival_staffs(
festival_staff_id NUMBER(15) NOT NULL,
festival_staff_name VARCHAR2(30),
festival_staff_salary NUMBER(15),
festival_id NUMBER(10) NOT NULL,
staff_id NUMBER(10) NOT NULL);

--creating festival type
CREATE OR REPLACE TYPE festival_item_type AS OBJECT(
festival_start_date DATE,
festival_end_date DATE,
festival_type VARCHAR2(30));
/

--create festival item table type
CREATE TYPE festival_item_table_type AS TABLE OF festival_item_type;
/

--creating nested table for Festivals
CREATE TABLE Festivals(
festival_id NUMBER(15) NOT NULL,
location_id NUMBER(15) NOT NULL,
festival_nature_id NUMBER(15),
festival_name VARCHAR2(30) NOT NULL,
festival_item festival_item_table_type)
NESTED TABLE festival_item STORE AS festival_item_table;

--create location_type
CREATE OR REPLACE TYPE location_type AS OBJECT(
location_street VARCHAR2(30),
location_city VARCHAR2(30),
location_state VARCHAR2(30)
);
/

--creating table for Location
CREATE TABLE locations(
location_id NUMBER(15) NOT NULL,
location_name VARCHAR2(30) NOT NULL,
location_country VARCHAR2(30),
location_longitude VARCHAR2(30),
location_latitude VARCHAR2(30),
locations location_type
);

--creating table for festival_nature
CREATE TABLE festival_natures(
festival_nature_id NUMBER(15) NOT NULL,
festival_nature_name VARCHAR2(30),
festival_program_type VARCHAR2(100),
festival_date DATE);


--creating sequence for staff table
CREATE SEQUENCE seq_staffs
INCREMENT BY 1
START WITH 101;

--creating sequence for festival staffs table
CREATE SEQUENCE seq_festival_staffs
INCREMENT BY 1
START WITH 201;

--creating sequence for location table
CREATE SEQUENCE seq_locations
INCREMENT BY 1
START WITH 301;

--creating sequence for festival table
CREATE SEQUENCE seq_Festivals
INCREMENT BY 1
START WITH 401;

--creating sequence for festival_nature
CREATE SEQUENCE seq_festival_natures
INCREMENT BY 1
START WITH 501;