Data Modeling ISYS 464 Creating Database Analysis System

Data Modeling ISYS 464

Creating Database • Analysis – System analysis • Data requirements analysis • Data modeling with Entity Relation Diagram, ERD • Design – Maps the data model on to a target database model. – Relational/object-relational/document model • Implementation: Efficiently store and retrieve data – File organization and index

Database Development Life Cycle

Data Requirements Analysis • Requirements Analysis is the stage in the design cycle when you find out everything you can about the data needs to store in the database. • It involves interviews with user groups to identify what functionality they require from the database, what kinds of data and information they wish to process. • Example: Assignment 1

Entity-Relationship Diagram • ER modeling is a top-down approach to database design that begins by identifying the entities and relationships between entities that must be represented in the model. – Relative ease of use. – are natural modeling concepts in the real world. The belief that entities and relationships – Widespread CASE tool and database management systems support. • Automate the database design based on ERD

ERD Models Data and Business Rules • Example: – A customer may submit any number of orders. However, each order must be submitted by only one customer. – A student may register for a section of a course only if he or she has successfully completed the prerequisites for that course.

Conceptual Database Design Methodology • Identify entity types. • Identity relationship types between the entity types. • Identify and associate attributes with entity or relationship types. • Determine attribute domains. • Determine candidate keys and primary key. • Validate conceptual model: – Check for redundancy, support required transactions, review the model with user

Entities and Attributes • An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. – Person: Employee, Student, patient – Place: Warehouse, Store – Object: Product, Machine. • Entity’s attributes define the characteristics or the properties of an entity – Student: ID, name, address, major, gender, email – Product: ID, name, price

Entity Type • A collection of entities that share common properties or characteristics. • An entity type represents a collection of entities. • In an ERD, it is given a singular name. • Diagrammatic representation: – A rectangle labeled with the name of the entity – Singular noun

Entity Instance • An entity instance is a single occurrence of an entity type: – Student entity: SID, Sname, Major – Two instances of Student entity type: • S 1, Peter, Bus • S 5, Paul, Sci

Entity Type and Entity Instances FIGURE 2 -3 Entity type EMPLOYEE with two instances Entity type: EMPLOYEE Attributes Attribute Data Type Example Instance Employee Number CHAR (10) 64217836 53410197 Name CHAR (25) Michelle Brady David Johnson Address CHAR (30) 100 Pacific Avenue 450 Redwood Drive City CHAR (20) San Francisco Redwood City State CHAR (2) CA CA Zip Code CHAR (9) 98173 97142 Date Hired DATE 03 -21 -1992 08 -16 -1994 Birth Date DATE 06 -19 -1968 09 -04 -1975 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

An Entity… • Should Be: – An object that will have many instances in the database – An object that will be composed of multiple attributes – An object that we are trying to model • Should Not Be: – A user of the database system – The company or organization that owns that database. – An output of the database system (e. g. , a report) Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Figure 2 -4 Example of Inappropriate Entities (a) System user (Treasurer) and output (Expense Report) shown as entities b) E-R diagram with only the necessary entities Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Relationship Type • Relationship: Interaction between entity types. – It is an association representing an interaction among the instances of one or more entity types that is interest to the organization. • It has a verb phrase name: – Faculty teach Course, Faculty advise Student – Customer open Account, Customer purchase Product.

Figure 2 -10 Relationship Type and Instances Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Binary Relationship • A relationship involves two entity types. • Three kinds of Binary Relationship – 1: 1 – 1: M – M: M • Determined by business rules

M: M Relationship Boy Peter Mary Paul Linda John Nancy Woody Mia Alan Pia A boy may date 0, 1, or many girls. A girl may date 0, 1, or many boys. Note: “Many boys date many girls” is not a correct interpretation of this M: M relationship. Girl

1: 1 Relationship Man Woman Peter Mary Paul Linda John Nancy Woody Mia Alan Pia A man may marry 0 or 1 woman. A woman may marry 0 or 1 man.

1: M Relationship Father Peter Paul John Woody Alan A father has 1 or many children. A child has 1 father. Child Mary Brian Linda Aron Nancy Ronald Mia Pia

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 (partial participation) – If one or more, then mandatory (full participation) • Maximum Cardinality – The maximum number Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Notations

Other Notations UML Notations: – 0. . 1, 1. . 1 – 0. . *, 1. . * – 3. . 5 Student Has 1. . 1 Account • Traditional: Student 1 Has 1 Account

Examples: 1: 1 Relationship • Examples: – Husband, Wife – State, State Governor – Country, President – Order, Invoice

1: M Relationship • Hierarchical relationship • Examples: – Father, Child – Department, Employee – Customer, Order – Primary physician, Patient

M: M Relationship • Examples: – Bank customer, Bank account – Student, Student organization – Employee, Skill

ERD Example https: //app. diagrams. net/

ERD with Optional/Mandatory Participation

Traditional ERD Notations 1 Student M M Advise Has 1 Account M Enroll 1 Faculty 1 Teach M Course

UML ERD Notations Student Has 1. . 1 0. . * Advise 1. . 1 Account Enroll 0. . * 0. . 1 Teach Faculty 1. . 1 1. . * Course

Other Examples • A database to record visitors and web pages they view. • An online shopping website database to record customers, orders (shopping carts) and products purchased by customers.

Two entity types may involve in more than one relationship Example: An auction database to record sellers and the items they sell, buyers and the items they purchase.

Recursive Relationship (Unary Relationship) • A relationship type where the same entity type participates more than once in different roles. • Examples: – Employee – Supervise -- Employee – Student -- Tutor– Student – Faculty – Evaluate -- Faculty

Supervise Supervisor Employee Supervisee Employee 1 M Supervise

Attributes: Properties of an entity or a relationship • Simple attributes: Attributes that does not have sub attributes. – Ex. Student ID, Bar. Code • Composite attributes – Address: Street address, City, State, Zip. Code – Street Address: Number, Street, Apt# – Phone#: Area Code, number • Single-valued and multi-valued attributes – Student’s Major attribute – Employee email attribute, phone attribute – Vehicle’s Color attribute • Derived attributes • Keys: Key attribute uniquely determines an entity. – Candidate key, primary key, composite key

ERD with Attributes

Figure 2 -7 A Composite Attribute: An attribute that has meaningful component parts (sub-attributes) • Address (Street address, City, State, Zip. Code) • Note: Use parenthesis to show sub-attributes on ERD Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Figure 2 -8 Multivalued Attributes • Multivalued: A attribute that may have more than one value. – Example: An employee can have more than one skill. • On ERD, use { } to show a multivalued attribute. – Example: {Skill} Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Figure 2 -8 Derived Attributes Values can be calculated from related attribute values (not physically stored in the database) – Years employed calculated from date employed and current date • On ERD, use [ ] to show derived attribute. – Example: [Years. Employed] Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Identifiers (Keys) • Identifier (Key) – an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type • Simple versus Composite Identifier • Candidate Identifier – an attribute that could be an identifier; it satisfies the requirements for being an identifier Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Figure 2 -9 Simple and Composite Identifier Attributes a) Simple identifier attribute b) Composite identifier attribute: Flight Number + Date Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved

Composite Key Examples • Exampe : • Course. Section Table: • Course. ID, Section. ID, Capacity • Isys 464 Section 1 32 • Isys 464 Section 2 30 • Isys 464 Section 3 36 • What is the key of this table? • Course. ID + Section. ID

Example: Student Entity Type SID {PK} Sname( Fname, Lname) Address( Street, City, State, Zip) {Phone} Sex Date. Of. Birth [Age]

UML Notations Student SID {PK} Sname Fname Lname Address Street City State Zip Phone[1. . 3] Sex Date. Of. Birth /Age

Fname SID Lname Sname Phone Date. Of. Birth Age Student

Domains of Attributes • The set of allowable values for one or more attributes. • Input validation • Examples: – Sex: F, M – Emp. Hourly. Wage: Between 6 and 300 – Emp. Name: 50 characters

Attributes on Relationship Online Shopping Cart CID Addr Cname Customer 1 Has Cart. ID M Date Shopping. Cart Sales. Person M Has M Product Price PID Pname

Order Form

Online Shopping Cart CID Addr Cname Customer 1 Has Cart. ID M Date Sales. Person Shopping. Cart M Qty Has M Product Price PID Pname

Attributes on Relationship • Examples: – Student/Course: Grade – Order/Product: Quantity

Student SID Enroll 0. . * Course CID Grade Student M Enroll M Grade Course

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

Ternary Relationship • Doctor – Patient – Ailment • Police – Criminal – Crime • Note: There is no deterministic relationship (1: 1 or 1: M) between any two of these entities.

Examples of Ternary relationship Note 1: a relationship can have attributes of its own. Note 2: This ternary relationship exists only if there is no binary relationship between these three entities.

If there is a 1: M relationship between Vendor and Part, then this is not a ternanry relationship Vendor. ID Part. ID Warehouse. ID V 1 P 1 W 2 V 1 P 3 W 1 V 1 P 1 W 3 V 2 P 2 W 2 V 2 P 2 W 1 V 2 P 2 W 3 Note: Part P 1’s vendor already know, and is duplicated.

Major Goals of Relational Database Design • Must be able to link related records. • There is no duplicated data in the database.

Figure 2 -21 Examples of multiple relationships a) Employees and departments Entities can be related to one another in more than one way Example: Auction site: User and Auction Item