CSCE 4350 Database Design
Assignment 2 Relational Algebra and SQL
Total: 110 points
Issued: 09/25/2007
Due: 10/09/2007
................................
1. (50 points) Given following database schema consisting of four relations:
Product(model*, maker, price)
PC(model*, speed, ram, hard-drive, removable-drive)
Laptop(model*, speed, ram, hard-drive, screen)
Printer(model*, color, type) /*type specificies laser or ink*/
The attribute(s) with * indicate the primary keys. We assume for convenience that model numbers are unique over all products. The Product relation gives the model number, manufacturer, and price of all the products. Other relations give the details of PC, Laptop, or Printer which are specifications of Product. Write relational algebra expressions for the following queries:
1. What PC models have a speed of at least 100?
2. Which manufacturers make laptops with a hard disk of at least one gigabyte?
3. Find the model number and price of all products (of any type) made by manufacturer B.
4. Find the model numbers of all color laser printers
5. Find those manufacturers that sell Laptops, but not PCs.
6. Find those hard-disk sizes that occur in two or more models of PCs (consider using self join, e.g. join PC with PC).
7. Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once; e.g., list (i,j) but not (j,i)
8. Find the average price of Laptops for each screen size.
9. Find the average price for each type of printers (color laser, color ink, black-white laser, and black-white ink).
10. Find the manufacturers who make all models of printers (hint: use divide).
2. SQL (60 points) A short help line on Oracle in CSE support website is available from http://esupport.csci.unt.edu/index.php?doc=prog-oracle-000. You are expected to submit following:
· How to log into your oracle account?
1. Log into any csp machine
1. Create a working directory for your project. I will call it 4350LabSQL.
2. Change dir into directory 4350LabSQL
3.
Type the following command:
sqlplus <userid>@erg.csci.unt.edu
4. Type in your password to complete the process.
5. Change your password using "password;" command in SQL*Plus.
· Oracles command line SQL interface may not be as friendly as you thought. You may find once you login into Oracle and are under SQL> prompt, it is hard to erase a character or go back to a previous line. You can type in l to look at the last statement and type in / to re-execute your last statement. However, the easiest way to get around this problem is to use a file to edit your command and execute the file instead. Here are the steps:
1. Get into your working directory 4350LabSQL from another window
2. Create a file named mySQLFile.sql using your favorite editor, e.g. emacs, vi, pico, and jedit.
3. Enter one or multiple SQL statement into file mySQLFile (--" means comment-out in Oracle)
4. Go back to SQL> prompt in your original window and type in command @mySQLFile. The statements in file mySQLFile will be executed
5. Edit and re-execute the file mySQLFile as necessary
· Now create the banking database and populate it. Follow the following steps:
1. Download the bank.sql (make sure the file is save as bank.sql. You may also need to edit out some HTML tags from the save file) SQL script file into your working directory 4350LabSQL.
2. Execute the SQL commands in the file by typing "@bank" under SQL> prompty.
3. Here are some useful commands that you can use to explore the schema of the database and re-direct inputs and outputs as well as some sample queries.
· Review basic SQL*Plus commands for SQL*Plus using a tutorial from Ullman . Review and list commands to carry out the following tasks:
1. List columns of a table.
2. Record(spool) your session
3. Execute SQL commands listed in a file
4. List all the user-defined tables
· Review the tables created via "SELECT * FROM USER_TABLES" and "DESCRIBE" commands.
· Write SQL 12 queries for following groups, choosing 4 from each group
------------ Group 1 ------------
1. List branches sorted by assets for branches with assets over 1 Million.
2. List the number of branches and total assets of all branches for each city.
3. Select cities which have at least 2 braches with assets greater than 1 Million.
4.
Find the names of the customers who live in city
5. Find the names of the customers who have an account;
6. Find the names of the customers who have an account in the city of Horseneck;
7. Find the names of the customers who have an account in the same city that he/she lives;
------------ Group 2 ------------
1. Find the names of the customers who do not have an account in the same city that he/she lives;
2.
Find the name of the customers who have both an account
and a loan at city
3. Find the average assets of branches in each city
4. Find the total balance for each customer in all his/her account
5.
Find the names of the customers who have an account in
every branch in city
6.
Find branch(es) with larger assets than
the sum of assets of all branches in '
7. Find the branches in the city of Horseneck with largest assets using a nested query. Do not use statistical functions, e.g. MAX.
8.
Find the branch in in the
city of Horseneck with more assets than that of
every branch in the city of
------------ Group 3 ------------
1.
Update assets of
2. Update balance of account A-101 with 10 percent increase.
3. Update customer Adams street from spring to Fall.
4.
The city of
5. Update assets of braches to reflect the following increase (using one SQL) :
a. Horseneck branches by 3
percent.
b. Brookly branches by 6 percent.
c. Other branches by 4 percent
Submission instructions:
-
submit
a hard copy of the relational algebra statements for question 1 by the due date
before class.
-
submit
an executable SQL file for question 2, including all the SQL statements for the
selected queries. For each query, please include the query number and its
corresponding English query as a comment (use -- to commend a line in SQL). Submit
answers to question2 by the due date using the 'project' program. The class
code is 4350s001 project banking.