Docstoc

Supplier Database Form

Document Sample
Supplier Database Form Powered By Docstoc
					Relational Database Design I




                    Database Principles
Good Tables versus Bad Tables:

• A table in a relational database is good if it is about one
  thing. A table that is not good is bad.

• Problem of RDB Design: Build good tables and convert
  bad tables into good tables.

• What is a table “about”? The key to the answer is the key.

• The key to a table is the identifier of whatever the table is
  about.



                         Database Principles
Good Table Examples:

  Supplier                                          Part
    Sno       Sname     Location                    Pno    Pdesc           Colour

     s1       Acme         NY                       p1       screw          red
     s2       Ajax         Bos                     p2        bolt         yellow
     s3       Apex         Chi                     p3        nut          green
     s4       Ace           LA                     p4        washer           red
     s5       A-1          Phil

                                              Supplier is good because its key is Sno, which
  Supplies                                    identifies different suppliers, and each column
    Sno      Pno      O_date                  in Supplier – Sname and Location – is a piece
                                              of information about Suppliers.
    s1       p1       nov 3
    s2       p2       nov 4
     s3      p1       nov 5                Exercise: Explain why Part is a good table.
    s3       p3       nov 6
    s4       p1       nov 7
    s4       p2       nov 8               Supplies is good because its key is (Sno,Pno),
    s4       p4        nov 9              which identify individual orders, and the only
                                          other column in the table – O_date – is a piece
                                          of info about individual orders.



                                   Database Principles
Bad Table Example:

        Supplier   Part     Supplies
        Sno        Sname          Location   O_date       Pno    Pdesc       Color

         s1          Acme              NY    nov 3        p1    screw       red
         s2          Ajax              Bos   nov 4         p2    bolt     yellow
         s3          Apex              Chi   nov 5        p1    screw       red
         s3          Apex              Chi   nov 6        p3    nut        green
         s4          Ace               LA    nov 7        p1    screw        red
         s4          Ace                LA   nov 8        p2      bolt    yellow
         s4          Ace               LA     nov 9       p4    washer      red




  Even though this table has info about Suppliers, Parts and Supplies its key is (Sno,Pno).
  And so this table is “about” whatever its key identifies, namely Supplies.

  But the table contains various columns – Sname, Location, Pdesc, Color – that are not
  about Supplies but about Supplier and Part respectively.

  So this table is “bad” and the process of RDB Design would be to reform this table into
  the three tables on the previous slide.

  Quick Observation: If your tables come from an ERD they are normally pretty “good”.

                                         Database Principles
Bad Tables can be Useful, if not Good:

• The previous table, called bad, is so only if it is a
  permanent table. As part of an on-going query it is not
  considered bad since data is not stored permanently in
  this format.

• Suppose you never, ever expect to look at the supplies
  table information without knowing the name of the
  supplier and the part supplied. If the join table does not
  exist then you will always have to construct the join. This
  can be time consuming and to save that time you might
  keep the table in pre-joined, permanent form.


                        Database Principles
What’s so Bad about a Bad Table?

• Suppose instead of three tables
   Supplier                                         Part                                            Supplies
    Sno       Sname       Location                  Pno         Pdesc              Colour             Sno      Pno   O_date
     s1       Acme           NY                      p1          screw              red
                                                                                                      s1       p1    nov 3
     s2       Ajax           Bos                    p2           bolt             yellow
     s3       Apex           Chi                    p3           nut              green               s2       p2    nov 4
     s4       Ace             LA                    p4           washer               red              s3      p1    nov 5
     s5       A-1            Phil                                                                     s3       p3    nov 6
                                                                                                      s4       p1    nov 7
                                                                                                      s4       p2    nov 8
                                                                                                      s4       p4     nov 9
  we only have one table
   Supplier   Part     Supplies
    Sno       Sname          Location   O_date            Pno             Pdesc             Color

     s1         Acme              NY    nov 3             p1            screw           red
     s2         Ajax              Bos   nov 4              p2            bolt         yellow
     s3         Apex              Chi   nov 5             p1            screw           red
     s3         Apex              Chi   nov 6             p3            nut            green
     s4         Ace               LA    nov 7             p1            screw            red
     s4         Ace                LA   nov 8             p2              bolt        yellow
     s4         Ace               LA     nov 9            p4            washer          red




                                                 Database Principles
Insert Anomaly:

• We want to add supplier A-1 to the database but for now
  we have no parts that A-1 supplies. Since the key to the
  table is (Sno,Pno) we can’t add a row until we have
  values for both Sno and Pno.

   Supplier   Part     Supplies
    Sno       Sname          Location        O_date        Pno     Pdesc         Color

    s1          Acme               NY        nov 3         p1    screw        red
    s2          Ajax               Bos       nov 4          p2    bolt      yellow
    s3          Apex               Chi       nov 5         p1     screw       red
    s3          Apex               Chi       nov 6         p3     nut        green
    s4          Ace                LA        nov 7         p1     screw        red
    s4          Ace                 LA       nov 8         p2      bolt     yellow
    s4          Ace                LA         nov 9        p4    washer       red
    s5          A-1               Phil       null         null   null         null



                                                                 not permitted
                                         Database Principles
