Introduction to database systems
Download
1 / 65

Introduction to Database Systems CS363607 - PowerPoint PPT Presentation


  • 351 Views
  • Updated 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 CS363607' - 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
Introduction to database systems

Introduction to Database Systems

CS363/607*

Lecture #2


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-one

In 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.


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 Ale

Example

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?


Star-of

Movie-of

Stars

Contracts

Movies

Studios-of

Studios


ad