The Relational ModelAdministrivia IAdministrivia IIAdministrivia III - Don’t ForgetData ModelsDescribing Data: Data ModelsLevels of AbstractionData Independence:The Big Breakthrough of the Relational ModelWhy Study the Relational Model?Relational Database: DefinitionsExample: University DatabaseEx: An Instance of Students RelationSQL - A language for Relational DBsSQL OverviewCreating Relations in SQLTable Creation (continued)Adding and Deleting TuplesKeysPrimary KeysPrimary and Candidate Keys in SQLForeign Keys, Referential IntegrityForeign Keys in SQLEnforcing Referential IntegrityIntegrity Constraints (ICs)Where do ICs Come From?Relational Query LanguagesThe SQL Query LanguageQuerying Multiple RelationsSemantics of a QueryCross-product of Students and Enrolled InstancesQueries, Query Plans, and OperatorsStructure of a DBMSRelational Model: SummaryAdministrivia IV - Don’t ForgetThe Relational ModelCS 186, Spring 2006, Lecture 2R & G, Chap. 1 & 3Administrivia I•CS 186 IS MOVING!!!!•Starting TUES 1/24 (next week) we will be in 105 NORTHGATE•CS 186 IS MOVING!!!!•Starting TUES 1/24 (next week) we will be in 105 NORTHGATEAdministrivia II•Recall: Discussion Sections– W11-12 70 Evans– W 2-3 70 Evans– W 3-4 241 Cory•Section on Tuesdays is Cancelled. •Still working on approval for 3rd TA.•Web site is getting there.•Details on Projects, Grading, TA office hours, etc. available by Tuesday.•I *will* be holidng office hours today as scheduled: 1-2pm 687 Soda HallAdministrivia III - Don’t Forget•CS 186 IS MOVING!!!!•Starting TUES 1/24 (next week) we will be in 105 NORTHGATEData Models•A Database models some portion of the real world.•Data Model is link between user’s view of the world and bits stored in computer.•Many models have been proposed.•We will concentrate on the Relational Model.1010111101Student (sid: string, name: string, login: string, age: integer, gpa:real)Describing Data: Data Models•A data model is a collection of concepts for describing data.•A database schema is a description of a particular collection of data, using a given data model.•The relational model of data is the most widely used model today.–Main concept: relation, basically a table with rows and columns.–Every relation has a schema, which describes the columns, or fields.Levels of Abstraction•Views describe how users see the data. •Conceptual schema defines logical structure•Physical schema describes the files and indexes used.•(sometimes called the ANSI/SPARC model)Physical SchemaConceptual SchemaView 1 View 2 View 3DBUsersData Independence:The Big Breakthrough of the Relational Model•A Simple Idea: Applications should be insulated from how data is structured and stored.Physical SchemaConceptual SchemaView 1 View 2 View 3DB•Q: Why are these particularly important for DBMS? •Physical data independence: Protection from changes in physical structure of data.•Logical data independence: Protection from changes in logical structure of data.Why Study the Relational Model? •Most widely used model currently.–DB2, MySQL, Oracle, PostgreSQL, SQLServer, …–Note: some “Legacy systems” use older models •e.g., IBM’s IMS•Object-oriented concepts have recently merged in–object-relational model•Informix, IBM DB2, Oracle 8i•Early work done in POSTGRES research project at Berkeley•XML (semi-structured)models emerging?Relational Database: Definitions•Relational database: a set of relations. •Relation: made up of 2 parts:–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) –Instance : a table, with rows and columns. •#rows = cardinality•#fields = degree / arity•Can think of a relation as a set of rows or tuples. –i.e., all rows are distinctExample: University Database•Conceptual schema: – Students(sid: string, name: string, login: string, age: integer, gpa:real)– Courses(cid: string, cname:string, credits:integer) – Enrolled(sid:string, cid:string, grade:string)•External Schema (View): –Course_info(cid:string,enrollment:integer)•One possible Physical schema :–Relations stored as unordered files. –Index on first column of Students.Physical SchemaConceptual SchemaView 1 View 2 View 3DBEx: An Instance of Students Relationsid name login age gpa 53666 Jones jones@cs 18 3.4 53688 Smith smith@eecs 18 3.2 53650 Smith smith@math 19 3.8 Cardinality = 3, Arity = 5 All rows must be unique (set semantics)• Q: Do all values in each column of a relation instance have to be Unique?• Q: Is “Cardinality” a schema property?• Q: Is “Arity” a schema property?SQL - A language for Relational DBs•SQL (a.k.a. “Sequel”), –“Intergalactic Standard for Data”–Stands for Structured Query LanguageTwo sub-languages:•Data Definition Language (DDL)–create, modify, delete relations–specify constraints–administer users, security, etc.•Data Manipulation Language (DML)–Specify queries to find tuples that satisfy criteria–add, modify, remove tuplesSQL Overview•CREATE TABLE <name> ( <field> <domain>, … )•INSERT INTO <name> (<field names>) VALUES (<field values>)•DELETE FROM <name> WHERE <condition>•UPDATE <name> SET <field name> = <value> WHERE <condition>•SELECT <fields> FROM <name> WHERE <condition>Creating Relations in SQL•Creates the Students relation.–Note: the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified. CREATE TABLE Students(sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT)Table Creation (continued)•Another example: the Enrolled table holds information about courses students take.CREATE TABLE Enrolled(sid CHAR(20), cid CHAR(20), grade CHAR(2))Adding and Deleting Tuples•Can insert a single tuple using:INSERT INTO Students (sid, name, login, age, gpa) VALUES (‘53688’, ‘Smith’, ‘smith@ee’, 18, 3.2)•Can delete all tuples satisfying some condition (e.g., name = Smith):DELETE FROM Students S WHERE S.name = ‘Smith’Powerful variants of these commands are available; more later!Keys•Keys are a way to associate tuples in different relations•Keys are one form of integrity constraint (IC)sid name login age gpa53666 Jones jones@cs 18 3.453688 Smith smith@eecs 18 3.253650 Smith smith@math 19 3.8sid cid
View Full Document
Unlocking...