🔒 Closed DELETE OPERATION in Database. (help) Please ;(

[Maginaku]

Up

 

[Maginaku]

Pa help naman po. Wala po kase akong laptop e. Kaylangan kopo mag record na may sample table po ako at ipapakita kopo yung delete operation at kung anong epekto nito sa Database. Sana po may tumulong salamat po. Pasesnya napo. Wala lang po talaga akong laptop. Tia.

 

[ravage]

sige isang spam pa ulit dyan at siguradong dun ka maghahanap ng tutulong sayo sa labas ng phc
ISANG THREAD LANG at mag-hintay ka ng sasagot, WAG MO MADALIIN sa kaka up mo dyan...
ikaw ang bibigyan ng grade ng teacher mo hindi yang tutulong sayo kaya maghintay ka

 

[AnimePahe]

'Yung sa XAMPP ba gagawan ko? Mamaya gawan ko hahaha nakatulog kasi ako.

 

[shucx022]

familiar ka ba sa Foriegn key?

 

[Maginaku]

[XX='AnimePahe, c: 1109656, m: 339224'][/XX] Opo kuys.

Okay lang po kung nakatulog. Hehe. Pasensya napo sa abala ha. C# at JS ang kaya ng CP ko e. Wala naman po ako mahihiraman na Laptop. Kaya eto nalang po pag-asa kong phc namay tumulong. Kaya salamat po talaga ng marami.

 

[Maginaku]

Pa sample nalang po ng table at at iexample po yung DELETE OPERATION. Tapos nalilito din po ako kung paano po eto nakakaapekto sa database. Kahit taglish lang po. Kung gusto nyopo invoice record mas okay po para mas magets pero kung hindi po okay lang po. Kahit explain nyo nalang po dito I type po. Salamat po talaga. Makakatulog po ako ngayun ng mahimbing at alam kong may tutulong na sakin. Salamat po talaga.

 

[1nn0c3ntv5]

Gawa muna tayo ng tables at mag insert sa tables na ginawa natin

[CODE lang="sql" title="Database"]CREATE TABLE Departments
(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

CREATE TABLE Students
(
StudentID INT PRIMARY KEY,
FullName VARCHAR(50) NOT NULL,
Gender VARCHAR(15) NOT NULL,
DepartmentID INT NOT NULL,
FOREIGN KEY (DepartmentID) REFERENCES Departments (DepartmentID) ON DELETE CASCADE
);


INSERT INTO Departments VALUES (1,'Information Technology')
INSERT INTO Departments VALUES (2,'Computer Science')
INSERT INTO Departments VALUES (3,'Information Systems')
INSERT INTO Departments VALUES (4,'Computer Engineering')
INSERT INTO Departments VALUES (5,'Human Resource Management')

INSERT INTO Students VALUES (1,'Illyasviel von Einzbern','Female',1)
INSERT INTO Students VALUES (2,'Emiya Shirou','Male',5)
INSERT INTO Students VALUES (3,'Tohsaka Rin','Female',3)
INSERT INTO Students VALUES (4,'Mashu Kyrielight ','Female',2)
INSERT INTO Students VALUES (5,'Miyu Edelfelt','Female',3)
INSERT INTO Students VALUES (6,'Artoria Pendragon','Male',4)
[/CODE]

Referential Integrity (RI) - The foreign key SHOULD have a matching primary key or in special circumstances, it should be null since it's main goal is to have no orphan records.

Basically kailangan lahat ng ginagamit mong foreign key ay dapat naka set as primary key sa table na pinagbabasihan mo. Tignan mo yung example na binigay ko sayo sa table ng Departments naka set as PRIMARY KEY ang DepartmentID dahil gagamitin siya ni Students table na nakaset as FOREIGN KEY.

Bakit ba natin kailangan mag implement ng Referential Integrity (RI) na yan? Ginagawa to para maiwasan magkaroon ng Orphan Records. Ang Orphan Records ay mga record na kung saan may nakareference sa isang table pero hindi naman siya nag e-exist sa table na pambabasihan mo.

Ito example (Same siya sa original example PERO tinanggal ko ang FOREIGN KEY(DepartmentID) REFERENCES Departments (DepartmentID))

CREATE TABLE Students
(
StudentID INT PRIMARY KEY,
FullName VARCHAR(50) NOT NULL,
Gender VARCHAR(15) NOT NULL,
DepartmentID INT NOT NULL
);

CREATE TABLE Departments
(
DepartmentID INT,
DepartmentName VARCHAR(50)
);

INSERT INTO Departments VALUES (1,'Information Technology')
INSERT INTO Departments VALUES (2,'Computer Science')
INSERT INTO Departments VALUES (3,'Information Systems')
INSERT INTO Departments VALUES (4,'Computer Engineering')
INSERT INTO Departments VALUES (5,'Human Resource Management')

INSERT INTO Students VALUES (1,'Illyasviel von Einzbern','Female',1)
INSERT INTO Students VALUES (2,'Emiya Shirou','Male',5)
INSERT INTO Students VALUES (3,'Tohsaka Rin','Female',3)
INSERT INTO Students VALUES (4,'Mashu Kyrielight ','Female',2)
INSERT INTO Students VALUES (5,'Miyu Edelfelt','Female',3)
INSERT INTO Students VALUES (6,'Artoria Pendragon','Male',4)

Pag nag insert ako sa Students ng

INSERT INTO Students VALUES (9,'Luviagelita Edelfelt','Female',100)

ma i-insert siya sa Students table natin pero yung problema sa example na ito ay hindi siya naka reference through FOREIGN KEY (sa Students table) at PRIMARY KEY (sa Departments table), malaking problema to kasi walang table na pagbabasihan na department si Students table.

--- --- ---
Tuple = Row
example. mag execute tayo ng SELECT * FROM Students may lalabas na 6 rows, meaning, 6 tuples. Kung 10 records ang lumabas, edi 10 tuples.

Balik tayo sa DELETE statement mo

OPERATION: This operation is acceptable and deletes one tuple - meaning isang row mismo ang dinedelete mo.
example:

DELETE * FROM Students
WHERE StudentID = 1 AND DepartmentID = 1

sa example na to isang row mismo ang dinedelete natin, acceptable to kasi yung row na gusto natin idelete ay madedelete mismo dahil nagbigay ka ng madaming conditions.

OPERATION: This deletion is not acceptable, because there are tuples in DepartmentID that refer to this tuple
example:

DELETE FROM Students
WHERE DepartmentID = 3

Halimbawa gusto mong i delete yung record ni Tohsaka Rin (StudentID: 3 | DepartmentID: 3), pero inispecify mo sa condition mo na DepartmentID na 3, may problema to dahil may mga ibang rows na may DepartmentID na number 3 kaya hindi lang si Tohsaka Rin ang madedelete mo kundi pati na rin si Miyu Edelfelt (StudentID: 5 | DepartmentID: 3) dahil nasa DepartmentID 3 din siya.

OPERATION: this deletion will result in even worse referential integrity violations, because the tuple involved is referenced by tuples from the EMPLOYEE, DEPARTMENT, WORKS_ON, and DEPENDENT relations. Basically, mas maraming violation to dahil pag nag execute ka ng DELETE FROM EMPLOYEE WHERE SSN='3334455555' mas marami kang maaapektuhan na rows sa ibang mga table.
(Taken from: You do not have permission to view the full content of this post. Log in or register now.)

 

[Maginaku]

Sige po. Salamat po. God bless.

 

[Maginaku]

Hindi po e.

 

[Maginaku]

Salamat po. God bless po.

 

[AnimePahe]

Meron pa lang mas detailed dito na sagot e hahaha.

 

[AnimePahe]

Sorry natagalan hahaha bagal mag upload e umay sa net hahaha



Hindi kasi ako maka upload sa ibang hosting laging failed kaya nag Google Drive na lang ako.

 

[shucx022]

-- Not a Pro Database person pero here's my simple explanation

CREATE TABLE test.EMPLOYEE(ID int not null auto_increment primary key, name varchar(50) not null);
CREATE TABLE test.WORKS_ON(ID int not null auto_increment primary key, EMPLOYEE_ID int not null, WORK_DESC varchar(255), FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(id) ON DELETE CASCADE);
CREATE TABLE test.DEPARTMENT(ID int not null auto_increment primary key, name varchar(50) not null);

CREATE TABLE test.EMP_DEPARTMENT(ID int not null auto_increment primary key, EMPLOYEE_ID int not null, DEPARTMENT_ID int not null,
FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(id) ON DELETE CASCADE, FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(id));

INSERT test.EMPLOYEE(ID, NAME) VALUES(1, 'Mark'),(2, 'Daniel'),(3, 'Ann');

INSERT test.DEPARTMENT(ID, NAME) VALUES(1, 'IT'),(2, 'ACCOUNTING'),(3, 'HR'),(4, 'Security');

INSERT test.EMP_DEPARTMENT(EMPLOYEE_ID, DEPARTMENT_ID)
values
(1, 1),
(2, 2),
(3, 3);

INSERT test.WORKS_ON(EMPLOYEE_ID, WORK_DESC) values
(1, 'Developing mobile app'),
(2, 'Doing payroll'),
(3, 'Recruiting new employee');

-- Restrict:
-- Same din ng explanation sa pinost mo. Ang Table EMP_DEPARTMENT may foreign key na employee_id at department_id, kapag denelete mo 'HR' in DEPARTMENT table, iprprevent kana kasi existing yung id ng HR department sa EMP_DEPARTMENT Table,
-- iniiwasan kasi ma orphan yung data kaya may mga ganitong constraint. Kapag naman delete 'Security' sa Department table, mag pproceed since hindi sya exist sa EMP_DEPARTMENT.
delete from test.DEPARTMENT where NAME = 'HR';

-- Cascade:
-- Same din sa explanation sa pinost mo ng cascade, kung mapapansin mo yung ddl ng WORKS_ON table, naka ON DELETE CASCADE, pag dinelete si 'Ann' sa EMPLOYEE table, expect mo na madedelete din sya sa WORKS_ON pati na rin sa EMP_DEPARTMENT dahil sa on cascade delete
delete from test.EMPLOYEE where name = 'Ann';

-- Modify:
-- modify mo yung fk to ibang valid tuple,
update test.EMP_DEPARTMENT set department_id = 2 where employee_id = 1; -- modify employee 1 department from IT to ACCOUNTING
-- then delete mo na yung IT
delete from test.DEPARTMENT where NAME = 'IT';
-- itong modify hindi sya advisable actually(unlogical kung imomodify before i delete), just to show lang siguro kung pano nag wwork yung delete if wala nang existing na tuples na gumagamit sa kanya.

Hope it helps

 

[Maginaku]

[XX='AnimePahe, c: 1110203, m: 339224'][/XX] need kopo ng record paps e.

 

[Maginaku]

Paps ganto po lumalabas pede po

Sorry natagalan hahaha bagal mag upload e umay sa net hahaha



Hindi kasi ako maka upload sa ibang hosting laging failed kaya nag Google Drive na lang ako.

Drompb nalang mas mabilis po doon. Hehe. Pasensya po ayaw po ma open e.

 

[Maginaku]

You do not have permission to view the full content of this post. Log in or register now.

Jan po paps. Sa Dromp mabilis lang po. Pasensya napo. Hindi kopo maaccess sa Gdrive

 

[Maginaku]

Salamat po paps. God bless po.

 

[Maginaku]

Paps. Ano po itong picture na maraming table? Para saan po sya? Ilalagay kopa poba sya sa explanation ko?

 

[Maginaku]

Yan pong maraming table ayan pobang nasa taas nyan ang explanation?