Oracle Question

[i_hate_toms]

I actually like Oracle, it's way better than Access.

Crome, thnx for writing bro
You like Oracle probably because you know how to use it, i don't
Access seemed easier..
Got two questions about Oracle, off topic for this thread, should I make a new thread?
The questions are:-
*Is there something i can install in my laptop to try out the sql commands?
Oracle 9i is not free
*Can you suggest a text book on DBMS/Oracle/SQL please?
Thanks

 

[Cromewell]

Yes a new thread, I've moved the post for you.

You should be able to get Oracle, I can download 11g from OTN without paying for it. (http://www.oracle.com/technetwork/d...on/downloads/index.html?ssSourceSiteId=ocomen). It's a bit of a pig to install though. 9i is getting old, it may not be available from Oracle anymore. Looks like there's a download of it on CNET, but then you'd have to go to CNET and that's never recommended

I had a textbook for everything Oracle, I cannot remember what it was called though. I'll see if I can dig it up when I get home.

 

[strollin]

You can download the Oracle software and use it to develop or test an application but once that app is actually used in production, that's when you have to pay.

11g is a much more robust db than Access. Access is OK for small projects but for large, enterprise projects you really need a full scale db like Oracle or SQL Server.

 

[Cromewell]

I can't find the book I had, it must have gotten lost in a move. I'm sure if you hit amazon for oracle books you'll find some high rated ones that you could use.

 

[i_hate_toms]

Yes a new thread, I've moved the post for you.

Thnx, i knew this deserves a new thread, someone here (don't remember who) suggested i should avoid making new threads for homework-type questions.. But i'm sure the "administrator" knows the rules better

9i is getting old, it may not be available from Oracle anymore.

Don't know why, but the college insists on teaching obsolete stuff

I had a textbook for everything Oracle, I cannot remember what it was called though. I'll see if I can dig it up when I get home.

Purchased this book called "Introduction to database systems" (pearson).
SQL doesn't start till chapter 5... i'm still on chapter 2, memorizing definitions

You should be able to get Oracle, I can download 11g from OTN without paying for it. It's a bit of a pig to install though.

Okay thnx, i'll download it now after i finish posting this

..but then you'd have to go to CNET and that's never recommended

Why?! I regularly download stuff from CNET, am i putting my computer at risk??
PS:Sorry for late reply, had a mathematics test..

 

[i_hate_toms]

You can download the Oracle software and use it to develop or test an application but once that app is actually used in production, that's when you have to pay.

11g is a much more robust db than Access. Access is OK for small projects but for large, enterprise projects you really need a full scale db like Oracle or SQL Server.

Thnx for the information about licensing terms of Oracle, didn't know i could get it for free
Hmm you must be right about oracle being better, because the professors in my class (and the Administrator here) say the same Access is easier to learn though
Thnx for your reply

 

[Cromewell]

I don't like CNET because I find their downloads are packed with bonuses.

 

[i_hate_toms]

DBMS question again

There's this chapter called "Conceptual Modelling",
According to it, if we say
BOOK published_by PUBLISHER
then BOOK, and PUBLISHER are two ENTITIES, each defined by their own set of attributes, and related to each other by the RELATIONSHIP "published_by".
The next chapter is called "The Relational Model".
Here, a relationship is explained as
BOOK (ISBN,Type,Page_count,Price,Rating)
Where the values in parentheses represent the "attributes" that define BOOK, and BOOK itself is called a "Relation".
I'm pretty confused. The first chapter definition of a "relationship" makes perfect sense. But in the second chapter, how come BOOK itself is a relation?
Why isn't BOOK an "Entity"?
Please help. Thanks.

 

[Cromewell]

Sounds like you've entered terminology hell.

I've never been a fan of database theory, there'll be some stuff that will really suck

I could be explaining this wrong but try to think of the book entity as being defined as the relationship of ISBN, Type, Page_count, Price, and Rating.

 

[i_hate_toms]

Sounds like you've entered terminology hell.

I've never been a fan of database theory, there'll be some stuff that will really suck

I could be explaining this wrong but try to think of the book entity as being defined as the relationship of ISBN, Type, Page_count, Price, and Rating.

Thanks Crome, you've been the most friendly and helpful person here in CF
And you must be right, there cannot be another logical way to explain why the book is a relation, I've been thinking about this, the only way to take book as a relation, is if we think of the defining attributes to be independent "sub-entities"(weak entities) and BOOK as a "super-entity" (Strong entity) associated with these sub-entities. I'm probably wrong, but I just cannot think of another explanation.
Now, what happens if we have another relation
PUBLISHER (Address, Phone, Email).
We now have two relations, this, and
BOOK (ISBN,Type,Page_count,Price,Rating).
Every book must be in full-participation in the relationship (double lines in ER Diagram, hope i could explain) as a BOOK cannot exist without a PUBLISHER.
Hence, BOOK itself is a "relation", and so is PUBLISHER. When I associate a BOOK with a PUBLISHER using a Foreign-key, am i establishing a relation among two relations??
I sure have entered terminology hell.
The next chapter is called "Relational Algebra and Calculus".
This too, is a theoretical chapter, but i think it'll be easier to learn, because the things they talk about, are mathematically explained.
And after that, is the chapter on SQL. I just can't wait to get past these two chapters and start the SQL chapter. SQL is cool. But having to understand a hundred theoretical definitions/ terminologies, is not so cool.
Thanks for replying bro

 

[Cromewell]

Once you are looking at a complete row (or relation as we are calling it right now) you can now think of the relation as an entity. Then you are simply relating 2 entities.

As we went through earlier, a book can identified by the relation of ISBN, publisher, etc (realistically, you'd just need ISBN to identify a book but lets ignore that for now). Each unique combination of those fields is a book. The same goes for publisher albeit with different fields.

What you are then doing is relating 2 entities, which is easier to wrap your head around.

 

[Cromewell]

Hey I found my book!

It's called "Guide to Oracle 9i" by Morrison and Morrison published by Thomson. ISBN 0-619-15959-6. I don't remember if was overly good or not. I passed so it must at least be ok

 

[i_hate_toms]

crome man i've another trouble here, say i do this:

CREATE TABLE REVIEW ( 
R_ID VARCHAR(4) NOT NULL, 
ISBN VARCHAR(15) NOT NULL, 
Rating NUMERIC(2), 
PRIMARY KEY(R_ID,ISBN), 
FOREIGN KEY (R_ID) REFERENCES AUTHOR (A_ID));

next say i want to drop the foreign key constraint from the above relation. Can this be done with ALTER TABLE? if yes -- how? if no, then how do i do it?
Don't want to drop the whole attribute (cannot, it's a part of the composite primary key anyway), i want R_ID to be present, i just want to remove the foreign key and prevent connection between review and author.
Also, is it necessary to write the two "not null" 's? if the attributes are used as constituents of the primary key, isn't not null implied anyway?
Thanks bro,(YOU and) this site's been real help!!! :good:
PS: Sorry for not replying about the book name which u took the trouble to look up for me. Honest mistake, NOT intentional. I just lost track of this thread (shouldn't have). Turns out i can use my student ID (and pwd of course!) and have access to a ridiculously huge library of books in the university page. But thanks for the name of the book, i'll chk if we got that, now

 

[Cromewell]

alter table <table> drop constraint <constraint_name>;

Usually your constraints are named i.e.
CREATE TABLE REVIEW (
R_ID VARCHAR(4) NOT NULL,
ISBN VARCHAR(15) NOT NULL,
Rating NUMERIC(2),
CONSTRAINT review_pk PRIMARY KEY(R_ID,ISBN),
CONSTRAINT review_fk FOREIGN KEY (R_ID) REFERENCES AUTHOR (A_ID));

 

[i_hate_toms]

alter table <table> drop constraint <constraint_name>;

Usually your constraints are named i.e.
CREATE TABLE REVIEW (
R_ID VARCHAR(4) NOT NULL,
ISBN VARCHAR(15) NOT NULL,
Rating NUMERIC(2),
CONSTRAINT review_pk PRIMARY KEY(R_ID,ISBN),
CONSTRAINT review_fk FOREIGN KEY (R_ID) REFERENCES AUTHOR (A_ID));

Thnx bro
So ur saying thai i should name all the constraints or else i lose the ability to alter them later.. ??
By the way, that book you suggested, is not on our university database
Thnx again bro, you guys are like having a second professor right inside my laptop Thanks fir all the help

 

[Cromewell]

From what I remember, oracle will force a name for constraints. It may end up being system generated if you don't specify

 

[i_hate_toms]

ok thnx, i'll use names for all my constraints. :good:
My next query (no pun intended lol ) can we nest select commands?
Say i want to retrieve the title and price of all the books that belong to the same category as 'C++' , can i write this:-

SELECT title, price 
FROM book 
WHERE category=
(SELECT category FROM book WHERE title='C++';);

A million thank-you 's for all the help bro