Database Schema for Library Management System by lqf20778

VIEWS: 2,950 PAGES: 51

More Info
									What is a Database Management
           System?
                   Spring 2008




Lecture slides by Dr. Sara Cohen
                                   1
              What and Why

• A database management system is a
  program used to:
  – Store large amounts of data
  – Allow easy access to the data (using a query
    language)
  – Separate the physical schema from the logical
    schema


                                                    2
             What and Why

– Protect the data from corruption or security leaks
– Allow multiple users to access the data
  simultaneously
– Provide crash recovery
– And more…




                                                       3
Entity-Relationship Diagrams

          Spring 2008




                               4
                         Scenario
• http://www.imdb.com wants to store information
  about movies and has chosen you to help them
• Three steps:
  – Requirements Analysis: Discover what information needs
    to be stored, how the stored information will be used, etc.
    Taught in course on system analysis and design
  – Conceptual Database Design: High level description of
    data to be stored (ER model)
  – Logical Database Design: Translation of ER diagram to a
    relational database schema (description of tables)
  – Physical Database Design: Done by the DB system
                                                                  5
               Requirements (1)
• For actors and directors, we want to store their
  name, a unique identification number, address and
  birthday (why not age?)
• For actors, we also want to store a photograph
• For films, we want to store the title, year of
  production and type (thriller, comedy, etc.)
• We want to know who directed and who acted in
  each film. Every film has one director. We store the
  salary of each actor for each film

                                                         6
              Requirements (2)
• An actor can receive an award for his part in a film.
  We store information about who got which award for
  which film, along with the name of the award and
  year.
• We also store the name and telephone number of
  the organization who gave the award. Two different
  organizations can give an award with the same
  name. A single organization does not give more than
  one award with a particular name per year.

                                                          7
                                                          address

                              birthday                                      id

                                                   Movie Person
                  phone                                                     name
name
                 number
                           In the rest of
 Organization             this lesson we                   ISA
                            explain this
       Gives                  diagram
                            picture  Actor                       Director

                  Won      salary
                                            Acted In                Directed
       Award

                             year           Film
year           name

                                    title          type
                                                                                   8
     ER-Diagrams: General Information

• ER-diagrams are a formalism to model real-world scenarios
• There are many versions of ER-diagrams that differ both in
  their appearance and in their meaning
• We will use the version appearing in the course book
  (Database Management Systems by Ramakrishnan)
• ER-diagrams have a formal semantics (meaning) that must
  be thoroughly understood, in order to create correct
  diagrams




                                                               9
             Entities, Entity Sets
• Entity (‫ :)ישות‬An object in the world that can be
  distinguished from other objects
   – Examples of entities:


   – Examples of things that are not entities:


• Entity set (‫ :)קבוצת ישויות‬A set of similar entities
   – Examples of entity sets:


    Entity sets are drawn as rectangles

                                                         10
                  Attributes

• Attributes (‫ :)תכונות‬Used to describe
 entities
  – All entities in the set have the same attributes
  – A minimal set of attributes that uniquely identify
    an entity is called a key
  – An attribute contains a single piece of
    information (and not a list of data)


                                                         11
              Attributes (2)

• Examples of attributes:


• Examples of things that cannot be
  attributes:


 Attributes are drawn using ovals
 The names of the attributes which make
 up a key are underlined
                                           12
       Example



                  birthday
id
        Actor


name             address




                             13
Another Option for a Key?



                      birthday
id
          Actor


name                 address




                                 14
Another Option for a Key?



                      birthday
id
          Actor


name                 address




                                 15
 Relationships, Relationship Sets

• Relationship (‫ :)קשר‬Association among two
  or more entities
  – Relationships may have attributes
  – Examples of Relationships:


• Relationship Set (‫ :)קבוצת קשרים‬Set of
  similar relationships
  – Examples of Relationship sets:


   Relationship sets are drawn using diamonds
                                                 16
                     Example

                                       title
       birthday


 id
           Actor         Acted In    Film      year

name


        address                         type




 Where does the salary      salary
   attribute belong?
                                                      17
            Recursive Relationships

• An entity set can participate more than once in a
  relationship
• In this case, we add a description of the role to the ER-
  diagram
                  phone
                 number
                                     manager


            id
                          Employee             Manages
                                      worker
           name

                      address

                                                              18
              n-ary Relationship
• An n-ary relationship R set involves exactly n entity
  sets: E1, …, En.
• Each relationship in R involves exactly n entities:
  e1 in E1, …, en in En
