CS430/630 Homework 1  Getting started with Oracle, keys, relational algebra

Due Monday September 24 in class, on paper. Neat handwriting is OK. Please provide answers in the order of this assignment. Note that only problem 1 requires access to our departmental systems. The rest need only the R&G book.

1. Getting started with Oracle:
a. Apply for a Linux account for cs630 by visiting S/3/158 and getting assistance or connecting to apply.cs.umb.edu from anywhere and logging in as apply.

b. After a day, check that the account has been set up by logging in to users.cs.umb.edu and seeing a cs630 subdirectory available, ready for your homework deliveries. Note: don't create this subdirectory, it needs to be specially set up. The existence of this account lists you for an Oracle account to be created. Also it gives you access to pe07.cs.umb.edu. Check this by trying "ssh pe07" when logged in on users.cs.umb.edu, or connect to pe07.cs.umb.edu/pe07 from outside and login. Note how the subdirectory cs630 is available to you from users and pe07. You can try to access Oracle's demo account "scott" from pe07 using the commands shown in DatabaseSetup.html.

c. Check the class home page for an announcement of creation of student Oracle accounts, and if seen, try out sqlplus on pe07 with your own account name and password, using the commands shown in DatabaseSetup.html.

d. Copy the directory /data/htdocs/cs630/tables to your homework directory. Start from your login directory and do the following command:

cp -r /data/htdocs/cs630/tables cs630

This can be executed on users or pe07. Then cd to cs630/tables and use "ls" to list the files (while logged in on either system). You should see dbbook.sh and a lot of other files. Then login to pe07 if you are not already there (and cd to cs630/tables). Load the R&G book's tables by executing "sh dbbook.sh" on pe07. You will be prompted for your Oracle username and password. When it is done, check your tables by using sqlplus on pe07 with "select * from student;", etc. Note that these tables are created using diskspace on dbs3.cs.umb.edu, the Oracle server system, but can only be accessed (by students) using sqlplus on pe07. We could install sqlplus on other systems, but this is all we need.

In your homework paper, report success or continuing problems in access to pe07 and Oracle, or trouble loading the tables. Make a typescript showing just "select * from boats" output. To do this, log into pe07, run the "script" command, then use sqlplus to access your tables and do the select, then exit from sqlplus and then exit again, which finishes the script in file "typescript". Copy this into your homework paper, or print it separately and attach it in order.

2. Keys. From DBPPP, another DB text. Assume that the tables of this exercise are unchanging, so that they have all the rows they are ever going to have, and we can figure out the intentions of the designer regarding keys for the table by merely looking at the contents of the rows. The named element values are all different.

T1

a. Find the three candidate keys for this table. One of the keys has two columns.

b. T2

 Find the two candidate keys for this table.

3. Keys. Exercise 3.5, page 95 (this is answered in online solutions linked from the class web page)

4. Keys. Answer the same questions of Exercise 3.5 for the table Enrolled, in Figure 3.4.  Part 3: Add another student in History 105 to the table and give student 53831 another course: show the resulting table. Part 4: What is the only candidate key (and thus the primary key) of this table?

5. Relational Algebra. Exercise 4.3, even-numbered parts, but skip 6 and 8 and 12 since these involve division. You may abbreviate the table names to S, P, and C. Note that these tables are loaded into Oracle by dbbook.sh. Do each part as a relational algebra query, i.e., don't worry about relational calculus.

6. Relational Algebra.Using the tables of Exercise 4.3:

1. Find the addresses of suppliers supplying Green parts.
2. Find the names of suppliers supplying parts of cost over 100.
3. Find the names of suppliers which supply a part named 'Acme Widget Washer'.
4. Find the name of suppliers that supply Red parts costing over 10.
5. Find the names of suppliers that supply only parts that cost 1.
6. Find the names of suppliers that supply a Green part or the part "Smoke Shifter End", in either case costing less than 100.
7. Find the addresses of suppliers that supply part "Smoke Shifter End", but do not supply any Green parts.
8. Find the names of suppliers that supply only one part.

7. Relational Algebra. Exercise 4.4 Explaining what a given query returns.

a. As it is written, this expression returns nothing because the projection on sid of selected part of Parts returns nothing. To get a non-trivial result, we need to replace the projection on sid with a projection on pid. Describe what is returned after this change.

Do the others as written in the text. Another one has a trivial result. It turns out this problem is answered in the answer set even though it is even-numbered. However, the first query in the solution does not agree with the first query in the text, so the answer is wrong for the one in the text.

8. Relational Algebra.  Exercise 4.2, page 127, parts 1-6.[cs630 only]