create user cms
identified by cms
default tablespace users
temporary tablespace temp
quota unlimited on users;
grant dba to cms;
conn cms/cms
===============================
CREATE TABLE USER_TBL
(USER_ID NUMBER PRIMARY KEY,
USER_TYPE VARCHAR2(6) DEFAULT 'USER',
USER_NAME VARCHAR2(20),
PASWORD VARCHAR2(20),
USER_STATUS VARCHAR2(10) DEFAULT 'ACTIVE');
SQL> insert into user_tbl
values(1,'ADMIN','sykot','sykot','ACTIVE');
SQL> insert into user_tbl
values(2,'USER','PNTL','PNTL','ACTIVE');
SQL> COMMIT;
Clinic_Info ====================================SYKOT
CREATE TABLE Clinic_Info
(clinic_id NUMBER PRIMARY KEY,
clinic_name VARCHAR2(20) NOT NULL,
registration_no NUMBER NOT NULL,
address VARCHAR2(50) NOT NULL,
motto varchar2 (50),
tin_number NUMBER NOT NULL,
last_renew DATE,
last_aduit DATE,
phone_no VARCHAR2(20) NOT NULL,
email VARCHAR2(30) NOT NULL);
Departments ===================================SYKOT
CREATE TABLE Departments
(department_id NUMBER PRIMARY KEY,
department_name VARCHAR(40) NOT NULL);
JOBS =========================================SYKOT
CREATE TABLE Jobs
(Job_id NUMBER PRIMARY KEY,
Job_title VARCHAR2(40) NOT NULL);
Employees =====================================SYKOT
CREATE TABLE Employees (
employee_id NUMBER PRIMARY KEY,
job_id NUMBER NOT NULL,
department_id NUMBER NOT NULL,
employee_name VARCHAR2(20) NOT NULL,
email VARCHAR2(20),
hire_date DATE NOT NULL,
mobile_no VARCHAR2(12) NOT NULL,
salary NUMBER (8,2),
comm_pct varchar2 (10),
fathers_name VARCHAR2(20) NOT NULL,
mothers_name VARCHAR2(20) NOT NULL,
gender VARCHAR2(10),
blood_group VARCHAR2(10),
marital_status VARCHAR2(15) NOT NULL,
religion VARCHAR2(10));
ALTER TABLE Employees
ADD CONSTRAINT fk_Employees_Jobs FOREIGN KEY (Job_id)
REFERENCES Jobs(Job_id);
ALTER TABLE Employees
ADD CONSTRAINT fk_Employees_Departments FOREIGN KEY (department_id)
REFERENCES Departments (department_id);
ROOMS ====================================RATNA
CREATE TABLE rooms (
room_id INT PRIMARY KEY,
room_name VARCHAR2(30) NOT NULL,
floor INT,
room_type VARCHAR2(20),
employee_id INT,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id));
DEPARTMENT_ROOMS ===============================RATNA
CREATE TABLE department_rooms (
department_room_id INT PRIMARY KEY,
department_id INT,
room_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id),
FOREIGN KEY (room_id) REFERENCES rooms(room_id));
DOCTOR_SCHDEULE ====================================RATNA
CREATE TABLE doctor_schedule (
doc_schedule_id INT PRIMARY KEY,
doc_type VARCHAR(50) NOT NULL,
employee_id INT NOT NULL,
day_of_week VARCHAR2(10) NOT NULL,
start_time VARCHAR2(30) NOT NULL,
end_time VARCHAR2(30) NOT NULL,
doc_schedule_date date NOT NULL,
substitute VARCHAR2(30),
available VARCHAR2(30),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id));
MEDICINE_CATEGORY ==========================================
CREATE TABLE medicine_category (
m_category_id INT PRIMARY KEY,
m_category_name VARCHAR(100) NOT NULL,
description VARCHAR2(100),
created_date DATE NOT NULL,
last_upd_date DATE NOT NULL,
availability VARCHAR2(30));
test_category
==============
create table test_category
(t_category_id number(6) primary key,t_category_name varchar2(50)
not null,description varchar2(100),created_date date not null,
last_upd_date date,
availability varchar2(10));
test_info
==========
create table test_info
(test_id number(6) primary key, test_name varchar2(50) not null,
t_category_id number(4),patient_id number(6),employee_id number(6),
test_cost number(8,2),test_date date not null,status varchar2(50),
result varchar2(50),precaution varchar2(50),default_value
varchar2(10), FOREIGN KEY(t_category_id) REFERENCES TEST_CATEGORY (t_category_id));
setup
======
create table setup
(setup_id number(6) primary key,opd_tkt_value number(8) not null,created_date date
not null, last_upd_date date not null);
supplier
=========
create table supplier
(supplier_id number(6) primary key,supplier_name varchar2(25) not null,
address varchar2(100) not null,phone_number varchar2(20),
email varchar2(25) );
medicine_info
=============
create table medicine_info
(medicine_id number(6) primary key,medicine_name varchar2(20) not null,
m_category_id number(4),generic_name varchar2(30),
brand_name varchar2(30),description varchar2(100),
medicine_form varchar2(50),indication varchar2(50),
side_effects varchar2(100) not null,
manufacturer varchar2(50), batch_number number(6),production_date date,
expiry_date date not null,cost number(6),
foreign key (m_category_id) references medicine_category (m_category_id));
