CMSC 4003 - University of Central Oklahoma



CMSC 4003

Lab Assignment 5: Data Loading and SQL

Name:

Due: See the due date in WebCT calendar.

1. Data Loading Documentation

In this assignment, we will first complete data loading. A row-by-row insertion into tables is not efficient for large databases. As an alternative method, data loading allows us to directly load data from files into tables.

Oracle Documentation:



2. We first create the relation schemas. Download lab5_schema.sql and run lab5_schema.sql in SQL*Plus (@lab5_schema). The schema is based on the EER diagram given on the next page.

What are the entity types and relationship types of the EER diagram that have been implemented in this schema?

Answer:

Entity type: Department, Faculty, Degree Program and Student.

Relationship type: chair, worksfor, coordinator, major and offers.

3. Open another PuTTY window. Create a directory called lab5_dataload and go into this new directory.

4. Download files faculty.ctl and faculty.dat. Put the files into the lab5_dataload directory.

5. Open both faculty.ctl and faculty.dat files. Examine how faculty.ctl file refers to the column positions in the faculty.dat. Review faculty data in faculty.dat.

6. Execute the following command in Linux:

sqlldr control=faculty.ctl log=faculty.log

System will ask for your username and password at this moment. Note that faculty.log contains data loading information. If any error occurs, you can check the log.

7. Go back to the SQL*Plus window.

(a) You may check the content of the faculty table now.

select * from faculty;

Is the table properly loaded?

Answer: Yes.

(b) Why do we need to let the foreign key column worksfor be NULL?

Answer: No department exists yet. If the column is not NULL, we will not be able to load the faculty data.

(c) Check the faculty.ctl file to see what columns are specified for loading.

Is the column worksfor specified?

Answer: No.

8. Now download department.ctl and department.dat and put them into the lab5_dataload directory. Review the content of both files and then run the following in Linux:

sqlldr control=department.ctl log=department.log

In SQL*Plus, check that the department table has been properly loaded.

9. We are ready to load the foreign key column, worksfor, of the faculty table now. Oracle does not permit updating a particular column using sqlldr. We have to use SQL UPDATE to do this.

10. The foreign key data is first loaded into the temporary table worktable_faculty. Check the definition of the table worktable_faculty in schema.sql. Also note that if you want to put a comment in an sql file, you start the line with the command REM.

11. Download worktable_faculty.ctl and put it into directory lab5_dataload. Note that we do not have a corresponding worktable_faculty.dat file. Check the content of worktable_faculty.ctl. Which file is used as the data file for worktable_faculty.ctl?

Answer: faculty.dat

Execute the following command:

sqlldr control=worktable_faculty.ctl log=worktable_faculty.log

In SQL*Plus, check that table worktable_faculty is loaded.

12. Now update the worksfor column of the Faculty table using the following SQL command:

update faculty a

set worksfor = (select b.worksfor

from worktable_faculty b

where a.facssno=b.facssno);

commit;

Note the syntax of the update statement, which is a correlated nested SQL statement. Also note that, to assign the query result into a column, the statement must guarantee that the result set contains only one record value. Otherwise, the statement will not work.

Now check the content of the faculty table in SQL*Plus.

13. Load data for tables DegreeProgram, Student and DDoffers. You need to download the .ctl and the .dat files for the three tables. Put the files into directory lab5_dataload. Use the following commands in Linux to load the files.

sqlldr control=degreeprogram.ctl log=degreeprogram.log

sqlldr control=student.ctl log=student.log

sqlldr control=ddoffers.ctl log=ddoffers.log

Can we load DDoffers before DegreeProgram? Why?

Answer: No. Tuples in DDoffers reference those in DegreeProgram.

14. Now we will review some algebraic expressions and the corresponding SQL statements.

(a) (major='P000' Student

Give the meaning of the expression in English.

Answer: Return all students whose major number is “P000”.

Run the following equivalent SQL statement and check your answer.

select * from student where major='P000';

(b) (sid, sname (major='P000' Student

Give the meaning of the expression in English.

Answer: Return id and name of all students whose major number is “P000”.

Run the following equivalent SQL statement and check your answer.

select sid, sname from student where major='P000';

(c) (facssno, facname (worksfor='CS' Faculty

Give the meaning of the expression in English.

Answer: Return SSN and name of faculty members who work for CS department.

Write an equivalent SQL statement and run it to check your answer.

Answer:

select facssno, facname from faculty where worksfor='CS';

15. Consider the following query in English:

Get program id and program name of all programs that offer BS degree.

(a) Give an algebraic expression for the query.

Answer: (progid, programname (progtype='BS' DegreeProgram

(b) Write an equivalent SQL statement.

Answer:

select progid, programname from degreeprogram where progtype='BS';

(c) Run the statement in SQL to verify the answer.

16. Run the following SQL statement

select * from faculty, department;

(a) What is the meaning of the SQL statement?

Answer: Return the cross product of Faculty and Department.

(b) Give an equivalent relational algebraic expression.

Answer: Faculty ( Department

17. (facssno, facname, deptname (worksfor=deptid (Faculty ( Department)

(a) Give the meaning of the expression in English.

Answer: Return SSN, name and department name of all faculty members.

(b) Run the following equivalent SQL statement and check your answer.

select facssno, facname, deptname

from faculty, department

where worksfor=deptid;

18. (facssno, facname, deptname

(worksfor=deptid and (officeaddress='Babbage Tower' or officeaddress='Edition Bldg') (Faculty ( Department)

(a) Give the meaning of the expression in English.

Answer: Return SSN, name and department name of all faculty members whose office is in Babbage Tower or Edition Bldg.

(b) Run the following equivalent SQL statement and check your answer.

select facssno, facname, deptname

from faculty, department

where worksfor=deptid and

(officeaddress='Babbage Tower'

or officeaddress='Edition Bldg');

19. Consider the following query in English:

Get student id, student name, program name and program type of all students from OKC and at levels less than or equal to 4.

(a) Give an algebraic expression for the query.

Answer:

(sid, sname, programname, progtype

(curaddress='OKC' and stulevel ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download