DATABASE6

2020-03-01 161浏览

  • 1.DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
  • 2.6 Database Design Q:how do we analyze an enterprise and list the data items for a database, then decide how to place these data items columns in relational tables .  put them all in the same table? NO redundency CAPORDERS := C X A X P X O where C.cid = O.cid and A.aid = O.aid and P.pid = O.pid Update Insert new 1.  2. 3. There are two approaches about separating tables for adatabase:Entity-Relationship modeling Normalization Zhejiang Sci-Tech University Lican Huang 2
  • 3.6 Database Design   Zhejiang Sci-Tech University Lican Huang 3
  • 4.6. Database Design  Zhejiang Sci-Tech University Lican Huang 4
  • 5.6. Database Design    Definition 6.1.1 Entity pp332 Definition 6.1.2 Attribute pp332 Figure 6.2 pp333 Zhejiang Sci-Tech University Lican Huang 5
  • 6.6. Database Design    Transformation rule 1 pp334 Example 6.1.1 Transformation rule 2 pp334 example 6.1.2 Zhejiang Sci-Tech University Lican Huang 6
  • 7.6. Database Design  Zhejiang Sci-Tech University Lican Huang 7
  • 8.6. Database Design    Definition 6.1.3 Relationship pp335 Figure 6.3 pp336 Figure 6.5 pp338 Zhejiang Sci-Tech University Lican Huang 8
  • 9.6. Database Design  Zhejiang Sci-Tech University Lican Huang 9
  • 10.6. Database Design    Cardinality of Entity participation in a Relationship Figure 6.6 pp339 Figure 6.7 pp341 Zhejiang Sci-Tech University Lican Huang 10
  • 11.6. Database Design       Transformation Rule 3. N-N Relationships pp 343 Figure 6.8 pp344 Transformation rule 4 N-1 Figure 6.9 pp345 Transformation rule 5 1-1, optional Transformation rule 6 1-1, mandatory Zhejiang Sci-Tech University Lican Huang 11
  • 12.6. Database Design  Zhejiang Sci-Tech University Lican Huang 12
  • 13.6. Database Design   Cardinality of Attributes Figure 6.10 pp 347 Zhejiang Sci-Tech University Lican Huang 13
  • 14.6. Database Design   See also Figure 6.11 Zhejiang Sci-Tech University Lican Huang 14
  • 15.6. Database Design   See also figure 6.12 Zhejiang Sci-Tech University Lican Huang 15
  • 16.6. Database Design--Case Study     Entity Flights, primary identifier flight no, descriptive attribute depart_time (e.g., Nov 19, 9:32 PM). Entity Passengers, primary identifier ti cketno. Entity Seats, identified by seatno, Entity Gates, with primary identifier g ateno. Zhejiang Sci-Tech University Lican Huang 16
  • 17.6. Database Design--Case Study ticketno Passengers gateno Gates marshalls depart_time ddate dtime Flights travels_on has_seat flightno Zhejiang Sci-Tech University Lican Huang seat_assign Seats seatno 17