Databases SystemsICS 184Professor MehrotraRoom 424Computer Science DepartmentUniversity of California IrvineTel: 949 824 5975CS 184 Course Web Serverw All course information will be posted on linew URL:n http://www.ics.uci.edu/~dbclass/ics184/index.htmlw Class Notes available before class on the Web.Course Infow TAsn Koushik NiyogiOffice and office Hours:w Monday and Wednesday : 2:00-3:00 PMn Rajat Mathur Office and office Hours: TBAw Instructorn Office Hours:n Tue 11-12 pmn (send email)n Email: [email protected] to contact me urgently, send email and mark subject line asCS 184 URGENTDesiderataw Course Text: (either of following two books willsuffice)n A First Course in Database Systems, Ullman and Widomw we will cover the entire bookn Database Systems Concepts, Silberschatz, Korth, and Sudarshanw we will cover chapters 1-9w Software:n Course will involve significant programming.n You will get exposure to database programming in DB2Desiderata (cont.)w Course Requirements:n Problem sets ~ approx. every week to 10 days.w Total not to exceed 8n Midtermn Final (comprehensive)n Grades:w Problem sets 15%w Personal Database Assignment (project) 15%w Midterm 30%w Final 40%Policiesw Late Assignmentsn No grace period after due date…. except under exceptionalcircumstancesn job interviews, out of town trip, breaks etc do not qualify asexceptional circumstances!w Working in Groupsn do your homework problem sets in group size not to exceed 3n learn moren get better gradesn get used to working in groups (important to employers)w Do exams individually!!Material Covered in CS 184w Four aspects of studying DBMSsn Modeling and design of databasesw allows exploration of issues before committing to animplementationn Programming: queries and DB operations like update.w SQL == “intergalactic dataspeak”n DBMS implementationn Effect of technology and application advances to databasetechnology.w CS 184 == (1) + (2)w CS 214 == (3)w CS215 == (3) + (4)Applications/queriesQuery processorStorage managerusermetadatadatabaseDatabase: collection ofinterrelated information aboutworld being modeledDBMS:general purposesoftware to define, create,modify, retrieve, delete andmanipulate a databaseVendors: Informix, Oracle, O2,Sybase, IBM, DECDBMSDatabase Management EnvironmentTraditional DBMS Goalsw Efficient management of (faster than files) largeamounts of (gigabytes) of persistent (outlastscreator), reliable (outlasts crashes) sharedinformation (multiple users).w DBMS Users:n small and large corporationsw E-commerce companies, banks, airlines, transportation companies,corporate databases, government agencies, defense.w Anyone you can think of!Databases and File Systemsw DBMSs evolved from file systems.w DBMSs provide many features that traditional filesystems do not.n Support for concurrent access and data sharing. Data consistency inpresence of concurrencyn Reliability in presence of failures and system crashes.n Efficient associative access to very large amounts of datan A high level Query language (SQL) to define, create, access, andmanipulate data. Support for unanticipated queriesn support for multiple data viewsn security and authorizationn data abstractionn prevention of data redundancy and inconsistenciesData Abstractionw program data independence:n ability to hide details of how data is stored and maintainedfrom application programsw program-operation independence:n ability to hide details of operation implementation fromapplication programs (Object-Orientation)Physical levelLogical LevelView1View 2 View nPhysical description of data, storageorganizationConceptual representationCustomized views Data Abstractionw Hiding system complexity, physical storagedetails from users and application programsw Instance:n set of data currently instantiated in databasen changes frequentlyw Schema:n overall design, structure, and constraints over the databasen referred to as metadatan changes infrequentlyw Example:Schema InstanceTablesEmp (ename, dep#)Dept(dep#, dname, mgr)Constraintseach department hasa single manager(John, 10), (Cindy, 15), (Martha, 10)(10, Toy, John), (15, Sales, Cindy)EmpSchemas and InstancesdeptData Modelw Set of concepts and tools used to describe thedatabase schemaw Different schemas at different abstraction levels:n physical schema: describes physical organization of datan logical schema: describes data at conceptual leveln sub schema: defines data at view levelw Different models used describe schemas atdifferent abstraction levelsTypes of Data Modelsw Object based Logical Modelsn Used to describe schema at view and logical levels.n Support abstract view of data as objects, relationships,constraintsn Example: Entity Relationship Model, Functional data Model,Semantic Model, Object Oriented Model like ODLTypes of Data Model (cont.)w Record-Based Logical Modelsn Used to define data at view and logical levels.n Provide a high level description of implementationn Examples:Relational Model, Hierarchical Model, NetworkModelw Physical Modelsn Used to describe data at implementation level.n Examples: Frame Memory Model, Unifying ModelEntity-Relationship ModelExample of schema in the entity-relationship modelEntity Relationship Model (Cont.)w E-R model of real worldn Entities (objects)w E.g. customers, accounts, bank branchn Relationships between entitiesw E.g. Account A-101 is held by customer Johnsonw Relationship set depositor associates customers with accountsw Widely used for database designn Database design in E-R model usually converted to design inthe relational model (coming up next) which is used forstorage and processingRelational Modelw Example of tabular data in the relational modelcustomer-nameCustomer-idcustomer-streetcustomer-cityaccount-numberJohnsonSmithJohnsonJonesSmith192-83-7465019-28-3746192-83-7465321-12-3123019-28-3746AlmaNorthAlmaMainNorthPalo AltoRyePalo AltoHarrisonRyeA-101A-215A-201A-217A-201AttributesA Sample Relational DatabaseClassification of DBMSs based on DataModelw Relational DBMSs:n modeling concept: tables and constraints on tablesn Query Language: SQLn Applications: suited for traditional business processing applicationsw Object Oriented DBMSsn modeling concepts: objects, classes, inheritancen Query Language: object oriented OQLn Applications: suited for CAD databases, CASE databases, officeautomationw Object Relational DBMSs:n incorporate OO concepts into relational modeln similar functionality as OODBMSs though