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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- university of oklahoma academic calendar 2019
- university of oklahoma semester schedule
- university of oklahoma philosophy dept
- university of oklahoma calendar
- university of oklahoma salaries
- university of oklahoma football players
- university of oklahoma continuing education
- university of oklahoma printable map
- university of oklahoma enrollment numbers
- university of oklahoma outreach program
- university of oklahoma extended campus
- university of oklahoma degree