relational dbms n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational DBMS PowerPoint Presentation
Download Presentation
Relational DBMS

Loading in 2 Seconds...

play fullscreen
1 / 33

Relational DBMS - PowerPoint PPT Presentation


  • 127 Views
  • Uploaded on

Relational DBMS. Motivating Questions From last time: Is MS Excel a DBMS? What makes some databases relational, while others are not? Why do so many people use them?. RDBMS: Basic Principles. Based on a variant of n-ary relations Relations are the main data structure

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 'Relational DBMS' - rollo


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
relational dbms
Relational DBMS
  • Motivating Questions
    • From last time: Is MS Excel a DBMS?
    • What makes some databases relational, while others are not?
    • Why do so many people use them?
rdbms basic principles
RDBMS: Basic Principles
  • Based on a variant of n-ary relations
  • Relations are the main data structure
  • A relational database consists of 1 or more relations
  • Connections and relationships represented by values of data, not by physical references
    • Reference by value
    • Reference by location
  • Natural representation as tables
slide3

Relations: Definitions

  • A relation R is a set of n-tuples <d1,d2 …,dn>
  • di is a domain
    • may be infinite and may be non-unique
    • all domains contain the NULL value. Null value represents either attributes whose value is not known, or do not exist
  • n is the degree of the relation
  • The number of tuples in a relation is the cardinality of the relation
slide4

Relations: Definitions and Properties

  • Because R is a set of n-tuples:
    • the ordering of the n-tuples in R is immaterial
    • the n-tuples are distinct
  • An n-tuple is an ordered list of values:
    • The ith value is in the ith domain
    • The ordering of domains is significant
relations properties
Relations: Properties
  • Values of each column are homogeneous(belong to the same domain)
  • Rows are different with respect to one another R is a set of tuples (not a bag)
  • The order of columns is irrelevant, they are identified by name, not by position
  • The order of rows is irrelevant, they are identified by content, not by position
relations definitions and properties
Relations: Definitions and Properties
  • Some loose definitions:
    • “attribute” corresponds to a named table column;
    • “tuple” corresponds to a table row;
    • “values” correspond to table cells
    • each attribute has a domain set from which its values come from
slide7

Relation: Example

Attributes

BUILDING

Build.num

Street name

Nb floors

Value

412

415

287

288

Laurier

Laurier

Ste-Foy

Ste-Foy

2

3

3

2

142000

167000

256000

195000

Tuple

Values

schemas and instances
Schemas and Instances
  • Relation schema: A relation name and a set of distinct attribute names: R(A1 A2 ….An) Database schema: A set of relation schema with distinct relation names: R
  • Relation instance defined over schema R (X): finite set r of tuples over XDatabase instance defined over schema R:R = (R1 (X1), R2 (X2), ….Rn(Xn) ) = a set of relations (r1, r2, …., rn)
slide9

Constraints

  • What are they ?
    • represent the semantics of the miniworld being modeled.
    • restrict the set of possible database states.
  • Why do we want to specify the constraints?
    • Useful information to application programmers. They can write programs to prevent constraints violation:
      • example: acct balance should not fall below 0 £
    • DBMS mightenforcespecified constraints directly making task of application writer easier !
slide10

Constraints in RDBMS

  • Domain constraints
  • Primary keys
  • Foreign keys
  • Entity integrity
  • Referential integrity
  • Semantic integrity
slide11

Constraints

  • Principles:
    • Defined for a database schema
    • Must hold for every database instance
  • Domain Integrity:
    • All values for an attribute must be a value from the attribute’s domain
    • Must be a SINGLETON (atomic) value(First-normal form)
primary keys
Primary Keys
  • A key is a set of columns that together makes each row distinct
  • No two rows will have the same value of this key (implies that no two rows are allowed to be identical)
  • A primary key is usually included as a single column (e.g., ID)
  • Every relation has a key
slide13

