cset 3300 databases & er data models. databases a database is a collection of data...
TRANSCRIPT
CSET 3300CSET 3300
Databases & ER Data ModelsDatabases & ER Data ModelsDatabases & ER Data ModelsDatabases & ER Data Models
DatabasesDatabases
• 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 Relational Database Management System
Attributes(columnheaders)
Tuples(rows)
Relation name
Name Manufacturer
Winterbrew Pete’s
Bud Lite Anheuser-Busch
Beers
Data organized as tables called relations
DataBase Management System DataBase Management System
High-levelHigh-levelQuery QQuery Q
DBMS
Answer
Data
TeraBytes PetaBytes<CD> <TITLE>Empire B.</TITLE> <ARTIST>Bob Dylan</ARTIST> <COUNTRY>USA</COUNTRY> <COMPANY>Columbia</COMPANY><PRICE>10.90</PRICE></CD>
Translates Q intobest execution plan
for current conditions,runs plan
Keeps data safe and correct
despite failures, concurrent
updates, online processing, etc.
Example: Car Sales DBExample: Car Sales DB
ID Name Age
12 Nemo 22
34 Ray 42
89 Gill 36
156 Dory 21
… … …
Make Model OwnerID
Honda Accord 12
Toyota Camry 34
Mini Cooper 89
Honda Accord 156
… … …
Cars Owners
Filter (Make = Honda andModel = Accord)
Join (Cars.OwnerID = Owners.ID)
Make Model OwnerID ID Name Age
Honda Accord 12 12 Nemo 22
Honda Accord 156 156 Dory 21
Owners ofHonda Accords
who are <=23 years old
Filter (Age <= 23)
DB ApplicationsDB 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 SystemsDatabase 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'sThree 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 DesignDatabase 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 ModelEntity-Relationship Model• The E/R model allows us to sketch the
design of a database informally.• Designs are pictures called entity-
relationship diagrams.• Fairly mechanical ways to convert E/R
diagrams to real implementations like relational databases exist.
Entity SetsEntity 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 DiagramsE/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.
Beers
name manufacturer
Entity set Beers has two attributes, name and manufacturer.
Each Beer entity has values for these two attributes, e.g. (Bud, Anheuser-Busch)
RelationshipsRelationships• A relationship connects two or more
entity sets.• It is represented by a diamond, with
lines to each of the entity sets involved.
ExampleExample
Drinkers addrname
Beers
name
Bars
name
license
addr
Note:license =beer, full,none
Sells Bars sell somebeers.
Likes
Drinkers likesome beers.
Frequents
Drinkers frequentsome bars.
manufacturer
Relationship SetRelationship 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 BeerJoe’s Bar BudJoe’s Bar MillerSue’s Bar BudSue’s Bar Pete’s AleSue’s Bar Bud Lite
Binary vs. Multiway RelationshipsBinary 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.
ExampleExample
Bars Beers
Drinkers
name nameaddr
name addr
license
Preferences
manufacturer
Bar Drinker BeerJoe’s Bar Ann MillerSue’s Bar Ann BudJoe’s Bar Bob BudJoe’s Bar Bob MillerJoe’s Bar Cal MillerSue’s Bar Cal Bud
One-One RelationshipsOne-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 RelationshipsMany-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 RelationshipsMany-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”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 BeersLikes
Favorite
Attributes on RelationshipsAttributes 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 BeersSells
price
Price is a function of both the bar and the beer,not of one alone.
Diagrams Without Attributes on RelationsDiagrams Without Attributes on Relations
• Create an entity set representing values of the attribute.
• Make that entity set participate in the relationship.
Bars BeersSells
price
PricesNote convention:arrow from multiwayrelationship = “all otherentity sets determine aunique one of these.”
RolesRoles• 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.
Drinkers
Married
husband wife
Relationship Set
Husband WifeBob AnnJoe Sue… …
DB Design ExampleDB Design Example• A university registrar’s office maintains data about the following
entities: a) courses, including number, title, credits, syllabus, and
prerequisitesprerequisites; b) course offerings, including course numbercourse number, year, semester, section
number, instructorinstructor(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.
EntitiesEntities
course
course-offerings
student instructor
CourseCourse• “courses including number, title, credits,
syllabus, and prerequisitesprerequisites”
Attribute?
Relationship?
prerequisite
maincourse
course_no
title
requires course
syllabus
credits
Course OfferingCourse Offering• “course offerings, including course numbercourse number,
year, semester, section number, instructorinstructor(s), timings, and classroom”
time
roomcourse
section_no
semester
year
course- offeringsis offered
course_no
Student, InstructorStudent, Instructor• “students, including student-id, name, and
program”• “instructors, including identification
number, name, department, and title”
student
namesid
program
instructor
titledept
nameiid
Enrollment RelationshipEnrollment Relationship• “Further, the enrollment of students in courses
and grades awarded to students in each course they are enrolled for must be appropriately modeled.”
studentcourse-
offeringsenrolls
grade
Teaching RelationshipTeaching Relationship
• Instructor teaches course…..
course-offerings
instructorteaches
titledept
nameiid
prerequisite
maincourse
course_no
requires course
syllabus
credits
time room
section_no
semester
year
course- offerings
is offered
enrolls
grade
namesid
program
student instructorteaches
title
Complete ER Diagram for UniversityComplete ER Diagram for UniversityComplete ER Diagram for UniversityComplete ER Diagram for University