1 / 20

IST 220 – Intro to Databases

IST 220 – Intro to Databases. Lecture 3 Database Design Guidelines. Recap – Database & DBMS. A database is a collection of related data A DBMS is a system that is designed for two main purposes To add, delete, and update the data in the database

charde-hancock
Télécharger la présentation

IST 220 – Intro to Databases

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. IST 220 – Intro to Databases Lecture 3 Database Design Guidelines

  2. Recap – Database & DBMS • A database is a collection of related data • A DBMS is a system that is designed for two main purposes • To add, delete, and update the data in the database • To provide various ways to view (on the screen or in print) the data in the database • In a relational database, data is stored in a number of tables

  3. A Sample Application – A Library

  4. DB In A Flat File • Problems for DB’s with one BIG table • Unnecessary repetition (or redundancy) • Name and phone number of the Big House publishers repeated six times • Data about different real-world entities is messed up all together • Hard to maintain and manipulate • The library table contains information about • Books • Authors • Publishers • Book-author (relationship)

  5. DB In Related Tables • A solution is to break the big table down to a number of tables (decomposition) • BOOKS – 1 record for each of the 14 books • AUTHORS – 1 record for each of the 13 authors • PUBLISHERS – only three publishers! • BOOK_AUTHOR • Some authors have published more than one book • Some books are co-authored by more than one person

  6. Tables For The Three Entities

  7. The Table For The Relationship One book with three co-authors One author with three books

  8. Complications Of RDB Design • Avoiding data loss during decomposition • Each attribute should be included in at least one table • Maintaining relational integrity • Defining and implementing relationship between tables correctly • Some attributes will show up in more than one tables

  9. Complications Of RDB Design • Creating views • Gathering data from more than one table when needed • Example • Display a list of all publishers that publish books priced under $20.00 – need to access BOOKS and PUBLISHERS tables

  10. Entity & Entity Set • Entities: real-world objects or concepts • Entity set: a set of entities sharing the same properties (or attributes) • Examples • Entity sets: books, authors, publishers • Attributes: author id (AuID), author name (AuName), and author phone (AuPhone) • Entities: Austen, Grumpy, Homer, etc

  11. Relationship & Relationship Set • Relationship– an association among entities • Relationship set– a set of relationships of the same type • Example • Relationship set: book-author • Relationship: Macbeth-Shakespeare, Iliad-Homer

  12. Relationship Among Tables AUTHORS BOOK-AUTHOR BOOKS PUBLISHERS

  13. An Example AUTHORS BOOK-AUTHOR BOOKS PUBLISHERS

  14. DB Design Rules – I • Compound attributes • An attribute which is made up by a few parts • Examples • Name = first-name + last-name • Address = st number, st name, city, state, zip • Rule 1: Field Uniqueness • Avoid using compound attributes • Instead, use a number of attributes, each for one of those parts

  15. DB Design Rules – II • Primary key • A primary key is one or more attributes which can uniquely identify records in a table • Examples • ISBN in the BOOKS table • ISBN and AuID in the BOOK-AUTHOR table • Rule 2: Primary Keys • Each table should have a primary key (PK)

  16. Foreign Keys • An attribute is referred as a foreign key (FK) if it is used as a primary key in another table • Example: PubID in the BOOKS table – FK PubID in the PUBLISHERS table – PK • FK’s are critical in linking the tables together • An attribute can be a foreign key and a part of the primary key at the same time

  17. PK & FK

  18. Cardinality Constraints AUTHORS 1 BOOK-AUTHOR n n BOOKS 1 n 1 PUBLISHERS

  19. DB Design Rules – III • Functional dependence • A property that each attribute in a table is determined by the PK • Satisfy: a book name is known for a given ISBN • Violate: in the flat table, a publisher’s phone may change regardless what the ISBN is • Rule 3: Functional Dependence • For each unique PK value, other attributes must be relevant to, and mustcompletely describe the subject of the table

  20. DB Design Rules – IV • Rule 4: Field Independence • You must be able to make a change to the data in any field (other than a field in the PK) without affecting the data in any other field • Example • In the flat table, if you’ve mistaken the publisher for the book 0-103-45678-9(Iliad). When you want to change the publisher id, you have to change the associated name and phone attributes as well.

More Related