DATABASE DESIGN ERD Entity Relational Diagram ERD Visual

DATABASE DESIGN

ERD • Entity Relational Diagram (ERD) • Visual display of tables and their relationships for a database • Used for “Relational Modeling” 2

ERD cont. • SQUARES indicate TABLES • LINES between them indicate the different relationships • “FEET” at end of line, indicates a “MANY” relationship • -1 CUSTOMER has MANY “receipts” • -1 STAFF MEMBER sells MANY “receipts” • -1 PRODUCT can go into many different “receipts” 3 • Each product has to be INDIVIDUALLY listed

ERD cont. • IF I wanted to know who sold me the order, I have to go to the receipt (order ) , in the “RECEIPT” table, I’ll find who the STAFF MEMBER is who sold me that receipt • IF I wanted to know what product a CUSTOMER has ordered, I have to go to the receipt (order ), in the “PRODUCT” table • -NO DIRECT relationship BETWEEN : • CUSTOMER & STAFF, or • CUSTOMER & PRODUCT 4

What are Tables or Entities • Used to represent things in the “real world” that is of significant interest to the user • i. e. “EMPLOYEES” might be an “Entity” in a company database Employees • NOUNS in a “Business Narrative” • each table is called an “entity” and the columns are referred to as an “attribute”. • within a table , each row is called a “record” and it represents 1 particular instance of its “entity” (not unlike the flat file). 5

Entities Continued • Another example: • A database regarding music • Artist & Song are both tables • “Performs” is the relationship (VERB) (usually is a “supporting document” which explains the relationship) 6

Entities and Their Attributes • “Entities” represent something of importance • “Entities” are made up of several “Attributes” (the columns), which describe the tables: ARTISTS SONGS Artist. Name Song. Title Gender Length 7

Entities Summarized • Each ENTITY is made up of rows and columns • Columns are called fields or attributes of a database • Rows are called records or an instance of an entity • If you have repetitive VALUES to 1 attribute, —create a: “JOINER TABLE” or “LOOKUP TABLE” • make this attribute a “Foreign” key • Create another table with same attribute • i. e. a: “Shoe Table” with “colours” • “Colours” will be a “Foreign” key, here: with “Colour. ID” • primary key in a new table called: colours, with “Colour. ID” • 8 -10 attributes per TABLE, after that create another table 8

Entities Summarized cont. • OR: When a table has the SAME VALUE REPETITIVELY for that attribute, you might need a “LOOKUP TABLE”: 1. People might misspell, the value, then you can’t do a: SEARCH i. e. LEVEL OF EDUCATION “Masters” What if 1 person spells it: “Masters” o Another person “Master” o Another person Masters of Education 2. You will have multiple occurences of 1 VALUE which takes up too much space in your database 9

Steps in Database Design 1. Complete a “Needs Assessment” (note: this is a huge process that is not covered in this course) • Basically, you speak with end users, managers, existing documents and think “outside the box” • If, in the design of the database, there is multiple occurrences of data (think of our “gender” example) this is an indicator that another table should be created. 2. Create a “Business Narrative” 3. Identify entities 4. Identify attributes & keys 5. Identify relationships 10

Business Narrative • Vital link between database designer and the end users • Purpose is to make clear the “data requirements” • The story, business rules, purposes of database; what kind of info it will hold • Will have to go over the DESIGN multiple times/days to make sure the DESIGN is correct • Get other people/users to comment on design • Example: • “Algonquin college employs many instructors to deliver various programs comprised of many different courses. “ 11

Starting an ERD • Identify the “entities” • Look at NOUNS of the “Business Narrative” “Algonquin college employs many instructors to deliver various programs comprised of many different courses. ” programs instructors 12 courses

When the list of Entities Seems Complete • Ask about each “entity”: 1. Is it significant? • List only entities that are important to your database users and that are worth the trouble and expense of computer tabulation. 2. Is it generic? • List only types of things, not individual instances • “EMPLOYEES”, “INSTRUCTORS” are generic of many different instances—we don’t say “Joe” 3. Is it fundamental? • List only entities that exist independently and do not need something else to explain them. • “Courses”-explains itself, you don’t need further elaboration 13

RELATIONSHIPS

Three Basic Types of Relationships • One to One (1: 1) • One to Many (1: M) • Many to Many (M: M) • The 1 is the “PARENT”, and the M is the “CHILD” • Called the CARDINALITY of the relationship • The cardinality indicates the MAXIMUM number of RELATIONSHIPS between the entities, • Whenever you see: “MANY”—this will be the FOREIGN KEY

One to One Relationships • 1: 1 relationships exists when a single record in one table has 1 and only 1 corresponding record in another table, and vice versa • VERY RARE • When you created a table for something, when it really should only have been a “field” • The 1 should really have been an “attribute” of the other (table/entity) an attribute IMPROPERLY DEFINED as an entity • i. e. 1 customer, 1 address • “Address” should have been the attribute of the “Customer” table

One to One Example • A voter can cast only one vote in an election. • A ballot paper can belong to only one voter. • So there will be a 1: 1 relationship between a Voter and a Ballot Paper. Voter Ballot

One to Many Relationships • MOST COMMON • 1: M or M: 1 • Relationship exists when a record can relate to 1 OR MORE records in a 2 nd table but. . • A record in the 2 nd table can ONLY relate to 1 record in the 1 st table

