L03 - Relational Data Model Why Study the Relational Model...

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

View Full Document Right Arrow Icon
Relational Data Model
Background image of page 1

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

View Full DocumentRight Arrow Icon
CS174A: Relational Data Model 2 Why Study the Relational Model? ± Most widely used model, many vendors: Oracle , Microsoft , IBM ( Informix ), MySQL , Sybase , etc. ± “Legacy systems” in older models – e.g., IBM’s IMS ± Recent competitors – Object-oriented model: ObjectStore , Versant , Ontos , O2 – Object-relational models: Informix Universal Server , UniSQL , O2 – ODMG standards – XML and XQuery
Background image of page 2
CS174A: Relational Data Model 3 Relational Database: Definitions ± Relation : made up of 2 parts: Instance : a table, with rows and columns #rows = cardinality , #columns = arity / degree 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) ± Relational database : a set of relations
Background image of page 3

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

View Full DocumentRight Arrow Icon
CS174A: Relational Data Model 4 Cardinality = 3 , arity = 5 , all rows distinct Do all columns in a relation instance have to be distinct? Example Instance of Students Relation 3.4 18 [email protected] Jones 53666 3.8 19 [email protected] Smith 53650 3.2 18 [email protected] Smith 53688 gpa age login name sid schema instance Cardinality arity
Background image of page 4
CS174A: Relational Data Model 5 Creating Relations in SQL CREATE TABLE Students ( sid CHAR(20) , name CHAR(20) , login CHAR(10) , age INTEGER , gpa REAL ) ± 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 ± As another example, the Enrolled table holds information about courses that students take CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20), grade CHAR(2))
Background image of page 5

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

View Full DocumentRight Arrow Icon
CS174A: Relational Data Model 6 ± Insert a single tuple using: ± Delete all tuples satisfying some condition (e.g., name = Smith): ( Powerful variants of these commands are available; more later! DELETE FROM Students S WHERE S.name = ‘Smith’ Insert and Delete Tuples INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, ‘Smith’, ‘[email protected]’, 18, 3.2)
Background image of page 6
CS174A: Relational Data Model 7 Integrity Constraints (ICs) ± IC : a condition that must be true for every instance of the database; e.g., domain constraints – ICs are specified when schema is defined – ICs are checked when relations are modified ± A legal instance of a relation is one that satisfies all specified ICs – DBMS should not allow illegal instances ± If the DBMS checks ICs, stored data is more faithful to real-world meaning – Avoids data entry errors, too!
Background image of page 7

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

View Full DocumentRight Arrow Icon
CS174A: Relational Data Model 8 Primary Key Constraints ± A set of fields is a key for a relation if : 1 . No two distinct tuples can have same values in all key fields, and 2 . Condition 1 is not true for any subset of the key ± Part 2 false? A superkey ± If there’s >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key ± E.g., sid is a key for Students . (What about name?) The set { sid , gpa } is a superkey
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/02/2010 for the course CS 174a taught by Professor Zhin during the Fall '09 term at UCSB.

Page1 / 35

L03 - Relational Data Model Why Study the Relational Model...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online