Attributes - Database Management Systems - Lecture Slides, Slides of Introduction to Database Management Systems

Download Attributes - Database Management Systems - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems Design

Some clarifications: Relational Model

• Relation model
  • Conceptual model for data
  • Data is modeled in tabular fashion
    • Rows is a record containing related information that describes an entity.
    • Columns are attributes of the entity (also called fields)
    • Row represents a group of related attributes that represent or give some information about the entity.
  • Relation schema – describes the characteristics of the relation
    • Relation name
    • List of attributes
      • Attribute name
      • Attribute domain
    • Domain
      • Name
      • Associated values

Example: University Students

• Relation Schema:

Students(sid:string, name:string,login:string,

age:integer,gpa:integer);

• Relation name: Students
• Attributes: sid, name, login, age, gpa
• Domains: string, integer

Relation instance

• Relation instance – set of rows (also called tuples) that follow a relation

schema R.

• called relation for short.
• Technically, a relation should not have duplicates, but most commercial

database management systems allow them.

• Example: Students(sid:string, name:string,login:string,

age:integer,gpa:integer);

sid name login age gpa

1234 Manuel manu@ece 18 3.

6767 Jose joe@ece 25 3.

8901 Luz luz@ece 21 4.

Relational schema notation

• Given a set of Domains D1, D2, …, Dn, a relation R over these domains is a subset of D1xD2x…Dn.
• R is specified as:
  • R = {a1:D1,a2:D2,…,an:Dn| where a1∈ D1, a2 ∈ D2, …, an ∈ Dn.
  • Sometimes, a relation R is described simply by schema as
    • R(a1:D2, a2:D2,…,an:Dn)
      • Lists the attribute names and their corresponding domain
  • If each domain is well-known or understood from the context of a document, then the relation is simply described by the attribute names - R(a1, a2, …, an) - Merely indicates the attribute names in the

Keys

• Super keys – a set of attributes that uniquely identifies a tuple r in a relation R. - Just call it a key. - Two tuples cannot have the same key - In commercial DBMS this must be enforced by a constrain - Remember that commercial systems allow duplicate tuples - A constrain is a restriction on the values of one or more attributes.
• Candidate keys – super key of minimal size
  • If an attribute is removed from a candidate key, it is no longer a key
• Primary keys – candidate key selected by a database designer to be the key of a relation R.

Some issues on keys

• Every relation has candidate keys – in theory
  • Relational DBMS might allow relation with no keys
    • Use the whole tuple as “key” but duplicates might be there
• A relation R might have multiple candidate keys
  • Example: Students can have either {sid} or {login,age}
• Primary key is chosen by DB designer. Usually is the candidate key that makes the most sense, meaning that is - Simple – as few attributes as possible. - Student ID – sid -> just one attribute - Intuitive – attributes naturally identify a tuple. - Student ID – sid -> natural way to identify a student

Relational Algebra

• Query language – specialized language to ask

questions to the database.

• These are normally called “queries”
• Relational algebra expresses queries as

operations to be executed on the data (input

relation).

• Algebra in the sense that they specify operation to be applied to an input relation
• The do not specify how to implement these expressions, so they are not a procedural

University Database Schema

• Students (sid:string, sname:string,

saddr:string, sage:integer, sgpa: float);

• Courses (cname:string,ccreds:integer,clevel:stri

ng, cdesc:string);

• Offering (cname:string,csection:integer,cterm:

string, croom:string, ccapcity:integer);

• Professor (pid:string, pname:string,

pdept:string, psalary:float, prank:string);

• Teaching (pid:string, cname:string,

csection:integer, cterm:string);

Docsity.com

Relational Instance: Students sid sname saddr sage sgpa 9291 Mary NY 30 4. 1234 Manuel LA 18 3. 6767 Jose Ponce 25 3. 3229 Bobby SJU 19 3. 9987 Rafael SJU 29 2. 8901 Luz NY 21 4. Information about enrolled students

Notation

• Let R(A,B,C,D) be a relation schema.
• r(R) denote a relational instance on the

schema R

• r[A] denotes the value of attribute A on tuple r
• It two tuples t ∈ r1(R) and s ∈ r2(R) are the

same (t=s) then it must be true that:

• t[A] = s[A]
• t[B] = sB]
• t[C] = s[C]

Selection Operation

• Extracts a set of tuples from a relation R based

on a boolean condition c.

• Results is a set new relation with the same schema as input relation R.
• Selection condition c is a predicate that restricts the tuples that are in the result. - Examples: isHonor(t), inProbation(t), gpa > 3.4, age = 10 - Tuples must “pass” the condition to be in the selection result.
• Formalism: Given a relation R, the selection operation: S ( R ) c = σ Docsity.com

Selection Example 2

• Get the course information for English I
• Relational Algebra Expression:
• Result: ( )

Offerings

cname = EnglishI

σ cid csection cname croom ccapacity 8010 1 English I 333 30 8010 2 English I 333 30 8010 3 English I 333 30 ( ) " " R Offering cname = EnglishI

Projection Operation

• Idea:

– For each tuple t ∈R, extract one or more tuples to

form a new tuple s.

• NOTE: we shall use r(R) and R interchangeably

– This tuple s is then added (if not already present)

to a new relation S

– S is called the projection of R under attributes A1,

A2, ,,,, An

• Example:

– R(A,B,C)

– The projection of R under A is written as:

,

S R

A B