Conceptual DB Design Conceptual database design ERDs Agenda
Conceptual DB Design Conceptual database design ERD’s
Agenda l l Review – modeling principles Conceptual design overview l l High level look at various ways to draw ERDs Detailed look at how we will draw ERD in this class l l Components of an ERD Drawing techniques for the ERD in this course
Abstraction & Modeling l l Abstraction: Mental processes through which we concentrate upon common properties of data disregarding all irrelevant details Modeling: A way to represent an abstraction l l Simplified picture Hide details until needed
Desired Features of Models l Expressiveness l l l Simplicity, easily understood Minimality l l Detailed representation of requirements Not specific to one particular technology No redundancy, eliminate extraneous information Smallest common denominator – don’t have two parts that equal each other Formality l l Formal notation/diagram Well defined, consistent with enterprise definitions and organization
Conceptual Design l l Definition: Constructing a model of the data and information, independent of all physical considerations Purpose: Communicate with the users; understand details of the user requirements; understand the nature of the data itself; understand how data will be used across user views. End goal is to determine the entities and attributes that will be stored in our database, and the relationships between the entities We represent conceptual design with an ERD (entity relationship diagram)
Entity-Relationship Diagram
Diagram Notation l l Graphical representation of conceptual data model Many methods of drawing are found in the literature l l Classic Crow’s feet UML Class Diagram This course notation
ERD (Classic) Attributes each in an oval, connected to entity (rectangle), relationship between entities indicated by a diamond. Primary key underlined. Problems: Can get crowded, large footprint. Hard to locate the relationship (many lines). Look Here cardinality notation (discussed later) can be confusing.
ERD (Crow’s feet) Single line represents one, split line represents many. Shows relationship type (1: 1 or 1: M) but not cardinality (discussed later) PK underlined. Double box = weak entity (discussed later). Adapted from Mc. Fadden et al. , 1999
ERD (MS Access) Number 1 represents one, infinity symbol represents many. Shows relationship type (1: 1 or 1: M) but not cardinality. PK in bold. Problems: Weak entity has no indication. Can’t do M: M relationship. Can’t use for conceptual design—you can’t make an ERD before you build tables.
ERD (UML Class Diagram) Top box is entity, Middle box has attributes, including data type Bottom has activities/processes (behavior) Issues: conceptual design is independent of physical considerations (so we may not know data types). We don’t need behavior/processes (for OO programming). Relationship only has verb one direction but cardinality in both directions; small nuances of cardinality representation are inconsistent--shows min and max EXCEPT (1, 1) is shown as 1
ERD (This Course) Modified from UML. Top box is entity, Middle box has attributes. Underline PK Relationship articulated both directions Relationship structure: (entity) verb, arrow, cardinality (entity) reads intuitively Student to class: Student takes (min of 0 and max of many) classes Class to student: Class has (min of 0 and max of many) students
ERD components l Entities l Instances – do not show on ERD (but may be helpful to think of example instances in thinking through the situation) l Relationships Attributes Structural constraints (cardinality) l Assumptions, Notes l l
ER Model Components l Entity, attribute, relationship l l l explain the STRUCTURE of the data changed by DB designer or developer Instance l l l Uniquely identifiable object of an entity type explains the actual data (CONTENT) at any given time changed by the end-users
Entity l also called “entity type” or concept l l l Group of objects or concepts with same properties Identified by the enterprise as having an independent existence. Will eventually become tables
Entity l l Examples of physical entities/objects (“real” objects) l Person l Medication l Lab tests Examples of conceptual entities (“abstract” objects) Hint: I think of entities as l Order the “nouns” of our DB—the person, place, thing, or l Work experience idea about which we want to store data
HW - Entities I own a small business that provides disposable supplies to a simulation center. Disposable supplies are items such as bandages, pretend medications, etc. that are used up during the course of simulation practice. I need to keep track of information about my employees and customers. Currently I have a paper-based system. Employee information is contained in handwritten papers that are grouped into folders by employee. Customers place orders for our products, these orders are tracked in order forms that are sorted chronologically by order date. Products are shipped by multiple companies including Fed. Ex and UPS. I would like to be able to mail advertisements to my customers. Unfortunately, my handwriting is sometimes difficult to read, and it takes quite a bit of time to go through all of the order forms to transcribe customer addresses and to get rid of duplicate addresses. In addition, I am having difficulty answering the following type of questions for my business: l l What are my employee teams? (Which employees work for each of my managers? ) For each order, which products were ordered? For each order, what was the unit price at the time of order, and how many units were ordered? How did I ship each order? I plan to create a relational database to store my customer and employee information. This database does not need to interface with any other information systems at the present time. Data to be stored about customers includes name and contact information (address, phone, and fax). Data about employees must include name, address, birth date, phone numbers, and hire date; in addition I want to store data about salary and tax withholding changes. I also need to track products and orders. From this database, my goal is to obtain, at minimum, answers to the questions above. Creating this database is anticipated to improve my efficiency and reduce inaccuracies that were caused by hand calculations and inability to decipher my handwriting.
Diagramming an Entity l l Rectangle labeled with the entity name Usually a singular noun First letter upper case Short but descriptive and meaningful Employee
- Slides: 18