introduction Database, Clinic system

User Generated

nffvtazrag055

Computer Science

Description

Topic: Clinic syste.

1- introduction.

A-Description of the clinic

B- What are the functions of the clinic.

2- Three problems get in the clinic and solve the problem in points and every problem and solve them at the point as shown in the example file


Part 1: A- case study

B- E_R = Entity relationship. 1- dector, 2- pharmacist, 3- medicine, 4- prescription, 5- patients, 6- paying

C- logical Schema

Part 2:

SQL = structured query language.

DDL = data definition language.

DML = Data Manipulaion.

Part 3:

PL = procedural language

SQC

* you have use Oracle SQL Developer application.


* I will attach file called sample for the steps only .

* i will attach also the case study its about the (clinic system )

D- when you finish from my assignment i have to submit in turnitin and that website its looking or searching in my assignment words if i taken from any resource of ( internet, students , books, newspaper ...) so you have be careful in that to not become higher then 10%






Unformatted Attachment Preview

Introduction to Databases ITDR2106 Assigment-part-1 Hostel System Introduction: Hostel is a building for girls students in Rustaq college to stay in it and it is a near from the college. It has 4 units and in each units it has 3 floors. In addition, in each floor it has 10 room and one TV room. In the beginning in the hostel it has a reception for students to have a promotion to go out and return to the hostel. In the hostel there are many problems that girls suffer from them. The one of them is late to get a promotion to go out the hostel. The objectives of this system are: 1.To solve the crowded girls while they get a promotion paper to go home. 2. To exchange the promotion paper process by electronic process using finger print. 3. The electronic process convert the information about the student from supervisor to the guard in easy way. Schema hostel system: • Hostel(SID,FULLNAME,FLOURNUM,ROOMNUM) • Student(SID,FNAME,LNAME,STUYEAR,STUADRESS,MROOMNUM,STUMAJOR) • ROOM(ROOMNUM , STUNUM ) • • Furniture(ROOMNUM,ITEM,FUNAME) • Registration(SID,SNAME,YEAR,PHONE,DATE,ROOM) ERD: Room Pk Registration Pk ROOMNUM SNAME YEAR ROOM PHONE DAY STUNUM Hostel Furniture Pk Student ROOMNUM FUNAME ITEM SID Pk Pk No-Of-items SID SID FNAME LNAME STYEAR STUMAJOR STUADDRESS ROOMNUM FULLNAME FLOURNUM ROOMNUM Introduction to Databases ITDR2106 Assigment-part-2 INSERT TABLE ROOM CREATE TABLE ROOM(ROOMNUM NUMBER (4) PRIMARY KEY, STUNUM NUMBER (4)); INSERT INTO ROOM VALUES(1,3); INSERT INTO ROOM VALUES(2,2); INSERT INTO ROOM VALUES(3,2); INSERT INTO ROOM VALUES(4,2); INSERT INTO ROOM VALUES(5,2); INSERT INTO ROOM VALUES(6,3); INSERT INTO ROOM VALUES(7,2); INSERT INTO ROOM VALUES(8,2); INSERT INTO ROOM VALUES(9,3); INSERT INTO ROOM VALUES(10,2); SELECT*FROM ROOM; INSERT TABLE STUDENT CREATE TABLE STUDENT (SID NUMBER (10) PRIMARY KEY, FNAME VARCHAR (10),LNAME VARCHAR(10), STYEAR NUMBER (4), STUMAJOR VARCHAR (10), STUADDRESS VARCHAR (10),ROOMNUM REFERENCES ROOM); INSERT INTO STUDENT VALUES(2014193021,'ASMA','SALEM',2014,'IT','ALRUSTAQ',1); INSERT INTO STUDENT VALUES(2014193028,'AFRAH','HILAL',2014,'ENGLISH','ALRUSTAQ',1); INSERT INTO STUDENT VALUES(2014193022,'NOOR','ALI',2014,'IT','ALRUSTAQ',1); INSERT INTO STUDENT VALUES(2013193029,'NOUF','SALEM',2013,'IT','SOHAR',2); INSERT INTO STUDENT VALUES(2013193023,'ASMA','ALI',2013,'IBA','SOHAR',2); INSERT INTO STUDENT VALUES(2013193025,'YARA','NASER',2012,'IBA','SOHAR',3); INSERT INTO STUDENT VALUES(2012193027,'SAMYA','MOHAMMED',2012,'IBA','IBRI',3); INSERT INTO STUDENT VALUES(2014193113,'AMJAAD','DARWISH',2014,'IT','ALRUSTAQ',4); INSERT INTO STUDENT VALUES(2014193345,'ABEER','SULIMAN',2014,'IT','SOUR',4); INSERT INTO STUDENT VALUES(2011193002,'ASMA','SALEM',2011,'ENGLISH','IBRI',5); INSERT INTO STUDENT VALUES(2011193003,'NOOR','NOOH',2011,'IT','MUSCAT',5); INSERT INTO STUDENT VALUES(2011193009,'NOOF','HAMED',2011,'IBA','MUSCAT',6); INSERT INTO STUDENT VALUES(2014193023,'SALWA','NASER',2014,'IT','NAKHAL',6); INSERT INTO STUDENT VALUES(2012193056,'ANWAR','ALI',2012,'ENGLISH','NAKHAL',6); INSERT INTO STUDENT VALUES(2013193654,'SARA','HAMED',2013,'IBA','SUR',7); INSERT INTO STUDENT VALUES(2013193543,'BUTHAINA','MARHOON',2013,'ENGLISH','SUR',7); INSERT INTO STUDENT VALUES(2013193123,'SALAMA','RASHID',2013,'IBA','MUSQAT',8); INSERT INTO STUDENT VALUES(2013193123,'SUMAIA','ALI',2013,'IBA','SAHAM',8); INSERT INTO STUDENT VALUES(2013193111,'REEM','AHMED',2013,'IT','SAHAM',9); INSERT INTO STUDENT VALUES(2013193504,'MAHA','KHALID',2013,'ENGLISH','SAHAM',9); INSERT INTO STUDENT VALUES(201319332,'NOOR','OMAR',2013,'IBA','NAKHAL',9); INSERT INTO STUDENT VALUES(2014193650,'INTISAR','HAMED',2014,'ENGLISH','ALRUSTAQ',10); INSERT INTO STUDENT VALUES(2014193123,'MARWA','SALEM',2014,'ENGLISH','IBRI',10); SELECT*FROM STUDENT; INSERT TABLE HOSTEL CREATE TABLE HOSTEL(SID NUMBER (10) PRIMARY KEY, FULLNAME VARCHAR (20), FLOURNUM NUMBER (3),ROOMNUM REFERENCES ROOM); INSERT INTO HOSTEL VALUES (2013193654,'SARA HAMED', 2,7); INSERT INTO HOSTEL VALUES (2013193543,'BUTHAINA MARHOON', 2,7); INSERT INTO HOSTEL VALUES (2013193123,'SALAMA RASHID', 1,8); INSERT INTO HOSTEL VALUES (201319332,'NOOR OMAR', 1,9); INSERT INTO HOSTEL VALUES (2014193028,'AFRAH HILAL', 2,1); INSERT INTO HOSTEL VALUES (2013193023,'ASMA ALI', 1,2); INSERT INTO HOSTEL VALUES (2014193113,'AMJAAD DARWISH', 2,4); INSERT INTO HOSTEL VALUES (2014193345,'ABEER SULIMAN', 2,4); SELECT*FROM HOSTEL; INSERT TABLE FURNITURE CREATE TABLE FURNITURE(ROOMNUM NUMBER (4) PRIMARY KEY, FUNAME VARCHAR(10), ITEM NUMBER(5)); INSERT INTO FURNITURE VALUES(10,'TABLE', 1); INSERT INTO FURNITURE VALUES(9,'TABLE', 2); INSERT INTO FURNITURE VALUES(8,'CHAIR', 1); INSERT INTO FURNITURE VALUES(7,'TABLE', 2); INSERT INTO FURNITURE VALUES(6,'CHAIR', 1); INSERT INTO FURNITURE VALUES(5,'TABLE', 3); INSERT INTO FURNITURE VALUES(4,'CHAIR', 2); INSERT INTO FURNITURE VALUES(3,'TABLE', 3); INSERT INTO FURNITURE VALUES(2,'CHAIR', 2); INSERT INTO FURNITURE VALUES(1,'CHAIR', 2); select *from FURNITURE; INSERT TABLE REGISTRATION CREATE TABLE REGISTRATION( SID NUMBER(10)PRIMARY KEY, SNAME VARCHAR2(15), ROOM NUMBER(3), YEAR NUMBER(4), PHONE NUMBER(9), DAY DATE ); INSERT INTO REGISTRATION VALUES(2013193654,'SARA',7,2014,91827646,'25/october/2015'); INSERT INTO REGISTRATION VALUES(2014193021,'ASMAA',7,2014,97142525,'10/october/2015'); INSERT INTO REGISTRATION VALUES(2014193123,'MARWA',10,2011,92183774,'22/october/2015'); INSERT INTO REGISTRATION VALUES(2013193111,'REEM',9,2012,92838741,'1/october/2015'); INSERT INTO REGISTRATION VALUES(2011193009,'NOOF',6,2013,91773646,'1/november/2015'); INSERT INTO REGISTRATION VALUES(2013193025,'YARA',3,2013,97176366,'15/october/2016'); INSERT INTO REGISTRATION VALUES(2014193028,'AFRAH',1,2011,91887277,'5/november/2016'); SELECT*FROM REGISTRATION; 1. select a horizontal and vertical subset of a table SELECT SID ,FULLNAME, FLOURNUM, ROOMNUM FROM HOSTEL WHERE SID=2013193123; SELECT FULLNAME FROM HOSTEL; 2 ) join 2 tables and project a reasonable subset of the columns SELECT * FROM STUDENT NATURAL JOIN REGISTRATIONN ; 3. a SELECT using LIKE on a text field SELECT * FROM HOSTEL WHERE FULLNAME LIKE 'A%' ; SELECT * FROM HOSTEL WHERE FULLNAME LIKE '%A%' ; 4. a query using GROUP BY SELECT STUADDRESS FROM STUDENT GROUP BY STUADDRESS; 5. a query using HAVING SELECT SID FROM STUDENT GROUP BY SID HAVING SID='2014193345'; 6. a query using ORDER BY SELECT FUNAME, ITEM FROM FURNITURE ORDER BY ITEM; 7. a query using a subquery SELECT FNAME, STYEAR FROM STUDENT WHERE STYEAR IN (SELECT STYEAR FROM ROOM WHERE STYEAR=2014); 8. a query using a correlated subquery SELECT a.FNAME,a.LNAME,a.STYEAR,a.SID FROM STUDENT a WHERE a.SID=( SELECT b.SID FROM REGISTRATIONN b WHERE b.PHONE='91887277'); 9. a query involving COUNT(*) SELECT COUNT (*)FROM REGISTRATIONN; 10. a query involving another aggregate function SELECT MAX(ITEM)FROM FURNITURE; 11. a selective DELETE DELETE FROM STUDENT WHERE SID='2013193654'; 12. a selective UPDATE UPDTE FURNITURE SET ITEM= '2'; 13. a SELECT on a DATE column (note: use the TO_DATE function) SELECT SNAME,DAY FROM REGISTRATION WHERE DAY>=('01-NOV-15') ORDER BY DAY; 14. a SELECT using an inequality SELECT SID,FULLNAME,ROOMNUM FROM HOSTEL WHERE FLOURNUM=1; Introduction to Databases ITDR2106 Assigment-part-3 *Trigger change the number of room when student change her room CREATE TABLE CHANGEROOMMM AS SELECT * FROM ROOM WHERE ROOMNUM=2; CREATE OR REPLACE TRIGGER ROOM_CHANGE3 AFTER UPDATE OF ROOMNUM ON ROOM FOR EACH ROW BEGIN INSERT INTO CHANGEROOMMM VALUES(:OLD.ROOMNUM,:OLD.STUNUM); END; / SELECT*FROM CHANGEROOM; UPDATE CHANGEROOM SET ROOMNUM=3 WHERE STUNUM=2; SELECT*FROM CHANGEROOM; Trigger remove old (GRADUATE) STUDENT from student table to new table called GRADUATE_STUDENTT CREATE TABLE GRADUATE_STUDENTT(ID NUMBER (12) PRIMARY KEY, STUDENTNAME VARCHAR(20), PHONE NUMBER (9)); CREATE or REPLACE TRIGGER OLD_STUDENT_trigger AFTER INSERT ON STUDENT FOR EACH ROW BEGIN INSERT INTO GRADUATE_STUDENTT VALUES (:old.ID, :old.STUDENTNAME, :old.PHONE); END; insert into GRADUATE_STUDENTT values(2011193003,'muna',91122332); select*from GRADUATE_STUDENTT; *Cursor RETURN THE STUDENT ID, YEAR AND MAJOR DECLARE CURSOR STU IS SELECT * FROM STUDENT; DREC STUDENT%ROWTYPE; BEGIN IF NOT STU%ISOPEN THEN OPEN STU; END IF; FETCH STU INTO DREC ; WHILE STU%FOUND LOOP DBMS_OUTPUT.PUT_LINE(DREC.SID||' ' || DREC.STYEAR||' '|| DREC.STUMAJOR); FETCH STU INTO DREC ; END LOOP; END; / *cursor RETYRN FIRST NAME AND LAST NAME OF STUDENT WHEN SEND ROOM NUMBER declare cursor Student_cursor is select FNAME ,LNAME from STUDENT where ROOMNUM in (1); begin for i in Student_cursor loop DBMS_OUTPUT.PUT_line('FNAME: '||i.FNAME||' LNAME: '||i.LNAME); end loop; end; FUNCTION RETURN THE ITEM IN EVERY ROOM WHEN SEND ROOM NUMBER CREATE OR REPLACE FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE) RETURN VARCHAR IS NS FURNITURE.ITEM%TYPE; BEGIN SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1; RETURN NS; END; DECLARE S FURNITURE.ITEM%TYPE; BEGIN S:=FUN_ROOM(4); DBMS_OUTPUT.PUT_LINE('THE NUMBER OF ITEM IS:' ||S); END; / Function RETURN NUMBER OF ROOM WHEN SEND STUDENT ID CREATE OR REPLACE FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE) RETURN VARCHAR IS NS STUDENT.ROOMNUM%TYPE; BEGIN SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021; RETURN NS; END; DECLARE S STUDENT.ROOMNUM%TYPE; BEGIN S:=FUN_STUDENT(2014193021); DBMS_OUTPUT.PUT_LINE('THIS STUDENT IS IN ROOM NUMBER :' ||S); END; / PROCEDURE RETURN NAME OF STUDENT WHEN SEND STUDENT ID CREATE OR REPLACE PROCEDURE PRC_DNAME IS DN STUDENT.FNAME%TYPE; BEGIN SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021; DBMS_OUTPUT.PUT_LINE(DN); END; / BEGIN PRC_DNAME; END; / PROCEDURE RETURN DATE OF BOOKING THE ROOM WHEN SEND STUDENT NAME CREATE OR REPLACE PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS RE REGISTRATION.DAY%TYPE; BEGIN SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA'; DBMS_OUTPUT.PUT_LINE(RE); END; / BEGIN PRC_REG(7); END; / Package CONTAIN 2 function CREATE OR REPLACE PACKAGE package_stu AS FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE) RETURN VARCHAR; FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE) RETURN VARCHAR; END PACKAGE_STU; / CREATE OR REPLACE PACKAGE BODY package_stu AS FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE) RETURN VARCHAR IS NS FURNITURE.ITEM%TYPE; BEGIN SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1; RETURN NS; END; FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE) RETURN VARCHAR IS NS STUDENT.ROOMNUM%TYPE; BEGIN SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021; RETURN NS; END; End; SELECT package_stu.FUN_ROOM(1) FROM DUAL; PACKAGE CONTAIN 2 PROCEDURE CREATE OR REPLACE PACKAGE package_pr_HOSTEL AS PROCEDURE PRC_DNAME ; PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE); END package_pr_HOSTEL; / CREATE OR REPLACE PACKAGE BODY package_pr_HOSTEL AS PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS RE REGISTRATION.DAY%TYPE; BEGIN SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA'; DBMS_OUTPUT.PUT_LINE(RE); END; PROCEDURE PRC_DNAME IS DN STUDENT.FNAME%TYPE; BEGIN SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021; DBMS_OUTPUT.PUT_LINE(DN); END; End; BEGIN package_pr_HOSTEL.PRC_DNAME; END; Introduction to Databases ITDR2106 Assigment-part-1 Hostel System Introduction: Hostel is a building for girls students in Rustaq college to stay in it and it is a near from the college. It has 4 units and in each units it has 3 floors. In addition, in each floor it has 10 room and one TV room. In the beginning in the hostel it has a reception for students to have a promotion to go out and return to the hostel. In the hostel there are many problems that girls suffer from them. The one of them is late to get a promotion to go out the hostel. The objectives of this system are: 1.To solve the crowded girls while they get a promotion paper to go home. 2. To exchange the promotion paper process by electronic process using finger print. 3. The electronic process convert the information about the student from supervisor to the guard in easy way. Schema hostel system: • Hostel(SID,FULLNAME,FLOURNUM,ROOMNUM) • Student(SID,FNAME,LNAME,STUYEAR,STUADRESS,MROOMNUM,STUMAJOR) • ROOM(ROOMNUM , STUNUM ) • • Furniture(ROOMNUM,ITEM,FUNAME) • Registration(SID,SNAME,YEAR,PHONE,DATE,ROOM) ERD: Room Pk Registration Pk ROOMNUM SNAME YEAR ROOM PHONE DAY STUNUM Hostel Furniture Pk Student ROOMNUM FUNAME ITEM SID Pk Pk No-Of-items SID SID FNAME LNAME STYEAR STUMAJOR STUADDRESS ROOMNUM FULLNAME FLOURNUM ROOMNUM Introduction to Databases ITDR2106 Assigment-part-2 INSERT TABLE ROOM CREATE TABLE ROOM(ROOMNUM NUMBER (4) PRIMARY KEY, STUNUM NUMBER (4)); INSERT INTO ROOM VALUES(1,3); INSERT INTO ROOM VALUES(2,2); INSERT INTO ROOM VALUES(3,2); INSERT INTO ROOM VALUES(4,2); INSERT INTO ROOM VALUES(5,2); INSERT INTO ROOM VALUES(6,3); INSERT INTO ROOM VALUES(7,2); INSERT INTO ROOM VALUES(8,2); INSERT INTO ROOM VALUES(9,3); INSERT INTO ROOM VALUES(10,2); SELECT*FROM ROOM; INSERT TABLE STUDENT CREATE TABLE STUDENT (SID NUMBER (10) PRIMARY KEY, FNAME VARCHAR (10),LNAME VARCHAR(10), STYEAR NUMBER (4), STUMAJOR VARCHAR (10), STUADDRESS VARCHAR (10),ROOMNUM REFERENCES ROOM); INSERT INTO STUDENT VALUES(2014193021,'ASMA','SALEM',2014,'IT','ALRUSTAQ',1); INSERT INTO STUDENT VALUES(2014193028,'AFRAH','HILAL',2014,'ENGLISH','ALRUSTAQ',1); INSERT INTO STUDENT VALUES(2014193022,'NOOR','ALI',2014,'IT','ALRUSTAQ',1); INSERT INTO STUDENT VALUES(2013193029,'NOUF','SALEM',2013,'IT','SOHAR',2); INSERT INTO STUDENT VALUES(2013193023,'ASMA','ALI',2013,'IBA','SOHAR',2); INSERT INTO STUDENT VALUES(2013193025,'YARA','NASER',2012,'IBA','SOHAR',3); INSERT INTO STUDENT VALUES(2012193027,'SAMYA','MOHAMMED',2012,'IBA','IBRI',3); INSERT INTO STUDENT VALUES(2014193113,'AMJAAD','DARWISH',2014,'IT','ALRUSTAQ',4); INSERT INTO STUDENT VALUES(2014193345,'ABEER','SULIMAN',2014,'IT','SOUR',4); INSERT INTO STUDENT VALUES(2011193002,'ASMA','SALEM',2011,'ENGLISH','IBRI',5); INSERT INTO STUDENT VALUES(2011193003,'NOOR','NOOH',2011,'IT','MUSCAT',5); INSERT INTO STUDENT VALUES(2011193009,'NOOF','HAMED',2011,'IBA','MUSCAT',6); INSERT INTO STUDENT VALUES(2014193023,'SALWA','NASER',2014,'IT','NAKHAL',6); INSERT INTO STUDENT VALUES(2012193056,'ANWAR','ALI',2012,'ENGLISH','NAKHAL',6); INSERT INTO STUDENT VALUES(2013193654,'SARA','HAMED',2013,'IBA','SUR',7); INSERT INTO STUDENT VALUES(2013193543,'BUTHAINA','MARHOON',2013,'ENGLISH','SUR',7); INSERT INTO STUDENT VALUES(2013193123,'SALAMA','RASHID',2013,'IBA','MUSQAT',8); INSERT INTO STUDENT VALUES(2013193123,'SUMAIA','ALI',2013,'IBA','SAHAM',8); INSERT INTO STUDENT VALUES(2013193111,'REEM','AHMED',2013,'IT','SAHAM',9); INSERT INTO STUDENT VALUES(2013193504,'MAHA','KHALID',2013,'ENGLISH','SAHAM',9); INSERT INTO STUDENT VALUES(201319332,'NOOR','OMAR',2013,'IBA','NAKHAL',9); INSERT INTO STUDENT VALUES(2014193650,'INTISAR','HAMED',2014,'ENGLISH','ALRUSTAQ',10); INSERT INTO STUDENT VALUES(2014193123,'MARWA','SALEM',2014,'ENGLISH','IBRI',10); SELECT*FROM STUDENT; INSERT TABLE HOSTEL CREATE TABLE HOSTEL(SID NUMBER (10) PRIMARY KEY, FULLNAME VARCHAR (20), FLOURNUM NUMBER (3),ROOMNUM REFERENCES ROOM); INSERT INTO HOSTEL VALUES (2013193654,'SARA HAMED', 2,7); INSERT INTO HOSTEL VALUES (2013193543,'BUTHAINA MARHOON', 2,7); INSERT INTO HOSTEL VALUES (2013193123,'SALAMA RASHID', 1,8); INSERT INTO HOSTEL VALUES (201319332,'NOOR OMAR', 1,9); INSERT INTO HOSTEL VALUES (2014193028,'AFRAH HILAL', 2,1); INSERT INTO HOSTEL VALUES (2013193023,'ASMA ALI', 1,2); INSERT INTO HOSTEL VALUES (2014193113,'AMJAAD DARWISH', 2,4); INSERT INTO HOSTEL VALUES (2014193345,'ABEER SULIMAN', 2,4); SELECT*FROM HOSTEL; INSERT TABLE FURNITURE CREATE TABLE FURNITURE(ROOMNUM NUMBER (4) PRIMARY KEY, FUNAME VARCHAR(10), ITEM NUMBER(5)); INSERT INTO FURNITURE VALUES(10,'TABLE', 1); INSERT INTO FURNITURE VALUES(9,'TABLE', 2); INSERT INTO FURNITURE VALUES(8,'CHAIR', 1); INSERT INTO FURNITURE VALUES(7,'TABLE', 2); INSERT INTO FURNITURE VALUES(6,'CHAIR', 1); INSERT INTO FURNITURE VALUES(5,'TABLE', 3); INSERT INTO FURNITURE VALUES(4,'CHAIR', 2); INSERT INTO FURNITURE VALUES(3,'TABLE', 3); INSERT INTO FURNITURE VALUES(2,'CHAIR', 2); INSERT INTO FURNITURE VALUES(1,'CHAIR', 2); select *from FURNITURE; INSERT TABLE REGISTRATION CREATE TABLE REGISTRATION( SID NUMBER(10)PRIMARY KEY, SNAME VARCHAR2(15), ROOM NUMBER(3), YEAR NUMBER(4), PHONE NUMBER(9), DAY DATE ); INSERT INTO REGISTRATION VALUES(2013193654,'SARA',7,2014,91827646,'25/october/2015'); INSERT INTO REGISTRATION VALUES(2014193021,'ASMAA',7,2014,97142525,'10/october/2015'); INSERT INTO REGISTRATION VALUES(2014193123,'MARWA',10,2011,92183774,'22/october/2015'); INSERT INTO REGISTRATION VALUES(2013193111,'REEM',9,2012,92838741,'1/october/2015'); INSERT INTO REGISTRATION VALUES(2011193009,'NOOF',6,2013,91773646,'1/november/2015'); INSERT INTO REGISTRATION VALUES(2013193025,'YARA',3,2013,97176366,'15/october/2016'); INSERT INTO REGISTRATION VALUES(2014193028,'AFRAH',1,2011,91887277,'5/november/2016'); SELECT*FROM REGISTRATION; 1. select a horizontal and vertical subset of a table SELECT SID ,FULLNAME, FLOURNUM, ROOMNUM FROM HOSTEL WHERE SID=2013193123; SELECT FULLNAME FROM HOSTEL; 2 ) join 2 tables and project a reasonable subset of the columns SELECT * FROM STUDENT NATURAL JOIN REGISTRATIONN ; 3. a SELECT using LIKE on a text field SELECT * FROM HOSTEL WHERE FULLNAME LIKE 'A%' ; SELECT * FROM HOSTEL WHERE FULLNAME LIKE '%A%' ; 4. a query using GROUP BY SELECT STUADDRESS FROM STUDENT GROUP BY STUADDRESS; 5. a query using HAVING SELECT SID FROM STUDENT GROUP BY SID HAVING SID='2014193345'; 6. a query using ORDER BY SELECT FUNAME, ITEM FROM FURNITURE ORDER BY ITEM; 7. a query using a subquery SELECT FNAME, STYEAR FROM STUDENT WHERE STYEAR IN (SELECT STYEAR FROM ROOM WHERE STYEAR=2014); 8. a query using a correlated subquery SELECT a.FNAME,a.LNAME,a.STYEAR,a.SID FROM STUDENT a WHERE a.SID=( SELECT b.SID FROM REGISTRATIONN b WHERE b.PHONE='91887277'); 9. a query involving COUNT(*) SELECT COUNT (*)FROM REGISTRATIONN; 10. a query involving another aggregate function SELECT MAX(ITEM)FROM FURNITURE; 11. a selective DELETE DELETE FROM STUDENT WHERE SID='2013193654'; 12. a selective UPDATE UPDTE FURNITURE SET ITEM= '2'; 13. a SELECT on a DATE column (note: use the TO_DATE function) SELECT SNAME,DAY FROM REGISTRATION WHERE DAY>=('01-NOV-15') ORDER BY DAY; 14. a SELECT using an inequality SELECT SID,FULLNAME,ROOMNUM FROM HOSTEL WHERE FLOURNUM=1; Introduction to Databases ITDR2106 Assigment-part-3 *Trigger change the number of room when student change her room CREATE TABLE CHANGEROOMMM AS SELECT * FROM ROOM WHERE ROOMNUM=2; CREATE OR REPLACE TRIGGER ROOM_CHANGE3 AFTER UPDATE OF ROOMNUM ON ROOM FOR EACH ROW BEGIN INSERT INTO CHANGEROOMMM VALUES(:OLD.ROOMNUM,:OLD.STUNUM); END; / SELECT*FROM CHANGEROOM; UPDATE CHANGEROOM SET ROOMNUM=3 WHERE STUNUM=2; SELECT*FROM CHANGEROOM; Trigger remove old (GRADUATE) STUDENT from student table to new table called GRADUATE_STUDENTT CREATE TABLE GRADUATE_STUDENTT(ID NUMBER (12) PRIMARY KEY, STUDENTNAME VARCHAR(20), PHONE NUMBER (9)); CREATE or REPLACE TRIGGER OLD_STUDENT_trigger AFTER INSERT ON STUDENT FOR EACH ROW BEGIN INSERT INTO GRADUATE_STUDENTT VALUES (:old.ID, :old.STUDENTNAME, :old.PHONE); END; insert into GRADUATE_STUDENTT values(2011193003,'muna',91122332); select*from GRADUATE_STUDENTT; *Cursor RETURN THE STUDENT ID, YEAR AND MAJOR DECLARE CURSOR STU IS SELECT * FROM STUDENT; DREC STUDENT%ROWTYPE; BEGIN IF NOT STU%ISOPEN THEN OPEN STU; END IF; FETCH STU INTO DREC ; WHILE STU%FOUND LOOP DBMS_OUTPUT.PUT_LINE(DREC.SID||' ' || DREC.STYEAR||' '|| DREC.STUMAJOR); FETCH STU INTO DREC ; END LOOP; END; / *cursor RETYRN FIRST NAME AND LAST NAME OF STUDENT WHEN SEND ROOM NUMBER declare cursor Student_cursor is select FNAME ,LNAME from STUDENT where ROOMNUM in (1); begin for i in Student_cursor loop DBMS_OUTPUT.PUT_line('FNAME: '||i.FNAME||' LNAME: '||i.LNAME); end loop; end; FUNCTION RETURN THE ITEM IN EVERY ROOM WHEN SEND ROOM NUMBER CREATE OR REPLACE FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE) RETURN VARCHAR IS NS FURNITURE.ITEM%TYPE; BEGIN SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1; RETURN NS; END; DECLARE S FURNITURE.ITEM%TYPE; BEGIN S:=FUN_ROOM(4); DBMS_OUTPUT.PUT_LINE('THE NUMBER OF ITEM IS:' ||S); END; / Function RETURN NUMBER OF ROOM WHEN SEND STUDENT ID CREATE OR REPLACE FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE) RETURN VARCHAR IS NS STUDENT.ROOMNUM%TYPE; BEGIN SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021; RETURN NS; END; DECLARE S STUDENT.ROOMNUM%TYPE; BEGIN S:=FUN_STUDENT(2014193021); DBMS_OUTPUT.PUT_LINE('THIS STUDENT IS IN ROOM NUMBER :' ||S); END; / PROCEDURE RETURN NAME OF STUDENT WHEN SEND STUDENT ID CREATE OR REPLACE PROCEDURE PRC_DNAME IS DN STUDENT.FNAME%TYPE; BEGIN SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021; DBMS_OUTPUT.PUT_LINE(DN); END; / BEGIN PRC_DNAME; END; / PROCEDURE RETURN DATE OF BOOKING THE ROOM WHEN SEND STUDENT NAME CREATE OR REPLACE PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS RE REGISTRATION.DAY%TYPE; BEGIN SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA'; DBMS_OUTPUT.PUT_LINE(RE); END; / BEGIN PRC_REG(7); END; / Package CONTAIN 2 function CREATE OR REPLACE PACKAGE package_stu AS FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE) RETURN VARCHAR; FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE) RETURN VARCHAR; END PACKAGE_STU; / CREATE OR REPLACE PACKAGE BODY package_stu AS FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE) RETURN VARCHAR IS NS FURNITURE.ITEM%TYPE; BEGIN SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1; RETURN NS; END; FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE) RETURN VARCHAR IS NS STUDENT.ROOMNUM%TYPE; BEGIN SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021; RETURN NS; END; End; SELECT package_stu.FUN_ROOM(1) FROM DUAL; PACKAGE CONTAIN 2 PROCEDURE CREATE OR REPLACE PACKAGE package_pr_HOSTEL AS PROCEDURE PRC_DNAME ; PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE); END package_pr_HOSTEL; / CREATE OR REPLACE PACKAGE BODY package_pr_HOSTEL AS PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS RE REGISTRATION.DAY%TYPE; BEGIN SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA'; DBMS_OUTPUT.PUT_LINE(RE); END; PROCEDURE PRC_DNAME IS DN STUDENT.FNAME%TYPE; BEGIN SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021; DBMS_OUTPUT.PUT_LINE(DN); END; End; BEGIN package_pr_HOSTEL.PRC_DNAME; END; Simple ette Alle opel additional al more than one Composite identifier Peimary key composed of Attributes te bile the Foot at Entity: Chen and Crow's 4.1 The Attributes of the Topic has the clinic system Database syster Part (1) 1) Case Study 2)_E_R Entity Relationship a) blogical Schema Part (2) DDL Data definition language SQL» DML = Data Manipulation Structured "Puerg language SQL (10) Part (3) PL Procedural Language PLEASE SQC Entities @ Rutlets Payment Melicine of Chesnas Presexplon Dector 2. Pharmacist 9 Medicine 4. Prescription 5. Patients 6. paying
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

At...


Anonymous
Excellent! Definitely coming back for more study materials.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags