E R Diagram for a Library Database by hft11353

VIEWS: 1,025 PAGES: 8

More Info
									                                                                        DBMS…


         CS 146 Database Systems

                Entity-Relationship (ER) Model




CS 146                                                     1   CS 146                                                  2




         A little history                                               Progression of Database Systems

           In DBMS: single instance of data maintained                   Early 1950’s- file proc, IBM’s Ramac system
           and accessed by different users                               1960’s: first generalized DBMS IBM Sabre
           File Processing: Earliest form of information                 1970’s: Relational model proposed, INGRES,
           storage systems                                               System R, Query languages-
                                                                         Sequel(SQL),QUEL
           each user keeps files needed for specific
           application                                                   1980’s: DBMS for PCs, commercial RDBMS-
                                                                         Oracle, Sybase, Informix
             one user keeps track of students fees and
             payments                                                    1990s: Object-relational DBMS, Multimedia,
                                                                         Spatial/GIS, Data Mining/OLAP, Dist.DB
             second user keeps files on student grades

CS 146                                                     3   CS 146                                                  4
          Part 1: Schedule                                                                  How Does One Build a Database?
                                                                                              Requirements Analysis: what data, apps, critical
                                                                                              operations
              Start with Data Models
                                                                                                Get from “client”
                 Relational Model with a little ER model intro                                  Typically expressed in some natural language
              Formal query languages- Relational algebra                                        May require going back to the client for resolving
              SQL                                                                               questions

              Database schema design: how to design a
              “good” schema, how to measure “good”?
                 Normal Forms (3NF, BCNF)
              Demonstrate concepts learnt on Commercial
              DBMS – Oracle, MySQL

CS 146                                                                         5   CS 146                                                            6




                                                                                            Conceptual Model: Why use a graphical
          Building a Database and Application
                                                                                            language ?
         1.   Start with a conceptual model
                  “On paper” using certain techniques
                  •   E-R Model                                                              Convey database design and properties in
                  ignore low-level details – focus on logical representation                 simple but precise manner
                  “step-wise refinement” of design with client input
                                                                                                Interpreted by any type of user
         2.   Design & implement schema
                                                                                                 • Does not need to know anything about CS
                  Design and codify (in SQL) the relations/tables
                  Refine the schema – normalization                                             Capture the business rules of the application
                  Do physical layout – indexes, etc.
         3.   Import the data
         4.   Write applications using DBMS and other tools
                                                                                             Picture is worth a thousand words
                 Many of the hard problems are taken care of by other people
                 (DBMS, API writers, library authors, web server, etc.)
              DBMS takes care of Query Optimization, Efficiency, etc.

CS 146                                                                         7   CS 146                                                            8
         An Example: “mini” banner                                                   Entity Relationship Model

           Database containing information about                                      Based on collection of real world objects or
             Students                                                                 concept called entities; ex: employee, student
             Faculty                                                                     attribute represents properties of entity; s.s.num
             Courses                                                                  relationship represents interaction between
           Students take courses                                                      entities
           Faculty teach courses                                                      overall logical structure represented by ER
           How to ‘define’ student/faculty/course ?                                   diagram representing entity sets,
                                                                                      relationships, attributes


CS 146                                                                 9    CS 146                                                                        10




         ER Model Basics                                                             ER Model Definitions
         Conceptual design:                                                           Entity: Real-world object distinguishable from other
           What are the entities and relationships in the                             objects.
           enterprise?                                                                   An entity is described (in DB) using a set of
                                                                                         attributes.
           What information about these entities and
                                                                                      Entity Set: A collection of similar entities. E.g., all
           relationships should we store in the database?                             employees.
           What are the integrity constraints or business rules that                     All entities in an entity set have the same set of attributes.
                                                                                         (Until we consider ISA hierarchies, anyway!)
           hold?
                                                                                         Each entity set has a key.
         Can map an ER diagram into a relational                                         Each attribute has a domain.
         schema.                                                                      Representation/Syntax:
                                                                                         Entity set represented by rectangle
                                                                                         Attribute represented by Oval
                                                                                          •   Key attribute underlined
CS 146                                                                 11   CS 146                                                                        12
         ER Model Basics (Contd.)                                                               Conceptual Design Process
           Relationship: Association among two or more entities.                                                                                  STUDENTS
           E.g., Dan takes Database Course; Attishoo works in                                    What are the entities being represented?
           Pharmacy department.
              Relationship can also have attributes (that appear only for this                                                       Takes
                                                                                                 What are the relationships?
              relationship set)
           Representation/Syntax: a Diamond symbol
                                                                                                 What info (attributes) do we store about each?           name
              Attributes represented by Oval (same as before)
           Relationship Set: Collection of similar relationships.                                                                exp-grade
              An n-ary relationship set R relates n entity sets E1 ... En; each                  What keys & integrity constraints do we have?
                                                                                                                                                          sid
              relationship in R involves entities e1 ∈ E1, ..., en ∈ En
                • Same entity set could participate in different relationship
                  sets, or in different “roles” in same set.

CS 146                                                                            13   CS 146                                                                   14




         Student Entity                                                                         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
                                  name
                    sid                                                                          and integrity constraints…

                               Student
                                                                                                 PROFESSORS            Teaches         COURSES


                                                                                                (warning: the book has a slightly different notation here!)
