Create a Relation

[deolalkar_pooja]

I created the following tables and inserted few values.

create table books(bookid integer primary key, booktitle varchar(20), year integer, publisherid integer foreign key references publisher(publisherid), price integer, number integer)

create table publisher(publisherid integer primary key, publishername varchar(20)) create table author(authorid integer primary key, authorname varchar(20))

create table bookauthor(bookid integer foreign key references books(bookid), authorid integer references author(authorid), earnings integer )

create table bookreference(bookid integer foreign key references books(bookid), referencebook varchar(20), times integer)

create table reviewer(reviewerid integer primary key, reviewername varchar(20))

create table bookreview(bookid integer foreign key references books(bookid), reviewerid integer foreign key references reviewer(reviewerid), score integer)

Now, I want to solve following query in SQL Server 2000. Find all the books published in 2003 and reviewed by both ‘Sammer Tulpule’ and ‘Hemant Mahta’ . I am not getting any idea about query. How can I write it?

Thanks, Pooja

[debasisdas]

Why you need to create the following tasbles ?

1. bookauthor
2. bookreview
3. bookreference

why not have reference in the book table itself.

[deolalkar_pooja]

actually, i have 4 tables which i normalised into above tables?

[debasisdas]

but i think you are making the design complex quiet unnecessarily.

[deolalkar_pooja]

@debasisdas, Actually..........the basic tables are as follows...
BookAuthor(book, authors, earnings)
BookReference(book, referenceBook, times)
BookReview(Book, reviewer, score)
BookPublish(book, year, publisher, price, num)
.....................................................
These tables I normalized into that tables which i stated in the query. What should be the table normalization according to you?

[debasisdas]

I suggest to go with this approach.

BOOK
BOOK_ID
TITLE
AUTHOR_ID
PUB_ID
REV_ID
ISBN
PRICE
….
…..
….

AUTHOR
AUTHOR_ID
AUTHOR_NAME
….
…..
….

PUBLICATION
PUB_ID
PUB_DATE
…..
….
….

REVIEWER
REV_ID
REV_NAME
…..
….
….

[deolalkar_pooja]

@debasisdas,
Hi, please read following info. about tables and then reply with your normalized tables.

-----------------------------------------------------------------------------
In this database, each book may have one or more authors and each author may make different amount of money from that book. One book may make reference to other books. One book may be reviewed by different reviewers and get difference scores. An author could also be a reviewer and a publisher.

[debasisdas]

Oh dear you should have passed all these info on day one.

Should have posted 14 days back itself.

I will look into the same once i am free.

[deolalkar_pooja]

@debasisdas, YAH......plz look it. i m very new to MS SQL. i studied it, but fail to solve queries