Transcript Document
Chapter 3 Database Development Process 7/17/2015 Database Concepts 1 Objectives • Definition of terms • Importance of data modeling • Write good names and definitions for entities, relationships, and attributes • Distinguish unary, binary, and ternary relationships • Model different types of attributes, entities, relationships, and cardinalities • Draw E-R diagrams for common business situations • Convert many-to-many relationships to associative entities • Model time-dependent data using time stamps 7/17/2015 Database Concepts 2 SDLC Revisited – Data Modeling is an Analysis Activity Project Identification and Selection Project Project Initiation Initiation and and Planning Planning Database activity – conceptual data modeling Analysis Analysis Logical Design Physical Design Implementation Purpose – thorough analysis Deliverable – functional system specifications 7/17/2015 Database Concepts Maintenance 3 Database Analyst Responsibilities • Identify and understand those rules that govern data • Represent those rules so that they can be unambiguously understood by – information systems developers and – users • Implement those rules in database technology 7/17/2015 Database Concepts 4 Business Rules • Statements that define or constrain some aspect of the business • Assert business structure • Control/influence business behavior • Expressed in terms familiar to end users • Automated through DBMS software 7/17/2015 Database Concepts 5 A Good Business Rule is: • Declarative • Expressible – what, not how – validated – structured – natural language • Precise • Distinct – clear, agreed-upon meaning – non-redundant • Business-oriented • Atomic – understood in the language used by business people – Indivisible, one rule • Consistent – internally and externally 7/17/2015 Database Concepts 6 A Good Data Name is: • Related to business, not technical, characteristics • Meaningful and self-documenting • Unique • Readable • Composed of words from an approved list • Repeatable 7/17/2015 Database Concepts 7 Data Definitions • Explanation of a term or fact – Term • word or phrase with specific meaning to the business • Key words such as course, instructor, student – Fact • association between two or more terms • Example Association to term term – A course is a module of instruction in a particular subject area. Association to term 7/17/2015 Database Concepts 8 Data Definitions • Guidelines for good data definition – Gathered in conjunction with systems requirements – Accompanied by diagrams – Iteratively created and refined – Achieved by consensus 7/17/2015 Database Concepts 9 E-R Model Constructs • Entity type • Relationship instance – collection of entities that share common properties or characteristics – Weak entity • Existence depends on another entity – Strong entity • Relationship type – category of relationship…link between entity types • Exists independently • Entity instance – A single occurrence of an entity type – Person, place, object, event, concept (the row in a table) 7/17/2015 – link between entities (corresponds to primary key-foreign key equivalencies in related tables) • Attribute – property or characteristic of an entity type (a field in a table) Database Concepts 10 Basic ER Notation Entity symbols Attribute symbols Relationship symbols A special entity that is also a relationship Relationship degrees specify number of entity types involved 7/17/2015 More about these later… Relationship cardinalities specify how many of each entity type is allowed Database Concepts 11 Sample E-R Diagram 7/17/2015 Database Concepts 12 Sample E-R Diagram (Figure 3-1) 7/17/2015 Database Concepts 13 What Should an Entity Be? • Should: – Be an object that will have many instances in the database – Be an object that will be composed of multiple attributes – Be an object that we are trying to model • Should Not Be: – Be a user of the database system – Be an output of the database system (e.g. a report) 7/17/2015 Database Concepts 14 Inappropriate entities System user Inappropriate entities System output Appropriate entities 7/17/2015 Database Concepts 15 Attributes • Is a property or characteristic of an entity type 7/17/2015 Database Concepts 16 Attributes • Classifications of attributes: – Required versus Optional Attributes – Simple versus Composite Attribute • LName vs. Address (Street, City, ST, Zip) – Single-Valued versus Multivalued Attribute • Student vs. Employee-Skill (C++, VB) – Stored versus Derived Attributes • SSN vs. YearsInService – Identifier Attributes • MaxNoPassengers vs. FlightID(Number, Date) 7/17/2015 Database Concepts 17 Identifiers (Keys) • Identifier (Key) – An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type – Simple Key vs. Composite Identifier • MaxNoPassengers vs. FlightID(Number, Date) • Candidate Identifier (Key) – an attribute that could be a key…satisfies the requirements for being a key – More about these in Chap 5 7/17/2015 Database Concepts 18 Characteristics of Identifiers • Will not change in value • Will not be null • No intelligent identifiers – Ex. containing locations or people that might change • Substitute new, simple keys for long, composite keys 7/17/2015 Database Concepts 19 A Composite Attribute An attribute broken into component parts Figure 3-8 Entity with multivalued attribute (Skill) and derived attribute (Years_Employed) Multivalued an employee can have more than one skill 7/17/2015 Derived from date employed and current date Database Concepts 20 Simple & Composite Key Attribute The identifier is boldfaced and underlined 7/17/2015 Database Concepts 21 Time-stamping Example This attribute that is both multivalued and composite 7/17/2015 Database Concepts 22 More on Relationships • Relationship Types vs. Relationship Instances – The relationship type is modeled as the diamond and lines between entity types – The relationship instance is between specific entity instances • Two entities can have more than one type of relationship between them (multiple relationships) • Associative Entity • Relationships attributes – combination of relationship and entity – describe features pertaining to the association between the entities in the relationship 7/17/2015 Database Concepts 23 Degree of Relationships • Degree of a relationship is the number of entity types that participate in it – Unary Relationship – Binary Relationship – Ternary Relationship 7/17/2015 Database Concepts 24 Figure 3-10 Relationship types and instances a) Relationship type b) Relationship instances 7/17/2015 Database Concepts 25 Degree of Relationships One entity related to another of the same entity type 7/17/2015 Entities of two different types related to each other Database Concepts Entities of three different types related to each other 26 Cardinality of Relationships • One-to-One – Each entity in the relationship will have exactly one related entity • One-to-Many – An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity • Many-to-Many – Entities on both sides of the relationship can have many related entities on the other side 7/17/2015 Database Concepts 27 Cardinality Constraints • Cardinality Constraints – the number of instances of one entity that can or must be associated with each instance of another entity • Minimum Cardinality – If zero, then optional – If one or more, then mandatory • Maximum Cardinality – The maximum number 7/17/2015 Database Concepts 28 Examples of Relationships of Different Degrees (Unary) 7/17/2015 Database Concepts 29 Examples of Relationships of Different Degrees (Binary) 7/17/2015 Database Concepts 30 Examples of Relationships of Different Degrees (Ternary) Note: a relationship can have attributes of its own 7/17/2015 Database Concepts 31 Examples of Cardinalities Constraints Mandatory cardinalities A patient history is recorded for one and only one patient 7/17/2015 A patient must have recorded at least one history, and can have many Database Concepts 32 Examples of Cardinalities Constraints One Optional/One Mandatory A project must be assigned to at least one employee, and may be assigned to many 7/17/2015 An employee can be assigned to any number of projects, or may not be assigned to any at all Database Concepts 33 Examples of Cardinalities Constraints Optional cardinalities A person is is married to at most one other person, or may not be married at all 7/17/2015 Database Concepts 34 Examples of Multiple Relationships Employees & Departments Entities can be related to one another in more than one way 7/17/2015 Database Concepts 35 Examples of Multiple Relationships Professors and Courses (fixed lower limit constraint) Here, min cardinality constraint is 2 7/17/2015 Database Concepts 36 Multivalued attributes represented as Relationships simple composite 7/17/2015 Database Concepts 37 Strong vs. Weak Entities • Strong entities – exist independently of other types of entities – has its own unique identifier – represented with single-line rectangle • Weak entities – dependent on a strong entity…cannot exist on its own – do not have a unique identifier – represented with double-line rectangle 7/17/2015 Database Concepts 38 Identifying Relationships • Identifying relationship – links strong entities to weak entities – represented with double-line diamond 7/17/2015 Database Concepts 39 Example of Weak Entity & its Identifying Relationships Strong entity 7/17/2015 Weak entity Database Concepts 40 Associative Entities • It is an entity – it has attributes • AND • It is a relationship – it links entities together 7/17/2015 Database Concepts 41 Associative Entities • When should a relationship with attributes be an associative entity? – The associative entity • relationships should be many • could have meaning independent of the other entities • preferably has a unique identifier, and should also have other attributes • may participate in other relationships other than the entities of the associated relationship – Ternary relationships should be converted to associative entities 7/17/2015 Database Concepts 42 A Binary Relationship with an Attribute Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationship 7/17/2015 Database Concepts 43 Associative Entity Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right. Note that the many-to-many cardinality between entities in Figure 3-11a has been replaced by two one-to-many relationships with the associative entity. 7/17/2015 Database Concepts 44 Associate Entity Bill-of-Materials Structure This could just be a relationship with attributes…it’s a judgment call 7/17/2015 Database Concepts 45 Ternary Relationship as an Associate Entity 7/17/2015 Database Concepts 46 Microsoft Visio Notation for Pine Valley Furniture E-R diagram Different modeling software tools may have different notation for the same constructs 7/17/2015 Database Concepts 47 Naming Relationships • A relationship name should be a verb phrase – Assigned_to, Teaches • Avoid vague names – Has or Is_related_to 7/17/2015 Database Concepts 48 Defining Relationships • A relationship definition – Explains what action is being taken and (possibly) why it is important – Gives examples to clarify the action – Should explain • any optional participation • the reason for any explicit maximum cardinality other than many • any mutually exclusive relationships • any restrictions on participation in the relationship • whether an entity instance involved in a relationship instance can transfer participation to another relationship instance 7/17/2015 Database Concepts 49 Homework Assignment • Homework Assignment 3 • Project Exercise – Page 138, #1, #2 – Due next week • In-class Activity – Page 134, #18, Create an ERD and then create an Access database to match your ERD. 7/17/2015 Database Concepts 50 7/17/2015 Database Concepts 51