CS 146                                                                            15   CS 146                                                                   16
           Entity-Relationship Diagram
                                                                                                Example: A Company Database
           for the Example
         Underlined attributes are keys
                                                                                                 COMPANY database keeps track of
                                             fid      PROFESSORS           name
                                                                                                 Employees and Departments
                                                                                                    Employees identified by SSN, Name, Location
               entity set      relationship set                                                     Department specified byDepartment ID (did),
                                                           Teaches     semester
                                                                                                    Name, Budget
                                                                                                 Each department has a unique manager
          STUDENTS                  Takes                  COURSES                                  Database must keep track of starting date
                                                                                                 Each employee works in a department
         sid                                       serno     subj    cid                            Database must keep track of starting date
                  name
                                 exp-grade
                                   attributes (recall these have domains)
CS 146                                                                            17   CS 146                                                        18




           Constraints – Key and Participation                                                  Properties of relations
                                                                                                Binary relationships can be classified as one-
                                                                                                to-one, many-to-one, one-to-many, many-to-
               Capture properties of the relationship and entities
                                                                                                many
               Every entity set has a key attribute
                  No two elements can have the same value on this attribute                     What is the type of mapping/relation
                    • Example: Student ID
               Does every element in the entity set
               appear/participate in the relationship ?
                  Must every student take a course ?
               Define constraints based on properties of the
               mapping/relation between entity sets
                                                                                                    1-to-1    1-to Many   Many-to-1   Many-to-Many



CS 146                                                                            19   CS 146                                                        20
         Example: the Teaches relationship                                                   Takes Relationship

          Want to model the info that each course is                                            Student can be enrolled in many courses and each
          taught by one faculty.                                                                Course can have many students
                                                                                                   Type of mapping:
             Type of mapping ???
                                                                                                    • Many to Many
             1-to-1                                                                             Want to model the condition that every student must
              • Note: This is a Mapping and not a function!                                     take at least one course
          Every course must have an instructor                                                     Each student must appear in Takes relationship
             Each element in the Course entity set must                                         How many courses can a student take ?
             participate/appear in the Teaches relationship                                     How many students must be enrolled in a course ?
          A faculty may teach zero or more courses

CS 146                                                                         21   CS 146                                                            22




         Mapping Cardinality, Participation
                                                                                             Roles: Labeled Edges
         Constraints, Structural constraints
                                                                                             Sometimes a relationship connects the same entity, and
                                                                                               the entity has more than one role:
          Type of mapping (cardinality)
             1-1, 1-many, many-many, many-1
             Provides some information on relationship sets
                                                                                                                          TA            course
          Participation constraints
             Total vs Partial                                                                              Student                 TA
              • Total: Every student sid must appear in Takes relationship
              • Partial: All faculty need not appear in Teaches relationship                                                              id
          Structural constraints:                                                                                       Student
             Minimum and maximum times they can appear in                                                                                name
             relationship                                                                                             (Nate, Darby)
             Syntax ??
                                                                                             This often indicates the need for recursive queries
CS 146                                                                         23   CS 146                                                            24
          Roles vs. Separate Entities                                                  Weak Entity Sets
                                                                                        A weak entity can be identified uniquely only by
                                                                                        considering the primary key of another (owner)
         id        Husband        Married            Wife           id                  entity.
                                                                                          Owner entity set and weak entity set must participate in a
                                                                                          one-to-many relationship set (one owner, many weak
                                                      name                                entities).
                  name
                                                                                          Weak entity set must have total participation in this
                                                                                          identifying relationship set.
   What is the difference         Married                                                 If Student is deleted, then we MUST delete the Parent
   between these two
                          Husband         Wife
   representations?                                                                       Syntax: Bold face rectangles, Double lined rectangles,…
                                                              id
                                            Person
                                                             name

CS 146                                                                   25   CS 146                                                                   26




          ISA Relationships: Subclasses
                                                                                       Relationships: Binary or n-ary
          (Structurally)
              Inheritance states that one entity is a “special
                                                                                        Binary: Relationship between two entity sets
              kind” of another entity: “subclass” should be
              member of “base class”                                                    N-ary: Relationship between any N entity sets
                                       id                                                  Not all n-ary can be converted to a set of binary
                         People                                                            relationships
                                     name

                           ISA


                      Employees         salary

CS 146                                                                   27   CS 146                                                                   28
         Conceptual Design Using the ER Model                                    Summary of Conceptual Design
         Design choices:                                                     Conceptual design follows requirements analysis,
           Should a concept be modeled as an entity or an                        Yields a high-level description of data to be stored
           attribute?                                                            Visual language – the diagram is the syntax!
           Should a concept be modeled as an entity or a                     ER model popular for conceptual design
           relationship?
                                                                                 Constructs are expressive, close to the way people think
           Identifying relationships: constraints, type,                         about their applications.
           participation
                                                                                 There are additional constructs in a “real” ER model based
         Constraints in the ER Model:                                            tools.
           A lot of data semantics can (and should) be captured.             Can automate mapping of ER model to relational
           But some constraints cannot be captured in ER                     tables!
           diagrams.
CS 146                                                             29   CS 146                                                            30

								
To top