Transcript DATABASE DESIGN - Algonquin College

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
ArtistName
SongTitle
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 “ColourID”
• primary key in a new table called: colours, with “ColourID”
• 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
12
instructors
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 2nd table but..
• A record in the 2nd table can ONLY relate to 1 record in the 1st
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=0lSLI7q-hVE
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_Prof
Professor
Many to Many Relationships
Resolving Many to Many Relationships By
Creating A Junction Or Joiner Entity
• http://www.youtube.com/watch?v=NvrpuBAMddw
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)
(M:M)
and Many to Many’s
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
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, 1st 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-bvjtYgRVU
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”