Data and Queries in the Relational ModelData Models – Describing DataSteps in Database DesignConceptual Design using ERSlide 5ER ExampleParticipation ConstraintsImplementation: The Relational ModelRelational Database: DefinitionsSome SynonymsEx: Instance of Students RelationSQL - A language for Relational DBsCreating Relations in SQLTable Creation (continued)Constraints - KeysPrimary and Candidate Keys in SQLForeign Keys, Referential IntegrityForeign Keys in SQLEnforcing Referential IntegrityIntegrity Constraints (ICs)Where do ICs Come From?Adding and Deleting TuplesRelational Query LanguagesThe SQL Query LanguageQuerying Multiple RelationsCross-product of Students and Enrolled InstancesQuery Optimization OverviewRelational OperationsA Really Simple Query OptimizerThe Query Optimization GameCost-based Query Sub-SystemQuery Processing OverviewIteratorsExample: ScanExample: SortSchema for ExamplesMotivating ExampleAlternative Plans – Push Selects (No Indexes)Slide 39Slide 40Slide 41Slide 42Alternative PlansSlide 44Relational Model: SummaryData and Queries in the Relational ModelCS 162 Guest LectureMike FranklinApril 6, 2011A relationship, I think, is like a shark, you know? It has to constantly move forward or it dies. And I think what we got on our hands is a dead shark.Woody Allen (from Annie Hall, 1979)Data Models – Describing Data•A Database design encodes some portion of the real world.•A Data Model is a set of concepts for thinking about this encoding.•Many models have been proposed.1010111101Student (sid: string, name: string, login: string, age: integer, gpa:real)•We will look at two related models:i) Entity-Relationship (graphical)ii) Relational (implementation)Steps in Database Design•Requirements Analysis user needs; what must the database capture?•Conceptual Design high level description (often done w/ER model)•Logical Design translate ER into DBMS data model•Typically: “relational” model as implemented by SQL•Schema Refinement - consistency, normalization•Physical Design - indexes, disk layout•Security Design - who accesses what, and howConceptual Design using ER•What are the entities and relationships?•What info about E’s & R’s should be in DB?•What integrity constraints (business rules) hold? •ER diagram is a representation of the `schema’•Can map an ER diagram into a relational schema.•Conceptual design is where the SW/data engineering beginsRails “models”ER Example An employee can work in many departments; a dept can have many employees. 1-to-1Many-to-ManysinceManagesdnamebudgetdidDepartmentssinceWorks_InlotnamessnEmployeesIn contrast, each dept has at most one manager, according to the key constraint on Manages.1-to-ManyMany-to-1Participation Constraints•Does every employee work in a department? •If so: a participation constraintparticipation of Employees in Works_In is total (vs. partial)What if every department has an employee working in it?•Basically means “at least one”lotnamednamebudgetdidsincenamednamebudgetdidsinceManagessinceDepartmentsEmployeesssnWorks_InImplementation: The Relational Model•The E-R model is not directly implemented by most DBMSs.•Fairly easy to map an E-R design to a Relational Schema•The Relational Model is UbiquitousMySQL, PostgreSQL, Oracle, DB2, SQLServer, …Note: some “Legacy systems” use older models •e.g., IBM’s IMS•Object-oriented concepts have been merged in•Early work: POSTGRES research project at Berkeley•Informix, IBM DB2, Oracle 8i•As has support for XML (semi-structured data)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 Students(sid: string, nam e: string, login: string, age: integer, gpa: real) Instance : the actual data at a given time •#rows = cardinality•#fields = degree / aritySome SynonymsFormal Not-so-formal 1 Not-so-formal 2Relation TableTuple Row RecordAttribute Column FieldDomain TypeEx: Instance of Students Relationsid name login age gpa 53666 Jones jones@c s 18 3.4 53688 Smith smith@eecs 18 3.2 53650 Smith smith@math 19 3.8 • Cardinality = 3, arity = 5 , all rows distinct• Do all values in each column of a relation instance have to be distinct?SQL - A language for Relational DBs•Say: “ess-cue-ell” or “sequel”But spelled “SQL”• Data Definition Language (DDL)create, modify, delete relationsspecify constraintsadminister users, security, etc.•Data Manipulation Language (DML)Specify queries to find tuples that satisfy criteriaadd, modify, remove tuplesCreating Relations in SQL•Create the Students relation: 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))Constraints - 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 grade53666 Carnatic101 C53666 Reggae203 B53650 Topology112 A53666 History105 BEnrolledStudentsPRIMARY KeyFOREIGN KeyPrimary and Candidate Keys in SQL•Possibly many candidate keys (specified using UNIQUE), one of which is chosen as the primary key.•Keys must be used carefully!•“For a given student and course, there is a single grade.” “Students can take only one course, and no two students in a course receive the same grade.”CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid))CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade)) vs.Foreign Keys, Referential Integrity•Foreign key: a “logical pointer”Set of fields in a tuple in one relation that `refer’ to a tuple in another relation. Reference to primary key of the other relation. •All foreign key constraints enforced?referential integrity!i.e., no dangling references.Foreign Keys in SQL•E.g. Only students listed in the Students relation should be allowed to enroll for courses. sid is a foreign key referring to Students: CREATE TABLE Enrolled (sid CHAR(20),cid CHAR(20),grade CHAR(2), PRIMARY KEY (sid,cid),
View Full Document
Unlocking...