Chapter2_more

Chapter2_more - CSIS0278A Introduc0on to Database...

Info icon This preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: CSIS0278A Introduc0on to Database Management Systems Module 2: En,ty ­Rela,onship Model Dr. Reynold C. K. Cheng Based on the Ch. 6 notes of “Database System Concepts” by A. Silberschatz, H. Korth & S. Sudarshan and notes by Dr. HO Wai Shing Overview ! Basics En,ty Sets, AMributes, Weak En,ty Sets ! Rela,onship Sets, Mapping Constraints ! ! E ­R Diagram ! Extended E ­R Features ! Design of an E ­R Database Schema ! Reduc,on of an E ­R Schema to Tables DB020:2 En0ty Sets ! In En,ty ­Rela,onship Model (E ­R Model), a database can be modeled as ! ! ! An en0ty is an object that exists and is dis,nguishable from other objects. ! ! e.g., a person, a company, a plant, etc. En,,es have [email protected] ! ! a collec,on of en,,es rela,onships among en,,es e.g., people have names and addresses An en0ty set is a set of en,,es of the same type that share the same proper,es (aMributes) ! e.g., a set of all persons, all companies, all trees, etc. DB020:3 En0ty vs. En0ty Sets id = 321 ­12 ­3123 name = Jones street = Main city = Harrison id = 677 ­89 ­9011 name = Hayes street = Main city = Harrison id = 019 ­28 ­3746 name = Smith street = North city = Rye En,ty Set Account … number = A ­102 amount = 1000 … number = A ­101 amount = 2000 En,ty Set Customer another en,ty an en,ty DB020:4 [email protected] ! An en,ty is represented by a set of aMributes, e.g., ! ! ! Domain ! ! Customer(customer_id, customer_name, customer_street, customer_city) Account(account_number, amount) the set of permiMed values of each aMribute AMribute types Simple vs. Composite aMributes ! e.g., address is a composite aMribute ! Single ­valued vs. Mul, ­valued aMributes ! e.g., phone_number (If this aMribute that can hold more than one phone number, it’s a mul, ­valued aMribute.) ! Derived aMributes ! e.g., age, if only DOB is stored. ! DB020:5 [email protected] ! Composite aMributes: ! Mul, ­valued aMribute: a customer id = 321-12-3123 name = Jones phone = 12345678, 23456789, 98765432 DB020:6 Rela0onship Sets ! A rela0onship is an associa,on among two or more en,,es. e.g., this is a relationship id = 677-89-9011 name = Hayes street = Main city = Harrison These two entities are related. number = A-102 amount = 1000 DB020:7 Rela0onship Sets ! A rela0onship is an associa,on among two or more en,,es. e.g., this is a relationship id = 677-89-9011 name = Hayes street = Main city = Harrison ! These two entities are related. number = A-102 amount = 1000 A rela0onship set is a mathema,cal rela,on among n ≥ 2 en,,es, each taken from an en,ty set. { (e1, e2, …, en) | e1 ∈ E1, e2 ∈ E2, … en ∈ En } : a rela,onship set ei : an en,ty Ei : an en,ty set (e1, e2, …, en) : a rela,onship e.g., Hayes ∈ Customer, A-102 ∈ Loan, (Hayes, A-102) is a rela,onship, in rela,onship set Depositor DB020:7 En0ty Set / Rela0onship Set ! Dis,nguish: ! en,ty vs. en,ty set ! rela,onship vs. rela,onship set ! e.g., ! Customer is an en,ty set, while the customer with customer_name Hayes is an en,ty ! Account is an en,ty set, while the account with account_number A ­102 is an en,ty ! The associa,on between customer Hayes and account A ­102 is a rela,onship, a collec,on of such rela,onship is a rela,onship set. ! Conven,on in notes: ! customer Hayes (en,ty), Customer (en,ty set) ! depositor (Hayes, A102) (rela,onship), Depositor (rela,onship set) DB020:8 Rela0onship Set Borrower Customer Loan rela,onship set Borrower DB020:9 Rela0onship Sets ! A rela,onship set can also have aMributes. e.g., Depositor(access_date) Customer(customer_name) Account(account_number) DB020:10 Degree of a Rela0onship Set Degree of a rela,onship set – the number of en,ty sets that par,cipate in a rela,onship set. ! Binary rela,onship set – involves only 2 en,ty sets. Generally, most rela,onship sets in a database system are binary. ! Rela,onship sets may involve more than two en,ty sets. e.g., ! Assignment(Employee, Job, Branch) is a ternary rela,onship set between three en,,es. An employee may have different jobs (responsibility) at different branches. ! This situa,on is rela,vely rare. (More on this later.) DB020:11 Mapping Cardinali0es Express the number of en,,es to which another en,ty can be associated via a rela,onship set. ! e.g., for each customer, how many saving accounts can he have? ! ! ! i.e., For each Customer en,ty, how many SavingAccount en,,es can be associated to it? For a binary rela,onship set, the mapping cardinality must be one of the following ! ! ! ! One to one One to many Many to one Many to many DB020:12 Mapping Cardinality One to one One to many Note: Some elements in A and B may not be mapped to any elements in the other set. e.g., without the associa,on between a4 and b4, the rela,onship set is s0ll one to one. DB020:13 Mapping Cardinality Many to one Many to many DB020:14 Mapping Cardinality Mapping cardinality may affect the database design ! If Depositor(Customer, Account) is one to many, we may put the access_date as an aMribute of Account, instead of an aMribute of Depositor. ! Customer(customer_name) Account(account_number, access_date) Depositor DB020:15 Overview Basics • • En,ty Sets, AMributes, Weak En,ty Sets Rela,onship Sets, Mapping Constraints • E ­R Diagram • Extended E ­R Features • Design of an E ­R Database Schema • Reduc,on of an E ­R Schema to Tables DB020:16 E ­R Diagram • Rectangles – en,ty sets • Diamonds – rela,onship sets • Lines – links between aMributes and E/R, between E and • R, or between aMributes and their components. Ellipses – aMributes • Double Ellipses – mul, ­valued aMributes • DB020:17 Example composite attribute multi-valued attribute derived attribute DB020:18 Example (2) A relationship set with attributes DB020:19 ! En,ty sets of a rela,onship need not be dis,nct. e.g., ! The label “manager” and “worker” are called roles. They specify how employee en,,es interact via the WorksFor rela,onship set. ! DB020:20 Roles: Example manager Employee John Works ­for worker May DB020:20 Cardinality Constraints We express cardinality constraints by drawing either a directed line (→), signifying “one,” or an undirected line (—), signifying “many,” between the rela,onship set and the en,ty set. ! e.g., One ­to ­One rela,onship: ! ! ! a customer is associated with at most one loan via rela,onship set Borrower. a loan is associated with at most one customer via Borrower DB020:21 One ­to ­Many Rela0onship ! In a one ­to ­many rela,onship (from customer to loan), a loan is associated with at most one customer via Borrower, and a customer can be associated with several loans via Borrower. DB020:22 Many ­to ­One Rela0onship ! In a many ­to ­one rela,onship (from customer to loan), a loan can be associated with several customers via Borrower, and a customer is associated at most one loan via Borrower. DB020:23 Many ­to ­Many Rela0onship ! In a many ­to ­many rela,onship a loan can be associated with several customers via Borrower, and a customer can be associated with several loans via Borrower. DB020:24 Par0cipa0on of an En0ty Set in a Rela0onship Set ! Total Par,cipa,on (indicated by double line): every en,ty in the en,ty set par,cipates in at least one rela,onship in the rela,onship set. ! ! e.g., par,cipa,on of Loan in Borrower is total – every loan must have a customer associated to it via Borrower. Par,al par,cipa,on: some en,ty may not par,cipate in any rela,onship in the rela,onship set. DB020:25 Alterna0ve Nota0on for Cardinality Limits One ­to ­many from customer to loan; total par,cipa,on for loan =? Many ­to ­one from customer to loan; total par,cipa,on for loan > DB020:26 Keys ! A super key of an en,ty set is a set of one or more aMributes whose values uniquely determine each en,ty. ! ! A candidate key of an en,ty set is a minimal super key ! ! ! ! i.e., no two en,,es have exactly the same values in super key. minimal – no redundant aMributes, i.e., no subset of a candidate key is s,ll a key. e.g., customer_id is a candidate key of Customer. how about {customer_id, customer_name}? Is it a super key? Is it a candidate key? Although several candidate keys may exist, one of the candidate keys is selected to be the primary key. DB020:27 Keys: Example candidate key super key candidate key HK_ID en,ty customer_id customer_name 120392 101 123354 102 Carol London 234425 103 Tammy Shenzhen 879562 104 Jim Cancun Tammy primary key perm_address Hong Kong DB020:27 Keys for Rela0onship Sets ! The combina,on of primary keys of the par,cipa,ng en,ty sets is a super key (not candidate/primary key!) ! ! ! e.g., {customer_id, account_number} is a super key of Depositor. NOTE: This means a pair of en,ty (each from one en,ty set) can have at most one rela,onship in a par,cular rela,onship set. We must consider mapping cardinality of the rela,onship set to determine the candidate keys. DB020:28 Keys for Rela0onship Sets: Example • super key for Depositor: (customer ­id, account ­number) John 8 Aug, 2009 9 Sep, 2010 Account DB020:28 E ­R Diagram with a Ternary Rela0onship (S. S.) ! We allow at most one arrow out of a ternary (or greater degree) rela,onship to indicate a cardinality constraint ! E.g. an arrow from WorksOn to Job indicates each employee works on at most one job at any branch. DB020:29 (S. S.) ! If there is more than one arrow, the meaning is ambiguous. e.g., ! ! ! ! each A en,ty is associated with a unique en,ty from B and C or each pair of en,,es from (A, B) is associated with a unique C en,ty, and each pair (A, C) is associated with a unique B Each alterna,ve has been used in different formalisms To avoid confusion we assume only one arrow C A B DB020:30 Binary vs. Non ­Binary Rela0onships (S. S.) ! Some rela,onship sets that appear to be non ­binary may be beMer represented by using binary rela,onship sets ! ! ! e.g., A ternary rela,onship set Parents, rela,ng a child to his/ her father and mother, is best replaced by two binary rela,onship sets, Father and Mother. Using two binary rela,onships allows par,al informa,on to be stored (e.g., only mother being known) But there are cases where the rela,onship sets are naturally non ­binary ! e.g., WorksFor DB020:31 Conver0ng Non ­Binary Rela0onships to Binary Form (S. S.) ! In general, any non ­binary rela,onship can be represented using binary rela,onships by crea,ng an ar,ficial en,ty set. create E, and relate E to A, B, C. ! create an iden,fying aMribute for E. ! add any aMribute in R to E. ! For each rela,onship (a, b, c) in R, create ! a new en,ty e, add (e, a), (e, b), (e, c) in respec,ve rela,onship sets. ! DB020:32 Weak En0ty Set Example ! Payment is a weak en,ty set ! no Loan, no Payment. ! payment_number (discriminator) cannot uniquely iden,fy a payment out of all payments. i.e., two payments from different loans may have the same number. ! We depict a weak en,ty set by a double rectangle. ! We underline the discriminator using a dashed underline. ! The iden,fying rela,onship set LoanPayment must be total on payment. Each payment must be mapped to one loan. ! Primary key of Payment is (loan_number, payment_number) DB020:34 Weak En0ty Set Example Payment: Weak En,ty Set payment ­ date NOT Primary key payment ­ amount payment ­ no 10 Jan 100 A 11 Feb 200 10 Jan 5 Mar Total Par,cipa,on Loan: Strong En,ty Set Primary key loan ­no amount B 1 100 1000 A 2 200 300 B Primary key of Payment: (loan ­no, payment ­no) DB020:34 Weak En0ty Sets ! An en,ty set that does not have a primary key is referred to as a weak en0ty set. ! We depict a weak en,ty set by a double rectangle. ! The existence of a weak en,ty set depends on the existence of an iden,fying en,ty set. i.e., the weak en,ty set must relate to its iden,fying en,ty set via a total, many ­to ­one rela,onship set from the weak en,ty set to the iden,fying en,ty set. ! This is called iden,fying rela,onship set, depicted using a double diamond. ! DB020:35 Weak En0ty Sets ! The discriminator (or, par,al key) of a weak en,ty set is a set of aMributes that dis,nguish among the weak en,,es that depend on the same iden,fying en,ty. ! The primary key of a weak en,ty set is formed by the primary key of the iden,fying en,ty set plus the weak en,ty set’s discriminator. DB020:36 Overview ! Basics En,ty Sets, AMributes, Weak En,ty Sets ! Rela,onship Sets, Mapping Constraints ! ! E ­R Diagram ! Extended E ­R Features ! Design of an E ­R Database Schema ! Reduc,on of an E ­R Schema to Tables DB020:37 Specializa0on Example Person is a generaliza,on of Employee. Employee is a specializa,on of Person, (name, street, city) are inherited aMributes, salary is an addi,onal aMribute DB020:38 Specializa0on a top ­down design process ! Specializa,on – we designate sub ­groupings within an en,ty set that are dis,nc,ve from other en,,es in the set. ! These sub ­groupings become lower ­level en,ty sets that have aMributes or par,cipate in rela,onships sets that do not apply to the higher ­level en,ty set ! e.g., Person is an en,ty set, Doctor may be a sub ­ groupings in Person; Doctor gets the aMributes from Person, and can involves in some rela,onship sets for doctors only. ! DB020:39 Specializa0on ! ISA rela,onship depicted by an inverted triangle labeled ISA. ! e.g., Customer “is a” Person. ! ! AMribute Inheritance A lower ­level en,ty set inherits all aMributes and rela,onship set par,cipa,on of the higher ­level en,ty set to which it is linked. ! e.g., Customer has all aMributes of Person ! e.g., Customer also joins all rela,onship sets that involves Person. ! DB020:40 Generaliza0on ! A boMom ­up design process ! Generaliza,on – combine a number of en,ty sets that share the same features into a higher ­level en,ty set. ! Specializa,on and generaliza,on are simple inversions of each other; they are represented in an E ­R diagram in the same way. ! The terms specializa,on and generaliza,on are used interchangeably in E ­R model, referring to the design involving ISA rela,onships. DB020:41 Specializa0on and Generaliza0on ! We can have mul,ple specializa,ons of an en,ty set based on different features. e.g., Perm_Employee and Temp_Employee ! Officer, Secretary and Teller ! ! Each par,cular employee would be a member of one of perm or temp employee ! and also a member of one of officer, secretary or teller. ! ! ISA rela,onship can also be referred to as super ­class and sub ­class rela,onships DB020:42 Design Constraints on a Specializa0on/ Generaliza0on ! Constraints on how en,,es are grouped Condi,on ­defined – depends on some condi,ons over aMributes. e.g., all customers over 65 years old are members of SeniorCitizen en,ty set. ! User ­defined – determined by user ! ! Constraints on whether en,,es may belong to more than one lower ­level en,ty set within a single generaliza,on Disjoint (explicit sta,ng is required) ! Overlapping (the default in E ­R diagram) ! DB020:43 Design Constraints on a Specializa0on/ Generaliza0on ! Constraints on par,cipa,on of higher ­level en,,es to lower ­level en,ty sets (Completeness Constraints). specifies whether an en,ty in the higher level ­en,ty set must belong to at least one of the lower ­level en,ty sets within a generaliza,on ! total ! depicted by double line from higher ­level en,ty set to ISA ! e.g., Employee to Perm_Employee and Temp_Employee ! ! par,al ! e.g., Person to Employee DB020:44 Specializa0on Example employee overlapping (default) ISA ISA disjoint Permanent Temporary Officer Secretary DB020:44 Aggrega0on (S. S.) Consider the ternary rela,onship WorksOn ! We want to record the managers for the task performed by an employee at a branch. ! DB020:45 Aggrega0on (S. S.) ! Rela,onship sets WorksOn and Manages represent overlapping informa,on ! ! ! ! Every “manages” rela,onship corresponds to a “works_on” rela,onship. However, some “works_on” rela,onships may not correspond to any “manages” rela,onship i.e., we cannot discard WorksOn rela,onship set. Eliminate this redundancy via aggrega/on ! ! ! treat rela,onship as an abstract en,ty allows rela,onships between rela,onships (note: not allowed in conven,onal E ­R model) An abstrac,on of a rela,onship into a new en,ty. DB020:46 E ­R Diagram with Aggrega0on (S. S.) ! ! An employee works on a par,cular job at a par,cular branch An employee, branch, job combina,on may have an associated manager DB020:47 E ­R Diagram for a Banking Enterprise E8 R5 E2 R4 R3 E4 E3 R2 R6 E1 R1 E6 E5 R7 E7 DB020:48 Overview ! Basics En,ty Sets, AMributes, Weak En,ty Sets ! Rela,onship Sets, Mapping Constraints ! ! E ­R Diagram ! Extended E ­R Features ! Design of an E ­R Database Schema ! Reduc,on of an E ­R Schema to Tables DB020:49 E ­R Design Decisions Use an aMribute or en,ty set to represent an object? ! Express a concept as an en,ty set or a rela,onship set? ! n ­ary rela,onship vs. a set of binary rela,onships ! The use of a strong or weak en,ty set. ! The use of specializa,on/generaliza,on ! ! ! modularity in the design. The use of aggrega,on ! treat the aggregate en,ty set as a single unit without concern for its internal structure. DB020:50 En0ty sets vs. [email protected] (1) ! How do you model an employee and his phone number? ! 1. Treat phone number as an aMribute of an employee Employee(employee_name, ..., phone_number, location) ! 2. Treat phone as a separate en,ty Employee(employee_name, ...) Telephone(phone_number, location, ...) EmpTelephone(employee_name, phone_number) ! What are the differences? ! phone sharing? mul,ple phone? phone informa,on? redundancy? DB020:51 En0ty sets vs. [email protected] (2) ! The choice depends on: 1. The structure of the enterprise being modeled ! ! 2. Do we need to store extra informa,on about a phone? Do we support mul,ple phones or phone sharing? The seman,cs associated with...
View Full Document

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern