Modeling Data using Visio 1 ITEC 3485 12252021

Modeling Data using Visio 1 ITEC 3485 12/25/2021

Relational Data Model • Each ENTITY will have multiple attributes. 2 INFO 4140 – Business Databases 12/25/2021

Relational Data Model • ENTITIES are connected to each other by PRIMARY and FOREIGN KEYS. 3 INFO 4140 – Business Databases 12/25/2021

Relational Data Model • What is the name of the driver who delivered at 55 Smith Lane? • How many deliveries has the Chevy made so far? • What is the most popular concrete type? • What is the delivery address(es) of the customer with the phone number 333 -3333? 4 INFO 4140 – Business Databases 12/25/2021

Relational Data Model 5 INFO 4140 – Business Databases 12/25/2021

Database Design Steps (in general order) 1. Database Project Processes 2. Gather Information – Terms and Facts, Data elements 3. Create the business rules, cardinalities 4. Generate the high level ERD (with M: N, no attributes) 5. Convert to an ERD with no M: N 6. Assign Primary and Foreign Keys 7. Generate attributes 8. Normalize 9. Iterate until all tables are in third normal form 10. Create database structure in SQL Server 6 INFO 4140 – Business Databases CONCEPTUAL DESIGN LOGICAL DESIGN CONCEPTUAL DESIGN 12/25/2021

Database Design Steps (in general order) 1. Database Project Processes 2. Gather Information – Terms and Facts, Data elements 3. Create the business rules, cardinalities 7 INFO 4140 – Business Databases 12/25/2021

Designing you ERD – Data Definitions • A definition is considered a type of business rule. • A definition is an explanation of a term or a fact. • Term § A word or phrase that has a specific meaning for the business. Example: course, section, flight, reservation • Fact § An association between two or more terms 8 Example: A customer may request a car model for a specific date. INFO 4140 – Business Databases 12/25/2021

Designing you ERD – Multiple Definitions • Beware of multiple definitions of a basic term. Example: Student Prospects/applicant/admitted applicants/matriculation student/continuing student/former students/graduate • Solution: create a student type attribute. 9 INFO 4140 – Business Databases 12/25/2021

Designing you ERD – Business Rules • Business rules are concept because they are an expression of business policy and guide individual and aggregate behavior. • Well structured business rules are good. § Poorly structured business rules are bad. • Business rules are better understood by the users because they are expressed in terms that they are familiar. • Enforcement of business rules can be automated through the use of software. SQL Server does this through integrity constraints (We will learn more about this in a few weeks). § 10 Integrity constraints – Null or not Null attributes, Primary and Foreign Keys 12/25/2021 INFO 4140 – Business Databases

Designing you ERD – Business Rules • A statement which defines or constrains some aspect of the company. • Designed to assert business structure or influence the behavior of the business. § Examples: 11 A student may register for a section of a course only if he or she has successfully completed the prerequisites for that course. A student may take no more than five courses per quarter. A preferred customer gets a 10% discount, unless he has an overdue account balance. A customer can not exceed his/her credit limit. INFO 4140 – Business Databases 12/25/2021

Designing you ERD – Business Rules • For database design, EACH relationship between entities will have TWO business rules that are mirror images of each other (i. e. read forwards and backwards): • <entity 1> <minimum cardinality> <relationship> <maximum cardinality> <entity 2> • AND • <entity 2> <minimum cardinality> <relationship> <maximum cardinality> <entity 1> “May” typically indicates “optional”. § Examples: 12 A customer may submit zero to many orders. A order is for one and only one customer. INFO 4140 – Business Databases 12/25/2021

Relational Data Model • There are 3 different cardinalities to choose to connect entities: 1. 2. 3. § 13 0 = zero 1 = one = many (crow’s foot) Think of this as making a FORWARD and BACKWARD sentence. WHILE considering the FEWEST and MOST scenarios. INFO 4140 – Business Databases 12/25/2021

Relational Data Model • There are 3 different cardinalities to choose to connect entities: 1. 2. 3. 0 = zero 1 = one = many (crow’s foot) • For database design, EACH relationship between entities will have TWO business rules that are mirror images of each other (i. e. read forwards and backwards): 1. <entity 1> <minimum cardinality> <relationship> <maximum cardinality> <entity 2> AND 2. 14 <entity 2> <minimum cardinality> <relationship> <maximum cardinality> <entity 1> INFO 4140 – Business Databases 12/25/2021

Relational Data Model • There are 3 different cardinalities to choose to connect entities: 1. 2. 3. § § 15 0 = zero 1 = one = many (crow’s foot) Considering the MINIMUM and MAXIMUM cardinalities. FORWARD: MINIMUM: A student can be enrolled in 0 Course Sections. MAXIMUM: A student can be enrolled in MANY Course 12/25/2021 INFO 4140 – Business Databases Sections.

Relational Data Model • There are 3 different cardinalities to choose to connect entities: 1. 2. 3. § § 0 = zero 1 = one = many (crow’s foot) Considering the MINIMUM and MAXIMUM cardinalities. BACKWARD: MINIMUM: Each Course Section must have at least 1 unique student. 16 INFO 4140 – Business Databases MAXIMUM: Each Course Section must only have 1 unique 12/25/2021

In Class Problem 1 17 INFO 4140 – Business Databases 12/25/2021

Creating an ERD • Many Types of Entity-Relationship Diagrams (a high level model of the data relation) § VISIO supports several: § Information Engineering (IE) [James Martin 1990] Uses “crow’s feet” to show the many sides of a relationship, and it is sometimes called the Crow’s Foot Method 18 Bachman Chen ERD, Database Model Diagram Express-G, Martin ERD ORM Source Model ER Source Model This is the one we will use for the design portion of the course, however you may switch later to another for your project. It is often easier to simply use the one generated in SQL Server. INFO 4140 – Business Databases 12/25/2021

Visio Demonstration • Let’s create Problem 1 in Visio 19 INFO 4140 – Business Databases 12/25/2021

Create a Data Design using Visio • Now you try! 1. 2. 3. 20 FIRST, as a team, add cardinalities to your database for an HR department of an IT company. Create this in Visio. Be prepared to present this in class as a team. INFO 4140 – Business Databases 12/25/2021

Create a Data Design using Visio • INDIVIDUAL assignment 1. 2. 3. 21 BEFORE CLASS NEXT WEEK: Individually create database design with entities, attributes and keys for a product company or service company. Next, DRAW cardinalities in your database you created on paper. Create this in Visio. INFO 4140 – Business Databases 12/25/2021

Create a Data Design using Visio • INDIVIDUAL Canvas assignment Due on Canvas at 3: 59 pm MT on Week 3 Day 1 1. • TEAM LOE Canvas assignment § Due on Canvas at 11: 59 pm MT on Week 3 Day 1 • Teamwork Rubric § Due on Canvas at 11: 59 pm MT on Week 3 Day 1 22 Each Teammate YOURSELF!!! INFO 4140 – Business Databases 12/25/2021