concepts of database management system

Outline

 Structure of Relational Databases
 Database Schema
 Keys
 Schema Diagrams
 Relational Query Languages
 The Relational Algebra
Chapter 2: Intro to Relational Model




Database System Concepts, 7th Ed.


LICET, CSE 2.2 ©Silberschatz, Korth, Sudarshan and GK




Example of a Instructor Relation Relation Schema and Instance

 A1, A2, …, An are attributes
attributes  R = (A1, A2, …, An ) is a relation schema
(or columns)
Example:
instructor = (ID, name, dept_name, salary)
tuples  A relation instance r defined over schema R is denoted by r (R).
(or rows)
 The current values a relation are specified by a table
 An element t of relation r is called a tuple and is represented by
a row in a table




LICET, CSE 2.3 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 2.4 ©Silberschatz, Korth, Sudarshan and GK

, Attributes Relations are Unordered

 The set of allowed values for each attribute is called the domain of the  Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
attribute  Example: instructor relation with unordered tuples
 Attribute values are (normally) required to be atomic; that is, indivisible
 The special value null is a member of every domain. Indicated that the
value is “unknown”
 The null value causes complications in the definition of many operations




LICET, CSE 2.5 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 2.6 ©Silberschatz, Korth, Sudarshan and GK




Database Schema Keys

 Database schema -- is the logical structure of the database.  Let K  R
 Database instance -- is a snapshot of the data in the database at a given  K is a superkey of R if values for K are sufficient to identify a unique tuple
instant in time. of each possible relation r(R)
 Example: • Example: {ID} and {ID,name} are both superkeys of instructor.
• schema: instructor (ID, name, dept_name, salary)  Superkey K is a candidate key if K is minimal
• Instance: Example: {ID} is a candidate key for Instructor
 One of the candidate keys is selected to be the primary key.
• Which one?
 Foreign key constraint: Value in one relation must appear in another
• Referencing relation
• Referenced relation
• Example: dept_name in instructor is a foreign key from instructor
referencing department




LICET, CSE 2.7 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 2.8 ©Silberschatz, Korth, Sudarshan and GK