Relational Database Management System RDBMS 1 Instances of
การจดการระบบฐานขอมล (Relational Database Management System: RDBMS) 1
Instances of Branch and Staff (part) Relations 11
ดกรความสมพนธ l The most common degree for relationships is binary. l Binary relationships are generally referred to as being: l l l one-to-one (1: 1) one-to-many (1: *) many-to-many (*: *) 12
Keys l Candidate Key l l Primary Key l l Minimal set of attributes that uniquely identifies each occurrence of an entity type. Candidate key selected to uniquely identify each occurrence of an entity type. Composite Key l A candidate key that consists of two or more attributes. 13
E-R Diagrams n Rectangles represent entity sets. n Diamonds represent relationship sets. n Lines link attributes to entity sets and entity sets to relationship sets. n Ellipses represent attributes l Double ellipses represent multivalued attributes. l Dashed ellipses denote derived attributes. n Underline indicates primary key attributes (will study later)
E-R Diagram With Composite, Multivalued, and Derived Attributes
Relationship Sets with Attributes
Roles l l Entity sets of a relationship need not be distinct The labels “manager” and “worker” are called roles; they specify how employee entities interact via the works_for relationship set. Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. Role labels are optional, and are used to clarify semantics of the relationship
One-To-Many Relationship l In the one-to-many relationship a loan is associated with at most one customer via borrower, a customer is associated with several (including 0) loans via borrower
Many-To-One Relationships l In a many-to-one relationship a loan is associated with several (including 0) customers via borrower, a customer is associated with at most one loan via borrower
Many-To-Many Relationship l l A customer is associated with several (possibly 0) loans via borrower A loan is associated with several (possibly 0) customers via borrower
Participation of an Entity Set in a Relationship Set n Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set l E. g. participation of loan in borrower is total 4 every loan must have a customer associated to it via borrower n Partial participation: some entities may not participate in any relationship in the relationship set l Example: participation of customer in borrower is partial
Alternative Notation for Cardinality Limits n Cardinality limits can also express participation constraints
Summary of Multiplicity Constraints 23
E-R Diagram with a Ternary Relationship
ทฤษฎการขนตอกนของฟงกชน Functional Dependency l พจารณาในเชงของความสมพนธของเอนตทและก ารขนอยกบกนและกน l Diagrammatic representation: u Determinant of a functional dependency refers to attribute or group of attributes on left-hand side of the arrow. 25
ตวอยาง - Functional Dependency 26
ผงเหตการณ Occurrence Diagram 27
ER Diagram of Staff and Branch Entities and their Attributes 28
Strong Entity Type called Client and Weak Entity Type called Preference 29
Relationship called Advertises with Attributes 30
Semantic Net of Staff Manages Branch Relationship Type 31
Multiplicity of Staff Manages Branch (1: 1) Relationship Type 32
Semantic Net of Staff Oversees Property. For. Rent Relationship Type 33
Multiplicity of Staff Oversees Property. For. Rent (1: *) Relationship Type 34
Semantic Net of Newspaper Advertises Property. For. Rent Relationship Type 35
Multiplicity of Newspaper Advertises Property. For. Rent (*: *) Relationship 36
Multiplicity of Ternary Registers Relationship 37
Semantic Net of Ternary Registers Relationship with Values for Staff and Branch Entities Fixed 38
Multiplicity as Cardinality and Participation Constraints 39
การแปลงโมเดลใหอยในรปของตาราง l กรณทความสมพนธเปนแบบ หนงตอหนง Student(scode, sname, major birthday, address) Project(proj_code, proj_name, year, scode) l กรณทความสมพนธเปนแบบ หนงตอหลาย Advisor(adv_code, adv_name, office, tel_no) Student(scode, sname, major, birthday, address, adv_code) 40
การแปลงโมเดลใหอยในรปของตาราง l กรณทความสมพนธเปนแบบ หลายตอหลาย Student(scode, sname, major, address) Course(course_code, cname, credit_lect, credit_lab) Register(scode, course_code, semester, year, grade) เมอนำเอาตารางทงหมดมาแสดงจะไดตารางดงน Student(scode, sname, major, address) Project(proj_code, proj_name, year, scode) Advisor(adv_code, adv_name, office, tel_no) Course(course_code, cname, credit_lect, credit_lab) 41
- Slides: 41