Queries

M

mmcclaf

I have to make some queries for 4 tables I have. The following
relations are:

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

The three 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.

The best way for me to understand would be relational algebra for each
of the statements.

Any help in this would be greatly appreciated.
 
G

Gary Herron

mmcclaf said:
I have to make some queries for 4 tables I have. The following
relations are:

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

The three 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.

The best way for me to understand would be relational algebra for each
of the statements.


Sounds like a homework assignment. Good luck with it.
 
M

Murray

It is, however I was able to get the first 8 done, I am struggling with
these 3 particular ones. I have to make an SQL file based off of it, so this
seems to be a blockage in my works.

-----Original Message-----
From: Gary Herron [mailto:[email protected]]
Sent: Thursday, February 26, 2009 1:46 AM
To: mmcclaf; (e-mail address removed)
Subject: Re: Queries
I have to make some queries for 4 tables I have. The following
relations are:

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

The three 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.

The best way for me to understand would be relational algebra for each
of the statements.


Sounds like a homework assignment. Good luck with it.

Any help in this would be greatly appreciated.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.237 / Virus Database: 270.11.3/1971 - Release Date: 02/25/09
06:40:00
 
S

Steve Holden

Murray wrote:
[top-posting corrected]
-----Original Message-----
From: Gary Herron [mailto:[email protected]]
Sent: Thursday, February 26, 2009 1:46 AM
To: mmcclaf; (e-mail address removed)
Subject: Re: Queries
I have to make some queries for 4 tables I have. The following
relations are:

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

The three 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)

Investigate a GROUP BY solution that selects groups having a count of 1.

Look at EXISTS for one possible solutions.
From your model description I don't even see where the position and
attitude of each ship is stored, so I don't think I can give you any
help at all with this one.
Sounds like a homework assignment. Good luck with it.

It is, however I was able to get the first 8 done, I am struggling with
these 3 particular ones. I have to make an SQL file based off of it, so this
seems to be a blockage in my works.
Good luck with the homework. Remember to acknowledge the help you've had
from this list (particularly your earlier issues: here you just have hints).

regards
Steve
 
M

mmcclaf

Murray wrote:

[top-posting corrected]


-----Original Message-----
From: Gary Herron [mailto:[email protected]]
Sent: Thursday, February 26, 2009 1:46 AM
To: mmcclaf; (e-mail address removed)
Subject: Re: Queries

Investigate a GROUP BY solution that selects groups having a count of 1.

Look at EXISTS for one possible solutions.
From your model description I don't even see where the position and

attitude of each ship is stored, so I don't think I can give you any
help at all with this one.
Sounds like a homework assignment.    Good luck with it.
It is, however I was able to get the first 8 done, I am struggling with
these 3 particular ones. I have to make an SQL file based off of it, so this
seems to be a blockage in my works.

Good luck with the homework. Remember to acknowledge the help you've had
from this list (particularly your earlier issues: here you just have hints).

regards
 Steve

Yeppers always do. And thanks! As for the number 3 one... to know if
they were damaged would appear in "Result" of the Outcomes table
 
M

mmcclaf

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?
 
S

Steve Holden