Primary keys: Example

  • ModuleInfo(C#, semester, room, time, instructor)
  • Each module: one semester and one instructor
    • key = {C#, semester}
  • Each course: one semester and > 1 instructor
    • key = {C#, semester, instructor}
slide14

Primary keys

  • Aplication :
    • DBMS can check if the the tuple being inserted matches an existing tuple on the attributes in the key and disallow such insertions
    • Similarly, a primary key should not contain a null value since then it may not be possible to identify some tuples
slide15

Foreign Keys

  • Foreign keys refer to primary keys in other tables (e.g., Phys)
  • Foreign keys allow to represent relationships in RDBMS
  • A referential integrity constraint from the attribute Phys of Patient to Physicians means that Phys is a foreign key that refers to the primary key of Physicians
foreign keys in relations example

Physicians

ID

Name

Phone

Foreign Keys in Relations: Example

Patient

Pt

Enc_Date

Num

Phys

slide17

Referential integrity

  • If we keep a list of existing Physicians - Physicians.ID = {1, 2, 3}, and have a referential integrity constraint saying that every value of Patient.Phys must also be a value of Physicians.Id then we can reject the following request:
  • insert into Patient values (“15”, “Smith”, “1-01-98”, “15”);
slide18

Entity & Referential Integrity

  • Entity Integrity:
    • Must be able to distinguish two entities by their primary key
    • No primary key value may be NULL
    • No restriction on non-primary key values
  • Referential Integrity:
    • A tuple containing a non-NULL foreign key must refer to an existing tuple in the foreign relation
semantic integrity

ID

Name

Age

10932664

10932561

10932561

Dick

Jane

Spot

140

41

36

Semantic Integrity
  • Not all finite set of tuples are acceptable :
    • even if tuples have the right degree and attribute values:
data definition language ddl
Data Definition Language (DDL)
  • Used to encode database structure, including:
    • Table Definition (Name, Field Names, Domains,Keys)
    • Indexes/Physical Keys
    • Constraints needed to ensure referential integrity, validity, etc.
data manipulation language dml
Data Manipulation Language (DML)
  • Used to add/delete/modify records in table instances
  • Categories:
    • Relational algebra and Relational Calculus
    • Transform-oriented language (e.g., SQL)
    • Query-By-Example (QBE)
  • User Views/Interfaces:
    • Forms
    • Application programs
  • Query/update language commands
levels of abstraction optimization
Levels of Abstraction & Optimization
  • Relational languages and DBMS are highly “non-procedural”
  • Query describes table needed
  • DBMS figures out how to produce it
  • … but some queries are better than others
  • ambiguities
  • speed
  • computer system resources used
relational dbms people involved
Relational DBMS: People involved
  • DBMS designers and implementers
  • Tool designers
  • Database administrator (DBA) I.e. ‘super-user’ for a database, similar to a system administrator. DBA can define schemas, views, authorization, indexes, tuning parameters, etc.
  • Application programmers
  • Database designers (interact with users to define database at all levels)
  • Operators and maintenance
  • End users
relational dbms technology trends
Relational DBMS: Technology Trends
  • Popularity of network of workstations (client-server database technology)
  • Expanding Internet (information filtering over the net, digital library, security and protection over the net, new transaction management issues)
  • Reducing cost of secondary storage.
    • large databases (many terrabytes, may be petabytes) of digital images, video and other complex data -- satellite images, medical images, etc.
    • data mining -- searching for useful knowledge in large volumes of data.
relational dbms application pull
Relational DBMS: Application Pull
  • New applications -- Computer Aided Design and Manufacturing, Image and graphic databases, geometric databases.
  • Relational model not very good for modeling data in these domains
  • Led to emergence of Object oriented database management systems. that are designed to meet the requirements of these complex domains.
  • Key features:
    • flexibility to meet the requirements of these domains without being limited by data type and query language.
    • power to designer to specify both structure of complex objects and their behavior (operations)
relational concepts
Relational concepts
  • Review:
    • What’s a RDBMS ?
    • What’s a relation ?
    • How are they represented in a DBMS?
    • Where do these relations come from?
discussion is ms excel a rdbms
Discussion: Is MS Excel a RDBMS?
  • Is a spreadsheet a database?
  • Does MS Excel do all the functions of a database? How?
  • Data definition
  • Data manipulation
  • Data security & integrity
  • Data recovery and concurrency
  • Data dictionary
  • Query optimization
rdbms product examples
RDBMS Product Examples
  • DBase:
    • A standard for PC DBMS products, includes
      • dBase language (std)
      • browser
      • application generator
      • SQL
      • and other tools
  • Paradox:
      • Paradox language
      • usual tools
      • Front end for SQL db areas
rdbms product examples1
RDBMS Product Examples
  • Gupta SQLBase with SQL Windows :
    • new kids on block (startup from other companies)
    • good early reviews
    • 1st client-server on PCs
    • clean gateway to IBM’s DB2
    • supports blobs, cursors
    • good development environment
rdbms product examples2
RDBMS Product Examples
  • Microsoft SQL Server :
    • fastest PC DBMS
    • usual suite of tools
    • extension of dBase language
  • Access :
    • newest offering
    • Visual Basic front end to SQL and usual tools
    • Can read files from other PC DBMS vendors
    • hot seller
rdbms product examples3
RDBMS Product Examples
  • Ingres :
    • Descendant of Berkeley project (Postgres)
    • still the most technically polished products, innovative (e.g. in distributed DBMSs, rules)
    • Good tools for DBA, application creation
  • Oracle and Sybase :
    • Fairly technically advanced (early with client/server, triggers)
    • Major commercial SQL product
review questions
Review Questions
  • What is meant by relational database structure?
  • What are the minimal constraints ? Why do we need them?
  • What’s a relation again?
  • How are they represented in a DBMS?
slide33

Exercises

CUSTOMER

SALES_ORDER

Cust-no

Order-no

Name

Address

Date

Cust-no

Smith

Smith

1/7/97

C45

C45

11

Baker

Paul

15/7/97

C46

C46

12

Foreign key

Primary key

(1) For each of the following tuples, states which, if any, of the integrity rules are violated when they are added to the SALES_ORDER relation:

<14, 1/6/97, C45> <15, 1/7/97, null>

<16, 5/7/97, C52> <17, 10/7/98, null>