Database Design
snvvkc
Second-RehabilitationSystemORACLETask2.docx
Insert Statements
Client Record
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (8,'Nirvana', '132 Platinum Drive Pennsylvania','severe', 'Cocaine', 'dopamine', '10%', 7248074603);
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (9,'Nikita', ' 3360 Hart Ridge Road Saint John, Washington','Mild', 'Nicotine', 'Sugar with sessions', '40%', 9893261403);
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (10,'Trevis', '2822 Cody Ridge Road Oklahoma','severe', 'Crystal meth', 'Chloroclyn', '60%', 5804359881);
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (11,'Jarvis', '1383 Limer Street Georgia','severe', 'Alcohol', 'dopamine', '0.2%', 7064392484);
Rehabilitation Centre
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES(6,'Nicotine' ,'20-JUN-2019');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES(7,'Alcohol ' ,'11-AUG-2015');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES(8,'Crystal Meth' ,'26-MAR-2012');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES(9,'Methadone' ,'15-FEB-2017');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES(10,'Coke' ,'02-FEB-2020');
Rehabilitation Team
INSERT INTO Rehabilitation_Team(MemberID,Name,Contact,CentreID) VALUES(6,'Mukhtara',9893261403,8);
INSERT INTO Rehabilitation_Team(MemberID,Name,Contact,CentreID) VALUES(7,'Nimra',7248074603,8);
INSERT INTO Rehabilitation_Team(MemberID,Name,Contact,CentreID) VALUES(8,'Adeel',989-326-1403,7);
INSERT INTO Rehabilitation_Team(MemberID,Name,Contact,CentreID) VALUES(9,'Noah',724-807-4603,7);
INSERT INTO Rehabilitation_Team(MemberID,Name,Contact,CentreID) VALUES(10,'Basit',989-326-1403,9);
INSERT INTO Rehabilitation_Team(MemberID,Name,Contact,CentreID) VALUES(11,'Rajesh',724-807-4603,9);
DATA_BASE
INSERT INTO Data_base(ID,Rehabilitation_fee,ClientID,OrderID,StakeholderID)VALUES(6, 24000,8,4,5);
INSERT INTO Data_base(ID,Rehabilitation_fee,ClientID,OrderID,StakeholderID)VALUES(7, 39900,9,3,4);
INSERT INTO Data_base(ID,Rehabilitation_fee,ClientID,OrderID,StakeholderID)VALUES(8, 150000,8,3,3);
INSERT INTO Data_base(ID,Rehabilitation_fee,ClientID,OrderID,StakeholderID)VALUES(9, 11000,8,2,2);
INSERT INTO Data_base(ID,Rehabilitation_fee,ClientID,OrderID,StakeholderID)VALUES(10, 21000,8,1,4);
Financial Information
INSERT INTO Financial_information(OrderID,CustomerID,FDate)VALUES (10,8,'10-JAN-2018');
INSERT INTO Financial_information(OrderID,CustomerID,FDate)VALUES (11,6,'29-MAY-2020');
INSERT INTO Financial_information(OrderID,CustomerID,FDate)VALUES (12,7,'12-DEC-2019');
INSERT INTO Financial_information(OrderID,CustomerID,FDate)VALUES (13,4,'21-APR-2016');
INSERT INTO Financial_information(OrderID,CustomerID,FDate)VALUES (14,2,'12-FEB-2020');
StakeHolder
INSERT INTO Stakeholder(Stakholder_ID,Name,Type,CID,TID,FID,CLID)VALUES (6,'GFC Motors','Sponsors',8,4,4,8);
INSERT INTO Stakeholder(Stakholder_ID,Name,Type,CID,TID,FID,CLID)VALUES (7,'Rehman Gardens','Sponsors',6,3,9,6);
INSERT INTO Stakeholder(Stakholder_ID,Name,Type,CID,TID,FID,CLID)VALUES (8,'Costa','Family Member',4,6,9,4);
INSERT INTO Stakeholder(Stakholder_ID,Name,Type,CID,TID,FID,CLID)VALUES (9,'Fatima','Family Member',10,10,10,10);
INSERT INTO Stakeholder(Stakholder_ID,Name,Type,CID,TID,FID,CLID)VALUES (10,'Mc donalds','Sponsors',4,6,7,9);
INSERT INTO Stakeholder(Stakholder_ID,Name,Type,CID,TID,FID,CLID)VALUES (11,'Harada san','Sponsors',3,6,1,9);
Data Analysis with queries for Select
Select * from Client_Record where addiction_drug='Marijuana';
Select * from Data_base where Rehabilitation_fee < 50000;
Select * from Stakeholder where Type = 'Sponsors';
Collecting information for sponsors for a client with stakeholder information with client information
Select Name, Type, Client_name, Address, Recovery_Status from Stakeholder JOIN Client_Record on CLID = Client_ID where type= 'Sponsors' ;
Collecting information for rehabilitation fee paid for a client with client information
Select Rehabilitation_fee,Client_name,Addiction_Drug,Addiction_Status,Recovery_status from Data_base JOIN Client_Record on ClientID = Client_ID where Addiction_Status='Starting to normal';
Getting the information of receiving rehabilitation fee in 2012 from a patient or client. Three different tables used
Select E.Rehabilitation_fee,C.Client_name, F.FDate from Data_base E JOIN Client_Record C on E.ClientID = C.Client_ID Join Financial_information F on E.OrderID = F.OrderID where F.FDate like '%12' ;
Memorandum
1. This week I am able to discuss the main problems on what the project should do and what it looks like. This week I have integrated the data with the database and populated the table with insert statements and provided the data to analyzing the tables and my ERD and DDL with the select queries and using those queries I have confirmed my speculations and theories of the database and thanks to the concept I grasped I didn’t have to update it because all the queries I have tested have the positive results.
2. I have applied the database logics for the join and inner joins and also I have provided the data with the selection of specific table columns and with those columns I have get the records for specific information. Also in database I was able to have the logics of like and date year specifications.
3. Yes I encountered several problems in the management system development. While creating the database it was easy but I forgot to enter the foreign key relations in the start. So I had to look up for the foreign key relation and then I used the ALTER statements and then inserted the foreign key relation for every entity. Then I got stuck up in INSERT operations and then I had to lookup for the solution again and I found the solution by applying the constraints on the key relation and insertion of the data was completed at last.