Dennis Lee Bieber wrote:
[...]
{Oh, and since I saw, at work, a later post with a bunch of nested UNION
statements: UNION requires all the tables to have the same number/type
of columns -- you don't have that}

A rather fine point, but technically all that's required is the the
united *queries* have the same number and type of columns. I assume that
the OP was selecting *, though ...

regards
Steve
 
M

mmcclaf

This came in the Python groups, and I put one up in the database
group, since I will later have to use Python to access the SQL file,
so therefore tackling one thing at a time. Also, there were no answers
that were coming up in the .database group.

Really? You can have ships in the Outcomes relation that DO NOT
APPEAR in the Ships relation... Goodbye referential integrity.

Besides which, if they don't appear in the Ships relation, then
there is NO INFORMATION to tell you what ship CLASS it is a member of --
do you group them all as "UNKNOWN" class, or do you declare each to be
the only ship of its class (and thereby use the ship name as the class
name)?

This meant that some ships weren't listed in the Ship table provided
but did show up in the Outcome. Therefore, they are not directly
referenced one to the other.
<sigh> Another gmail user found only by back referencing responses...

What is meant by this statement?

So in short of the responses given, I need to study further: GROUP BY,
HAVING, AS, COUNT, and subselect queries, right?

The relational algebra, I am able to translate it into SQL, or most of
it, meaning turning it into the queries of SQL. This is not an
advanced database course, but more a basic intro to database systems.
 
D

Dennis Lee Bieber

This came in the Python groups, and I put one up in the database
group, since I will later have to use Python to access the SQL file,

Uhm... To me, an "SQL file" is a file containing SQL statements. It
is NOT a database.

This is part of an SQL file (it's a backup from a MySQL database)

-=-=-=-=-=-
-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.0.24a-community-max-nt-log


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;


--
-- Create schema bestiaria
--

CREATE DATABASE IF NOT EXISTS bestiaria;
USE bestiaria;

--
-- Definition of table `artlinks`
--

DROP TABLE IF EXISTS `artlinks`;
CREATE TABLE `artlinks` (
`ID` int(11) NOT NULL auto_increment,
`category` enum('archive','artist','zine','reference','news') NOT NULL
default 'archive',
`name` varchar(75) NOT NULL default '',
`URL` varchar(75) NOT NULL default '',
`banner` varchar(75) default NULL,
`width` int(11) default NULL,
`height` int(11) default NULL,
`description` varchar(250) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `artlinks`
--

/*!40000 ALTER TABLE `artlinks` DISABLE KEYS */;
INSERT INTO `artlinks`
(`ID`,`category`,`name`,`URL`,`banner`,`width`,`height`,`description`)
VALUES

(1,'archive','Epilogue','http://www.epilogue.net/',NULL,NULL,NULL,NULL),

(2,'archive','FurNation','http://www.furnation.com/','images/furnation.gif',404,44,'&quot;The
Artists\' Image Archive&quot;'),

(3,'archive','Elfwood','http://www.elfwood.com/',NULL,NULL,NULL,'Reorganized,
subsuming the former <i>Lothlorien</i> and <i>Zone47</i> subsites'),
(5,'archive','The Orlando Furry
Archives','http://fanarchive.net/',NULL,NULL,NULL,NULL),
(6,'archive','Side 7','http://www.side7.com/',NULL,NULL,NULL,'&quot;the
Artist\'s Archive&quot;'),

(7,'archive','VCL','http://us.vclart.net/vcl/Artists/',NULL,NULL,NULL,'Furry
Image Archives'),
(37,'archive','Deviant
Art','http://www.deviantart.com/',NULL,NULL,NULL,'An online artist
collective and marketplace. Various genres.'),
(8,'archive','YERF Archives on
ArtSpots','http://yerf.artspots.com/',NULL,NULL,NULL,'Formerly known as
<i>The Squeeky Clean Furry Archives</I>. YERF.com has shutdown and
ArtSpots is archiving the directories of those artists wanting them
preserved.'),
(9,'zine','Herd
Mentality','http://www.furry.org.au/aatheus/herdmentality/',NULL,NULL,NULL,'&quot;The
Centaur Fanzine!&quot; (although the definition of &quot;centaur&quot;
is rather loose)'),

(10,'zine','Huzzah','http://www.huzzah.com/',NULL,NULL,NULL,'&quot;Anthropomorphics
for Artists&quot;'),
(11,'zine','Sofawolf
Press','http://www.sofawolf.com/','images/swplogo.gif',158,62,'Sofawolf
Press showcases quality furry fiction in book and magazine format.'),
(12,'artist','Theresa Larsson: Aspects of
Art','http://www.theresalarsson.se/','images/aspects_of_art.jpg',468,60,NULL),
(13,'artist','Megan
Giles','http://www.furnation.com/Megan_giles/','images/mgiles.jpg',468,80,NULL),
(14,'artist','MorganKeith
Studios','http://www.morgankeithstudios.com/',NULL,NULL,NULL,'&quot;MorganKeith
Studios is geared to provide high quality art for print, web and
multimedia as well as practical models, maquettes, prototypes and
concepts.&quot; (from their website)'),
(15,'artist','Anna-Karin
Larsson','http://www.twospotz.com/','images/twospotz.jpg',468,60,NULL),
(16,'artist','White Pony
Productions','http://www.whiteponyproductions.com/',NULL,NULL,NULL,'Formerly
known as <i>Beth Productions</i>.'),

(17,'artist','Kyoht.com','http://www.kyoht.com/','images/kyoht1.jpg',400,80,NULL),
(18,'artist','Mitternachts
Lair','http://www.mitty-art.com/',NULL,NULL,NULL,NULL),

(19,'artist','Sidian.net','http://www.sidian.net/','images/sidian_banner.jpg',351,89,NULL),
(20,'artist','Eric &quot;Coyote&quot;
Elliott','http://www.templeofluna.com/ee/',NULL,NULL,NULL,NULL),
(21,'artist','Dark
Natasha','http://www.darknatasha.com/','images/icon2.jpg',300,114,NULL),
(22,'artist','Synnabar (Stephanie M.
Hahn)','http://synnabar.critter.net/',NULL,NULL,NULL,NULL),

(23,'artist','Huskie','http://www.fanarchive.net/Artists/Huskie/',NULL,NULL,NULL,NULL),
(24,'artist','Featherdust
Studios','http://featherdust.critter.net/',NULL,NULL,NULL,NULL),
(25,'artist','Caribou
Ink','http://www.redpanda.com/default.asp',NULL,NULL,NULL,NULL),
(26,'artist','Kemono
Inukai','http://www.kemonet.com/','images/kemonet_m.gif',200,40,NULL),
(27,'artist','SummerDragoness.com (Hannah
Spute)','http://www.summerdragoness.com/','images/summer_fly.jpg',312,80,NULL),

(28,'artist','Foxprints.com','http://www.foxprints.com/',NULL,NULL,NULL,'Hosting
Tracy Butler, Candy Palmer, and Cynthia Rowen'),
(29,'artist','JC Amberlyn (aka
DesertCoyote)','http://www.jcamberlyn.com/',NULL,NULL,NULL,NULL),
(30,'artist','Becky\'s Wildlife &amp; Fantasy
Art','http://www.wildlife-fantasy.com/',NULL,NULL,NULL,NULL),
(31,'artist','Tracy
Reynolds','http://www.calicougar.com/',NULL,NULL,NULL,NULL),

(32,'artist','Toonaplooza!','http://www.toonapalooza.net/','images/button_sheep.jpg',170,50,NULL),
(33,'artist','GoldenWolfen (Christy
Grandjean)','http://www.goldenwolfen.com/','images/direbanner.jpg',400,80,NULL),
(35,'artist','Nethersphere (Stephanie D.
Lostimolo)','http://www.nethersphere.com/',NULL,NULL,NULL,NULL),
(36,'artist','Michael
Whelan','http://www.michaelwhelan.com/',NULL,NULL,NULL,NULL),

(38,'archive','furtopia.org','http://www.furtopia.org/artwork/artwork.shtml',NULL,NULL,NULL,'Art,
literature, music, and discussion forums'),
(39,'reference','Furry Is...','b_furryis.html',NULL,NULL,NULL,'An essay
by Chuck Melville'),
(40,'reference','The State of Furry','b_stateof.html',NULL,NULL,NULL,'A
1996 Usenet article by Chuck Melville'),

(41,'reference','WikiFur','http://furry.wikicities.com/wiki/WikiFur_Furry_Central',NULL,NULL,NULL,'Furry
specific wiki-type encyclopedia'),

(42,'reference','Wikipedia','http://en.wikipedia.org/wiki/Main_Page',NULL,NULL,NULL,'General
wiki-type encyclopedia'),
(43,'news','Flayrah.com','http://www.flayrah.com/',NULL,NULL,NULL,'In
their words: \"Unusually Good Information\"'),
(44,'news','Dutch
Furs','http://www.dutchfurs.com/',NULL,NULL,NULL,'News, art, and more...
with a Dutch perspective'),

(45,'archive','FurAffinity','http://www.furaffinity.net/',NULL,NULL,NULL,'Art,
Stories, Poetry, and Music'),
(46,'reference','A Defense of Furry
Fandom','b_defense.html',NULL,NULL,NULL,'A 2006 essay by \'Coyoty\' Dave
Wright'),
(47,'reference','What are you guys actually fans of,
though?','b_fans_of.html',NULL,NULL,NULL,'A 2006 essay by \'Coyoty\'
Dave Wright'),

(48,'archive','ArtSpots','http://www.artspots.com/','images/artspots-logo.jpg',361,125,NULL),
(49,'archive','Furry Art
Pile','http://www.furryartpile.com',NULL,NULL,NULL,'&quot;an art
community for <b>furry artists</b> and <b>connoisseurs</b>.&quot;');
/*!40000 ALTER TABLE `artlinks` ENABLE KEYS */;

-=-=-=-=-=-=-

SQL is a nearly standardized language used to make queries into a
database; the language maps fairly closely to the structures used by
most relational database engines, but a relational database is not a
requirement (though I pity the person that has to write an SQL frontend
for an old hierarchical database engine).

What is meant by this statement?
About 90% of the junk for sale spam that shows up in
comp.lang.python is posted by people using gmail accounts. As a result,
my newsreader is configured to not download messages in the group if the
"from" address contains gmail. When seeing a something potentially
interesting quoted by some other responder, I can use the "references"
headers to instruct my news reader to retrieve specific messages -- such
retrieval bypasses the kill filter.

I'm making an effort to see if I can get gmane to pick up messages
that are otherwise killed... Not sure how effective that is. It found
this (your) message, but that's only because I had it fetch the most
recent 50 headers -- not part of the normal "get new" run.
So in short of the responses given, I need to study further: GROUP BY,
HAVING, AS, COUNT, and subselect queries, right?
Since it's homework, we (this group) won't be giving direct SQL
statements...
The relational algebra, I am able to translate it into SQL, or most of

.... and formal relational algebra is a notation many users of database
engines like MySQL, SQLite, Access/JET, Visual FoxPro, Paradox, Sybase,
etc. have never encountered -- heck, Access tries to hide SQL from the
user via a form of QBE interface.

If I were to attempt a textual version of relational algebra, you'd
be getting something on the order of:

t1 = Ships x Classes
t2 = restrict t1(Ships.class = Classes.class)
res = project t2(Ships.name, Classes.class, Classes.country)

instead of straight forward SQL

select Ships.name, Classes.class, Classes.country from Ships
inner join Classes
on Ships.class = Classes.class


--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
S

Steve Holden

Dennis said:
This came in the Python groups, and I put one up in the database
group, since I will later have to use Python to access the SQL file, [...]
The relational algebra, I am able to translate it into SQL, or most of

... and formal relational algebra is a notation many users of database
engines like MySQL, SQLite, Access/JET, Visual FoxPro, Paradox, Sybase,
etc. have never encountered -- heck, Access tries to hide SQL from the
user via a form of QBE interface.

If I were to attempt a textual version of relational algebra, you'd
be getting something on the order of:

t1 = Ships x Classes
t2 = restrict t1(Ships.class = Classes.class)
res = project t2(Ships.name, Classes.class, Classes.country)

instead of straight forward SQL

select Ships.name, Classes.class, Classes.country from Ships
inner join Classes
on Ships.class = Classes.class
I think the real problem is that the database design the OP is using os
far from normalized. This will tend to contort the logic, as well as
making the programming harder to understand.

regards
Steve
 
M

mmcclaf

So basically you are complaining that groups don't pick up post by
GMail users?

As for the SQL thing, you still don't see how I am linking this to
SQL? I have to make in your terms: a textual representation of the
statements I'd make in SQL. The statement I made with the bold SELECT,
etc, replace those with the relational algebra symbols, hence needing
to make a relational algebra expression.
Since it's homework, we (this group) won't be giving direct SQL statements...

I never said I wanted direct SQL statements, moreso guidance as to
what direction I should be heading for each of the queries. I just
wanted to make sure that there might not be other things I might want
to study as well that might be required for the queries. Try not to
interpret what isn't there... I'm looking for help, not direct answers.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top