|
Hi everyone,
I want to make a library project,I have made this model and I have a question is this a correct desgin for implementation or no
Main table is Books I have these fields on it
Book ID Bookname Title publish year description ISBN
Authors table
Book ID author name phone address email
publishers table
BookID publisher name phone address email
BookID's are primary key in this databse and I am going to use of it for relationship
is this a correct design and do I need to insert anything?
Thanks
|
|
|
|
|
messages wrote: question is this a correct desgin for implementation or no
Instead of having each design validated here, you could use the normalization-rules and validate it yourself.
Bastard Programmer from Hell 
|
|
|
|
|
could you help me ,please I have read normalization-rules I want only know is this correct or it has problem(I mean is your sentence).I have almost like this problem I want to know do I need to normalization-rules no?
Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )
|
|
|
|
|
Hamid. wrote: I have almost like this problem I want to know do I need to normalization-rules no?
If you want a decent relational model, then it would be wise to normalize them, at least up to 3NF. You can "prove" that your model is in 1NF by checking the five conditions mentioned on the Wikipedia[^].
I know that you're looking for a yes/no answer, but that's not good enough. If things go wrong you're not helped much by shouting that "Eddy" told you that your model is right/wrong. You want to be able to defend your model, as opposed to taking the word of a stranger.
Bastard Programmer from Hell
|
|
|
|
|
I don't think "book ID" is a good name for the primary key of an authors table.
|
|
|
|
|
Yeah I think ISBN is a good idea.
|
|
|
|
|
Your structure is quite incorrect. Think about 1 to many and ask these questions.
Does a publisher publish only 1 book?
Does and Author ALWAYS only write 1 book?
Does a book ALWAYS have only 1 author.
Go follow Eddies suggestion, it will help you understand the basics.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your answer I have read wikipedia 1NF,....but how can I those rules in my project, I need to have a clear idea to write let me explain in this way
(1)we have some publishers
(2)we have some authors
publishers publish different books of different authors so we need to three tables(publisher,author and book) I guess that they should have a many to many relationship is it correct?
could you tell me please how can I implement relationships between these tables?
Thanks
|
|
|
|
|
Now you are getting the idea.
You need Link tables
lnkPublisherBook
LinkID
BookID
PublisherID
lnkAuthorBook
LinkID
BookID
AuthorID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your help may I ask my questions in the future?
|
|
|
|
|
Never stop asking questions, just make sure you have thought about the questions you are asking. ALWAYS do some research before dumping it into the forum, especially as you are learning. Almost every question you can come up with will have already been asked many times.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi again
I have made this schema,is this correct(its my first question)
LogIn table
user_name(primary key) password AuthorID(Foreign key) PublisherID(foreign key)
Publishers table
publisherID(primark key) publisher name phone address (and other fields)
Authors table
AuthorID(primary key) Author name phone address publisherID(Foreign key) (and other fields)
Books Table
BookID(primary key) Book name Year Title (and other fields)
and its my second question I thought to make this table for AuthorBook
AuthorBook table
AuthorBookID(primary key) AuthorID(Foreign key) BookID(Foreign key)
Do I need to Foreign key in Authors table or Book tables?
Thanks
|
|
|
|
|
Don't you think an author might publish different books via different publishers?
messages wrote: Do I need to Foreign key in Authors table or Book tables?
You should always use foreign keys in your tables whenever possible. It lowers the risk of corruption.
BTW, you're having a good naming standard for your fields.
|
|
|
|
|
Thank you for your help so my code is good and I need only to insert foreign key to Authors,right?
Thanks again
|
|
|
|
|
It's ok so far, but you're not ready yet.
To clarify what I mentioned in my previous message, is that I doubt that it's correct that the Authors table is having the field PublisherID in it. As an author might choose to publish different books at different publishers.
I would argue that you need another link table, the BookPublisher table.
|
|
|
|
|
FYI.
In the world of libraries it is important to note that a book might be bought multiple times even for the same library.
Like when Stephen King comes out with a new book. The library might buy 2 hardback copies that will be kept for a long time, and then they might buy later a set of paperbacks that won't last as long.
So, not only do you need a books table but you also need a table called 'items' that track the existence of the multiple copies of the same book.
When someone checks out a book they are actually checking out an 'item'.
Is this for a real library with patrons or is it a personal library, or an exercise?
In my personal library I have some books where I have more than one copy. Each instance has a different reason for being bought twice but that isn't important.
If you are doing this for a real public library you might have more things to add. You might also have to study the world of MARC records.
http://en.wikipedia.org/wiki/MARC_standards[^]
_____________________________
A logician deducts the truth.
A detective inducts the truth.
A journalist abducts the truth.
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
I am having such a hard time writing a SQL query. Can someone please look over this query and see where am I going wrong?
I will be using the XML elements to describe the table even though I am using SQL Server 2008 R2..
<vPSR>
<ProjectID/>
<ReportMonth Comment="2 digit Month. (MM)" />
<ReportYear Comment="4 digit Year. (YYYY)" />
</vPSR>
Here is an example of the data that is listed.
ProjectID ReportMonth ReportYear
1000700 05 2008
1000700 06 2008
1000700 07 2008
1000700 12 2008
1000700 01 2009 <-- I want the query to find this! Even though the Month is not the max value, but the Year is the Max value.
Here is what I have so far. Where am I going wrong?
SELECT vPSR.ProjectID, vPSR.ReportMonth, vPSR.ReportYear
FROM vPSR
WHERE vPSR.ReportMonth =
(SELECT MAX(ReportMonth)
FROM vPSR AS f
WHERE f.ProjectID = ProjectID
AND ReportYear =
(SELECT MAX(ReportYear)
FROM vPSR AS k
WHERE k.ProjectID = vPSR.ProjectID))
ORDER BY vPSR.ProjectID ASC
I got the idea on how to write the above query here.[^] I don't understand it that well.
If you need me to clarify anything please do not hesitate to ask.
|
|
|
|
|
I'm thinking that ReportMonth and ReportYear should instead have been a ReportDate with some sort of Date datatype. Since they're not, concatenating the two columns seems to work:
SELECT ProjectID, reportMonth, reportYear
FROM test
WHERE (reportYear + reportMonth =
(SELECT MAX(reportYear + reportMonth) AS Expr1
FROM test AS b
WHERE (ProjectID = ProjectID)))
Scott
|
|
|
|
|
You could try something like
SELECT vPSR.ProjectID, vPSR.ReportMonth, vPSR.ReportYear
FROM vPSR
INNER JOIN (
SELECT ProjectID, MAX(reportYear + reportMonth) AS Expr1
FROM vPSR
GROUP by ProjectID
) AS v2
ON vPSR.ProjectID = v2.ProjectID
AND (vPSR.ReportYear + vPSR.ReportMonth) = v2.Expr1
|
|
|
|
|
I whole heartedly agree about concatenating the ReportMonth + ReportYear columns together. That was another idea that I have been thinking about.
Thanks!
|
|
|
|
|
Can you extend the sample population to give some more detail?
In the meantime, here's something to play with;
SET NOCOUNT ON;
BEGIN TRANSACTION
IF OBJECT_ID('vPSR') IS NOT NULL DROP TABLE vPSR
SELECT 1000700 as ProjectID,
05 as ReportMonth,
2008 as ReportYear
INTO vPSR
UNION SELECT 1000700, 06, 2008
UNION SELECT 1000700, 07, 2008
UNION SELECT 1000700, 12, 2008
UNION SELECT 1000700, 01, 2009
UNION SELECT 1000701, 11, 2011
UNION SELECT 1000701, 12, 2009
SELECT TOP 1 *
FROM vPSR
WHERE ReportYear = (SELECT MAX(ReportYear) FROM vPSR)
ORDER BY ReportMonth DESC
SELECT TOP 1 *
FROM vPSR
ORDER BY ReportYear DESC, ReportMonth DESC
SELECT DISTINCT ProjectID
, (SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID)
, (SELECT TOP 1 ReportMonth FROM vPSR WHERE ProjectID = A.ProjectID AND ReportYear = (
SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) ORDER BY ReportMonth DESC)
FROM vPSR A
ROLLBACK
My results;
ProjectID ReportMonth ReportYear
----------- ----------- -----------
1000701 11 2011
ProjectID ReportMonth ReportYear
----------- ----------- -----------
1000701 11 2011
ProjectID
----------- ----------- -----------
1000700 2009 1
1000701 2011 11
Bastard Programmer from Hell 
|
|
|
|
|
Quote: Can you extend the sample population to give some more detail?
Yes, I sure can!
What I need to do is organize a table that has multiple listings of a Project ID and return only 1 of the latest (date related) per Project ID. Therefore, here is a sample listing of the data that I need to organize.
ProjectID ReportYear ReportMonth
----------- ----------- -----------
100005000 2008 5
100005000 2008 6
100005000 2008 7
100006600 2008 8
100006600 2008 9
100006600 2008 10
100006600 2008 11
100006600 2008 12
100006600 2009 1
100006800 2008 8
100006800 2008 9
100006800 2009 1
100006800 2009 2
100006800 2009 3
100006800 2009 4
Note: I trimmed down the data so as not to overwhelm this post, but still relay the idea of what I am dealing with.
There is also one more thing I would like to ask your advice. It's about an idea that I have been thinking about. What if I were to instead create a temporary table and create an extra column with "ReportDate" and add the Year and Month together. (I know someone has also mentioned this in a reply to my first post). So, it would be something to the affect of (ReportYear * 100) + ReportMonth. Then I could write a query with the above sample data and return back per ProjectID the latests info. What do you think? Further, can you please link me to a "How-To" article on creating temp tables in SQL Server 2008?
One last question about something that I don't understand and have been searching MSDN and Google to no avail. It's this whole alias thing ("AS" keyword). You can place use this in for the column name and/or the table name. But I don't understand how I can do something to affect of ...
SELECT MAX(ReportYear) FROM vPSR AS f WHERE f.ProjectID = vPSR.ProjectID
I don't understand how f.ProjectID can equal vPSR.ProjectID. This seems very circular to me. This is a technique that I don't use often. If you have any info/advice about this way of writing a query can you link me?
Thanks!
|
|
|
|
|
Clark Kent123 wrote: What I need to do is organize a table that has multiple listings of a Project ID and return only 1 of the latest (date related) per Project ID.
The last sql-statement is almost a literal translation of that request; it fetches a unique list of all projectid's (distinct), and then the maximum year for that projectid. Lastly, it fetches the maximum month for that projectid in the max(year).
SET NOCOUNT ON;
BEGIN TRANSACTION
SELECT 100005000 as ProjectID,
2008 as ReportYear,
05 as ReportMonth
INTO vPSR
UNION SELECT 100005000, 2008, 6
UNION SELECT 100005000, 2008, 7
UNION SELECT 100006600, 2008, 8
UNION SELECT 100006600, 2008, 9
UNION SELECT 100006600, 2008, 10
UNION SELECT 100006600, 2008, 11
UNION SELECT 100006600, 2008, 12
UNION SELECT 100006600, 2009, 1
UNION SELECT 100006800, 2008, 8
UNION SELECT 100006800, 2008, 9
UNION SELECT 100006800, 2009, 1
UNION SELECT 100006800, 2009, 2
UNION SELECT 100006800, 2009, 3
UNION SELECT 100006800, 2009, 4
SELECT DISTINCT ProjectID
, (SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) AS MaxReportYear
, (SELECT TOP 1 ReportMonth FROM vPSR WHERE ProjectID = A.ProjectID AND ReportYear = (
SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) ORDER BY ReportMonth DESC)
AS MaxReportMonth
FROM vPSR AS A
ROLLBACK
ProjectID MaxReportYear MaxReportMonth
100005000 2008 7
100006600 2009 1
100006800 2009 4
Clark Kent123 wrote: What if I were to instead create a temporary table and create an extra column with "ReportDate" and add the Year and Month together. (I know someone has also mentioned this in a reply to my first post). So, it would be something to the affect of (ReportYear * 100) + ReportMonth. Then I could write a query with the above sample data and return back per ProjectID the latests info. What do you think? Further, can you please link me to a "How-To" article on creating temp tables in SQL Server 2008?
There's two ways of declaring a temp table in Sql Server, and there's a CodeProject article[^] that compares them 
Clark Kent123 wrote: I don't understand how f.ProjectID can equal vPSR.ProjectID. This seems very circular to me. This is a technique that I don't use often. If you have any info/advice about this way of writing a query can you link me?
It's a subquery; we introduced the table under a new name, being called "F" "A". Let's simply walk through the query; it first fetches all projectid's from vPSR (which we'll call "table A"). For each record, it tries to display the values in the columns of our select-statement, and our second column is a new query. So, it executes that query.
SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID
Now, the table in the query was called "A", so for the first record this subquery would read as below;
SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = 100005000
Then it returns that value instead that results from that query.
The technique is called "correlation", which sounds fancy, but really merely means that you're referring to a field in the outer query from a subquery. You could substitute the "A" in the example with any string you'd like, and Sql Server will treat it as if it were a new table with the same contents under that name.
Bastard Programmer from Hell
|
|
|
|
|
Wow. You have given me much food for thought. Let me digest all this info. If I have any questions I will bug you again! 
I really do appreciate the help. 
|
|
|
|
|
My pleasure, and you're welcome 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
