1Principles of Information and Database Management198:336Week 4 – Feb 14Matthew StoneTodayRelational model– Big picture– Schemas and tables– ER diagrams to schemas– Queries and SQLRecapWeek 1: Motivation for info managementWeek 2: Theory of representationWeek 3: Practice of representationWeek 4: A classic case studyPictorial viewTELLINFORMATIONMANAGERASKPictorial viewTELLINFORMATIONMANAGERASKDEFINEDefinition operationDescribes the structure of specific information to IM– Corresponds to a conceptual model– Helps avoid errors2Relationssid 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 Example of students relationNamed after logical relationCorresponds toStudent(53666,Jones,jones@cs,18,3.4)Student(53688,Smith,smith@eecs,18,3.2)Student(53650,Smith,smith@math,19,3.8)sid 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 Also called Tablessid 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 For obvious reasonsIn the relational model, IM stores set of relations or tablesTELLINFORMATIONMANAGERASKDEFINERelational modelDefine– Creates new tablesTell– Inserts new rows into tablesQuery– SQL expressions describe data of interest– Results are returned as tablesRelational Model: ExampleDefinition statementcreate table books( isbn char(13) not null primary key,author char(80),title char(100),price float(4,2));3Relational model exampleTell statementinsert into books values('0-672-31697-8','Michael Morgan','Java 2 for Professional Developers',34.99);Relational model exampleTell statement– Adds a row to the specified table in the information manager to include the specified entity or relationship.Relational model exampleQuery exampleSELECT author,titleFROM booksWHERE price > 30;Relational model exampleThis returns a new tableauthor title Michael Morgan Java 2 for Professional Developers ... … Relational ModelMost widely used model.– Vendors: IBM, Informix, Microsoft, Oracle, Sybase, etc.“Legacy systems” in older models – E.G., IBM’s IMSRelational Query LanguagesQueries 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.4SchemasCREATE TABLE name(field TYPE OPTIONS,…field TYPE OPTIONS);Typesintchar(n)float(x,y)datevarchartextOptionsnot nullprimary keyreferences table(field)Interactionuse database;show tables;describe