Database lecture 02 slides, Slides of Database Management Systems (DBMS)

Download Database lecture 02 slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Database Systems

Khwaja Fareed University of Engineering &

IT

Lecture 2 Lecture 2

The Relational Data Model The Relational Data Model

Communications of the ACM 13[6] June Ted Codd proposed the relational data model in 1970. He received the ACM Turing Award in 1981.

A data model defines the constructs available for defining a schema  (^) defines possible schemas  A schema defines the constructs available for storing the data  (^) defines database structure  (^) limits the possible database states  A database state (or instance ) is all the data at some point in time the database content

data model  (^) fixed by the DBMS  schema  (^) defined by the DB designer  (^) generally fixed once defined *  database state  (^) changes over time due to user updates

• schema modifications are possible once the database is populated, but this generally causes difficulties

External View External View Internal Schema Conceptual Schema relation specifications mapping from relations to storage layout (files) External View

users of the data application program(s) application program(s) application program(s) application program(s) relations relation schemas data definition processor query processor security manager concurrency manager index manager database designer enters the definition of relation schemas SQL DDL = relation definition language (CREATE TABLE)

A relation is defined by a name and a set of attributes  Each attribute has a name and a domain  (^) a domain is a set of possible values  (^) types are domain names  (^) all domains are sets of atomic values – RDM does not allow complex data types  (^) domains may contain a special null value

StockItem

Attribute Domain

ItemID string(4)

Description string(50)

Price currency/dollars

Taxable boolean

relation name set of attributes attribute domains attribute names

STOCKITEM(ItemId, Description, Price, Taxable) dom(ItemId) = string(4) dom(Description) = string(50) dom(Price) = currency/dollars dom(Taxable) = boolean degree of STOCKITEM = 4

A relation is denoted by  (^) R is the name of the relation schema for the relation  A relation is a set of tuples r(R) r(R) = (t 1 , t 2 , … , t m )

r(STOCKITEM) = { < I119, "Monopoly", $29.95, true >, < I007, "Risk", $25.45, true >, < I801, "Bazooka Gum", $0.25, false > } t 2 = < I007, "Risk", $25.45, true > t 2 [Price] = t 2 .Price = $25. t 2 [Price] є dom(Price) = currency/dollars

A relation is a set  (^) tuples are unordered  (^) no duplicate tuples  Attribute values within tuples are ordered  (^) values are matched to attributes by position  (^) alternate definition defines a tuple as a set of (name,value) pairs, which makes ordering of tuple unnecessary (we won’t use this definition)

The theoretical data model is mathematical:  (^) a relation is a set of tuples  (^) this is Codd's definition  In the real-world, the model is practical:  (^) efficiency concerns  (^) excepted standard: SQL  (^) a relation is a table, not a set  (^) a relation may have order and duplicates

A relation is viewed as a table  The attributes define the columns of the table  Each row in the table holds related values for each attribute  (^) a row often represents a conceptual entity (object)  Values in each column must come from the domain of the attribute  (^) the values are instances of the attribute type