Relational Algebra

Relational Algebra

Post by mmccla » Sun, 01 Mar 2009 09:34:10


I'm having trouble with some relational algebra questions in a school
assignment. I have the first 15 completed but stuck on the last 4. I'm
hoping someone can provide some help... I'm not necessarily looking
for straight up answers, but perhaps a good hint at trying to solve
the problem. Unless someone wants to provide the equation and explain
why it's that, it would be great too. Reference to responses will be
given in the assignment cine an SQL file will be developed from it.
This is what I have:

Classes(class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)

The four queries I'm stuck on are the following:

1. Find the classes that have only one ship as a member of that class
(not all ships are listed in the Ship table)
2. Find the countries that had both battleships and battlecruisers
(those fall under type in Classes)
3. Find those ships that "lived to fight another day"; they were
damaged in one battle, but later fought in another.

Another problem with this assignment... I have to list all the ships
mentioned in the database. All the ships may not appear in the Ships
relations... I said the following in algebraic expression

SELECT name(Ships UNION (SELECT ship (Outcome UNION(SELECT class
(Classes)))

Would that work?
 
 
 

Relational Algebra

Post by Jasen Bett » Sun, 01 Mar 2009 20:22:17


hint: you want to group by class


hint: you want to join class to class by country=country


you should join outcome to get the date battle when ships were damaged
and then join that to a similar join where the date is later but ant
outcome is allowed.




if it would I don't understans the language you are using.

 
 
 

Relational Algebra

Post by mmccla » Mon, 02 Mar 2009 00:16:21


I am just using relational algebra.... like a textual representation
of a SQL statement for a query.... Except replace the capital words
with the symbols for relational algebra.
 
 
 

Relational Algebra

Post by Jim Carloc » Wed, 04 Mar 2009 12:06:33

"mmcclaf" wrote...

First you have the DCL (data control language) which offers
ways to control access to the data. (SELECT)

Then there exists the DDL (data definition language). You use
this to first to create the tables. You need to know the data
types available to use this efficiently. (INSERT, UPDATE)

Finally, the DML (data manipulation language) offers methods to
update the data. (CREATE)

One other thing that helps, involves employing Hungarian names
(commonly refered to as Hungarian Notation). It makes things a
little more readable and understandable.

I'll provide a simple CREATE TABLE statement. For further help,
provide us with the rest of the statements for the rest of the
tables.

CREATE TABLE tClasses (
id INTEGER PRIMARY KEY,
sClass CHARACTER VARYING (6),
sType CHARACTER VARYING (40),
iCountry INTEGER NOT NULL,
iNumGuns INTEGER NOT NULL,
iBore INTEGER NOT NULL,
iDisplacement INTEGER NOT NULL
);

We all know computers compare numbers faster than they compare
character sequences. We all know 32-bit computers work with 32-
bit characters/numbers faster than they do with 16-bit or 8-bit
numbers. Just some things to keep in mind when working with the
databases and computers in general.

Take note that the iCountry field expects a TABLE to exist that
stores an id INTEGER PRIMARY KEY field as well as the Country
name string and possibly a field which holds a number for the
number of states within the country. I'm thinking that sType
might be an INTEGER pointer to a TABLE of types of guns. No need
to store duplicate strings and you might as well make the data
work a little faster, even if the SQL for the SELECT queries
becomes a little more complicated.

Hope this helps.

--
Jim Carlock
You Have More Than Five Senses
http://www.yqcomputer.com/