1 / 99

Database Models and Entity-Relationship Design

Learn about the different types of database models, including hierarchical, network, and relational models, and their advantages and disadvantages. Explore the concept of entity-relationship design and its importance in database management.

gonzalom
Download Presentation

Database Models and Entity-Relationship Design

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. Database Management Systems & Programming LIS 558 - Week 2 Entity Relationship Modeling I Faculty of Information & Media Studies Summer 2000

  2. Class Outline • Database Models • Database Design Problems • Conceptual Design Methodology • Break • Purpose of Data Modeling • Entity-Relationship Design • E-R Terminology • Entity-Relationship Method Examples

  3. Database Models • A data model is the relatively simple representation, usually graphic, of complex real-world data structures. It represents data structures and their characteristics, relations, constraints, and transformations. • The database designer usually employs data models as communications tools to facilitate the interaction among the designer, the applications programmer, and the end user. • A good database is the foundation for good applications.

  4. Database Models • Two Categories of Database Models • Conceptual models focus on the logical nature of the data representation. They are concerned with what is represented rather than how it is represented. • Implementation models place the emphasis on how the data are represented in the database or on how the data structures are implemented.

  5. Database Models • Three Types of Implementation Database Models • Hierarchical database model • Network database model • Relational database model

  6. A Hierarchical Structure

  7. Database Models • Hierarchical Database Model • Basic Structure • Collection of records logically organized to conform to the upside-down tree (hierarchical) structure. • The top layer is perceived as the parent of the segment directly beneath it. • The segments below other segments are the children of the segment above them. • A tree structure is represented as a hierarchical path on the computer’s storage media.

  8. Database Models • Hierarchical Database Model • Advantages • Conceptual simplicity • Database security • Data independence • Database integrity • Efficiency dealing with a large database • Disadvantages • Complex implementation • Difficult to manage • Lacks structural independence • Applications programming and use complexity • Implementation limitations • Lack of standards

  9. Database Models • Network Database Model • Basic Structure • Set -- A relationship is called a set. Each set is composed of at least two record types: an owner (parent) record and a member (child) record. • A set is represents a 1:M relationship between the owner and the member.

  10. A Network Database Model

  11. Database Models • Network Database Model • Advantages • Conceptual simplicity • Handles more relationship types • Data access flexibility • Promotes database integrity • Data independence • Conformance to standards • Disadvantages • System complexity • Lack of structural independencem

  12. Evolution of Conceptual Data Models

  13. The Evolution of Conceptual Data Models • Common characteristics required for data models: • A data model must show some degree of conceptual simplicity without compromising the semantic completeness. • A data model must represent the real world as closely as possible. • The representation of the real-world transformations (behavior) must be in compliance with the consistency and integrity characteristics of any data model.

  14. Database Models • Relational database • Codd, E.F. (1970). A relational model for large shared data banks. CACM, 13(6), 377-87. • First relational prototype - IBM’s system/R • Other variants, e.g., INGRES - UC, Berkeley • 1983 IBM released DB2 • Relational databases required considerable computing resources (not feasible until mid- 1980s) • low end (Access, Paradox, dBase, FoxPro, Clipper) • high end (DB2, Oracle, Sybase, Informix, INGRES commercial)

  15. Database Models • Relational Database defined: • Logical database model that treats data as if they are stored in separate two-dimensional but related tables • Each table consists of data elements describing a common theme among which is one (or more) elements that uniquely describes each record in the table • Tables are related as long as two tables share a common data element • Information in these tables cam be combined on an as-needed basis to get answers to queries and generate complex reports

  16. But #1 problem is still data redundancy and inconsistency! Database Models • Advantages • Mechanisms for minimizing data redundancy and inconsistency • Logical database design is separated from physical aspects • Relatively program-data independent • Management of data for access, manipulation, and security • Flexible mechanisms for generating reports and queries • Program development and maintenance costs are reduced • Data can be accessed in a multiplicity of ways within and amongst organizations • Disadvantages • Ease of use - many untrained people create and use databases without considering its design - usually incorporate many errors • Processing resources required

  17. The Relational Database Model Agents Instruments Clients Entertainers Engagements Entertainer styles • represented by tables (like spreadsheets) • tables are linked with software pointers • unlike earlier systems, all three types of relationships can be represented • accommodates the design of larger databases that involve complex relationships and intricate manipulations

  18. Linking Relational Tables

  19. Database Models • Entity-Relationship Data Model • It is one of the most widely accepted graphical data modeling tools. • It graphically represents data as entities and their relationships in a database structure. • It complements the relational data model concepts.

  20. Database Models • E-R model is commonly used to: • Translate different views of data among managers, users, and programmers to fit into a common framework. • Define data processing and constraint requirements to help us meet the different views. • Help implement the database.

  21. Database Models • Entity Relationship Data Model • Basic Structure • E-R models are normally represented in an entity relationship diagram(ERD). • An entity is represented by a rectangle. • Each entity is described by a set of attributes. An attribute describes a particular characteristics of the entity. • A relationship is represented by a diamond connected to the related entities.

  22. Database Models • Three Types of Relationships • One-to-many relationships (1:M) • A painter paints many different paintings, but each one of them is painted by only that painter. • PAINTER (1) paints PAINTING (M) • Many-to-many relationships (M:N) • An employee might learn many job skills, and each job skill might be learned by many employees. • EMPLOYEE (M) learns SKILL (N) • One-to-one relationships (1:1) • Each store is managed by a single employee and each store manager (employee) only manages a single store. • EMPLOYEE (1) manages STORE (1)

  23. Relationship Depiction: The ERD

  24. Database Models • Entity-Relationship Data Model • Advantages • Exceptional conceptual simplicity • Visual representation • Effective communication tool • Integrated with the relational database model • Disadvantages • Limited constraint representation • Limited relationship representation • No data manipulation language • Loss of information content

  25. Introduction to Database Design Database schema defines database’s structure, tables, relationships, domains, and constraint rules • Tables • BOOK (ISBN, Title, AuthID, PubID, Price) • PUBLISHER (PubID, PubName, PubPhone) • AUTHOR (AuthID, AuthName, AuthPhone) • Relationships • Each book is published by one and only one publisher • Each publisher publishes one or more books • Domains (set of values in a column) • Physical description (e.g., set of integers 0 < x < 99999) • Constraints (business rules) • Price cannot be less than zero; Author phone field cannot be left blank

  26. Database Terminology • Tables hold the data • Database design is the process of separating information into multiple tables that are related to each other • Single table designs work only for the simplest of situations • Anomalies often arise in single table designs as a result of inserting, deleting, or updating records

  27. Table Users view their data in two-dimensional tables. table = file = relation

  28. Field The fields within records contain data. Data within a field must be of the same data type. Each field within a table must have a unique name. Order of fields is unimportant. column = field = attribute

  29. Record A record is a group of related fields of information about a single instance of one object or event in a database. Tables consist of zero, one, or more records. Order of rows is unimportant. row = record = tuple

  30. Data Dictionary and the System Catalog • Data dictionary contains metadata to provide detailed accounting of all tables within the database. • System catalog is a very detailed system data dictionary that describes all objects within the database. • System catalog is a system-created database whose tables store the database characteristics and contents. • System catalog tables can be queried just like any other tables. • System catalog automatically produces database documentation.

  31. Sample Data Dictionary

  32. Keys • A key helps define entity relationships. • The key’s role is based on a concept known as determination, which is used in the definition of functional dependence. • The attribute B is functionally dependent on A if A determines B. • An attribute that is part of a key is known as a key attribute. • A multi-attribute key is known as a composite key. • If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

  33. Keys • Controlled redundancy (shared common attributes) makes the relational database work. • The primary key of one table appears again as the link (foreign key) in another table. • If the foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key are said to exhibit referential integrity.

  34. Indexes • An index is composed of an index key and a set of pointers.

  35. Relational Database Keys

  36. Integrity Rules

  37. Database Components • Tables • Queries • Forms • Reports • Modules

  38. 1. Form- data entry 2. Report- summarizes & prints 3. Query- asks questions 4. Menu - organizes components Major Components of a Database Application 5. Program - used to automate a database

  39. Levels of Database Representation • Three levels of representation • external - user views of data • conceptual - abstract description of data • internal - physical implementation access methods, index construction, data structures • Starting point for design? • Conceptual general description which is then represented in terms of the data contained in the database • Conceptual level is primary focus of this course

  40. A Logical View of Data • Relational database model’s structural and data independence enables us to view data logically rather than physically. • The logical view allows a simpler file concept of data storage. • The use of logically independent tables is easier to understand. • Logical simplicity yields simpler and more effective database design methodologies.

  41. What is Relational Database Design? • Relational Database Summary • logical database model that treats data as if they are stored in separate but related tables • Tables are related as long as two tables share common data element • Information in these tables can be combined on an as-needed basis to retrieve answers to queries and to generate complex reports

  42. What is Relational Database Design?

  43. Why use a Relational Data Model Design? • Small databases can easily be maintained as a single flat file (like a spreadsheet) • For design of larger databases that involve complex relationships and intricate manipulations, a relational model is essential • Originally the major limitation of relational model was memory and processing speed but this is no longer the case • Relational design model resolves a number of potential problems

  44. Database Design Problems • Numerous anomalies can arise during the design of databases • Redundancy • Multi-valued problems • Update anomalies • Insertion anomalies • Deletion anomalies

  45. Database Design Problems • Redundancy • unnecessary repetition of data

  46.   Database Design Problems • Multi-valued problems • e.g., 1 - Add multiple rows, one for each value • Data about a book must be repeated for as many times as there are authors of a book (also creates redundancy)

  47.   Database Design Problems • Multi-valued problems • e.g., 2 - Add multiple columns, one for each value • How many columns for authors must be included in the design (empty fields waste space too)?

  48. Database Design Problems • Multi-valued problems • e.g., 3 - Include all author’s names in a single field • How do you search for a single author’s name or create an alphabetical list of authors

  49. Database Design Problems • Update Anomalies • To update an author’s telephone, each instance must be changed • if we miss an item or enter it incorrectly we create an unreliable table • sometimes previous errors propagate errors further

  50. Propagation of Error Database Design Problems • Update Anomalies • e.g., Consider the author Austen in the following table. What happens if we change her telephone number?

More Related