{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

SQL_1 - The Relational Model Chapter 3 Database Management...

Info iconThis preview shows pages 1–10. Sign up to view the full content.

View Full Document Right Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1 The Relational Model Chapter 3
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2 Why Study the Relational Model? Most widely used model. Vendors: IBM, Informix, Microsoft, Oracle, Sybase, etc. “Legacy systems” in older models E.G., IBM’s IMS Recent competitor: object-oriented model ObjectStore, Versant, Ontos A synthesis emerging: object-relational model • Informix Universal Server, UniSQL, O2, Oracle, DB2
Background image of page 2
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3 Relational Database: Definitions Relational database : a set of relations Relation: made up of 2 parts: Instance : a table , with rows and columns. #Rows = cardinality , #fields = degree / arity. Schema : specifies name of relation, plus name and type of each column. E.G. Students( sid : string, name : string, login : string, age : integer, gpa : real). Can think of a relation as a set of rows or tuples (i.e., all rows are distinct).
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4 Example Instance of Students Relation Cardinality = 3, degree = 5, all rows distinct Do all columns in a relation instance have to be distinct?
Background image of page 4
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5 Relational Query Languages A major strength of the relational model: supports simple, powerful querying of data. Queries can be written intuitively, and the DBMS is responsible for efficient evaluation. The key: precise semantics for relational queries. Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6 The SQL Query Language Developed by IBM (system R) in the 1970s Need for a standard since it is used by many vendors Standards: SQL-86 SQL-89 (minor revision) SQL-92 (major revision) SQL-99 (major extensions, current standard)
Background image of page 6
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7 The SQL Query Language To find all 18 year old students, we can write: SELECT * FROM Students S WHERE S.age=18 •To find just names and logins, replace the first line: SELECT S.name, S.login
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8 Querying Multiple Relations What does the following query compute? SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid=E.sid AND E.grade=“A” Given the following instance of Enrolled (is this possible if the DBMS ensures referential integrity?): we get:
Background image of page 8
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9 Creating Relations in SQL Creates the Students relation. Observe that the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified.
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 10
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}