• Formally, R E1x …x En

                   id     Director   name



  id
           Actor          Produced          Film   title

 name
                                                           19
                          Example
 • Suppose that there are:
    – Actors: Mickey Mouse, Donald Duck
    – Directors: Big Bird, Kermit
    – Films: Mickey’s Club        How many triplets can be in the
How many pairs can be in the      relationship set “Produced”?
relationship set “Produced”?

                     id      Director   name



   id
             Actor           Produced          Film      title

  name
                                                                 20
Another Option: Remember
  Recursive Relationships




                            21
                     Important Note

• The entities in a relationship set must identify the
  relationship
• Attributes of the relationship set cannot be used
  for identification!
• Suppose we wanted to store the role of an actor
  in a film.
      – How should we store the role of the actor?
      – How would we store information about a person who
        acted in one film in several roles?

 id
             Actor        Acted In       Film        title

name
                                                             22
       Key Constraints (‫)אילוצי מפתח‬

• Key constraints specify whether an entity can participate in
  one, or more than one, relationships in a relationship set
• When there is no key constraint an entity can participate
  any number of times
• When there is a key constraint, the entity can participate at
  most one time
 Key constraints are drawn using an arrow from the entity
  set to the relationship set



                                                                  23
                     One-to-Many
        A film is directed by at most one director
        A director can direct any number of films

 id
          Director          Directed      Film        title

name




                 Director   Directed     Film                 24
                      Many-to-Many
        A film is directed by any number of directors
        A director can direct any number of films

 id
           Director          Directed     Film           title

name




                  Director   Directed    Film                    25
                    One-to-One
        A film is directed by at most one director
        A director can direct at most one film

 id
         Director          Directed      Film         title

name




                Director   Directed     Film                  26
        Another Example

Where would you put the arrow?



       age
                      father

 id
             Person            FatherOf
                      child
name




                                          27
         Key Constraints in
        Ternary Relationships


                  id     Actor     name



 id
       Director         produced          Film   title

name




                  What does this mean?

                                                         28
         Participation Constraints
             )‫)אילוצי השתתפות‬
• Participation constraints specify whether or not an
  entity must participate in a relationship set
• When there is no participation constraint, it is
  possible that an entity will not participate in a
  relationship set
• When there is a participation constraint, the entity
  must participate at least once
 Participation constraints are drawn using a thick
 line from the entity set to the relationship set
                                                         29
                       Example (1)
   • A film has at lease one director
   • A director can direct any number of films

  id
            Director          Directed     Film   title

 name


Do you think
   that there
 should be a
 participation
  constraint
from Director
 to Directed?      Director   Directed    Film            30
                   Example (2)

 • We can combine key and participation
   constraints.
 • What does this diagram mean?

 id
        Director      Directed    Film    title

name




                                                  31
       Storing Award Information

• What do you think of this?


           org_
           name                           Won
                           Award
           phone
          number    year           name




• To model this correctly we need weak entity sets

                                                     32
            Weak Entity Sets

• Weak entity sets are entity sets that are not
  uniquely identified by their attributes
• A weak entity set has an "identifying
  relationship" with an entity set that is the
  "identifying owner" of the weak entity set




                                                  33
           Weak Entity Sets

A weak entity set must:
  – participate fully in the identifying
    relationship ( a thick line)
  – participate in a one to many relationship
    with the identifying owner ( an arrow)
   Weak entity sets have a thick rectangle,
   their keys are underlined with a broken
   line, and the identifying relationship has a
   thick diamond
                                                  34
                  Example
                                   phone
                 name
                                  number


                  Organization

What would be
the meaning if          Gives

this was not a
                                       Won
  thick line?
                        Award


                 year           name



                                             35
               ‫‪Example‬‬
                                 ‫גדוד‬
                   ‫מספר גדוד‬
                                ‫שייכת ל‬

‫‪How are the‬‬
 ‫‪entity sets‬‬       ‫אות פלוגה‬     ‫פלוגה‬
‫?‪identified‬‬
                                ‫שייכת ל‬


                                ‫מחלקה‬

                               ‫מספר מחלקה‬

                                            ‫63‬
                     Example

• Suppose that you were storing information
 about books.
• Should books be modeled as a weak entity
 set or a regular entity set?
  – Does ISBN identify a book
  – The answer: it depends what type of data you
    are interested in storing!

                                                   37
Copies of Books in Libraries

name       Library                 Owned By

                                               author

                           title     Book
 id
                                                 isbn

  Person
                                    Copy Of


              Borrowed
                                     Copy

                     copy number              condition

                                                          38
              ISA Hierarchies

