Need db expert in answering easy question like ERD

Shirsendu

1702114274.rar

Home >Computer Science homework help >Need db expert in answering easy question like ERD

marvel/MarvelTables.docx

FACULTY

FAC

NUMB

FACNAME

FACADDR

SAL

ARY

EARN

YTD

DEPT

NUMB

DEG

REE

FIELD

CURR

RANK

PLAN

NUMB

DEP

COV

119

209

421

462

485

507

564

683

Martin, Barb

Newton, Jane

Jones. Al

Johnson, Tom

Sparks, Pam

Canton, Rob

Ferris, Tim

Nils, Frank

94 Ridge, Grant, MI

210 Leonard,Ada, MI

121 Colton,Hart, MI

604 46th,Lowell, MI

201 Olive, Holt, MI

5 State, Cutler, MI

16 W. 9th, Troy, MI

46 Creston, Ada, MI

32500

39000

30000

35000

43500

29500

38500

46500

25425

21402

26952

30115

21010

28540

32050

PhD

Comm

Topology

Database

Op. Sys.

Analysis

Volcano

Minerals

Laser

Asst

Asso

Asst

Asso

Prof

Asst

Asso

Prof

DEPARTMENT

DEPT

NUMB

DEPTNAME

DEPTLOC

Computer Sci.

Mathematics

Geology

Physics

408 Kelly Hall

253 Watson Hall

707 Case Tower

118 Font Tower

SPEAKING_TOPICS

FAC

NUMB

TITLE

TMS

GIVEN

LST

GIVEN

119

462

485

507

683

Distributed Database

Why Network Micros?

Communications

The Future of Unix

The Regular Polyhedra

Mount St. Helens

Laser Technology

Nuclear Power ProCon

21-Mar-10

15-May-10

2-Sep-10

13-May-10

15-Oct-09

12-Feb-10

22-May-10

15-Jun-10

JOB_HISTORY

FAC

NUMB

RANK

DATEATT

119

209

421

462

485

507

564

683

Asst

Asso

Asst

Asso

Asst

Asso

Prof

Asst

Asso

Asst

Asso

Prof

15-Sep-96

1-Sep-91

13-Sep-98

15-Aug-98

12-Apr-86

14-Sep-96

2-Apr-80

12-Sep-89

13-Sep-98

12-Sep-96

21-May-91

1-Sep-06

11-Jun-81

2-Aug-89

12-Aug-98

INSURANCE_PLAN

PLAN

NUMB

PLANDESC

Regular

Deluxe

Special

marvel/MarvelTables_updated.docx

FACULTY

FAC

NUMB

FACNAME

FACADDR

SAL

ARY

EARN

YTD

DEPT

NUMB

DEG

REE

FIELD

CURR

RANK

PLAN

NUMB

DEP

COV

119

209

421

462

485

507

564

683

Martin, Barb

Newton, Jane

Jones. Al

Johnson, Tom

Sparks, Pam

Canton, Rob

Ferris, Tim

Nils, Frank

94 Ridge, Grant, MI

210 Leonard,Ada, MI

121 Colton,Hart, MI

604 46th,Lowell, MI

201 Olive, Holt, MI

5 State, Cutler, MI

16 W. 9th, Troy, MI

46 Creston, Ada, MI

32500

39000

30000

35000

43500

29500

38500

46500

25425

21402

26952

30115

21010

28540

32050

PhD

Comm

Topology

Database

Op. Sys.

Analysis

Volcano

Minerals

Laser

Asst

Asso

Asst

Asso

Prof

Asst

Asso

Prof

DEPARTMENT

DEPT

NUMB

DEPTNAME

DEPTLOC

Computer Sci.

Mathematics

Geology

Physics

408 Kelly Hall

253 Watson Hall

707 Case Tower

118 Font Tower

SPEAKING

FAC

NUMB

TITLE

TMS

GIVEN

LST

GIVEN

119

462

485

507

683

Distributed Database

Why Network Micros?