One to Many Example • A person can own more than one car. A car can only have one owner. Owner Vehicle

Many to Many Relationships • M: M • Relationship exists when 1 record in either table can relate to more than 1 record in the other table • These should be eliminated • 2 related “PARENT” tables, but their relationship is NOT “direct” • Instead, it needs to be related through an additional “CHILD” table

Many to Many Example • A student can have more than one professor; the same professor can have many students Student • http: //www. youtube. com/watch? v=0 l. SLI 7 q-h. VE Professor

Resolving Many to Many • M: M is not allowed, as an attribute could have more than 1 value: • A “Multi-Valued Attribute” • Difficult to generate reports, or do searches • Would have to repeat attributes multiple times • Instead, we create a “JUNCTION” or “JOINER ENTITY” • The name of the joiner entity is often a COMBINATION of the 2 entities Student Stud_Professor

Many to Many Relationships

Resolving Many to Many Relationships By Creating A Junction Or Joiner Entity • http: //www. youtube. com/watch? v=Nvrpu. BAMddw

SHEETS OF ICE • 1 arena with many sheets • ARENA ID-primary key in ARENA table • Add a FOREIGN KEY to SHEETS OF ICE (AREN_AID) • Can a sheets of ice belong to many arenas? NO—so we know it’s a one-to-many • TEAMS table-PRIMARY KEY: TEAM ID to PLAYERS table FOREIGN KEY: TEAM ID

Relationship Review

Normalization • Refining the ERD • Task of analyzing entities and the relationships created have been formalized into a process called normalization. • Should resolve all your relationships to One to Many (1: M) relationships • Eliminate all One to One’s (1: 1) and Many to Many’s (M: M)

Sample Business Narrative • CD Collection example • I have many cd’s by various artists. I like all types of music 28

Sample Business Narrative • Courses example: • A course can have many different learning objectives and each learning objective has various examples and a method of testing. 29

Sample Business Narrative • New Home Builders example: • Bamarack is a builder of new homes in the Ottawa area. They subcontract all the work that needs to be completed for the build of a new home. Each subcontractor has a specialized trade. 30

We Build a Business Narrative • Create a business narrative for your shoe collection at home STYLE ID STYLE NAME 1 SPORTY 2 SPORTY 3 DRESSY 31

Attributes for Our Shoe Scenario • Identify the attributes for all entities identified for our shoe inventory database 32

Selecting Attributes • Attributes are the fields/columns that describe an entity • Attributes can be: • A Characteristic • A Quality • A Feature • A Fact • An attribute is a “non-decomposable” piece of information about an entity • Can’t be broken down any further, only 1 single value per column • i. e. NAME—last name, 1 st name • Attribute “Naming Conventions” • Single word (or more than 1 word with an underscore (no spaces) • Unique • Can’t be a reserved word” • i. e. “Number”, “Date” ( these are date types), OR name of column can’t be same as name of table 33

Attributes • Identify the entity and attributes for the following business narrative: • ABC Property Management Inc has a building with 20 units. Some are rented, some are vacant. Some are 1 bedroom, some are 2 bedrooms. The rent varies. ABC Property Management Inc keeps track of the tenant and lease information 34

• ONLY WATCH UNTIL 5 minutes 31 seconds (until it mentions: “QUERYSMITH”) • http: //www. youtube. com/watch? v= Y-bvjt. Yg. RVU 35

REMOVE SORT • HOME tab, in the SORT & FILTER group, click the REMOVE SORT button:

FILTER BY SELECTION in a “TABLE” • HOME > SORT & FILTER group > click on the FILTER icon > • To FILTER: • Click on the drop-down arrow on the COLUMN HEADING (field) you want to filter • Select or de-select the VALUES you want to filter

NUMBER FILTERS • Click on the drop-down arrow on the COLUMN HEADING (field) you want to filter • NUMBER FILTERS > select one of this options: • When you select “Less Than” or • “Greater Than”, by default it will • Also contain “Equal To”, so key in • The next number UP (for GREATER than), or The next number DOWN (for LESS than) • I. E. If you want >200, you would have to key in >201 (as it contains “equal to”, and will flag “ 200” also)

FILTER BY FORM • SORT & FILTER group • ADVANCED button • FILTER BY FORM

FILTER BY SELECTION in a “QUERY” • To filter all the rows in a table that contain a value that matches a selected value in a row • Right-click the VALUE you want to filter. > select: “EQUALS” the specific value you want • IF A FILTER IS ALREADY APPLIED: • HOME tab, > SORT & FILTER group, > Advanced, > click: CLEAR ALL FILTERS.

ADDING A “TOTAL” ROW IN A QUERY • QUERY TOOLS DESIGN tab, > SHOW/HIDE group, click the TOTALS button • In the TOTAL row below the AMOUNT field, click the drop-down arrow and select: MIN. • DESIGN tab, > RESULTS group, > RUN button. • VIEW the query in DESIGN view

HYBRID: MYITLAB • MYITLAB HOMEWORK: 1. myitlab ASSIGNMENT: “EXPLORING series” Access Chapter 1 - GRADER PROJECT [HOMEWORK] 2. myitlab TRAINING: Access VOLUME 1 - “Skill-Based TRAINING”