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

Due Monday Feb, 6 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 topcat.cs.umb.edu and pe07.cs.umb.edu. Check this by trying "ssh topcat" or "ssh pe07" when logged in on users.cs.umb.edu, or connect to topcat.cs.umb.edu from outside and login. Also try "ssh pe07" when logged into topcat. This system (pe07) is not directly accessible from outside our network.You can try to access Oracle's demo account "scott" from topcat or 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 topcat 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 topcat 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 topcat or 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 topcat or pe07. You will be prompted for your Oracle username and password. When it is done, check your tables by using sqlplus on topcat or pe07 with "select * from student;", etc.

d. In your homework paper, report success or continuing problems in access to topcat, pe07, and Oracle, or trouble loading the tables. Make a typescript showing just "select * from boats" output. To do this, log into topcat or 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. You can view a relevant example by searching Google Books for "Every table T has at least one key".

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 B C D
a1 b1 c1 d1
a2 b3 c1 d2
a3 b4 c2 d2
a4 b2 c2 d1

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

b.

T2

A B C D E
a1
b1
c1
d1
e1
a2
b1
c1
d1
e2
a3
b1 c2
d1
e1
a4
b2 c1
d1
e1

 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]