Communications

The Future of Unix

The Regular Polyhedra

Mount St. Helens

Laser Technology

Nuclear Power ProCon

21-Mar-10

15-May-10

2-Sep-10

13-May-10

15-Oct-09

12-Feb-10

22-May-10

15-Jun-10

JOB

FAC

NUMB

RANK

DATEATT

119

209

421

462

485

507

564

683

Asst

Asso

Asst

Asso

Asst

Asso

Prof

Asst

Asso

Asst

Asso

Prof

15-Sep-96

1-Sep-91

13-Sep-98

15-Aug-98

12-Apr-86

14-Sep-96

2-Apr-80

12-Sep-89

13-Sep-98

12-Sep-96

21-May-91

1-Sep-06

11-Jun-81

2-Aug-89

12-Aug-98

INSURANCE

PLAN

NUMB

PLANDESC

Regular

Deluxe

Special

marvel/readme.txt

Marvel College Database 1. Create a folder for marvel college. 2. Create two folders in your marvel college folder. Call them sql and data. (The sql folder is for all the sql files that will create your application. The data folder will house the database.) 3. Go into the data folder and copy the path to this folder by right clicking on the path at the top of the data folder. Select Copy Address. 4. Go back to the sql folder and create a shortcut to sqlplus, by right clicking somewhere inside on the sql folder. Choose New, Shortcut. Click on the Browse button to find the location of the file sqlplus.exe. I had to go to Computer >> Local Disk (C:) >> oraclexe >> app >> oracle >> product >> 11.2.0 >> server >> bin Find and select sqlplus. ( you need to click on the application file called sqlplus.exe ) Click OK, Next, and hit the Finish button. This will give you a short cut to sqlplus. 5. Right click on the sqlplus short cut in your sql folder. Select properties. Using your mouse select the text to the right of Start in: Using the backspace key, delete the selected text in Start in: 6. Click the Apply button at the bottom of this dialog box and then click the OK button. 7. Copy the sql files that you copied from BlackBoard for marvel to this sql folder. 8. You need to edit the create_tablespace.sql file. Right click on this file and choose "open with". Choose notepad to open this file. In the CREATE TABLESPACE command, edit the second line that contains a string. That line looks like this: DATAFILE 'C:\Users\hondas\Documents\school\OracleDataFiles\marvel\data\marvel_data.dbf' You don't have a folder called hondas. Erase part of my path within the quote that says: C:\Users\hondas\Documents\school\OracleDataFiles\marvel\data and paste the path that you copied in step 3. This is the path to your marvel data folder. Add the following text to the end of the copied path, inside the close quote: \marvel_data.dbf' Save the create_tablespace.sql file, replacing the old one in the sql folder. The second line in the CREATE TABLESPACE should now read: DATAFILE 'C:\....\marvel\data\marvel_data.dbf' where \... \ contains the path to your marvel folder. (Make certain that you have the start and end single quotes.) The end of this string contains the name of this datafile that you are creating, namely marvel_data.dbf. Look in the marvel\data folder. You should now see a large file called: marvel_data.dbf 9. Double click on the sqlplus file that is found in the sql folder, to open sqlplus. Login as the system administrator with username system , and whatever you gave as a password when you installed Oracle. My password is admin for the system account. you can type system/admin all at once if you want to. 10. When you are at the sql prompt, run the createTablespace and createUser command files by typing the following after the SQL> prompt: SQL> @createTablespace.sql [ENTER] SQL> @createUser.sql [ENTER] 11. Connect as the new user you just created by typing: SQL> CONN marvel_dba/marvel_dba [ENTER] The SQL-Plus command CONN is short for CONNECT. 12. As the marvel college dba, you can create all the tables and insert records into these tables by typing the following: SQL> @create_tables [ENTER] SQL> @insert_data [ENTER] Now you can play with the database tables. 13. Later, when you do not have a need for this database and you are logged in as marvel_dba, type the following: SQL> @DROP_TABLES [ENTER] SQL> CONN system/admin [ENTER] {Use your own system password. Mine is admin} SQL> @DROP_TABLESPACE [ENTER] SQL> @DROP_USER [ENTER] 14. Now, go to the data folder in your marvel folder and check to see that the filemarvel_data.dbf is gone from this folder!

