introduction to database systems
Download
Skip this Video
Download Presentation
Introduction to Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 65

Introduction to Database Systems - PowerPoint PPT Presentation


  • 352 Views
  • Uploaded on

Introduction to Database Systems. CS363/607* Lecture #2. Last class. What is a database? What is a DBMS? Database history DBMS components Terminology Data model, schema, data, DDL, DML, DBA Relational database system. Entity-Relationship Model.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Introduction to Database Systems' - bernad


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
last class
Last class
  • What is a database?
  • What is a DBMS?
  • Database history
  • DBMS components
  • Terminology

Data model, schema, data, DDL, DML, DBA

Relational database system

entity relationship model
Entity-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 process to convert E/R diagrams to real implementations like relational databases exist.
entity sets
Entity Sets
  • Entity = “thing”, an abstract object of some sort.
    • Similar to an object in object-oriented languages.
  • Entity set = a collection of similar entities.
    • Similar to a class in object-oriented languages.
  • Examples:

students, movies, stars

attributes
Attributes
  • Attributes = properties of the entities in an entity set.
    • Generally, all entities in a set have the same properties.
    • Attributes are simple values, e.g. integers or character strings.
  • Examples:

studentNo, accountNo

relationships
Relationships
  • Relationships are connections among two or more entity sets.
  • Example:

Entity: Star, movie

Relation: Star-in

A movie entity m is related to a star entity s by the relationship star-in if s appears in movie m

e r diagrams
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,
  • Relationships are represented by diamonds
  • Edges are used to connect an entity to its attributes and also connect a relation ship to its entity sets.
example
Example
  • Entity set Beers has two attributes, name and manufacturer.
  • Each Beer entity has values for these two attributes, e.g. (Budweiser, Anheuser-Busch)

name

manufacturer

Beers

example1
name

addr

name

manf

Bars

Beers

Sells

Bars sell some

beers.

license

Drinkers like

some beers.

Frequents

Likes

Note:

license =

beer, full,

none

Drinkers frequent

some bars.

Drinkers

name

addr

Example
entity set and relationship set
Entity Set and Relationship Set
  • The current “value” of an entity set is the set of entities that belong to it.
    • Example: the set of all bars
  • The “value” of a relationship is a set of lists of currently related entities, one from each of the related entity sets.
example2
Example

Bar Beer

Joe’s Bar Budweiser

Joe’s Bar Miller

Sue’s Bar Budweiser

Sue’s Bar Pete’s Ale

Sue’s Bar Bud Lite

example3
Example

name

phone

ID

Students

height

Each student has four attributes:

ID, name, phone, height

example4
Example

Students Courses

Sally C131

Sally C363

Joe M130

… …

Taking

Students

Courses

example an instance
Example: An instance

The members of the relationship set are the

rows of the table. For Instance,

(Basic Instinct, Sharon Stone)

Is a tuple in the relationship set for the current

instance of relationship Star-in

binary relationship
Binary Relationship
  • A binary relationship can connect any member of one of its entity sets to any members of the other entity set. It is the simplest relationship which only connects two entity sets.
  • Example

Student Class

many many relationships
Many-Many Relationships
  • In a many-many relationship, an entity of either set can be connected to many entities of the other set.
  • Example:
    • Think of a relationship between two entity sets, such as Sells between Bars and Beers. A bar sells many beers; a beer is sold by many bars.
many one relationships
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.
example6
Example
  • 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.
one one relationships
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 Manufacturers 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).
example7
Example

Runs

Studios

Presidents

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.
  • In some situations, we can also assert “exactly one,” i.e., each entity of one set must be related to exactly one entity of the other set. To do so, we use a rounded arrow.
in picture
many-many many-one one-oneIn Picture
  • Representation of Many-One
  • E/R: arrow pointing to “one.”
    • Rounded arrow = “exactly one.”
example8
Example

Likes

Drinkers

Beers

Favorite

example9
Example
  • Consider Best-seller between Manufacturers and Beers.
  • Some beers are not the best-seller of any manufacturer, so a rounded arrow to Manufacturers would be inappropriate.
  • But a manufacturer has to have a best-seller (we assume they are beer manufacturers).
in the e r diagram
In the E/R Diagram

Best-

seller

Manufacturers

Beers

roles
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.
example10
Relationship Set

Husband Wife

Bob Ann

Joe Sue

… …

Married

husband

wife

Drinkers

Example
attributes on relationships
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.
slide31
price

Sells

Bars

Beers

price

Prices

Sells

Bars

Beers

multiway relationships
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.
example11
name

addr

