SQL Server R2 Management Studio

Personal Home Library

The purpose of this database is to create a home library for my family. It will store data about the tons of MEDIAs (which are DVDs, CDs, books, magazines, newspapers, video games and etc) that need to be organized within the home.
These items will be organized within the four bookshelves located in the library section of the family room. The requirements of the application are to enable users to locate MEDIAs by media type, writer (author or producer), publisher, category (science, math, etc) and which section to locate the media.

Business Rules

Directly-Related Entity Type Pairs and Business Rules:
Media - Writer
A MEDIA is written by one or many Writers. [M]
A WRITER must write at least one or many MEDIAs. [M]
      MEDIA M:N WRITER.
      AE: WRITTENBY
Media - Publisher
A MEDIA is published by one or many PUBLISHER. [M]
A PUBLISHER has to publish at least one or many MEDIAs. [M]
      MEDIA M:N PUBLISHER.
      AE: PUBLISHEDBY
Media - Category
A MEDIA is organized into one CATEGORY. [1]
A CATEGORY may organize one or many MEDIAs. [M]
      CATEGORY 1:M MEDIA.
      CATEGORIZEDBY
Bookshelf - Section
A BOOKSHELF may contain one or
more SECTIONS [M]
A SECTION can be in one or
more BOOKSELF. [M]
      SECTION M:N BOOKSHELF.
      AE: SECTIONRATINGS
Because a new section may begin at the bottom of the bookshelf.
Bookshelf is the strong entity while section is the weak.

2NF and 3NF

Cutting some steps for the purpose this page.
Details for 2NF can be found:
      Hoffer, J. A., Ramesh, V., and Topi, H.
      (2011). Modern Database Management,
      10th Edition. Upper Saddle River:
      Prentice Hall.

2NF: The entities with composite keys have no attributes that are not reliant upon the whole key
      (Hoffer, et al., 2011, p. 185).

3NF: There are no transitive dependences
      (Hoffer, et al., 2011, p. 186).

Updated List of Attributes for Each Entity:
Update is highlighted

MEDIA - Media_ID (PK),
      Media_Type, Category_ID (PK)(FK)

WRITTENBY - Writter_ID (PK),
      LastName, FirstName,
      MiddleName,
      Media_ID (PK)(FK), Media_Title,
      Category_ID(PK)(FK),
      Publisher_ID (PK)(FK)

PUBLSIHEDBY – Publisher_ID (PK),
      Media_ID ((PK)(FK), Publisher_Name

CATEGORIZEDBY - Category_ID (PK),
      Category_Name, Media_ID(PK, FK)

BOOKSHELF - Bookshelf_ID (PK),
      Bookshelf_Name, Section_ID(PK,FK)

SECTIONCATEGORY - Section_ID (PK),
      Section_Name, Bookshelf_ID(PK)(FK),
      Category_ID(PK,FK)

Stages of Database Design

List of Entity Types & Business Rules
Tentative List of Attributes for Each Entity
Database Documentation (Data Dictionary)
Includes 5 tables but no more than 10 tables
Ensure design is in Boyce-Codd normal form (BCNF)
First Iteration of ERD
Apply atomicity constraint
Second Iteration of ERD
State cardinality
Update tables as needed
Draw Final ERD (entity-relationship diagram)

Tentative Attributes

Tentative List of Attributes for Each Entity:
MEDIA - Media_ID (PK)
      Media_Type, Category_ID (PK)(FK)

WRITTENBY - Writter_ID (PK),
      Writer_ Name
      Media_ID (PK)(FK), Media_Title
      Category_ID(PK)(FK), Publisher_ID (PK)(FK)

PUBLSIHEDBY – Publisher_ID (PK)
      Media_ID ((PK)(FK), Publisher_Name

CATEGORIZEDBY - Category_ID (PK)
      Category_Name, Media_ID(PK, FK)

BOOKSHELF - Bookshelf_ID (PK)
      Bookshelf_Name, Section_ID(PK,FK)

SECTIONCATEGORY - Section_ID (PK)       Section_Name, Bookshelf_ID(PK)(FK)
      Category_ID(PK,FK)

Next Step

First Iteration of ERD
Requirements for First Normal Form

• There are no repeating groups in the relation
• A primary key uniquely identifies the relation
• All data elements should contain only one
      value “atomic”

Third Iteration of ERD

This is a 3NF - final ERD

 

Click the ERD below to enlarge

 

Here is the ERD if tables were built
in Microsoft Access

Click here to review Data Dictionary and Data Sample