marvel/sql/aveSalary.sql

SET SERVEROUTPUT ON DECLARE v_ave faculty.salary%TYPE; BEGIN SELECT avg(salary) INTO v_ave FROM faculty WHERE deptNumb = 2; DBMS_OUTPUT.PUT_LINE('Average salary of Department 2 is $' || TO_CHAR(v_ave)); DBMS_OUTPUT.PUT_LINE('bye'); END; / SET SERVEROUTPUT OFF

marvel/sql/create_department.sql

--------------------------------------------------------------- -- File: create_department.sql -- Date Feb 19, 2013 -- Purpose: Create department table for Marvel College --------------------------------------------------------------- -- DROP TABLE DEPARTMENT; PROMPT... Creating department CREATE TABLE DEPARTMENT (DEPTNUMB NUMERIC(2), DEPTNAME VARCHAR2(16), DEPTLOC VARCHAR2(16), CONSTRAINT dept_pk_deptnumb PRIMARY KEY(DEPTNUMB)) /

marvel/sql/create_faculty.sql

--------------------------------------------------------------- -- File: create_faculty.sql -- Date: Feb. 19, 2013 -- Purpose: Create faculty table for Marvel College --------------------------------------------------------------- --DROP TABLE FACULTY; CREATE TABLE FACULTY (FACNUMB NUMERIC(3), FACNAME VARCHAR2(13), FACADDR VARCHAR2(20), SALARY NUMERIC(9,2), EARNYTD NUMERIC(9,2), DEPTNUMB NUMERIC(2), DEGREE CHAR(3), FIELD VARCHAR2(8), CURRRANK CHAR(4), PLANNUMB NUMERIC(2), DEPCOV CHAR(1), CONSTRAINT fac_pk_facnumb PRIMARY KEY(FACNUMB), CONSTRAINT fac_fk_deptnumb FOREIGN KEY (DEPTNUMB) REFERENCES Department(deptnumb), CONSTRAINT fac_fk_plannumb FOREIGN KEY (PLANNUMB) REFERENCES Insurance(plannumb) ) /

marvel/sql/create_insurance.sql

--------------------------------------------------------------- -- File: create_insurance.sql -- Date: Feb 19, 2013 -- Purpose: Create insurance table for Marvel College --------------------------------------------------------------- --DROP TABLE INSURANCE; PROMPT ... Creating insurance CREATE TABLE INSURANCE (PLANNUMB NUMERIC(2), PLANDESC CHAR(8), CONSTRAINT insur_pk_plannumb PRIMARY KEY(PLANNUMB)) /

marvel/sql/create_job.sql

--------------------------------------------------------------- -- File: create_job.sql -- Date: Feb 19, 2013 -- Purpose: Create job history table for Marvel College --------------------------------------------------------------- --DROP TABLE JOB; PROMPT ... Creating job CREATE TABLE JOB (FACNUMB NUMERIC(3), RANK CHAR(4), DATEATT DATE, CONSTRAINT job_pk_facnum_rank PRIMARY KEY (FACNUMB,RANK), CONSTRAINT job_fk_fac_facnumb FOREIGN KEY (FACNUMB) REFERENCES FACULTY(FACNUMB) ) /

marvel/sql/create_speaking.sql

--------------------------------------------------------------- -- File: create_speaking.sql -- Date: Feb 19, 2013 -- Purpose: Create speaking topics table for Marvel College --------------------------------------------------------------- --DROP TABLE SPEAKING; PROMPT ... Creating table speaking CREATE TABLE SPEAKING (FACNUMB NUMERIC(3), TITLE VARCHAR2(23), TMSGIVEN NUMERIC(2), LSTGIVEN DATE, CONSTRAINT spk_pk_facnumb_title_tmsgiv PRIMARY KEY (FACNUMB,TITLE), CONSTRAINT spk_fk_facnumb_fac_facnumb FOREIGN KEY (FACNUMB) REFERENCES FACULTY (FACNUMB) ) /

marvel/sql/create_tables.sql

REM ========================================================= REM file: create_tables.sql REM date: Feb. 19, 2013 REM This sql procedure makes all the relations for marvel REM You need to create department and insurance tables before REM you create table faculty because of the fk constraints REM in faculty. The job_history and speaking_topcis tables REM must be created after faculty also. REM ========================================================= prompt... in create_tables.sql... @@drop_tables @@create_department @@ins_department @@create_insurance @@ins_insurance @@create_faculty @@ins_faculty @@create_job @@ins_job @@create_speaking @@ins_speaking select * from faculty / select * from department / select * from insurance / select * from job / select * from speaking / select table_name from user_tables /

marvel/sql/create_tablespace.sql

REM ================================================================ REM fILE: create_tablespace.sql REM DATE: February 19, 2013 REM PURPOSE: To Create tablespace for Marvel College REM ================================================================ PROMPT ... CREATING TABLESPACE FOR Marvel College CREATE TABLESPACE marvel_data DATAFILE 'C:\Users\hondas\Documents\school\OracleDataFiles\marvel\data\marvel_data.dbf' SIZE 2M DEFAULT STORAGE (INITIAL 8k PCTINCREASE 0) / PROMPT .. Don't forget to create the user for marvel...

marvel/sql/create_user.sql

REM =============================================== REM FILE: create_user.sql REM DATE Feb. 7. 2012 REM PURPOSE: To create a user for marvel college REM =============================================== drop user marvel cascade / create user marvel_dba identified by marvel_dba default tablespace marvel_data / grant dba to marvel_dba / PROMPT ... Now login as marvel_dba and create the tables for marvel ...

marvel/sql/drop_tables.sql

REM ===================================================== REM FILE: drop_tables.sql REM DATE: Feb. 19, 2013 REM PURPOSE: DROP TABLES REM Note: table dropping order is important REM ===================================================== drop table job / drop table speaking / drop table faculty / drop table department / drop table insurance /

marvel/sql/drop_tablespace.sql

REM ================================================================ REM fILE: drop_tablespace.sql REM DATE: February 19, 2013 REM PURPOSE: To Drop tablespace for Marvel College REM This should only be done by the system manager. REM ================================================================ PROMPT ... ARE YOU LOGGED IN AS THE SYSTEM MANAGER?...Hit Enter if yes... PAUSE DROP TABLESPACE marvel_data INCLUDING CONTENTS AND DATAFILES / prompt Don't forget to drop marvel_dba and check to see if the dbf file called marvel_data.dbf is gone from the data folder.

marvel/sql/drop_user.sql

REM =============================================== REM FILE: drop_user.sql REM DATE Feb. 19, 2013 REM PURPOSE: To drop user for marvel college REM =============================================== drop user marvel_dba /

marvel/sql/ins_department.sql

---------------------------------------------------------------------- -- File: ins_department.sql -- Date: Feb. 19, 2013 -- Purpose: To insert data into department table ---------------------------------------------------------------------- PROMPT ... inserting data into department INSERT INTO DEPARTMENT VALUES (1,'Computer Sci.','408 Kelly Hall') / INSERT INTO DEPARTMENT VALUES (2,'Mathematics','253 Watson Hall') / INSERT INTO DEPARTMENT VALUES (3,'Geology','707 Case Tower') / INSERT INTO DEPARTMENT VALUES (5,'Physics','118 Font Tower') /

marvel/sql/ins_Faculty.sql

