1 / 16

Microsoft Access

Microsoft Access. CS 110 Fall 2005. Entity Relationship Model. Entities Principal data object about which information is to be collected employees, projects, invoices, etc. Relationships An association between two or more entities employees are assigned to projects. Entities.

kelsie-oneil
Download Presentation

Microsoft Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Access CS 110 Fall 2005

  2. Entity Relationship Model • Entities • Principal data object about which information is to be collected • employees, projects, invoices, etc. • Relationships • An association between two or more entities • employees are assigned to projects

  3. Entities • Entities have attributes • name, ID, key • May be a way to uniquely identify an instance of an entity • May be a non-unique characteristic of an entity instance

  4. Relationships • Relationships have connectivity and cardinality • One-to-one – at most one instance of entity A is associated with one instance of entity B • One-to-many – one instance of entity A is associated with zero, one, or many instances of entity B • Many-to-many – one instance of entity A is associated with zero, one, or many instances of B (and vice versa)

  5. Building a data model • Identify entities and relationships • What are the entity attributes • Confirm the desired query results can be generated

  6. What makes an object an entity or an attribute? • “Employees work on projects” Are employees an entity or an attribute of projects? • Analyze narratives from users, meeting notes, policy and procedure documents, … • Consider database properties

  7. Normalization • Put data into table form by removing repeated groups and duplicated data • A theoretical definition based on logic exists

  8. Example • A company obtains parts from multiple suppliers • Each supplier is in a city • A city can have more than one supplier • Each city has a status code • Each supplier may provide many parts

  9. Example • s#: supplier ID • status: city status • city: name • p#: part ID • qty: quantity

  10. Redundant data • City and status repeated for each part

  11. Update anomalies • Problems that occur when information is inserted, deleted, or updated • Caused by redundant data

  12. Insert anomalies • Cannot addnew supplierw/o addingpart supplied

  13. Delete anomalies • Deleting a row loses information about quantity/part AND supplier

  14. Update anomalies • If supplier s1 moves from NY to London, six rows must be updated

  15. Refinement • Following rigor of normalization, tables are split/merged to reduce anomalies

  16. Primary, secondary, and foreign keys • Primary key: attribute that uniquely identifies all other attributes in a row • Secondary key: attribute that identifies a set of records • Foreign key: attribute in one table that matches the primary key of another

More Related