ISA Relationships: Define a hierarchy
between entity sets
  – ISA is similar to inheritance
   ISA relationships are drawn as a triangle with
    the word ISA inside it. The "super entity-set" is
    above the triangle and the "sub entity-sets" are
    below


                                                        39
                           Example
                                                 What are the
                                                   keys of:
                        address
                                          id     1. Movie Person
  birthday
                     Movie Person                2. Actor
                                          name   3. Director


                         ISA



picture      Actor             Director



                                                                   40
           Overlap Constraints

• Overlap constraints: Determine whether
 two sub-entity sets can contain the same
 entity
  – Example: Can an Actor be a Director?
   Write "Actor OVERLAPS Director". If not
   written, assume no overlap



                                              41
          Covering Constraints

• Covering constraints: Determine whether
 every entity in the super-entity set is also in
 at least one of the sub-entity sets
  – Example: Is every movie person either an Actor
   or a Director?
   Write "Actor AND Director COVER Movie
   Person". If not written, assume no covering


                                                     42
               Aggregation

• Aggregation: Allows us to indicate that a
 relationship set participates in a relationship
 set


• Remember, we want to store information
 about Actors, Films and their award. We will
 see why aggregation is needed for this…
                                                   43
What’s Wrong?
                                 All the actors for all
                                their participation in
                                all the films must get
picture            Actor
                                       an award!

salary
                  Acted In       Award


  year            Film


          title          type




                                                          44
What’s Wrong?
                                An actor may get an
                                 award for a film in
                                which he has never
picture            Actor
                                     acted in!

salary
                  Acted In         Won       Award


  year            Film


          title          type




                                                       45
                     The Solution
            Suppose that there are:
                    3 actors
                     2 films
                    4 awards
   How many pairs can there be in ActedIn?
    How many pairs can there be in Won?
Note that the pairs of Won are of a special type

                                      picture            Actor

                           Won       salary
                                                        Acted In
                  Award

                                       year             Film


                                                title          type


                                                                      46
Final Diagram                                             address

                             birthday                                       id

                                                     Movie Person
                  phone                                                     name
name
                 number


 Organization                                              ISA



       Gives              picture            Actor               Director

                  Won     salary
                                            Acted In                Directed
       Award

                            year            Film
year           name

                                    title          type
                                                                                   47
               References

• “Database Management Systems”, by
 Raghu Ramakrishnan & Johannes Gehrke,
 third edition, McGraw-Hill, 2003.
 Chapters: 2




                                         48
                              Assignment #2
•   In the Ramakrishnan book, exercises 2.2 and 2.3 (Chapter 2). – page 52.:
•   Exercise 2.2:
     – A university DB contains information about professors (identified by social security
       number, or SSN) and courses (identified by courseid). Professors teach courses;
       each of the following situations concerns the Teachers relationship set. For each
       situation, draw an ER diagram that describes it (assuming no further constraints
       hold).
          • Professors can teach the same course in several semesters, and each ofering must be
            recorded.
          • Professors can teach the same course in several semesters, and only the most recent such
            offering needs to be recorded. (Assume this condition applies in all subsequent questions.)
          • Every professor must teach some course.
          • Every professor teaches exactly one course.
          • Every professor teaches exactly one course, and every course must be taught by some
            professor.
          • Now suppose that certain courses can be taught by a team of professors jointly, but it is
            possible that no one professor in a team can teach the course.


                                                                                                          49
                        Assignment #2
• Exercise 2.3:
   – Design and draw ER diagram capturing all the following constraints
     regarding an university DB:
       • Professors have an SSN, a name, an age, a rank, and a research specialty.
       • Projects have a project number, a sponsor name, a starting date, an ending
         date, and a budget.
       • Graduate students have an SSN, a name, an age, and a degree program (M.S.
         or Ph.D.)
       • Each project is managed by one professor (known as the project’s principal
         investigator).
       • Each project is worked on by one or more professors (known as the project’s co-
         investigators).
       • Professors can manage and/or work on multiple projects.
       • Each project is worked on by one or more graduate students (the project’s
         research assistants).


                                                                                           50
                     Assignment #2
• Exercise 2.3 – cont.:
      • When grad. students work on a project, a professor must supervise
        their work on the project. Grad. Students may work on many projects (in
        this case they may have more than one supervisor).
      • Departments have a department number, name, and a main office.
      • Departments have a professor, who runs the department.
      • Professors work in one or more departments, and for each department
        that they work in, a time percentage is associated with their job.
      • Grad. students have one major department in which they are working
        on their degree.
      • Each grad. Student has another, more senior grad. student (a student
        advisor) who advises him/her on what courses to take.



                                                                                  51

								
To top