Steps in building a DB application CS411 Pick application Conceptual design domain Database Systems What data do I What data do I need for my need for my application application 02: The Entity-Relationship Model How can I How can I domain? domain? describe that describe that Kazuhiro Minami data? data? Entity Relationship (ER) Model Steps in building a DB application by Peter Chen Step 1 Step 2 • Born in Taiwan Pick application • Ph.D from Harvard University in 1973 Conceptual design domain • Professor at Louisiana State University ER diagram Ted Codd SQL & SQL & Java/C+++/etc + Java/C+++/etc + user interface user interface Step 3 Steps 4 and 5 Convert ER diagram Implement "A Relational Model of Data for "The Entity-Relationship Model-- to the data model Large Shared Data Banks” Toward a Unified View of Data” application code & of your DBMS user interface product 1970 1976
The ER model is very simple Entity – real-world object distinguishable from other name name objects category price – described by its attributes makes Company Attribute Product – Has an atomic domain: string, integers, date, stockprice … Entity set : all have the same set of attributes buys employs price name category name Company Person � Product boardOfDirectors stockprice name ssn address •5 We can show the cardinality of Relationships a relationship E F If A, B are sets, then a relation R is a subset of 1 a A x B. 2 b 3 c one-one d 1 a a A = {1, 2, 3} B = {a, b, c, d} 1 b 2 A 2 b many-one c 3 R = {(1,a), (1,c), (3,b)} d c 3 B d 1 a makes is a subset of Product x Company : 2 b 3 c many-many d makes Company makes Company Product Product
Exercise name category name Design a database for a bank, including information about price customers and their accounts. Information about a customer includes their name, address, phone, and Social Security makes Company number. Accounts have numbers, types (e.g., saving, checking) Product and balances. Also record the customer(s) who own an account. stockprice 1) Draw the E/R diagram for this database. 2) Change your diagram so an account can have only one buys employs customer. 3) Further change your diagram so a customer can have only Person one account 4) Change your original diagram in (1) so that a customer have a set of addresses. name ssn address How do we model a multiway Phone Address relationship? Product Customers own Accounts Purchase Store Person SSN Type Balance Name Can still model as a mathematical set (how?)
What do arrows mean in n-way What if there are several arrows? relationships? Invoice Invoice VideoStore VideoStore Rental Movie Movie Rental Person Person If I know the store, person, and invoice, then there is only one possible movie. store, person, invoice determines movie; “VideoStore, Invoice, and Person determines store, invoice, movie determines person Movie” How do I say “invoice determines What if we need an entity set twice store”? in one relationship? No good way; best approximation: the “role” Product Invoice VideoStore Purchase Store Rental Movie salesperson buyer Person Person Person Why is this incomplete?
Some versions of the ER model What if we need an entity set twice allow attributes on relationships in one relationship? Product date Product Purchase Store Purchase Store buyer salesperson Person Person You can “upgrade” a Constraint = assertion about the relationship to be an entity DB that must always be true set date Key: social security number uniquely identifies a person. ProductOf Product Single-value constraint: a person can have only one father. Referential integrity: if a person works for a company, Purchase the company must also be in the DB. StoreOf Store Domain constraint: peoples’ ages are between 0 and 150. General constraint: all others BuyerOf (at most 100 students in this course) Person
Underline the key for each entity set Constraints are very important name category • Help us to come up multi-attribute with efficient price keys are okay! storage, query processing, etc. Product Multiple • Help us keep “candidate keys”? garbage out of the Pick just one to be Is this a good DB Person the key. key? – Garbage in, Examples: garbage out! name ssn address Erbana, IL 61801 Brittany Speers Referential Integrity Constraints Degree Constraints • The reference integrity constraint on relationships explicitly requires a reference to exist • Constraints on degree of a relationship • The DB equivalent of a dangling pointer <= 5 makes Company Product Students takes Courses ) makes Company Product
Sometimes your entity might not seem to have a key Weak entity set : some or all of its key attributes come from other classes to which it is related. ) affiliation Team University sport record name
Recommend
More recommend
