Database Design
snvvkc
First-DatabaseDevelopmentRehabProgram.docx
Logical ERD
Physical ERD (DDL)
Data Definition Language
Create Statements
CREATE TABLE Client_Record(
Client_ID Number(18, 0) NOT NULL,
Client_name Nvarchar2(50) NULL,
Address Nvarchar2(50) NULL,
Addiction_Status Nvarchar2(50) NULL,
Addiction_Drug Nvarchar2(50) NULL,
Treatment_given Nvarchar2(100) NULL,
Recovery_status Nvarchar2(50) NULL,
Client_Contact Number(18, 0) NULL,
CONSTRAINT PK_Client_Record PRIMARY KEY
(
Client_ID
)
);
CREATE TABLE Data_base(
ID Number(18, 0) NOT NULL,
Rehabilitation_fee Nvarchar2(50) NULL,
ClientID Number(18, 0) NULL,
OrderID Number(18, 0) NULL,
StakeholderID Number(18, 0) NULL,
CONSTRAINT PK_Database PRIMARY KEY
(
ID
)
);
CREATE TABLE Financial_information(
OrderID Number(18, 0) NOT NULL,
CustomerID Number(18, 0) NULL,
FDate date NULL,
CONSTRAINT PK_Financial_information PRIMARY KEY
(
OrderID
)
);
CREATE TABLE Rehabilitation_Centre(
CentreID Number(18, 0) NOT NULL,
CType Nvarchar2(50) NULL,
CDate date NULL,
CONSTRAINT PK_Rehabilitation_Centre PRIMARY KEY
(
CentreID
)
);
CREATE TABLE Rehabilitation_Team(
MemberID Number(18, 0) NOT NULL,
Name Nvarchar2(30) NULL,
Contact Number(18, 0) NULL,
CentreID Number(18, 0) NULL,
CONSTRAINT PK_Rehabilitation_Team PRIMARY KEY
(
MemberID
)
);
CREATE TABLE Stakeholder(
Stakholder_ID Number(18, 0) NOT NULL,
Name Nvarchar2(50) NULL,
Type Nvarchar2(50) NULL,
CID Number(18, 0) NULL,
TID Number(18, 0) NULL,
FID Number(18, 0) NULL,
CLID Number(18, 0) NULL,
CONSTRAINT PK_Stakeholder PRIMARY KEY
(
Stakholder_ID
)
);
Alter Statements
ALTER TABLE Data_base ADD FOREIGN KEY (ClientID) REFERENCES Client_Record(Client_ID);
ALTER TABLE Data_base ADD FOREIGN KEY (OrderID) REFERENCES Financial_information(OrderID);
ALTER TABLE Data_base ADD FOREIGN KEY (StakeholderID) REFERENCES Stakeholder(Stakholder_ID);
ALTER TABLE Rehabilitation_Team ADD FOREIGN KEY (CentreID) REFERENCES Rehabilitation_Centre(CentreID);
ALTER TABLE Stakeholder ADD FOREIGN KEY (CLID) REFERENCES Client_Record(Client_ID);
ALTER TABLE Stakeholder ADD FOREIGN KEY (CID) REFERENCES Rehabilitation_Centre(CentreID);
ALTER TABLE Stakeholder ADD FOREIGN KEY (FID) REFERENCES Financial_information(OrderID);
ALTER TABLE Stakeholder ADD FOREIGN KEY (TID) REFERENCES Rehabilitation_Team(MemberID);
Data Manipulation Language
Insert Statements
Rehabilitation Centre
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES (1,'Weed', '04-MAR-2018');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES (2,'Marijunana', '28-JUN-2019');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES (3,'Marijunana', '26-MAR-2019');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES (4,'Injections and Drug', '12-MAR-2020');
INSERT INTO Rehabilitation_Centre (CentreID ,CType ,CDate) VALUES (5,'Sex and Masturbation', '22-MAR-2019');
Rehabilitation Team
INSERT INTO Rehabilitation_Team (MemberID , Name, Contact, CentreID) VALUES (1,'Samia', 9113234054384, 5);
INSERT INTO Rehabilitation_Team (MemberID , Name, Contact, CentreID) VALUES (2,'Troy', 912903949192, 2);
INSERT INTO Rehabilitation_Team (MemberID , Name, Contact, CentreID) VALUES (3,'Trish', 9120488956883, 4);
INSERT INTO Rehabilitation_Team (MemberID , Name, Contact, CentreID) VALUES (4,'Yori', 918789999347, 2);
INSERT INTO Rehabilitation_Team (MemberID , Name, Contact, CentreID) VALUES (5,'Harry', 91739384594, 5);
Client Record
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (1,'James Cortney','28 street St Avanue Florida','Starting to take part in activities','Marijuana','Rehab activities and SSLR Injections' ,'10% ',93323789990)
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (3,'Micheal Jimsworth','87 Edgefield Avenue Akron','Starting to normal','Weed','Rehab activities and SSLR Injections' ,'40% ',912849302875)
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (4,'William Shergil','443 Lafayette Dr.Hamtramck','Healing Fast','Injections and Drugs','Rehab activities and SSLR Injections' ,'60% ',938573829384)
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (5,'Charles Hook','850 North Chapel Road Wantagh','Bad situation','Marijuana','Rehab activities and SSLR Injections' ,'0% ',912748596746)
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (6,'Joseph Neon','8438 Branch Lane Mcminnville','Starting to take part in activities','Injections and Drugs','Rehab activities and SSLR Injections' ,'10% ',910384938495)
INSERT INTO Client_Record (Client_ID ,Client_name ,Address ,Addiction_Status ,Addiction_Drug ,Treatment_given ,Recovery_status ,Client_Contact) VALUES (7,'Brian Furry','81 Studebaker St.Stow','Starting to take part in activities','Weed','Rehab activities and SSLR Injections' ,'10% ',991738493857)
Financial Information
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (1,4,'22-APR-2019');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (2,4,'22-APR-2019');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (3,2,'02-AUG-2018');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (4,5,'29-FEB-2012');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (5,6,'19-JUN-2017');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (6,2,'12-DEC-2019');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (7,4,'08-OCT-2018');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (8,4,'02-MAR-2020');
INSERT INTO Financial_information(OrderID ,CustomerID ,FDate) VALUES (9,5,'01-MAY-2018');
Stakeholder
INSERT INTO Stakeholder (Stakholder_ID ,Name ,Type ,CID ,TID ,FID ,CLID) VALUES (1,'Mr Andrew','Family Member', 1, 2, 3, 2);
INSERT INTO Stakeholder (Stakholder_ID ,Name ,Type ,CID ,TID ,FID ,CLID) VALUES (2,'Mr Walter','Family Member', 3, 3, 3, 3);
INSERT INTO Stakeholder (Stakholder_ID ,Name ,Type ,CID ,TID ,FID ,CLID) VALUES (3,'Mr Douglas Silva','Sponsor', 3, 4, 3, 4);
INSERT INTO Stakeholder (Stakholder_ID ,Name ,Type ,CID ,TID ,FID ,CLID) VALUES (4,'Dr Roger Smith','Family Member', 2, 2, 2, 2);
INSERT INTO Stakeholder (Stakholder_ID ,Name ,Type ,CID ,TID ,FID ,CLID) VALUES (5,'Cocomo Ltd','Sponsors', 4, 1, 2, 5);
DATABASE
INSERT INTO Data_base (ID ,Rehabilitation_fee ,ClientID ,OrderID ,StakeholderID) VALUES(1,'50000',3,3,1);
INSERT INTO Data_base (ID ,Rehabilitation_fee ,ClientID ,OrderID ,StakeholderID) VALUES(2,'100000',1,5,1);
INSERT INTO Data_base (ID ,Rehabilitation_fee ,ClientID ,OrderID ,StakeholderID) VALUES(3,'35000',6,2,1);
INSERT INTO Data_base (ID ,Rehabilitation_fee ,ClientID ,OrderID ,StakeholderID) VALUES(4,'150000',4,3,1);
INSERT INTO Data_base (ID ,Rehabilitation_fee ,ClientID ,OrderID ,StakeholderID) VALUES(5,'20000',3,2,1);
Memorandum
1. This week I am able to discuss the main problems and solutions to the problems with my members. Now this week I was able to understand the database entity relation diagrams clearly and I was able to create that ERD with related to my project and that I am able to assist that learning ERD making and understanding the points in the database system to draw the logical erd’s on that basis, then I am also able to understand the difference between the DDL and DML because I have created the database for the project and also have made the physical ERD to represent the database project according to the working condition. Learning points from course has helped me understanding the relation building and understanding and creating the data types and legitimate entries for the system.
2. I have applied the database logics for the creation of database of rehabilitation system and also have applied the method of making the logical ERD with key relations and those relations to make the foreign and primary key integration. Also in database I was able to create the database ERD within the SQL management Studio. I was able to create it with the database I build in it.
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.
4. Course content relating the data manipulation and definition helped me creating the database management system and also to develop the foreign and primary keys relation through which I was able to create the whole system database for the project.
Client_Record
Client_ID
Client_name
Address
Addiction_Status
Addiction_Drug
Treatment_given
Recovery_status
Client_Contact
Data_base
ID
Rehabilitation_fee
ClientID
OrderID
StakeholderID
FK__Data_base__Clien__1CF15040
Financial_information
OrderID
CustomerID
Date
FK__Data_base__Order__1DE57479
Rehabilitation_Centre
CentreID
Type
Date
Rehabilitation_Team
MemberID
Name
Contact
CentreID
FK__Rehabilit__Centr__25869641
Stakeholder
Stakholder_ID
Name
Type
CID
TID
FID
CLID
FK__Stakeholde__CLID__1FCDBCEB
FK__Stakeholder__FID__20C1E124
FK__Stakeholder__TID__22AA2996
FK__Stakeholder__CID__239E4DCF
FK__Data_base__Stake__24927208
