databases and dbmss n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Databases and DBMSs PowerPoint Presentation
Download Presentation
Databases and DBMSs

Loading in 2 Seconds...

play fullscreen
1 / 37

Databases and DBMSs - PowerPoint PPT Presentation


  • 106 Views
  • Uploaded on

Databases and DBMSs. Todd S. Bacastow January 2005. 1. Evolution. Ways of storing data. Files (1960) (ancient times) Databases Hierarchical (1970) Network (1970) Relational (1980) Object (1990). File terms. Record

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 'Databases and DBMSs' - lanza


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
databases and dbmss

Databases and DBMSs

Todd S. Bacastow

January 2005

1

ways of storing data
Ways of storing data
  • Files (1960) (ancient times)
  • Databases
    • Hierarchical (1970)
    • Network (1970)
    • Relational (1980)
    • Object (1990)
file terms
File terms
  • Record
    • data items related to a single logical entity (e.g. a student’s information) or row in a table
  • Field
    • a place for a data item in a record (first name field in a student record) or a column in a table
  • File
    • a sequence of records of the same type (the table)
file structures
File structures

A file: “STUDENT”

field

ID Last First Grade

3 Smith Jane A

1 Wood Bob C

2 Kent Chuck B

4 Boone Dan B

record

file organization
File organization
  • Serial
    • new records appended
  • Sequential
    • records ordered in file, usually according to a numeric key
file structures1
File structures
  • Serial
    • list of entries in which the order of entry into the list determines the order of the list

ID Last First Grade

3 Smith Jane A

1 Wood Bob C

2 Kent Chuck B

4 Boone Dan B

file structures2
File structures
  • Search of a simple list entails going through each record until the query is satisfied (linear search), which is inefficient

ID Last First Grade

3 Smith Jane A

1 Wood Bob C

2 Kent Chuck B

4 Boone Dan B

file structures3
File structures
  • Sequential
    • list of entries ordered in some way (e.g. numerically or alphabetically)

ID Last First Grade

1 Wood Bob C

2 Kent Chuck B

3 Smith Jane A

4 Boone Dan B

file structures4
File structures
  • Search of an ordered sequential list can use a search method

ID Last First Grade

1 Wood Bob C

2 Kent Chuck B

3 Smith Jane A

4 Boone Dan B

file structures5
File structures
  • Indexes provide a reference to records based on an index field, which is ordered

Last Pointer

ID Last First Grade

Boone *

Kent *

Smith *

Wood *

1 Wood Bob C

2 Kent Chuck B

3 Smith Jane A

4 Boone Dan B

problems with files
Problems with files
  • Redundancy
    • number of files grows with applications, and data is duplicated
  • Inconsistency
    • data is updated in one application’s files, but not in another’s
  • Maintenance problems
    • changes to data structures mean changes to many programs
  • Difficulty combining data
    • business needs may mean users want data from different applications
other ways to organize
Other ways to organize
  • Data model
    • A data model is a particular way of conceptually organizing multiple data files in a database
  • Other common models
    • Hierarchical
    • Network
    • Relational
    • Object
network data model
Relationships:

one-to-one

one-to-many

many-to-one

many-to-many

Network data model

Class

Student

Instructor

Department

Grade

ID

network data model1
Network data model
  • Advantages
    • flexible, fast, efficient
  • Disadvantages
    • Complex
    • Restructuring can be difficult because of changing all the pointers
hierarchical data model
Hierarchical data model
  • Parent-child relationship:
    • one-to-one
    • one-to-many

Class

Student

Instructor

Department

Grade

ID

hierarchical data model1
Hierarchical data model
  • Advantages
    • easy to search
    • add new branches easily
  • Disadvantages
    • Must establish the types of search prior to development of the hierarchical structure
summary
Summary
  • Hierarchical and network data models have generally been replaced by the relational data model
  • Relational databases dominate the database market
    • Oracle
    • Informix
    • SQL Server
    • DB2
    • ……..
relational database model
Relational database model
  • Stores both
    • Data about real world objects (entities) in tables
    • Relationships between the tables
