database information architecture
DESCRIPTION
This is an introduction to relationships and normalization and how to model relationships to design databases.TRANSCRIPT
Requirements GatheringRelationship approach – gather information
requirements, organize into entities, form relations, and add desired fields
Data requirements approach – gather information requirements about individual fields, organize into entities, create relationships between the entities
What is a relation?Rank Country Population1 China 1,136,429,6382 India 849,638,0003 United States 258,115,7254 Indonesia 179,247,7835 Brazil 150,367,000
What is a relationship?Rank Country Population1 China 1,136,429,6382 India 849,638,0003 United States 258,115,7254 Indonesia 179,247,7835 Brazil 150,367,000
Country Province/StateChina AnhuiIndia AssamUnited States WashingtonCanada British ColumbiaBrazil Bahia
Normalization1st form – All cells are atomic2nd form – all rows in a relation have a unique
identifier and are dependent on a primary or candidate key
3rd form – no rows are transitively dependent – all attributes must rely on the primary key
Conceptual Modeling
Conceptual Modeling
Logical Modeling
Logical Modeling
Physical ModelingHow much data in each table?How many operations on each table?What type of operations will be performed?
PrototypingCreate the database tablesCreate the operations that will be performedPut in some sample dataTry it outAre the results what you expected?
RefinementGive users scenariosAsk them about the results that are returnedSee if there is additional information you
needDo performance testing
SSPiNCreating a data model for structuring
unstructured data about generic objects. + support for versioning, user evaluation of
content, and tagging
The Big IdeaA wiki that supports
structured properties about generic items
Post articles, reviews, comments about wikis
Rate user contentAssociate structured
properties (text) or ratings (numbers) that can be shared between different types of items
Project GoalsWiki – support user collaboration and
versioningGeneric content – support data about
different objectsUser ratings – allow users to rate content and
itemsDynamic submission – application changes as
the properties of items change
PersonaPam the NinjaHas trouble finding ninja movies and games
to enjoyDislikes regular review sites because they
may be biased or influenced by advertisersHates pirates
User Scenarios / TasksCreate a userLog inUpdate a wiki entryPost comments, reviews, and articlesRate a wiki entry by posting a reviewVote on quality of articles and reviewsView a users profileCreate a wiki entry
Conceptual Model
Logical Model
Physical ModelTable Rows Field Freq/day Type
article 1,000,000 predicate: pageId 50,000Read
join: articleBridge ON articleId predicate: pageId 50,000Read
predicate: articleId 30,000Read
predicate: userId 5,000Read
300 Insert
articleBridge 1,200,000 join: article ON articleId predicate: pageId 50,000Read
300 Insert
articleType 10 500Read
articleVotes 3,000,000 2,000 Insert
predicate: userId 50,000Read
avgRating predicate: pageId 10,000Read
join: avgRating on wikIEntry predicate: pageId 10,000Read
comments 5,000,000 predicate: pageId 50,000Read
predicate: userId 5,000Read
2,000 Insert
predicate: commentId 50,000Read
hits 50,000,000 predicate: id, tableName, ip (index necessary) 150,000Read
100,000 Insert
Physical Model
admin
about
article
copyright
comment
browse
createreview
index
postarticle
postcomment processcomment
processarticle
processreview
createuser
createwiki
processuser
processwiki
processwikiediteditwiki
review
search support terms
updateuseruser
wikieditwikientry
blog
login
Usability TestingTwo usersTested on ability to complete
user tasks – register, login, create wiki entry, evaluate content etc.
Results:Both users successfully able to
complete all tasks
Shneiderman’s InfoVis TasksOverview – index pageFilter – by item type of the wiki (browse)Details on Demand – linking to wiki entryRelate – properties / tags
ConclusionGoals:Wiki – wiki entries, wiki editsGeneric content – item type categorizations,
text fields, propertiesUser ratings – reviews, ratingsDynamic submissions – forms for reviews,
articles, and wiki entries dynamically created based on current state of database