Data Modeling in ISAD: Understanding Entities, Attributes, and Relationships, Exams of Information Technology

Download Data Modeling in ISAD: Understanding Entities, Attributes, and Relationships and more Exams Information Technology in PDF only on Docsity! 1 InfSci 2510 Information System Analysis and Design Lecture 2 9/9/2002 …from our previous lecture 1. Types of Information Systems 2. The roles of the System Analyst/Designer 3. System development life cycle (SDLC) 4. System and Organization 5. Project planning 6. Term projects System Development Life Cycle • Identify: problems or opportunities • Determine: the requirements • Analyze: the system needs • Design: a solution – the intended system • Develop: the system • Test: functional correctness and performance • Implement: deploy the system • Maintenance/Evaluation: fixes/enhancements Term Project (get started early!) • In groups up to 4 persons; OK to do by yourself, but no special consideration for credit. • One page proposal: goal and scope – review. • Final Report about up to 40 pages. • Presentation in class, with questions, discussion. • Small project: analysis/design to implementation • Large project: analysis and/or design. InfSci 2510 Information System Analysis and Design Fundamentals of Entity-Relationship Modeling Reading Reference Book • Silberschatz, Korth, and Sudarshan Database System Concepts. Chapter 2 (Sections 2.1 – 2.6) …on the fundamentals of data modeling. 2 Topics • Data Modeling in the context of ISAD • Basic Concepts: Entities and Attributes • Relationships and Roles • Design Issues • Structural Constraints • Uniqueness and Key Attributes • Weak Entity Set • Assignment 1 Our view of Information System • People using software programs to access and interrogate data. • Many people using many software programs, but one set of data. Our view of Information System • While the software programs provide the functions which the users need, the information in use must have a stable structure. • The system designer must discover a structure to integrate the different user/functional views. • Such an information structure is called the conceptual schema. …for ISAD: the development cycle • Identify: problems or opportunities • Determine: the requirements • Analyze: the system needs • Design: a solution – the intended system • Develop: the system • Test: functional correctness and performance • Implement: deploy the system • Maintenance/Evaluation: fixes/enhancements …for ISAD: the development cycle For analysis, we focus on: • Determine: the requirements • Analyze: the system needs – first for the DATA we are interested in; – then for the software programs (applications) to provide the FUNCTIONS we need. Data Modeling in ISAD To determine the DATA requirements… • To discover the “inherent” information structure • To integrate the different views of different users • To design a “unified” view – hopefully stable • To formulate a model of the information we will use in the system – that is, a conceptual schema, independent of the specific software or platform we may use. 5 Basic concept: Attributes and Values How do we represent an entity in our model? specific?? • with the appropriate values to a set of attributes about the entity… one value for each attribute. • A teacher is an entity. • We model each teacher with two attributes: – name: – phone: • Every specific teacher is modeled by the specific values to the attributes name and phone. – name: M. Spring – phone: x9429 Basic concept: Attributes and Values Note that… (difference between attribute and value) • An attribute is like a property or characteristic. (attributes of a course: title, course number, …) • Each attribute is identified by a name. (Entity Set: course. Attributes: title, course number.) • Entities of the same Entity Set have the same attributes. (Each course should have a title and a course number.) • Each entity may have different values for the attributes. • For a specific course (ie, entity in the Entity Set course): (the attribute name is title; the value is “Information Systems”.) (the attribute name is course number; the value is 2510.) Entity Set with Attributes • An entity type describes an entity set. • Graphically, we use a rectangle to represent the entity set, and use an oval to represent each attribute we are interested in… Entity Attribute1 Attribute2 Entity Set with Attributes - Example • Entity Type – course, describes an entity set. • Every course (entity in the entity set course) has two attributes – title and number. course title number • Each specific course has specific values for each of the two attributes. The title is a text string; the number is a 4 digit decimal number. Example… Entity Set with Attributes … think of each entity set as a deck of cards. Each card is labeled with entity type, and the same attributes, being of the same type. Each card has its own specific values for each of the attributes, representing a specific entity. course title: doc proc number: 2770 course title: intro to IS number: 2000 course title: info system number: 2510 Attributes and Value Sets • An attribute is a property or characteristic of an entity. For example, the title of a course, the name of an instructor. • For an entity, an attribute takes a specific value. For example, this specific course has the title “Information System Analysis and Design.” • An attribute should have a data type, which indicates the set of values it can take. For example, the title of a course is a text string. 6 Attributes and Value Sets • An attribute may be very limited in terms of the values it can take. For example, color of phones on sale can only be black, or white. Then we say that value set is { Black, White }. phone on sale color: { Black, White } { Tone, Pulsed }? : Entity and Attribute, formally • For an entity set E, we are interested in the attributes A1, A2, … An of its entities e ∊ E. • Each attribute Ai is a function Ai: E→Di, where Di is the value set for Ai. • Every entity e ∊ E, is represented by the values corresponding to its attributes A1, A2, … An such that (d1,d2,…,dn) ∊ D1×D2 × … × Dn • The entity type is a statement P(e) about e, that is the attribute values; the entity set is therefore E = {e as (d1,d2,…,dn) ∊ D1×D2 × … × Dn : P(e) } Entity and Attribute, formally • For an entity set E, we are interested in the attributes A1, A2, … An of its entities e ∊ E. • Each attribute Ai is a function Ai: E→Di, where Di is the value set for Ai. • Every entity e ∊ E, is represented by the values corresponding to its attributes A1, A2, … An such that (d1,d2,…,dn) ∊ D1×D2 × … × Dn • The entity type is a statement P(e) about e, that is the attribute values; the entity set is therefore E = {e as (d1,d2,…,dn) ∊ D1×D2 × … × Dn : P(e) } Attributes and Value Sets An attribute may be… • simple/composite: different parts of an attribute, each being an attribute on its own. student name class last name first name middle i An attribute may be … • optional: the attribute may or may not have a value for some entities Attributes and Value Sets student name A student may or may not have locker assigned.locker An attribute may be … • single-valued/multi-valued: the attribute takes more than one value. Attributes and Value Sets student name A student may have taken many courses…courses 7 An attribute may be … • stored/derived: the attribute is computed from other attribute values. Attributes and Value Sets student name class year 2001, 2002,… freshman, sophomore, junior, senior. Entity and Attributes: ER Diagram • An Entity Set and its Attributes… Entity simple attribute composite attribute simple attribute simple attribute multi-valued attribute key attribute derived attribute optional attribute Topics • Data Modeling in the context of ISAD • Basic Concepts: Entities and Attributes • Relationships and Roles • Design Issues • Structural Constraints • Uniqueness and Key Attributes • Weak Entity Set • Assignment 1 Relationship and Roles • Entities may be related to one another, each entity takes a role in the relationship. Example: a course, a teacher, and a student may be related – the student is taking the course taught by the teacher. • The number of roles (therefore entities) involved is called the degree of the relationship. • In our ER modeling, we will focus on: – relationships of a fixed degree; – relationships of degree 2, binary relationships. • Justification: sufficient, and readily implementable. Relationship Type, Relationship Set • A relationship type is a generic description of the nature of a relationship between the entity types. • An instance of the relationship type an ordered tuple of the related entities – called a relationship instance. • A relationship set is the collection of all such instances of the relationship type. • Example: “an instructor teaches a course” is the generic description. (Wu, 2510), (Spring, 2770) are two instances of the relationship type. • Relationship Set = {(Wu,2510), (Spring,2770), …}. Relationship Instances… course title: intro to IS number: 2000 course title: info system number: 2510 teacher name: P.Wu phone: x 9427 teacher name: M.Spring phone: x 9420 teaches (P.Wu, 2510) teaches (M.Spring, 2000) Relationship Set 10 Example: attribute or entity? We want to build a directory info system for a company. We may model office as an entity… employee name room# phone e-mail office Example: Implied Relationship? To make office assignments, we may add these attributes, implying the relationship between the entities. But this is not desirable: we want to model relationship explicitly. employee name room# phone e-mail office office occupant Example: Explicit Relationship! We should model relationship explicitly and avoid using attributes to imply a relationship. employee name room# phone e-mail officeoccupies office occupantX X Example: Explicit Relationship. So that we may also keep track of changes in the near future within our database… employee name room# phone e-mail officeoccupies effective date Example: relationship or entity? Men and Women members of a skating club are going to pair up in a competition…. Man Womanpairs up with pair number Example: relationship or entity? Each pair will compete and get rated by three judges, each giving a score… Judge Pairrates score 11 Example: relationship to relationship? • A relationship is the association of entities, should not be that of other relationships! Judge rates Man Womanpairs up with score pair number ? Example: relationship to entities! Judge rates Man Woman score pair number Pair • Each entity is specific, and can be identified. • Each relationship instance relates specific entities, and therefore can also be identified. • The attributes and values of an entity must NOT imply a relationship with other entities; relationship should be explicit in the model. • A relationship set may relate entity sets, NOT other relationship sets. • It is preferred for relationship set to relate only two entity sets: binary relationships only. ER Modeling Design Rules Q: Relationship with Relationship? • Graphically in the ER Diagram, it means that we connect a diamond to another diamond, and this is NOT allowed. Why? • The answer to this may be difficult now; we will try to understand more about ER model and hopefully it will become clearer… • Let us study the constraints on a relationship. Topics • Data Modeling in the context of ISAD • Basic Concepts: Entities and Attributes • Relationships and Roles • Design Issues • Structural Constraints • Uniqueness and Key Attributes • Weak Entity Set • Assignment 1 Structural Constraints • The information structure depicted in our ER model may have certain constraints, pertaining to the nature of the relationships in the model: – Participation Constraint: about how the entity sets are participating in the relationship. – Cardinality Constraint: about the ratio of the number of entities in each entity sets being related. • Collectively, these are known as the structural constraints of the Entity-Relationship model. 12 Participation Constraint When an entity set participates in a relationship, it may participate partially, or totally. • Partial Participation: some entities of the set do not participate in any relationship instance. • Total Participation: every entity of the set participate in at least one relationship instance. Partial Participation • Example of partial participation: some employees may not work on any project. The entity set Employee participates partially in the relationship “works on”. • But we may not allow a project with no employees. Employee Projectworks on Employee Project works on Total Participation • Every project must have at least one employee. • The entity set Project participates totally in the “works on” relationship: a double connection line. Employee Projectworks on Employee Project works on Cardinality Ratio • When an entity set participates in a (binary) relationship, the cardinality ratio specifies the ratio between number of related entities in the relationship. • For example: at most one manager for every department, and a manager manages no more than one department. one-to-one. Employee Department manages Employee Department manages 1 1 Cardinality Ratios • There may be other forms of ratios... • We label only these forms – 1:1, 1:N, N:1, M:N. • Many employees may serve the same department, but each employee may not serve more than one department. Employee Departmentserves Employee Department serves N 1 Cardinality Ratios • An example of M:N ratio – an employee may work on more than one project; any project may have more than one employee working on it. M-to-N. Employee Projectworks on Employee Project works on M N 15 Key Attributes • The designer should choose a minimal key attribute set, and indicate the choice… course offered title CRN number classroom term class time Q: Key attributes for a relationship? • A relationship may have attributes. • But a relationship should NOT have key attributes. Why? • Should the relationship instances each be uniquely identified? • How can each relationship instance be uniquely identified? Topics • Data Modeling in the context of ISAD • Basic Concepts: Entities and Attributes • Relationships and Roles • Design Issues • Structural Constraints • Uniqueness and Key Attributes • Weak Entity Set • Assignment 1 Weak Entity Set • Every normal entity set should have a set of key attributes specified. It is called the uniqueness contraint – required in an entity set, so that we can identify each specific entity. • However, sometimes we allow an entity set to have NO key attributes but only Partial key attributes – it is called a Weak Entity Set. • An entity set with key attributes specified is therefore sometimes called a Strong Entity Set. Weak Entity Set • A strong entity set should have at least one set of key attributes – so that every entity can be uniquely identified, fulfilling the uniqueness constraint. • A weak entity set does not have a set of key attributes (may have partial key attributes), but the uniqueness constraint still requires every entity to be uniquely identified. • A weak entity set must participate totally in at least one dependency relationship, and, if necessary, have a set of partial key attributes, to fulfill the uniqueness constraint. Weak Entity Set: Example • Consider modeling the information in a library. We have the entity set Book for books… Book title author year Call No 16 Weak Entity Set: Example • Members of the library may borrow books: we have a relationship between Book and Member… Member Bookloan due date • Can we have two different members borrowing the same book? (More accurately stated: two different copies of the same book?) PROBLEM! Weak Entity Set: Example • The Book entity set: does each entity represent a book? OR a copy of a book? Need copy number! Book Copy title author year Call No + Copy No Weak Entity Set: Example • We need to deal with two different concepts in the library: book, and copy of a book: Book CopyBook title title authorauthor call no call no copy no Weak Entity Set: Example • With both Book and Book Copy, we can properly model the relationships with Member. Book Copy Book Member expected date due date reserves borrows(0,4) (0,1) (0,*) (0,*) Weak Entity Set: Example • We duplicate a good deal of information in the entity sets: Book and Book Copy – the two should be related! (implied relationship) Book CopyBook title title authorauthor call no call no copy no copies N1 Weak Entity Set: Example • We will allow a weak entity set for Book Copy, with partial key copy no (underlined by dash), and a dependency relationship copies to a strong entity set. Book CopyBook title author call no copy no copies N1 17 Weak Entity Set: Example • Each book copy (entity in a weak entity set) is then uniquely identified by the copy no (partial key) for each book (strong entity it depends on). Book CopyBook title author call no copy no copies N1 Weak Entity Set • A weak entity set must participate totally in at least one 1-to-1 or N-to-1 relationship which is the dependency relationship. • A weak entity set may have a set of partial key attributes. (Each is underlined by dashed line). • The partial key must be sufficient to identify each entity in the weak entity set, within each group (of N) related to the same entity through the dependency relationship, fulfilling the uniqueness constraint. Weak Entity Set: Example • To model dependents of company employees, we model children in family units as entities in an entity set – child. • Each child has a first name, may or may not have a social security number; therefore the attributes are not sufficient to identify each child, except within the family. • A Weak Entity Set has a double rectangle, and the attributes for a partial key, underlined in dash. child first name social security no. Weak Entity Set: Example • The weak entity set depends on the relationship with a strong entity set to identify each entity in the set. It is called a dependency relationship. family child first name soc sec no dependents last name 1 N Topics • Data Modeling in the context of ISAD • Basic Concepts: Entities and Attributes • Relationships and Roles • Design Issues • Structural Constraints • Uniqueness and Key Attributes • Weak Entity Set • Assignment 1 Assignment 1 • Due Monday next week, in class. • OK to work in groups (up to 4 persons): submit only one copy, bearing all the names. • Requirements spelled out in natural language (such as English) may be fuzzy sometimes; use common sense assumptions. • May draw diagrams using Power Point, Visio, or Word, Corel Draw…. hand drawn diagram OK too – as long as they are readable, but please do not use pencil.