how to convert to 3NF - SQL Server Forums

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.

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?

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

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

n03c03
Starting Member

4 Posts

Posted - 2015-05-03 : 22:25:56

Thanks you for responding!

n03c03
Starting Member

4 Posts

Posted - 2015-05-03 : 22:25:56

Thanks you for responding!

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources