1 / 23

Database Conceptual and Logical Design

2. Databases Anonymous: A 6-Step Program. Requirements Analysis: what data, apps, critical operationsConceptual DB Design: high-level description of data and constraints typically using ER modelLogical DB Design: conversion into a schemaSchema Refinement: normalization (eliminating redundan

gabe
Download Presentation

Database Conceptual and Logical 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 Conceptual and Logical Design Susan B. Davidson University of Pennsylvania CIS330 – Database Management Systems

    2. 2 Databases Anonymous: A 6-Step Program Requirements Analysis: what data, apps, critical operations Conceptual DB Design: high-level description of data and constraints – typically using ER model Logical DB Design: conversion into a schema Schema Refinement: normalization (eliminating redundancy) Physical DB Design: consider workloads, indexes and clustering of data Application/Security Design

    3. 3 Entity-Relationship Diagram

    4. 4 Conceptual Design Process What are the entities being represented? What are the relationships? What info (attributes) do we store about each? What keys & integrity constraints do we have?

    5. 5 Translating Entity Sets to Logical Schemas & SQL DDL

    6. 6 Translating Relationship Sets Generate schema with attributes consisting of: Key(s) of each associated entity (foreign keys) Descriptive attributes

    7. 7 … OK, But What about Connectivity in the E-R Diagram? Attributes can only be connected to entities or relationships Entities can only be connected via relationships As for the edges, let’s consider kinds of relationships and integrity constraints…

    8. 8 Logical Schema Design Roughly speaking, each entity set or relationship set becomes a table (not always be the case; we’ll discuss this later) Attributes associated with each entity set or relationship set become attributes of the relation; the key is also copied (ditto with foreign keys in a relationship set)

    9. 9 Binary Relationships & Participation Binary relationships can be classified as 1:1, 1:Many, or Many:Many, as in:

    10. 10 1:Many (1:n) Relationships Placing an arrow in the many ? one direction, i.e. towards the entity that’s ref’d via a foreign key Suppose profs teach multiple courses, but may not have taught yet: Suppose profs must teach to be on the roster:

    11. 11 Many-to-Many Relationships Many-to-many relationships have no arrows on edges The “relationship set” relation has a key that includes the foreign keys, plus any other attributes specified as key

    12. 12 Examples Suppose courses must be taught to be on the roster Suppose students must have enrolled in at least one course

    13. 13 Representing 1:n Relationships in Tables

    14. 14 1:1 Relationships If you borrow money or have credit, you might get: What are the table options?

    15. 15 Roles: Labeled Edges Sometimes a relationship connects the same entity, and the entity has more than one role: This often indicates the need for recursive queries

    16. 16 DDL for Role Example

    17. 17 Roles vs. Separate Entities

    18. 18 ISA Relationships: Subclassing (Structurally) Inheritance states that one entity is a “special kind” of another entity: “subclass” should be member of “base class”

    19. 19 But How Does this Translate into the Relational Model? Compare these options: Two tables, disjoint tuples Two tables, disjoint attributes One table with NULLs

    20. 20 Weak Entities A weak entity can only be identified uniquely using the primary key of another (owner) entity. Owner and weak entity sets in a one-to-many relationship set, 1 owner : many weak entities Weak entity set must have total participation

    21. 21 Translating Weak Entity Sets Weak entity set and identifying relationship set are translated into a single table; when the owner entity is deleted, all owned weak entities must also be deleted

    22. 22 Let’s rethink the schema for running example… Problem: currently, information about the subject is repeated for every offering of the course.

    23. 23 N-ary Relationships Relationship sets can relate an arbitrary number of entity sets:

    24. 24 Summary of ER Diagrams One of the primary ways of designing logical schemas CASE tools exist built around ER (e.g. ERWin, PowerBuilder, etc.) Translate the design automatically into DDL, XML, UML, etc. Use a slightly different notation that is better suited to graphical displays Some tools support constraints beyond what ER diagrams can capture

More Related