【正文】 signer Seminar Consulting Customer Product Time Date SeminarID ConID ConHours ConType CustID CustName CustPhone Attended By Requests Buys ProdID ProdName ProdType Location Title CustAddr CustEmail ProdPrice ConRate ProdQty 1 N M N N M 8/12/2022 Sanjay Goel, School of Business, University at Albany 36 Database Management ER Diagram: Interior Designer Seminar Consulting Customer Product Time Date SeminarID ConID ConHours ConType CustID CustName CustPhone Attended By Requests Buys ProdID ProdName ProdType Location Title CustAddr CustEmail ProdType ConRate ProdQty 1 N M N N M 8/12/2022 Sanjay Goel, School of Business, University at Albany 37 ? An anization purchases items from a number of suppliers. It keeps track of the items purchased from each supplier, and it also keeps a record of suppliers39。 addresses. Items are identified by ITEMTYPE and have a DESCRIPTION. There may be more than one such address for each supplier, and the price charged by each supplier for each item is stored. Suppliers are identified by SUPPLIERID. Database Management ER Diagrams 8/12/2022 Sanjay Goel, School of Business, University at Albany 38 Database Management ER Diagram: Supplier Address Items SupCity SupStreet ProdID ProdName ProdDesc Supplies SupZip SupPhone M N Supplier Has 1 N SupID SupPhone SupName ProdType 8/12/2022 Sanjay Goel, School of Business, University at Albany 39 Supplies Database Management ER Diagram: Supplier Address Items SupCity SupStreet SupID ProdID ProdName ProdDesc SupPhone SupZip N N Supplier Has 1 N Purchases SupID Quantity ProdID Price Procured 1 SupName ProdType Date 8/12/2022 Sanjay Goel, School of Business, University at Albany 40 ? A hospital stores data about patients, their admission and discharge from departments and their treatments, For each patient, we know the name, address, sex, social security number, and insurance code (if existing). For each department, we know the department39。s name, its location, the name of the doctor who heads it, the number of beds available, and the number of beds occupied. Each patient gets admitted at a given date and discharged at a given date. Each patient goes through multiple treatments during hospitalization。 for each treatment, we store its name, duration, and the possible reactions to it that the patient may have. Database Management ER Diagrams 8/12/2022 Sanjay Goel, School of Business, University at Albany 41 Database Management ER Diagram: Hospital Patients Treatments Departments Contributor PSex PLName PSSN TID TReact T DID DName AuPhone Admitted By Get Is A ConID ConType ConLevel PCode PFName 8/12/2022 Sanjay Goel, School of Business, University at Albany 42 ? Each entity bees a new table ? Each attribute bees a column of the table ? Relationships – One to many relationship: Add the key from the many side of the relationship to the one side of the relationship . add the publisher id to the book entity The pubID is called a foreign key because this is a key to a foreign entity – One to one relationship: Add the primary key of one entity to the other entity – Many to many relationship: We can not add the foreign key of each to the other considering these as two one to many relationships. This leads to duplicated rows. – To implement a manytomany relationship add an artificial entity to break the manytomany relationship into two onetomany relationships. Database Management Translating ERD to a Database 8/12/2022 Sanjay Goel, School of Business, University at Albany 43 ? Referential Integrity: Each value of foreign key must have a matching value in the referenced key. – Otherwise we will have a dangling reference . If there was no publisher matching PubID then we have a problem. ? Violations can occur in two ways – We add a new entry in the books table with no corresponding publisher in the publisher table – We delete an entry in the referenced table without deleting the corresponding entry in the referencing table ? Two ways to ensure Integrity – Cascading updates: If a referenced key is changed then all matching entries in the foreign key are automatically updated – Cascading deletions: If a value of the referenced table is deleted by deleting a row then all rows in the referencing table that refer to the deleted key value will also be deleted. Database Management Referential Integrity 8/12/2022 Sanjay Goel, School of Business, University at Albany 44 ? Abstract representation of the database – ., Entities, Attributes amp。 Relationships ? Super Keys, Keys and Primary Keys ? ER diagrams – Semantic Network Database Management Summary