Database Models and Entity-Relationship Design

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?