Update Anomaly:

• What happens if the part, p2, changes its color from
  yellow to purple?
     Supplier   Part     Supplies
      Sno       Sname          Location      O_date       Pno    Pdesc      Color

      s1          Acme              NY       nov 3        p1    screw      red
      s2          Ajax              Bos      nov 4         p2    bolt    yellow
      s3          Apex              Chi      nov 5        p1    screw      red      multiple
      s3          Apex              Chi      nov 6        p3    nut       green     changes
      s4          Ace               LA       nov 7        p1    screw       red
      s4          Ace                LA      nov 8        p2      bolt   yellow
      s4          Ace               LA        nov 9       p4    washer     red




• We must search every row of the join-table and change
  every instance of yellow to purple in rows involving the
  supplying of part, p2.

                                          Database Principles
Update Anomaly (cont):

• This one change in the real world makes for many
  changes in the database.
• What if we mess up and end up not making all changes?
   Supplier   Part     Supplies
    Sno       Sname          Location     O_date       Pno     Pdesc     Color

    s1          Acme              NY      nov 3        p1     screw      red
    s2          Ajax              Bos     nov 4         p2     bolt    yellow
                                                                                 two
    s3          Apex              Chi     nov 5        p1     screw      red
    s3          Apex              Chi     nov 6        p3     nut       green    different
    s4          Ace               LA      nov 7        p1     screw       red    colors
    s4          Ace                LA     nov 8        p2       bolt   purple
                                                                       yellow
    s4          Ace               LA       nov 9       p4     washer     red



• Now, what color is p2?


                                        Database Principles
Delete Anomaly:

• What if we cancel the order for bolts from supplier, s2?
    Supplier   Part     Supplies
     Sno       Sname          Location     O_date       Pno     Pdesc      Color

     s1          Acme              NY      nov 3        p1     screw      red
     s2          Ajax              Bos     nov 4         p2     bolt    yellow
     s3          Apex              Chi     nov 5        p1     screw      red
     s3          Apex              Chi     nov 6        p3     nut       green
     s4          Ace               LA      nov 7        p1     screw       red
     s4          Ace                LA     nov 8        p2       bolt   yellow
     s4          Ace               LA       nov 9       p4     washer     red




• A consequence is that we lose all information about the
  supplier, s2.



                                         Database Principles
So What Can Be Done?

• Suppose we keep these two tables
   Supplier                                         Part                                            Supplies
    Sno       Sname       Location                  Pno         Pdesc              Colour             Sno       Pno      O_date
     s1       Acme           NY                      p1          screw              red
                                                                                                      s1        p1       nov 3
     s2       Ajax           Bos                    p2           bolt             yellow
     s3       Apex           Chi                    p3           nut              green               s2        p2       nov 4
     s4       Ace             LA                    p4           washer               red              s3       p1       nov 5
     s5       A-1            Phil                                                                     s3        p3       nov 6
                                                                                                      s4        p1       nov 7
                                                                                                      s4        p2       nov 8
                                                                                                      s4        p4        nov 9
  and we also keep this table
   Supplier   Part     Supplies
    Sno       Sname          Location   O_date            Pno             Pdesc             Color

     s1         Acme              NY    nov 3             p1            screw           red
     s2         Ajax              Bos   nov 4              p2            bolt         yellow
     s3
     s3
                Apex
                Apex
                                  Chi
                                  Chi
                                        nov 5
                                        nov 6
                                                          p1
                                                          p3
                                                                        screw
                                                                        nut
                                                                                        red
                                                                                       green
                                                                                                            Any ideas?
     s4         Ace               LA    nov 7             p1            screw            red
     s4         Ace                LA   nov 8             p2              bolt        yellow
     s4         Ace               LA     nov 9            p4            washer          red




                                                 Database Principles
So What Can Be Done? (cont)

• There is the issue of data consistency.
• Given the same information stored in several places it
  becomes a big job to make sure this data is consistent.
• If we lose data consistency then all the data essentially
  becomes “noise”.




                        Database Principles
Some Notation:
• A table is sometimes called a relation.
   – We use R, S and T and nearby letters to represent
     tables.
• Table columns are also called attributes.
   – We use A, B and C and nearby letters to represent
     columns.
• The possible values in a column A of table R are called the
  domain of A, dom(A).
• Table schemas are lists of table columns.
   – We use R, S and T to represent schemas.




                       Database Principles
Some Notation (cont):
• Table rows are also called tuples.
   – We use r, s and t and nearby letters to represent rows

Subsets of a table schema are represented by X, Y and Z and
  nearby letters.
   – X R is a subset of the list of all columns in a table.
       ∩




