Transcript cset3300-module-1 - The University of Toledo

Databases & ER Data Models
CSET 3300
Databases
• A database is a collection of data (information).
• A DataBase Management System (DBMS) is a software system
that manages execution of users applications to access and
modify database data.
– Manages very large amounts of data
– Supports efficient access to very large amounts of
data
– Provides data security and data reliability
– Supports multi-user concurrent access
– Safety from system failures – Recovery
– High throughput (thousands ~ millions transactions
per minute)
Relational Database Management System
 Data organized as tables called relations
Attributes
(column
headers)
Tuples
(rows)
Name
Manufacturer
Winterbrew
Pete’s
Bud Lite
Anheuser-Busch
Beers
Relation name
DataBase Management System
High-level
Query Q
Keeps data safe
and correct
despite failures,
concurrent
DBMS
updates, online
processing, etc.
Answer
Translates Q into
best execution plan
for current conditions,
runs plan
TeraBytes  PetaBytes
Data
<CD>
<TITLE>Empire B.</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia
</COMPANY>
<PRICE>10.90</PRICE>
</CD>
Example: Car Sales DB
Make
Model
OwnerID
ID
Name
Owners of
Honda
Accord
12
12
Nemo
Honda Accords
Honda
Accord
156
156
Dory
who are <=
Join (Cars.OwnerID = Owners.ID)
23 years old
Filter (Make = Honda and
Model = Accord)
Cars
Age
22
21
Filter (Age <= 23)
Owners
Make
Model
OwnerID
ID
Name
Age
Honda
Accord
12
12
Nemo
22
Toyota
Camry
34
34
Ray
42
Mini
Cooper
89
89
Gill
36
Honda
Accord
156
156
Dory
21
…
…
…
…
…
…
DB Applications
• It used to be about boring stuff: employee records, bank
records, etc.
• Today, the field covers all the largest sources of data, with
many new ideas.
– Web search.
– Scientific and medical databases.
– Market transaction databases (sales data)
• You may not notice it, but databases are behind almost
everything you do on the Web.
– Google searches.
– Queries at Amazon, eBay, etc.
Database Systems
• The big commercial database vendors:
–
–
–
–
Oracle
IBM (with DB2) bought Informix recently
Microsoft (SQL Server)
Sybase
• Some free database systems (Unix) :
– Postgres
– MySQL
– Predator
• Here we use MySQL. You may use something else, but then
you are on your own.
Three Aspects to Studying DBMS's
• Three broad categories
– Modeling and design of databases.
• Allows exploration of issues before committing to an implementation.
– Database programming: queries and DB operations like
update.
• SQL
– DBMS implementation.
• How does one build a DBMS (query processing, organizing
storage, etc.)
Database Design
• The process of designing the general structure of the database:
– Logical Design – Deciding on the database schema.
• Business decision – What properties should we record in
the database?
• Computer Science decision – What relation schemas
should we have and how should the attributes be
distributed among the various relation schemas?
– Physical Design – Deciding on the physical layout of the database
Entity-Relationship Model
• The E/R model allows us to sketch the
design of a database informally.
• Designs are pictures called entityrelationship diagrams.
• Fairly mechanical ways to convert E/R
diagrams to real implementations like
relational databases exist.
Entity Sets
• Entity = “thing” or object.
• Entity set = collection of similar entities.
– Similar to a class in object-oriented languages.
• Attribute = property of an entity set.
– Generally, all entities in a set have the same
properties.
– Attributes are simple values, e.g. integers or
character strings.
E/R Diagrams
• In an entity-relationship diagram, each entity set is
represented by a rectangle.
• Each attribute of an entity set is represented by an
oval, with a line to the rectangle representing its
entity set.
name
manufacturer
 Entity set Beers has two attributes,
name and manufacturer.
 Each Beer entity has values for these
two attributes, e.g. (Bud, AnheuserBusch)
Beers
Relationships
• A relationship connects two or more
entity sets.
• It is represented by a diamond, with
lines to each of the entity sets
involved.
Example
name
addr
name
Bars
Beers
Sells
license
Note:
license =
beer, full,
none
Frequents
manufacturer
Likes
Bars sell some
beers.
Drinkers like
some beers.
Drinkers frequent
some bars.
name
Drinkers
addr
Relationship Set
• The current “value” of an entity set is the set of
entities that belong to it.
– Example: the set of all bars in our database.
• The “value” of a relationship is a set of lists of
currently related entities, one from each of the
related entity sets.
 For the relationship
