Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pooja28d
Starting Member
8 Posts |
 Posted - 2011-03-04 : 21:36:15
|
| Hi to all, I have no idea about how to create relation and solve their queries. I want to relation as follows:BookAuthor(book, author, earnings)BookReference(book, referenceBook, times)BookReview(book, reviewer, score)BookPublih(book, year, publisher, price, numbar)In these database, each book may have 1 or more authors & each author may make a diff. amt. of money from that book. One book may make reference to other book.1 book may be reviewed by diff. reviewers and get diff. scores. an author could also be a reviewer & a publisher.I want to solve following queriesFind all books published in 2003 & reviewed by both Sammer Tulpule & Hemant Mehta.Find all the reviewers who never reviewed their own books.Find all authors who reviewed more than 2 books written by Sita Mitra.Find all authors who have written exactly 1 book and reviewed more than 1 book.Find all the reviewer who reviewed every book from 'Stephen King'.Find all books published in 1995-2000 in descending order.I know, these is not good to find an answer, but believe me i really don't underThanks & regards,Pooja. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-03-04 : 23:44:04
|
| Homework ?????PBUH |
 |
|
|
pooja28d
Starting Member
8 Posts |
Posted - 2011-03-05 : 00:06:49
|
| YES..........actually, i don't have an idea about how to create the relation. Also, i don't understand anything about example which I mentioned above.Thanks & regards,Pooja. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-05 : 01:56:24
|
| relations can be defined through the Primary and Foreign Keys. and as far as finding the information is concerned all of them could be find either via Simply Where Condition or by Joining tables along with where condition(s)CheersMIK |
|
|
|
pooja28d
Starting Member
8 Posts |
Posted - 2011-03-05 : 07:56:54
|
| @Mike...............will you please tell me by giving example.....it will be better for me, if you explain my tables in details using 'relation'.Thanks & regards,Pooja. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-05 : 10:46:30
|
as far as database design is concern, its solely dependent to the requirements of the business process of the stack holders. It your case my thinking in terms of the tables design could be as following Books (BookId, BookTitle, year, publisherID , price, numbar)Publishers (PublisherId,PublisherName)Authors (AuthorID,AuthorName)BookAuthor(BookId, authorID, earnings)BookReference(BookId, referenceBook, times)Reviewer (ReviewerID,ReviewerName)BookReview(BookId, reviewerID, score)Red Color Highlights the Primary Keys and blue ones are the foreign Keys. However this is just how I assumed and one could come up questions on this structure. as far as the relations are concerned, they are of three types 1) one to one 2) one to many OR Many to One 3) many to many. Google these terms you will get a good amount of info  Yes, you might say that there are a couple tables added in addition to the ones you have mentioned in your example. And its because that your sampled tables are not Normalized. (if you are not familiar with this term google Datbase Normalization)at last, we hope you'll know about database design but if not we would appreciate if you read about it. And then you would be warmly welcome by the folks here when you come up with specific question and ask for Details. Otherwise Google is full of Details kindly refer to it.CheersMIK |
|
|
|
chris_n_osborne
Starting Member
34 Posts |
|
|
|
|
|
|
|
Create a Relation