• r[A] is the value in row r column A.

• r[X] is the subrow of r consisting of the values in the
  columns of X.


                         Database Principles
Database Principles
What is a Key to a Table?
             Def’n: For any table R, if X is a subset of R, then X is a key to the
             table R if the following is true:
               for any two rows r and s of R, if r[X] = s[X] then r = s. In other
               words, r and s are the same row.

             In other words, any two rows that agree on X agree everywhere

• A key is a set of columns of a table whose values
  uniquely identify distinct rows of the table.
• A key is a set of columns of a table such that if you
  know the values of the columns in the key, there is at
  most one row in the table with these values.
  Supplier
   Sno          Sname      Location
                                                 One key to Supplier is {Sno}; are there any others?
    s1          Acme          NY
    s2          Ajax          Bos
    s3          Apex          Chi                What about {Sno, Location}?
    s4          Ace            LA
    s5          A-1           Phil


                                          Database Principles
Not all Columns are Keys:

• Why isn’t {Location} a key to Supplier?
      Supplier
       Sno       Sname   Location

        s1       Acme       NY
        s2       Ajax       Bos
        s3       Apex       Chi
        s4       Ace         LA
        s5       A-1        Phil




• Because at some point in the future we may add a new
  supplier who comes from Boston, for example.




                                    Database Principles
Database Principles
Exercise:

• Prove that any table has at least one superkey.

• Answer: The schema itself is a superkey.




                       Database Principles
Keys, Keys and More Keys:

• Some keys are smaller (fewer columns) than others.
• Some keys can’t be made any smaller (fewer columns).
       Supplier
        Sno       Sname     Location

         s1       Acme         NY
         s2       Ajax         Bos
         s3       Apex         Chi
         s4       Ace           LA
         s5       A-1          Phil

           Def’n: For a table R, if X is a key of R and for any Y




                                                                ∩
                                                                    X, we know
           that Y is not a key of R, then X is called a candidate key of R.

           The only candidate key to Supplier is {Sno}.




                                  Database Principles
Candidate Keys:

• What made us decide {Sno} was a candidate key of
  Supplier?
           We know that no two suppliers were assigned the same number



• What makes us say the {Location} is not a key to
  Supplier?
      We know there is no rule saying suppliers must come from different locations.



• What makes us decide that something is a key is a rule
  about the real world that makes it so. We call such a rule
  and Enterprise Rule.

                                  Database Principles
Multiple Candidate keys (1):

• In the table below there are 2 possible candidate keys:
         Student
            StudentID   SSN   Fname    Lname   DOB   Address




                   {StudentID} and {SSN}




                                      Database Principles
Multiple Candidate keys (2):

• In the table below there are 2 possible candidate keys:

        Fall08RoomAssignments
        CourseID SectionID    RoomNum   BldgID   TimeSlot




        {CourseID,SectionID} and {RoomNum, BldgID,TimeSlot}



• Candidate keys don’t need to be the same size.




                                  Database Principles
Primary key:

• What do you do when you have candidates?

                hold an election


• The candidate key that wins the election is called the
  primary key. There is only one primary key in a table.




                            Database Principles
Primary Key Examples:

• What are the primary keys of each of the tables below.
   Supplier                                             Part
    Sno       Sname     Location                        Pno       Pdesc      Colour

     s1         Acme       NY                            p1        screw      red
     s2         Ajax       Bos                          p2         bolt     yellow
     s3         Apex       Chi                          p3         nut      green
     s4         Ace         LA                          p4         washer       red
     s5         A-1        Phil
   pk = {Sno}                                                 pk = {Pno}

                              Supplies
                                   Sno     Pno        O_date

                                   s1      p1          nov 3
                                   s2      p2          nov 4
                                    s3     p1          nov 5
                                   s3      p3          nov 6
                                   s4      p1          nov 7
                                   s4      p2          nov 8
                                   s4      p4           nov 9

                          pk = {Sno,Pno,O_date} or {Sno,Pno}

 the difference in what is the primary key is determined by the Enterprise Rules
                                         Database Principles
Foreign Keys:

• What makes someone a foreigner?
           being physically in a country other than their own

• What makes a set of columns a foreign key?
     columns are a foreign key if they are a primary key in some other table



• In the Supplies table both {Sno} and {Pno} are foreign
  keys because they are primary keys in other tables;
  Supplier and Part respectively.




                                  Database Principles
What are the Foreign Keys?
    Cardholder

      borrowerid       b_name             b_addr    b_status   loan_limit

             pk


    Reserves

      borrowerid       isbn          r_date

                  pk
        fk                 fk
    Book

     isbn         author          title     pub_name      pub_date       c_price

      pk



    Copy                                                       Borrows

     accession_no               isbn      p_price              borrowerid        accession_no   l_date

            pk                  fk
                                                                   fk       pk      fk


                                                     Database Principles

				
DOCUMENT INFO
Description: Supplier Database Form document sample