Relational Database I. Relational Database 1. Introduction 2. Database Models 3. Relational Database 4. Entity-Relationship Models 5. RDB Design Principles 1. Introduction Database Is a collection of related data Is persistent Database Management System Software system to add, delete, search, and modify the data in the DB To provide various ways to view the data in the DB 2. Database Models Flat Database Hierarchical DB Network DB Relational DB Flat Database Flat Database (Library DB) Redundancy Publisher name is repeated Publisher phone number is repeated Author phone is repeated If publisher address is included, … ? Update Anomalies To modify publisher phone, all need to be modified Insertion Anomaly A new publisher, with no data about book, author, and other details which are not available, cannot be included. Deletion Anomaly If a book is deleted, its publisher data, or author data, can also be eliminated (e.g., last book in the list) Solution Break up Flat-file DB into Separe Tables Tables (Library DB) Hierarchical DB Model Agency Database Agents Entertainers Clients Schedules Engagements Payments Back to Ref Ing (This and remaining slides on DB Models are adapted from Database Design for Mere Mortals, Michael Hernandez, Addison Wesley, 1999) Hierarchical DBM Characteristics One table acts as a root of an inverted tree; other tables are branches Each child table may have only one parent table Each parent table may have many child tables To access any data, must start at root table Hierarchical DBM Advantages & Disadvantages Advantages: Data retrieve is fast, because table structure is explicitly linked Referential integrity is built in Disadvantages: Difficult to store data in child table when no corresponding record exists in parent tables. E.g., a new entertainer cannot be entered in the DB until a specific Agent is assigned Difficult to model complex relationships Difficult to add new tables Referential Integrity Each record in a child table is linked to an existing record in the parent table. E.g., every Payment record is associated with a particular Client. If a record is deleted in a parent table,all associated record in child tables are also deleted. E.g., If a particular Client record is deleted, all related records in Payments and Engagements are deleted—no orphans. Network Database Model Agency Database Agents represents manages Clients Entertainers makes schedules performs plays Payments Engagements Musical Styles Network DBM Characteristics Invented to address problems with the Hierarchical DB Model A child table can have more than one parent table Can go up or down the structure. E.g., to answer “Who was the agent that booked a particular engagement?”, start with Engagements, Clients, then to Agents. Relational Database Collection of tables e.g., books, authors, publishers, clients Each cell in a table is atomic i.e., no formulas, pointers, but single data item Tables are linked by common values in selected columns i.e., not by pointers Books Table Publishers Table Basic Terminology Data & Information These are data values Kashimata 25 October 31 25000 This is information Customer’s last Name: Kashimata Age: 25 Birthday: October 31 Savings Account Balance: $25,000 Basic Terminology Null Null, zero, and blank Age = Null age is undefined Age = 0 age is 0 MiddleName = Null middle name is undefined MiddleName = “” person has no middle name MiddleName = “ “ no middle name, takes up 1 byte Basic Terminology Table, Record, Field Formal Informal Non-relational relation table database tuple row record attribute column field Basic Terminology Table Table (Books Table) Represents an entity (category) of objects with its (relevant) attributes Consists of rows (records) and columns (fields) In the Books table, each record is an instance of the Books category. Each field represents an attribute Basic Terminology Attributes (fields) To provide specific information about entity To help identify individual entities--e.g., ISBN for books, SSN for employee, Sales_ID for sales. To describe relationship between entity entities in different entity classes For example... Your Turn. Identify Attributes.. Entity Classes for School Database Students Faculty Staff Buildings Rooms Course Class Identify Attributes... Entity Classes For a Bank Database Customers Account Employee Transaction Basic Terminology Keys Superkey Set of attributes to identify a record uniquely in a set--e.g., to distinguish one book from all others. ISBN, ISBN+TITLE, ISBN+TITLE+AUTHOR Key or Candidate Key Minumum superkey. E.g., ISBN Primary Key A Key that is chosen for a particular table Primary Key Employee Class Candidate Key FirstName SSN LastName FirstName SSN +LastName + PhoneNumber DateOfBirth DateOfBirth E-mail? E-mail E-mail + DateOfBirth Primary Key SSN Types of Relationships (Library DB) Publishers Books How are the tables associated with each other, so that information can be Authors extracted from multiple tables? Types of Relationships 1 to many (most common) A publisher may publish many books, but each book may have only one publisher may to many (undesirable) A book may be written by many authors, and an author may write many books. 1 to 1 (not as common) One author has at most one secretary, and each secretary works for only one author One-To-Many Relationship Publishers Books One record in ∞ Publishers can be 1 related to many records in Books Publishers Books But, each record in 1 Books can be 1 associated with only one record in Publishers Many-to-Many Relationship Authors Books ∞ 1 One record in Authors can be related to many records in Books Authors Books ∞ 1 And, each record in Books can be associated with many records in Authors One-to-One Relationship Authors Secretaries 1 1 One record in Authors can be related to only one record in Secretaries Authors Secretaries 1 And, each record in 1 Secretaries can be associated with only record in Authors Example Database Type Library Tables Books Publishers One-to-Many Relationship? Yes Why? Example Database Type Order Tracking Tables Customers Orders One-to-Many Relationship? Yes Why? Example Database Type School Scheduling Tables Classes Students One-to-Many Relationship? No (Many-to-Many) Why? Data Integrity Table-level integrity E.g., no duplicate records (by defining a primary key) Field-level integrity E.g., do all “State” field require 2 characters? Each field contains a single atomic value? (e.g., Author field should not contain 2 authors Relationship-level integrity E.g., no many-to-many relationships Prevent “orphans.” e.g., if a publisher record is deleted, a book record is left without a publisher.
Pages to are hidden for
"rkmaruyama.netClass420ppt202RelationalDatabase.ppt"Please download to view full document