文字内容
1. DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican Huang Email: licanhuang@zist.edu.cn huang_lican@yahoo.co.uk http://virgo.sourceforge.net/licanhuang.html Zhejiang 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 a database: 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