990 likes | 995 Views
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.
E N D
Database Management Systems & Programming LIS 558 - Week 2 Entity Relationship Modeling I Faculty of Information & Media Studies Summer 2000
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
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.
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.
Database Models • Three Types of Implementation Database Models • Hierarchical database model • Network database model • Relational database model
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.
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
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.
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
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.
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)
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
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
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
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.
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.
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.
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)
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
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
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
Table Users view their data in two-dimensional tables. table = file = relation
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
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
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.
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).
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.
Indexes • An index is composed of an index key and a set of pointers.
Database Components • Tables • Queries • Forms • Reports • Modules
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
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
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.
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
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
Database Design Problems • Numerous anomalies can arise during the design of databases • Redundancy • Multi-valued problems • Update anomalies • Insertion anomalies • Deletion anomalies
Database Design Problems • Redundancy • unnecessary repetition of data
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)
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)?
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
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
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?