Week 10 - Data Models PDF

INFMGMT – INFORMATION MANAGEMENT WEEK 10 Data Model  Part # 2 Objectives Understand concepts of data modeling and its purpose Learn how relationships between entities are defined and refined, and how such relationships are inc...

INFMGMT – INFORMATION MANAGEMENT WEEK 10 Data Model  Part # 2 Objectives Understand concepts of data modeling and its purpose Learn how relationships between entities are defined and refined, and how such relationships are incorporated into the database design process Learn how ERD components affect database design and implementation Learn how to interpret the modeling symbols 2  Part # 2 Data Modeling and Data Models Data modeling is the first step in the database design journey, serving as a bridge between real-world objects and the computer database. A data model is a relatively simple representation, usually graphical, of more complex real-world data structures.  Part # 2 Data Model (con’t) The data modeling revolves around discovering and analyzing organizational and users data requirements. Requirements based on policies, meetings, procedures, system specifications, etc. Identify what data is important Identify what data should be maintained 4  Part # 2 The Importance of Data Model Blue print: official documentation Blue print of house Employee’s w/o DB knowledge can understand a data model diagram vs. a list of tables Used as an effective Communication Tool Improve interaction among the managers, the designers, and the end users Independence from a particular DBMS Network DB, Object-oriented DB, etc.  Part # 2 Access Data Model using ERD 6  Part # 2 What is an Entity Relationship Diagram (ERD)? ERD is a data modeling technique used in software engineering to produce a conceptual data model of an information system. So, ERDs illustrate the logical structure of databases. ERD development using a CASE to ol Powerdesigner by SAP 7  Part # 2 ERD The major activity of this phase is identifying entities, attributes, and their relationships to construct model using the Entity Relationship Diagram. Entity  table Attribute  column Relationship  line 8  Part # 2 How to find entities? Entity: "...anything (people, places, objects, events, etc.) about which we store information (e.g. supplier, machine tool, employee, utility pole, airline seat, etc.).” Tangible: customer, product Intangible: order, accounting receivable Look for singular nouns (beginner) BUT a proper noun is not a good candidate…. 9  Part # 2 Entity Instance Entity instance: a single occurrence of an entity. 6 instances Entity: student Student Last First ID Name Name 2144 Arnold Betty 3122 Taylor John 3843 Simmons Lisa instance 9844 Macy Bill 2837 Leath Heather 2293 Wrench Tim 10  Part # 2 How to find attributes? Attribute: Attributes are data objects that either identify or describe entities (property of an entity). In other words, it is a descriptor whose values are associated with individual entities of a specific entity type The process for identifying attributes is similar except now you want to look for and extract those names that appear to be descriptive noun phrases. 11  Part # 2 How to find relationships? Relationship: Relationships are associations between entities. Typically, a relationship is indicated by a verb connecting two or more entities. Employees are assigned to projects Relationships should be classified in terms of cardinality. One-to-one, one-to-many, many-to-many 12  Part # 2 Relationships One-to-many (1:M or 1..*) relationship A painter creates many different Associations among two paintings, but each is painted by or more entities that are only one painter. Thus, the used by data models. painter (the “one”) is related to In a 1:M relationship, the paintings (the “many”). one entity instance is Therefore, database designers associated with many label the relationship “PAINTER instances of the related paints PAINTING” as 1:M. entity. 13  Part # 2 Relationships Many-to-many (M:N or *..*) relationship An employee may learn many job Association among skills, two or more entities in and each job skill may be learned which one occurrence by many employees. Database of an entity is associated designers label the relationship with many occurrences “EMPLOYEE learns SKILL” as M:N. of a related entity and one occurrence of the Similarly, a student can take related entity is associated many with many occurrences of classes and each class can be the first entity. taken by many students, thus yielding the M:N label for the relationship expressed by “STUDENT takes CLASS.” 14  Part # 2 Relationships One-to-one (1:1 or 1..1) relationship. A retail company’s management structure may require that each of its Associations among two stores be managed by a single or more entities that are employee. In turn, each store used by data models. manager, who is an employee, In a 1:1 relationship, manages only a single store. one entity instance is Therefore, the relationship associated with only one “EMPLOYEE manages STORE” is instance of the related labeled 1:1 entity. 15  Part # 2 Relationships Exercise a CAR has only one PLATE and a PLATE can only be assigned to one CAR 16  Part # 2 Relationships Exercise a CAR has only one PLATE and a PLATE can only be assigned to one CAR One-to-one 1:1 17  Part # 2 Relationships Exercise a book can be written by many authors and an author can write many books 18  Part # 2 Relationships Exercise a book can be written by many authors and an author can write many books Many-to-many M:N 19  Part # 2 Relationships Exercise a DOCTOR can consult many PATIENTs and a PATIENT can be consulted by many DOCTORs 20  Part # 2 Relationships Exercise a DOCTOR can consult many PATIENTs and a PATIENT can be consulted by many DOCTORs Many-to-many M:N 21  Part # 2 Relationships Exercise Band and Member 22  Part # 2 Relationships Exercise Band and Member One-to-Many 1:M 23  Part # 2 Relationships Exercise Movie and Actor/Actress 24  Part # 2 Relationships Exercise Movie and Actor/Actress One-to-Many 1:M 25  Part # 2 Relationships Exercise Mayor can only lead one city and a city can only be lead by one mayor 26  Part # 2 Relationships Exercise Mayor can only lead one city and a city can only be lead by one mayor One-to-one 1:1 27  Part # 2 Relationships Exercise bank account can only be assigned to one person; a person can have several accounts 28  Part # 2 Relationships Exercise bank account can only be assigned to one person; a person can have several accounts One-to-Many 1:M 29  Part # 2 Attributes “Describe detail information about an entity ” Entity: Employee Attributes: Employee-Name Address (composite) Phone Extension Date-Of-Hire Job-Skill-Code Salary 30  Part # 2 Classes of attributes Simple attribute Composite attribute Derived attributes Single-valued attribute Multi-valued attribute 31  Part # 2 Simple/Composite attribute A simple attribute cannot be subdivided. Examples: Age, Gender, and Marital status A composite attribute can be further subdivided to yield additional attributes. Examples: ADDRESS -- Street, City, State, Zip PHONE NUMBER -- Area code, Exchange number 32  Part # 2 Derived attribute is not physically stored within the database instead, it is derived by using an algorithm. Example 1: Late Charge of 2% MS Access: InvoiceAmt * 0.02 Example 2: AGE can be derived from the date of birth and the current date. MS Access: int(Date() – Emp_Dob)/365) 33  Part # 2 Single-valued attribute can have only a single (atomic) value. Examples: A person can have only one social security number. A manufactured part can have only one serial number. A single-valued attribute is not necessarily a simple attribute. Part No: CA-08-02-189935 Location: CA, Factory#:08, shift#: 02, part#: 189935 34  Part # 2 Multi-valued attributes can have many values. Examples: A person may have several college degrees. A household may have several phones with different numbers A car color 35  Part # 2 Required and Optional Attributes A required attribute is an attribute that must have a value; in other words, it cannot be left empty. Example, lastname and firstname require data entries because all person are assumed to have a names. 36  Part # 2 Required and Optional Attributes An optional attribute is an attribute that does not require a value; therefore, it can be left empty 37  Part # 2 Example - “Movie Database” Entity: Movie Star Attributes: SS#: “123-45-6789” Cell Phone: “(661)123-4567, (661)234- 5678” Name: “Harrison Ford” Address: “123 Main Str., LA, CA” Gender: “Female” Age: 24 38  Part # 2 Example - “Movie Database” Entity: Movie Star Attributes: SS#: “123-45-6789” (single-valued) Cell Phone: “(661)123-4567, (661)234- 5678” (multi-valued) Name: “Harrison Ford” (composite) Address: “123 Main Str., LA, CA” (composite) Gender: “Female” (simple) Age: 24 (derived) 39