---------------------------------------------------------------------- -- File: ins_faculty.sql -- Date: Feb 19, 2013 -- Purpose: To insert data into faculty table ---------------------------------------------------------------------- PROMPT ... inserting data into faculty INSERT INTO faculty VALUES (119,'Martin, Barb', '94 Ridge, Grant, MI', 32500.0, 25425.0, 1, 'MA','Comm','Asst',2 , 'N') / INSERT INTO faculty VALUES (209,'Newton, Jane', '210 Leonard,Ada, MI', 39000.0, 28945.0, 2, 'MS','Topology','Asso',1 , 'Y') / INSERT INTO faculty VALUES (421,'Jones. Al', '121 Colton,Hart, MI', 30000.0, 21402.0, 1, 'PhD','Database','Asst',2 , 'N') / INSERT INTO faculty VALUES (462,'Johnson, Tom', '604 46th,Lowell, MI', 35000.0, 26952.0, 1, 'MS','Op. Sys.','Asso',2 , 'Y') / INSERT INTO faculty VALUES (485,'Sparks, Pam', '201 Olive, Holt, MI', 43500.0, 30115.0, 2, 'PhD','Analysis','Prof',3 , 'Y') / INSERT INTO faculty VALUES (507,'Canton, Rob', '5 State, Cutler, MI', 29500.0, 21010.0, 3, 'MS','Volcano','Asst',1 , 'Y') / INSERT INTO faculty VALUES (564,'Ferris, Tim', '16 W. 9th, Troy, MI', 38500.0, 28540.0, 3, 'PhD','Minerals','Asso',1 , 'N') / INSERT INTO faculty VALUES (683,'Nils, Frank', '46 Creston, Ada, MI', 46500.0, 32050.0, 5, 'PhD','Laser','Prof',2 , 'Y') /

marvel/sql/ins_insurance.sql

---------------------------------------------------------------------- -- File: ins_insurance.sql -- Date: Feb 19, 2013 -- Purpose: To insert data into insurance table ---------------------------------------------------------------------- PROMPT ... inserting data into insurance INSERT INTO INSURANCE VALUES (1,'Regular') / INSERT INTO INSURANCE VALUES (2,'Deluxe') / INSERT INTO INSURANCE VALUES (3,'Special') /

marvel/sql/ins_job.sql

---------------------------------------------------------------------- -- File: ins_job.sql -- Date: Feb 19, 2013 -- Purpose: To insert data into job table ---------------------------------------------------------------------- PROMPT ... inserting data into job INSERT INTO JOB VALUES (119,'Asst','15-Sep-96') / INSERT INTO JOB VALUES (209,'Asst','1-Sep-91') / INSERT INTO JOB VALUES (209,'Asso','13-Sep-98') / INSERT INTO JOB VALUES (421,'Asst','15-Aug-98') / INSERT INTO JOB VALUES (462,'Asst','12-Apr-86') / INSERT INTO JOB VALUES (462,'Asso','14-Sep-96') / INSERT INTO JOB VALUES (485,'Asst','2-Apr-80') / INSERT INTO JOB VALUES (485,'Asso','12-Sep-89') / INSERT INTO JOB VALUES (485,'Prof','13-Sep-98') / INSERT INTO JOB VALUES (507,'Asst','12-Sep-96') / INSERT INTO JOB VALUES (564,'Asst','21-May-91') / INSERT INTO JOB VALUES (564,'Asso','1-Sep-06') / INSERT INTO JOB VALUES (683,'Asst','11-Jun-81') / INSERT INTO JOB VALUES (683,'Asso','2-Aug-89') / INSERT INTO JOB VALUES (683,'Prof','12-Aug-98') /

marvel/sql/ins_speaking.sql

