overview of database systems n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Overview of Database Systems PowerPoint Presentation
Download Presentation
Overview of Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 32

Overview of Database Systems - PowerPoint PPT Presentation


  • 155 Views
  • Uploaded on

Overview of Database Systems. CPSC 315 – Programming Studio Spring 2009 Team Project 1, Lecture 1. Project. Your first project (next week) will involve putting together a very basic database system There will be a few lectures to give you an overview of database systems

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 'Overview of Database Systems' - ginata


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
overview of database systems

Overview of Database Systems

CPSC 315 – Programming Studio

Spring 2009

Team Project 1, Lecture 1

project
Project
  • Your first project (next week) will involve putting together a very basic database system
  • There will be a few lectures to give you an overview of database systems
  • This is nowhere close to what you would get in a full database course
  • Slides adapted from Jennifer Welch (some of hers were from Jeffrey Ullman)
database systems
Database Systems
  • Systems designed to manage very large amounts of data, and to query that data to pull out useful information
  • Often, key considerations include:
    • Efficiency
    • Reliability
    • Ease of access (querying, distributed)
creating a database
Creating a Database
  • A database schema determines what will be represented in the database
  • This should be tightly controlled by a database manager
  • Specified through a data definition language
querying databases
Querying Databases
  • Once database has been populated, users can query the data
  • A data manipulation language controls how the user can specify queries, (and thus what types of queries are allowed)
    • SQL is probably the most well-known
other database topics
Other Database Topics
  • “Real” database courses include lots of other things that we’ll be ignoring here
    • More complete theory behind design
    • Query optimization
    • Efficient storage
    • Processing Transactions – grouped queries that provide atomic operations
      • Scheduling, logging, recovery
entity relationship model
Entity-Relationship Model
  • Way of expressing (in diagrammatic form) a database design
    • Kinds of data and how they connect
  • Easy first way to think about databases
  • Later, relational model described
entities and attributes
Entities and Attributes
  • Entities are things
  • Entity sets are collections of those things
  • Attributes are properties of entity sets
entity sets and attributes
Entity Sets and Attributes

Name

Party

Senator

Bill

State

Text

Name

Years

relationships
Relationships
  • Connect two or more entity sets

Name

Contributed

Party

Organization

Senator

Lobbyist

State

Sponsored

Name

Years

Wrote

Bill

Name

Text

values of relationships
Values of Relationships
  • The “value” of an entity set is the entities it contains
  • The “value” of a relationship is a list of currently related entities (one from each entity set)
multi way relationships
Multi-Way Relationships
  • E.g. Lobbyist lobbied Senator about Bill

Name

Party

Organization

Senator

Lobbied

Lobbyist

State

Name

Years

Bill

Name

Text

relationship types
Relationship Types
  • Consider binary relationships (two entity groups in a relationship)
  • One-to-one
    • Each entity can have at most one in the other category
    • e.g. entity groups: Baseball player, Team
    • relationship: Team MVP
    • A team can only have one MVP, and a player can only be MVP for one team.
relationship types1
Relationship Types
  • Consider binary relationships (two entity groups in a relationship)
  • One-to-one
  • Many-to-one
    • Each entity of first set can go to at most one of the second set
    • e.g. entity groups: Person, Town
    • relationship: BornIn
    • A person can is born in only one town, but a town can have many people born there
relationship types2
Relationship Types
  • Consider binary relationships (two entity groups in a relationship)
  • One-to-one
  • Many-to-one
  • Many-to-many
    • Any number from one set to the other
    • e.g. Senators can sponsor many bills, and each bill can be sponsored by many Senators
diagrams of relationships
Diagrams of Relationships
  • Arrow shows “to one”

Lived In

Born In

Person

Town

MVP

Baseball

Player

Team

attributes on relationships
Attributes on Relationships
  • Can be converted to multi-way diagrams

Born In

Person

Town

Hospital

attributes on relationships1
Attributes on Relationships
  • Can be converted to multi-way diagrams

Born In

Person

Town

Hospitals

Hospital

attributes on relationships2
Attributes on Relationships
  • Note arrows

Injured

Person

Date

Hospitals

Hospital

roles
Roles
  • If multiple references to same entity set, label edges by roles

Team Lead

Team

Programmer

Students

Tester

subclass
Subclass
  • Fewer entities, more properties

Name

Elected Official

Party

isa

State

U.S.

Representative

District

subclass1
Subclass
  • Entity in multiple subclasses

Elected Official

Name

isa

isa

isa

U.S.

Representative

Republican

Democrat

District

State

slide23
Keys
  • A key is a set of attributes for an entity set such that no two entities agree on all the attributes.
  • We must have a key for every entity set

Elected Official

Name

For an isa

hierarchy,

only root can

have a key.

Party

isa

U.S.

Representative

District

key for multiple attributes
Key for multiple attributes
  • Must choose one set of attributes

First

Name

Last

Name

Number

Baseball Player

Position

Birthdate

Nationality

Salary

Team

key for multiple attributes1
Key for multiple attributes
  • Must choose one set of attributes

First

Name

Last

Name

Number

Baseball Player

Position

Birthdate

Nationality

Salary

Team

key for multiple attributes2
Key for multiple attributes
  • Must choose one set of attributes

First

Name

Last

Name

Number

Baseball Player

Position

Birthdate

Nationality

Salary

Team

weak entity sets
Weak entity sets
  • Need “help” to determine key

First

Name

Last

Name

Number

Note arrrow:

indicates many

to one.

Baseball Player

Position

Birthdate

Plays

On

Team

Nationality

Salary

Name

City

design techniques
Design Techniques
  • Avoid redundancy
    • Say the same thing two ways

First

Name

Last

Name

Number

Baseball Player

Position

Birthdate

Plays

On

Team

Team

Name

Salary

Name

City

design techniques1
Design Techniques
  • Avoid redundancy
    • Say the same thing two ways

First

Name

Last

Name

Number

Baseball Player

Position

Birthdate

Team

Name

Team

Town

design techniques2
Design Techniques
  • Don’t use entity set if attribute will do
  • Entity lists should either
    • Have some non-key attribute
    • Be the “many” in a many-one/many-many relationship

Plays

On

Name

Baseball Player

Team

Name

City

design techniques3
Design Techniques
  • Don’t use entity set if attribute will do
  • Entity lists should either
    • Have some non-key attribute
    • Be the “many” in a many-one/many-many relationship

Name

Baseball Player

Team

design techniques4
Design Techniques
  • Don’t overuse weak entity sets
  • Usually use unique key for each entity set (e.g. UIN, SSN, VIN)
  • Not always possible, though