name

manf

Bars

Beers

license

Preferences

Drinkers

name

addr

Example
a typical relationship set
Bar Drinker Beer

Joe’s Bar Ann Miller

Sue’s Bar Ann Budweiser

Sue’s Bar Ann Pete’s Ale

Joe’s Bar Bob Budweiser

Joe’s Bar Bob Miller

Joe’s Bar Cal Miller

Sue’s Bar Cal Bud Lite

A Typical Relationship Set
example12
Example

Contract

Stars

Movies

Studios

(Studio, Star, Movie)

converting multiway to 2 way
BBP

The-Bar

The-Beer

The-Price

Bars

Beers

Price

Converting Multiway to 2-Way
  • Many-one relationships from the connecting E.S. to the others.
subclasses
Subclasses
  • An entity set contains certain entities that have special properties not associated with all members of the set.
  • Subclass = special case = fewer entities = more properties.
  • Example: Ales are a kind of beer.
    • Not every beer is an ale, but some are.
    • Let us suppose that in addition to all the properties (attributes and relationships) of beers, ales also have the attribute color.
subclasses in e r diagrams
Subclasses in E/R Diagrams
  • Assume subclasses form a tree.
    • I.e., no multiple inheritance.
  • Isa triangles indicate the subclass relationship.
    • Point to the superclass.
example15
Example

Beers

name

manufacturer

isa

Ales

color

e r vs object oriented subclasses
E/R Vs. Object-Oriented Subclasses
  • In the object-oriented world, objects are in one class only.
    • Subclasses inherit properties from superclasses.
  • In contrast, E/R entities have components in all subclasses to which they belong.
    • Matters when we convert to relations.
example17
Pete’s AleExample

Beers

name

manf

isa

Ales

color

exercise 1
Exercise 1
  • Let us design a database for a bank, including information about customers and their accounts. Information about a customer includes their name, address, phone, and SSN. Accounts have numbers, types and balances. We also need to record the customers who own an account. Draw the E/R diagram for this database. Be sure to include arrows where appropriate, to indicating the multiplicity of a relationship.
summary
Summary
  • E/R model
  • Elements of E/R model
    • Entity sets
    • Attributes
    • Relationships
  • Entity-Relationship diagram
  • Binary and multiway relationships
  • Roles
  • Converting multiway to binary
  • subclass
design principle
Design principle
  • Faithfulness
  • Avoiding redundancy
  • Simplicity counts
  • Choosing the right relationships
  • Picking up the right kind of elements
faithfulness
Faithfulness
  • The design must be faithful to the specifications, which is, entity sets and their attributes should reflect the reality
  • Examples:
    • Stars-in between stars and movies
    • Teaches between courses and instructors

Stars-in

Stars

Movies

Teaching

Courses

instructors

avoiding redundancy
Avoiding redundancy
  • Not illegal, but dangerous because:
    • Take more space to store
    • Increase complexity, may result in inconsistency
  • Example:

studioname

owns

Studios

Movies

name

address

simplicity
Simplicity
  • Less elements is good
  • Example:

Represents

owns

Movies

Holdings

Studios

owns

Movies

Studios

choosing the right relationships
Choosing the right relationships
  • Entity sets can be connected with various relationships, adding more in the design is not good because:
    • Redundancy
    • More space usage
    • Modifying becomes more complex
example18
Example

Stars-in

Movies

Stars

Contracts

owns

Studios

another example
Another example

Stars-in

Movies

Stars

Works-for

owns

Studios

picking the right kind of elements
Picking the right kind of elements
  • In general, an attribute is simpler to implement that an entity set or a relationship, however, you can not make everything attributes.
example19
Example

year

title

name

owns

Movies

Studios

length

address

type

year

title

Studio-name

Movies

length

Studio-address

type

conditions
Conditions
  • To replace an entity set E by an attribute or attributes of several other entity sets
    • All relationships in which E is involved must have arrows entering E
    • The attributes for E must collectively identify an entity
    • No relationship involves E more than once.
how to replace
How to replace
  • If there’s a many-one relationship R from some entity set F to E, then remove R and make the attributes of E be attributes of F
  • If there is a multiway relationship R with an arrow to E, make the attributes of E be attributes of R and delete the arc from R to E
example20
price

Sells

Bars

Beers

price

Prices

Sells

Bars

Beers

Example
example21
Example

(star, movie, studio of star, producing studio) If a contract involves a set of studios,

(star, movie, set-of-studios) adequate?

slide65
Star-of

Movie-of

Stars

Contracts

Movies

Studios-of

Studios

ad