---------------------------------------------------------------------- -- File: ins_speaking.sql -- Date: Feb 19, 2013 -- Purpose: To insert data into speaking_topics table ---------------------------------------------------------------------- Prompt ... Inserting into speaking INSERT INTO SPEAKING VALUES (119,'Distributed Database',5,'21-Mar-10') / INSERT INTO SPEAKING VALUES (119,'Why Network Micros?',2,'15-May-10') / INSERT INTO SPEAKING VALUES (119,'Communications',1,'2-Sep-10') / INSERT INTO SPEAKING VALUES (462,'The Future of Unix',3,'13-May-10') / INSERT INTO SPEAKING VALUES (485,'The Regular Polyhedra',2,'15-Oct-09') / INSERT INTO SPEAKING VALUES (507,'Mount St. Helens',1,'12-Feb-10') / INSERT INTO SPEAKING VALUES (683,'Laser Technology',3,'22-May-10') / INSERT INTO SPEAKING VALUES (683,'Nuclear Power ProCon',1,'15-Jun-10') /

marvel/sql/readme.txt

Marvel College Database 1. Create a folder for marvel college. 2. Create two folders in your marvel college folder. Call them sql and data. (The sql folder is for all the sql files that will create your application. The data folder will house the database.) 3. Copy the sql files that you copied from BlackBoard for marvel into the sql folder. 4. Go into the data folder and copy the path to this folder by right clicking on the path at the top of the data folder. Select Copy Address. 5. You need to edit the create_tablespace.sql file. Right click on this file and choose "open with". Choose notepad to open this file. In the CREATE TABLESPACE command, edit the second line that contains a string. That line looks like this: DATAFILE 'C:\Users\hondas\Documents\school\OracleDataFiles\marvel\data\marvel_data.dbf' You don't have a folder called hondas. Erase part of my path within the quote that says: C:\Users\hondas\Documents\school\OracleDataFiles\marvel\data and paste the path that you copied in step 4. This is the path to your marvel data folder. Add the following text to the end of the copied path, inside the close quote: \marvel_data.dbf' Save the create_tablespace.sql file, replacing the old one in the sql folder. The second line in the CREATE TABLESPACE should now read: DATAFILE 'C:\....\marvel\data\marvel_data.dbf' where \... \ contains the path to your marvel folder. (Make certain that you have the start and end single quotes.) The end of this string contains the name of this datafile that you are creating, namely marvel_data.dbf. Look in the marvel\data folder. You should now see a large file called: marvel_data.dbf 6. Go back to the sql folder and create a shortcut to sqlplus, by right clicking somewhere inside on the sql folder. Choose New, Shortcut. Click on the Browse button to find the location of the file sqlplus.exe. I had to go to Computer >> Local Disk (C:) >> oraclexe >> app >> oracle >> product >> 11.2.0 >> server >> bin Find and select sqlplus. ( you need to click on the application file called sqlplus.exe ) Click OK, Next, and hit the Finish button. This will give you a short cut to sqlplus. 7. Right click on the sqlplus short cut in your sql folder. Select properties. Using your mouse select the text to the right of Start in: Using the backspace key, delete the selected text in Start in: 8. Click the Apply button at the bottom of this dialog box and then click the OK button. 9. Double click on the sqlplus file that is found in the sql folder, to open sqlplus. Login as the system administrator with username system , and whatever you gave as a password when you installed Oracle. My password is admin for the system account. You can type system/admin all at once if you want to. 10. When you are at the sql prompt, run the createTablespace and createUser command files by typing the following after the SQL> prompt: SQL> @createTablespace.sql [ENTER] SQL> @createUser.sql [ENTER] 11. Connect as the new user you just created by typing: SQL> CONN marvel_dba/marvel_dba [ENTER] The SQL-Plus command CONN is short for CONNECT. 12. As the marvel college dba, you can create all the tables and insert records into these tables by typing the following: SQL> @create_tables [ENTER] SQL> @insert_data [ENTER] Now you can play with the database tables. 13. Later, when you do not have a need for this database and you are logged in as marvel_dba, type the following: SQL> @DROP_TABLES [ENTER] SQL> CONN system/admin [ENTER] {Use your own system password. Mine is admin} SQL> @DROP_TABLESPACE [ENTER] SQL> @DROP_USER [ENTER] 14. Now, go to the data folder in your marvel folder and check to see that the filemarvel_data.dbf is gone from this folder!