1 / 14

Define data required

Textbook info : ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Copy Number Status at EIU SSN of student Student Name Student Major Course ID(s) Title of Course(s) Credit for course(s). Define data required.

lilli
Download Presentation

Define data required

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Copy Number Status at EIU SSN of student Student Name Student Major Course ID(s) Title of Course(s) Credit for course(s) Define data required Here are some fields used by textbook library. They need to know what books are used for what classes. They also need to know what books are checked out by what students. One student can have many individual books checked out, but one individual book can be used by only one student at a time. One course can use many books and the same set of books can be used by multiple classes.

  2. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Copy Number Status at EIU SSN of student Student Name Student Major Course ID(s) Title of Course(s) Credit for course(s) Define data required 1. Select the Primary Key

  3. First Normal Form – Remove Repeating Fields • Textbook info: • ISBN • Author • Book Title • Publisher • Date Published • Edition • Date Adopted at EIU • EIU Copy Number • Status at EIU • SSN of student • Student Name • Student Major • Course ID(s) • Title of Course(s) • Credit for course(s) • Remove repeating groups into a new entity. • Copy the PK of the new entity to link back to the original entity • Select the PK for the new entity. Usually of PK of the original key plus a unique component of the repeating group.

  4. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU EIU Copy Number Status at EIU SSN of student Student Name Student Major Course ID(s) Title of Course(s) Credit for course(s) First Normal Form – Remove Repeating Fields • Remove repeating groups into a new entity. • Copy the PK of the new entity to link back to the original entity • Select the PK for the new entity. Usually of PK of the original key plus a unique componentof the repeating group.

  5. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU EIU Copy Number Status at EIU SSN of student Student Name Student Major First Normal Form – Remove Repeating Fields • Copy the PK of the new entity to link back to the original entity • Select the PK for the new entity. Usually of PK of the original key plus a unique component of the repeating group. • Remove repeating groups into a new entity. There is still a repeating group in the original entity so we get to do it again. • Textbook-Course info: • ISBN FK • Course ID(s) • Title of Course(s) • Credit for course(s)

  6. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Copy Number Status at EIU SSN of student Student Name Student Major First Normal Form – Remove Repeating Fields • Remove repeating groups into a new entity. • Copy the PK of the new entity to link back to the original entity • Select the PK for the new entity. Usually of PK of the original key plus a unique component of the repeating group. • Textbook-Course info: • ISBN • Course ID(s) • Title of Course(s) • Credit for course(s) There are many individual copies of books for one ISBN (set of books)

  7. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU First Normal Form – Remove Repeating Fields • Textbook-Copy info: • ISBN FK • Copy Number • Status at EIU • SSN of student • Student Name • Student Major • Textbook-Course info: • ISBN FK • Course ID(s) • Title of Course(s) • Credit for course(s)

  8. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Second Normal Form – Remove Partial Dependencies • Textbook-Copy info: • ISBN FK • Copy Number • Status at EIU • SSN of student • Student Name • Student Major Check tables with composite keys. Are any of the non-key attributes dependent on only part of the key? • Textbook-Course info: • ISBN FK • Course ID(s) • Title of Course(s) • Credit for course(s)

  9. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Second Normal Form – Remove Partial Dependencies • Textbook-Copy info: • ISBN FK • Copy Number • Status at EIU • SSN of student • Student Name • Student Major Check tables with composite keys. Are any of the non-key attributes dependent on only part of the key? • Textbook-Course info: • ISBN FK • Course ID(s) • Title of Course(s) • Credit for course(s) Yes, Title/Credit are dependent on only the Course ID [part of the key]. Move title/credit to a new table and copy the course ID.

  10. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Second Normal Form – Remove Partial Dependencies • Textbook-Copy info: • ISBN FK • Copy Number • Status at EIU • SSN of student • Student Name • Student Major • Textbook-Course info: • ISBN FK • Course ID(s) FK • Course info: • Course ID(s) • Title of Course(s) • Credit for course(s)

  11. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Third Normal Form – Remove Non-key Dependencies • Textbook-Copy info: • ISBN FK • Copy Number • Status at EIU • SSN of student • Student Name • Student Major Student Name and Major are dependent on SSN of student. All of these are non-key fields. Move the non-key fields that are dependent on another non-key field to another table. Copy of field that they are dependent on to that table. It will be the PK of the new table. • Textbook-Course info: • ISBN FK • Course ID(s) FK • Course info: • Course ID(s) • Title of Course(s) • Credit for course(s)

  12. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Third Normal Form – Remove Non-key Dependencies • Textbook-Copy info: • ISBN FK • Copy Number • Status at EIU • SSN of student FK • Student info: • SSN of student • Student Name • Student Major • Textbook-Course info: • ISBN FK • Course ID(s) FK • Course info: • Course ID(s) • Title of Course(s) • Credit for course(s)

  13. Textbook info: ISBN Author Book Title Publisher Date Published Edition Date Adopted at EIU Remove easily computed fields • Textbook-Copy info: • ISBN FK • Copy Number • Status at EIU • SSN of student FK • Student info: • SSN of student • Student Name • Student Major • Textbook-Course info: • ISBN FK • Course ID(s) FK • Course info: • Course ID(s) • Title of Course(s) • Credit for course(s) There are no easily computed fields in this example.

  14. Student info: SSN Name Major Check the ERD for consistency • Textbook info: • ISBN • Author • Book Title • Publisher • Date Published • Edition • Date Adopted at EIU Course/Textbook info: • Course ID FK • ISBN FK Course info: • Course ID • Title of Course • Credit 1:M M:1 M 1:M Textbook Copies info: • ISBN • Copy Number • Status at EIU FK • SSN of student M:1

More Related