Sells, we might
have a relationship
set like:
Bar
Joe’s Bar
Joe’s Bar
Sue’s Bar
Sue’s Bar
Sue’s Bar
Beer
Bud
Miller
Bud
Pete’s Ale
Bud Lite
Binary vs. Multiway Relationships
• Sometimes, we need a relationship that
connects more than two entity sets.
• Suppose that drinkers will only drink certain
beers at certain bars.
– Our three binary relationships Likes, Sells, and
Frequents do not allow us to make this
distinction.
– But a 3-way relationship would.
Example
name
license
addr
name
Bars
manufacturer
Beers
Preferences
Bar
Joe’s Bar
Sue’s Bar
Joe’s Bar
Joe’s Bar
Joe’s Bar
Sue’s Bar
Drinkers
name
addr
Drinker
Ann
Ann
Bob
Bob
Cal
Cal
Beer
Miller
Bud
Bud
Miller
Miller
Bud
One-One Relationships
• In a one-one relationship, each entity of
either entity set is related to at most one
entity of the other set.
 Example: Relationship Best-seller
between entity sets manufacturer
and Beers.
A
beer cannot be made by more than
one manufacturer, and no
manufacturer can have more than
one best-seller (assume no ties).
Many-One Relationships
• Some binary relationships are many -one from one entity set to
another.
• Each entity of the first set is connected to at most one entity of
the second set.
• But an entity of the second set can be connected to zero, one, or
many entities of the first set.
 E.g.
Favorite, from Drinkers to
Beers is many-one.
 A drinker has at most one
favorite beer.
 But a beer can be the favorite
of any number of drinkers,
including zero.
Many-Many Relationships
• Think of a relationship between two entity sets,
such as Sells between Bars and Beers.
• In a many-many relationship, an entity of either set
can be connected to many entities of the other set.
– E.g., a bar sells many beers;
a beer is sold by many bars.
Representing “Multiplicity”
• Show a many-one relationship by an arrow
entering the “one” side.
• Show a one-one relationship by arrows entering
both entity sets.
Drinkers
Likes
Favorite
Beers
Attributes on Relationships
• Sometimes it is useful to attach an attribute to a
relationship.
• Think of this attribute as a property of tuples in
the relationship set.
Bars
Sells
Beers
price
Price is a function of both the bar and the beer,
not of one alone.
Diagrams Without Attributes on
Relations
• Create an entity set representing values of the
attribute.
• Make that entity set participate in the relationship.
Bars
Sells
Prices
price
Beers
Note convention:
arrow from multiway
relationship = “all other
entity sets determine a
unique one of these.”
Roles
• Sometimes an entity set appears more
than once in a relationship.
• Label the edges between the relationship
and the entity set with names called roles.
Relationship Set
Married
husband
wife
Drinkers
Husband
Bob
Joe
…
Wife
Ann
Sue
…
DB Design Example
• A university registrar’s office maintains data about the following
entities:
a) courses, including number, title, credits, syllabus, and prerequisites;
b) course offerings, including course number, year, semester, section
number, instructor(s), timings, and classroom;
c) students, including student-id, name, and program;
d) instructors, including identification number, name, department,
and title.
– Further, the enrollment of students in courses and grades awarded
to students in each course they are enrolled for must be
appropriately modeled.
Entities
courseofferings
student
instructor
course
Course
• “courses including number, title, credits,
syllabus, and prerequisites”
Attribute?
Relationship?
course_no
credits
prerequisite
requires
course
maincourse
syllabus
title
Course Offering
• “course offerings, including course
number, year, semester, section number,
instructor(s), timings, and classroom”
semester
course_no
course
time
courseofferings
is offered
year
room
section_no
Student, Instructor
• “students, including student-id, name,
and program”
• “instructors, including identification
number, name, department, and title”
sid
name
iid
student
program
name
instructor
dept
title
Enrollment Relationship
• “Further, the enrollment of students in
courses and grades awarded to students in each
course they are enrolled for must be
appropriately modeled.”
student
enrolls
grade
courseofferings
Teaching Relationship
• Instructor teaches course…..
courseofferings
teaches
instructor
Complete ER Diagram for University
semester
sid
time
room
name
student
iid
courseofferings
enrolls
program
year
grade
teaches
section_no
is offered
course_n
o
credits
prerequisite
requires
course
maincourse
title
syllabus
name
instructor
dept
title