Download Relational Databases and SQL: Understanding Entities, Relationships, and Queries - Prof. M and more Study notes Accounting in PDF only on Docsity! Chapter 6-Relational Databases and SQL 1) Data model: simplified representation of a complex entity or phenomenon, describe entities by capturing their essential characteristics (attributes) 2) Entity: Resource, Event, or Agent (Client), REA approach helps to identify complete set of entities. IMPORTANT: attributes allow users of database to uniquely identify each entity 3) Instance: one specific thing of the type defined by an entity (Peter) 4) Attribute: item of data that characterizes an entity or relationship (Name, Address, Number) a. Composite Attributes: attributes that consist of multiple subattributes (Street Address, City, State) b. Key Attribute: attribute whose value is unique for every entity that will ever appear in the database, most meaningful way of identifying each entity AKA PRIMARY KEY i. Most designers would use numeric-valued or non-naming alphabetic attribute Relationships: associations between entities, Entities must be logically linked to show relationships Mapping: 3-step strategy is most effective in identify all relationships in model 1) Identify users’ existing and desired information requirements a. To determine whether relationships in data model can fulfill those requirements b. If going from paper to electronic, will electronic provide all the same info as paper 2) Evaluate each other the entities in pairs a. to determine which entity in the pair provides a better location to include in attribute b. better to track students major’s via student table or course registration table? 3) Evaluate each entity a. To determine if there would be any need for two occurrences of the same entity to be linked b. Should there be one or two inventory tables? *most common way: to collect info is via surveys/ interviews, BOTTOM UP APPROACH Recursive Relationship: occurs between two different instances of an entity( ex/ one supervisor will supervise two employees. (shown via DIAMOND SHAPE) Constraints: 1) Cardinality: most common constraint specified in E-R diagrams, specifies maximum part. 2) Participation constraint: specifies the degree of minimum participation of one entity in the relationship with the other entity, provides more info but used less than cardinality a. 1= mandatory b. 0= optional c. Useful in designing tables so that null values (fields w/ missing values) occur infrequently i. Null values: waste memory space, may cause problems when running queries ii. Minimizing null values req using a new set of rules for the creation of add. Tables *Fundamental requirement for moving toward and event-driven model, such as REA, is the complete integration of data related to an organization’s business events *Despite push toward object-orientated database, relational database-driven legacy systems exist in most large organizations, efforts to switch them over is cost prohibitive