relational database
Relational database
  • Fields (columns) in the table store attributes.
    • Each attribute has a specific domain.
  • Tuples (or records or rows) in the table store information.
    • Each tuple is a unique instance of an object.
  • Tables are composed of a set of tuples.
    • A table is also called a relation.
terms
Terms
  • Table
    • A collection of relevant data relating to one type of real world objects.
  • Column
    • A specific place for one type of data relating to one type of real world objects.
  • Domain
    • Set of all possible values for a specific column.
  • Row
    • Collection of data describing one real world object.
  • Primary Key
    • Columns, which are part of the row and uniquely identify any one row.
records
Records
  • Each record represents a logical entity (e.g. a student)
  • Each field represents an attribute of the logical entity

Student

ID Last First Grade Class

1 Wood Bob C IST357

2 Kent Chuck B IST115

3 Smith Jane A IST357

4 Boone Dan B IST357

slide25
Keys
  • Each table has a primary key, one field (or a combination of fields) that has a unique value for each and every record in the table

Student

ID Last First Grade Class

ID is the primary key in this table (two students may share either a last or first name)

1 Wood Bob C IST357

2 Kent Chuck B IST115

3 Smith Jane A IST357

4 Boone Dan B IST357

relating tables
Relating tables
  • Tables can be related (joined) together based on their keys
  • The idea is to decompose into separate tables with no redundancy and to provide a capability to reassemble with no information loss

Student

Class

ID Last First Grade Class

Name #Stud Instructor

1 Wood Bob C IST357

2 Kent Chuck B IST115

3 Smith Jane A IST357

4 Boone Dan B IST357

IST357 48 Jones

IST115 120 Brower

IST20 120 Fountain

relating tables1
Relating tables

Primary key

Foreign key

Primary key

Student

Class

ID Last First Grade Class

Name #Stud Instructor

1 Wood Bob C IST357

2 Kent Chuck B IST115

3 Smith Jane A IST357

4 Boone Dan B IST357

IST357 48 Jones

IST115 120 Brower

IST20 120 Mennis

relating tables2
Relating tables

Student

Class

ID Last First Grade Class

Name #Stud Instructor

1 Wood Bob C IST357

2 Kent Chuck B IST115

3 Smith Jane A IST357

4 Boone Dan B IST357

IST20 120 Brower

IST115 120 Jones

IST357 48 Jones

Instructor

Name Office

Jones 332

Brower 517

dbms schema

The view on data used by

External Schema

application programs.

The logical model of data that is

Conceptual Schema

separate from how it is used.

The physical storage of data in

Internal Schema

files and indexes.

DBMS Schema
  • Ultimately data in databases is stored in files, but their structure is hidden
rdbms features
RDBMS Features
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Integrity Constraints
  • Transaction Management
  • Concurrency
  • Security
  • Tuning of Storage
relationships
Relationships
  • Link between entities.
  • A relationship may define constraints.
    • E.G, a person can only have one SSN.
advantages of rdbms
Advantages of RDBMS
  • Eliminate unnecessary duplication of data
  • Enforce data integrity through constraints
  • Changes to conceptual schema need not affect external schema
  • Changes to internal schema need not affect the conceptual schema
  • Many tools are available to manage the database
disadvantages of rdbms
Disadvantages of RDBMS
  • To store objects (e.g., drawings) in a relational database, the objects have to be ‘flattened’ into tables
    • e.g., a digital representation of a parcel must be separated from the behaviour of other parcels
  • Complex objects have to be taken apart and the parts stored in different tables
  • When retrieved from the database, the object has to be reassembled from the parts in different tables
other types of dbms
Other Types of DBMS
  • Object DBMS
    • store objects as objects
    • designed to handle complex nested objects for graphical and multimedia applications
  • Object-relational DBMS
    • hybrid databases that can store data in tables but can also store objects in tables
object dbms
Object DBMS
  • ODBMS have the advantage that objects can be stored directly
  • Object databases are closely linked to programming languages with ways of navigating through the database