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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to convert to 3NF

Author  Topic 

n03c03
Starting Member

4 Posts

Posted - 2015-05-03 : 12:59:42
The State of California has contracted you to build a database for their bookstores.
The State of California operates twelve Community College Bookstores where they currently store
information about the Colleges, Publishers, Authors, and Books.
You have been given the task of automating the antiquated process the bookstores is currently using.
Each one of the twelve Community Colleges has a unique number as an identifier, a College name,
College location and bookstore employees.
Each publisher has a Name, City and a code that uniquely identifies the publisher.
After talking with the employees and studying the data needs of the bookstore, it has been determined that they
need to access and report on the following information:
Requirement 1: For each publisher, list the publisher code, publisher name, and the city in which the
publisher is located.
Requirement 2: For each college, list the number, name, location and number of employees working at
the bookstore.
Requirement 3: For each book, list its code, title, publisher code, publisher name and price.
Requirement 4: For each author, list its author(s) name. A book could have more than one author.

Make sure all tables are normalized to 3NF and exhibit proper relationships

Kristen
Test

22859 Posts

Posted - 2015-05-03 : 13:24:04
For homework you need to show your working so far, and ask a question about which part you are stuck on.
Go to Top of Page

n03c03
Starting Member

4 Posts

Posted - 2015-05-03 : 13:49:33
Here is my work so far
College(CollegeID, CollegeName, CollegeLocation, NumberOfEmployees)
Publishers(PublisherID, PublisherCode, PublisherName, PublisherLocation)
Authors(AuthorFirstName, AuthorLastName,BookCode)
Books(BookCode, Title, PublisherCode, PublisherName, BookPrice)

How does it look?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-03 : 19:38:41
So far not bad. Show the foreign keys that make the relatioships

Gerald Britton
Toronto PASS Chapter
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-03 : 19:40:11
BTW remove publisher name from book table. It belongs in the publisher table

Gerald Britton
Toronto PASS Chapter
Go to Top of Page

n03c03
Starting Member

4 Posts

Posted - 2015-05-03 : 22:25:56
Thanks you for responding!
Go to Top of Page

n03c03
Starting Member

4 Posts

Posted - 2015-05-03 : 22:25:56
Thanks you for responding!
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-05-04 : 01:59:49
Shouldn't book code in Authors be part of a Link table , which would link Authors and books ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-04 : 03:43:16
I would have an AuthorID column in the Authors table, and then as Jackk says add a BookAuthorLink table with columns AuthorID and BookCode

By the by, if BookCode is a number, like 1,2,3,... same as CollegeID and PublisherID I would name it BookID (for consistency). If it is an "ABC123" type code then the name is fine.
Go to Top of Page
   

- Advertisement -