Chapter2_more - CSIS0278A Introduc0on to Database...

Info iconThis 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 the aMribute ! e.g., Can customer_name be an independent en,ty?? DB020:52 En0ty sets vs. rela0onship sets ! How to model a loan? ! 1. as a Loan en,ty Loan(loan_number, amount) R(customer_name, loan_number, branch_name) ! 2. as a rela,onship between a customer and a branch. Loan(customer_name, branch_name, loan_number, amount) ! What are the differences? ! Can we have joint loan? DB020:53 En0ty Sets vs. Rela0onship Sets ! Guideline 1: use a rela,onship set to describe an ac0on that occurs between en,,es ! Hint: en,ty sets ouen have “nouns” as name, and rela,onship sets have “verbs” as name ! Guideline 2: Create an en,ty set. If it fails to meet the requirement, change it to a rela,onship set ! Remember: understand the requirements! DB020:54 Binary vs. n ­ary rela0onship sets ! Although we can replace any non ­binary rela,onship set by a number of dis,nct binary rela,onship sets, an n ­ary rela,onship set is more visually easy to understand ! Placement of res more DB020:55 Summary of Symbols Used in E ­R Diagrams DB020:56 Summary of Symbols DB020:57 Alterna0ve E ­R Nota0ons DB020:58 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 ! Reduc0on of an E ­R Schema to Tables DB020:59 Reduc0on of an E ­R Schema to Tables ! A database which conforms to an E ­R diagram can be represented by a collec,on of tables. ! Each table has a number of columns (generally corresponding to aMributes), which have unique names ! Conver,ng an E ­R diagram to table format is the basis for rela,onal database design from E ­R diagram. DB020:60 Represen0ng En0ty Sets as Tables ! A strong en,ty set reduces to a table with the same aMributes. Customer Each row represents an en,ty. Each column represents an aMribute (simple aMribute only) of an en,ty. DB020:61 Represen0ng Composite & Mul0 ­Valued [email protected] ! Composite aMributes are flaMened out by crea,ng a separate aMribute for each component aMribute ! ! e.g., name becomes name.first_name and name.last_name A mul, ­valued aMribute M of an en,ty set E is represented by a separate table EM. ! e.g., EmpDependentName(employee_id, dname) EmpDependentName employee_id dname E ­1023 Alice E ­1023 Bob E ­1024 Candy DB020:62 Represen0ng Weak En0ty Sets ! A weak en,ty set becomes a table that includes the columns for the primary key of the iden,fying strong en,ty set LoanPayment 5 Key of strong ­ en,ty set discriminator DB020:63 Represen0ng Rela0onship Sets This depends on their mapping cardinali,es ! A many ­to ­many rela,onship set is a table with columns for the primary keys of the par,cipa,ng en,ty sets, and any aMributes of the rela,onship set. ! Borrower DB020:64 Represen0ng Rela0onship Sets Many ­to ­one and one ­to ­many rela,onship sets that are total on the many ­side can be represented by adding extra aMributes to the “many ­side”, containing the primary key of the “one ­side”. ! e.g., instead of crea,ng table AccountBranch, we can add aMribute branch-name to the table represen,ng the en,ty set Account ! DB020:65 Represen0ng Rela0onship Sets ! For one ­to ­one rela,onship sets, either side can be chosen to act as the “many ­side”. ! ! i.e., we can add primary key of the other side to either side. However, if the par,cipa,on on the “many ­side” is par,al, replacing the table by extra aMributes may result in null values. ! ! e.g., Employee(emp_id, …, manager_id) // no WorksFor table What happens if an employee works for no manager ! We never need a separate table for iden,fying rela,onship set of a weak en,ty set. DB020:66 Represen0ng Specializa0on (Method 1) ! Form a table for the higher ­level en,ty set ! Form a table for each lower ­level en,ty set, which contains the primary key of the higher ­level en,ty set and local aMributes. e.g., ! ! ! Person(name, street, city) Customer(name, credit_rating) Employee(name, salary) ! Advantage: no redundant informa,on or null values ! Drawback: gewng informa,on about, say, an employee, requires accesses to two tables. DB020:67 Represen0ng Specializa0on (Method 2) Form a table for each en,ty set with all local and inherited aMributes ! Person(name, street, city) ! Customer(name, street, city, credit_rating) ! Employee(name, street, city, salary) ! Advantages: Efficient access to any en,ty ! Drawback: street and city may be stored redundantly for persons who are both customers and employees ! If the specializa,on is total, the generalized en,ty set may not require a table! ! It is a view derived from the union of specialized en,ty sets. ! But if foreign key constraints are required, the table is s,ll required (to be discussed later). ! DB020:68 Represen0ng Aggrega0on (S.S) ! Create a table containing the primary key of the aggregated rela,onship set ! the primary key of the associated en,ty set ! any aMributes of the aggrega,on rela,onship set ! DB020:69 Rela0ons Corresponding to Aggrega0on (S.S) ! e.g., To represent aggrega,on Manages between rela,onship set WorksOn and en,ty set Manager, create a table Manages(employee_id, branch_name, job_title, manager_name) ! Table WorksOn is redundant if we store null values for aMribute manager_name in table Manages. DB020:70 Conclusions ! ER ­diagram is a graphical tool developed to capture requirements of an enterprise ! Important to understand and use the concepts like en,,es, rela,onships, mapping cardinali,es, and specializa,on ! Conver,ng an ER ­diagram to rela,onal tables allows a data to be stored and queried in a DBMS ! Next, we study the querying of rela,onal tables DB020:71 ...
View Full Document

This note was uploaded on 12/01/2010 for the course CS 0278 taught by Professor Dr during the Spring '10 term at HKU.

Ask a homework question - tutors are online