200 likes | 333 Views
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
E N D
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 • 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
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)
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
The Table For The Relationship One book with three co-authors One author with three books
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
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
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
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
Relationship Among Tables AUTHORS BOOK-AUTHOR BOOKS PUBLISHERS
An Example AUTHORS BOOK-AUTHOR BOOKS PUBLISHERS
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
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)
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
Cardinality Constraints AUTHORS 1 BOOK-AUTHOR n n BOOKS 1 n 1 PUBLISHERS
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
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.