ECS 165A: Introduction to Database Systems

1 ECS 165A: Introduction to Database SystemsTodd J. Greenbased on material and slides byMichael GertzandBertram Lud ascherWinter 2011 Dept. of Computer ScienceUC DavisECS-165A WQ 1111. Introduction to Relational DatabasesWhat is a Database system ? In this class we will learn aboutDatabases(DBs) andDatabaseManagement Systems (DBMSs) A Database is a (typically very large) integrated collection ofinterrelated data which are stored in a persistent format (files).Data describe information and activities about one or morerelated organizations (portion of thereal world). For example,a university Database might contain information about entities ( , students, courses, faculties.)

2 Relationships among entities ( , Bill is taking ecs 165a ) A Database Management system is a collection of softwarepackages designed to store, access, and manage databases. Itprovides users and applications with an environment that isconvenient, efficient, and of Computer ScienceUC Davis1. IntroductionECS-165A WQ 112 File system versus DBMSFile processing approach:prominent approach supported byconventional Operating Systems for data intensive applicationprograms in the 60 s and 70 s (and even sometimes today).In the file processing approach, each application uses its own files:ProductsSuppliersApplicationsAppli cationsCustomersProductsDrawbacks: Data are stored redundantly in many files using differentformats (= inconsistencies among data, because changesare not coordinated) No transaction management and concurrency control tosupport multiple users Difficult to access data (no high-levelquery languages) No security mechanisms (OS file permissions are not enough!)

3 No recovery mechanisms Program data dependence (= high maintenance costs) Poor data modeling concepts ..Dept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 113 Purpose of a Database system Data IntegrationAll data are uniformly managed. Efficient Data AccessDatabase languages are provided tostore, access and manage data. Data DictionaryContains all data about objects and structuresof the Database (metadata) User/Application-ViewsDifferent views for different users andapplications Integrity Constraintsare enforced by the DBMS. Security Mechanismsto protect data from security threats Transactionscombine sets of operations on data into logicalatomic units Synchronizationof concurrent user transactions Recoveryof data after system crash ad-hocqueries, report generation, interfaces to other databasesystems, interfaces for application programming.

4 Dept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 114 Trends in Database SystemsSome Database Application Settings EOS NASA s Earth Observation system : Petabyte-sizeddatabases, thousands of customers and applications E-Commerce and Financial Applications:Integration ofheterogeneous information sources ( , catalogs) Health-Care Information Systems and Electronic PatientRecord: Integration of heterogeneous forms of legacy data Digital Libraries & Digital Publishing:Management anddelivery of large bodies of textual and multimedia data Collaborative Work and Design: Integration of heterogeneousinformation sources, concurrency control, workflowsTrends that Affect Database Management Systems Record-based data has been joined by various kinds ofsemistructured and multimedia data Information Superhighway:Web = Constantly growingcollection of heterogeneous information sources High bandwidth, more storage capacity, high performancecomputing.

5 Challenges Data management in sensor networks ( , RFID) Grid computing Integration of multimedia objects and standard data User interfaces development, information visualization Distribution, heterogeneity, complexity of data; security andprivacy; data integration and conversion; data quality, ..Dept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 115 Why Study Database Systems ? Shift from DBS as a specialized application to centralcomponent of modern computing environments ( Data andInformation Science ) Shift from computation to information at the low end, we have a very messy Web space containinghundreds of thousands of information sources at the high end, we have various needs for scientificapplications Datasets are increasing in diversity, complexity, and volume Digital Libraries, E-, Human Genome Project.

6 The need for (advanced) DBMS is exploding .. !!! Database Systems encompass most of CS topics Operating Systems (file management, process management, .. ) Theory (languages, algorithms, computation, complexity, .. ) Artificial Intelligence (knowledge based Systems , intelligent search, .. ) Software Engineering (application development, GUIs, .. ) Multimedia (video on demand, audio, movies, .. ) Logic (query languages, query optimization, .. )Dept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 116 Databases are UbiquitousDept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 117 Different Views of DataA major purpose of a DBMS is to provide users with an abstractview of data, it hides details of how data are stored andmaintained on a (or Conceptual ) Level Physical LevelView 1 View 2 View of abstractionLevels of Abstraction in a DBMS Physical Leveldescribeshowdata records are actually storedand how files and indexes are organized and used Logical Level(sometimes also called Conceptual Level)describeswhatdata are stored in the DBS in terms of entitiesand relationships; emphasis on logical structure of the Database View Leveldescribes how users and applications see the data.

7 Abstraction is achieved by describing each level in terms of adatabase schema, which, in turn, is based on a data modelDept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 118 Data Models, Schemas, and Instances AData Modelis a collection of concepts for describing data and relationships among data data semantics and data constraints Object-Based logical Models Entity-Relationship (ER) Model Object-Oriented (OO) Model Record-Based logical Models Relational Model Network Model Hierarchical Model Adatabase schemais a description of a particular collectionof data, using a given data a Database schema is the actual content of thedatabase at a particular point in time.

8 Schemas exist at different levels of abstraction Conceptual (or Logical) Schema: typically builds the basisfor designing a Database (main focus in this course) View (or External) Schemas: typically determined duringrequirements analysis (often require integration into oneconceptual schema) Physical Schema:storage structures associated withrelationsDept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 119 Example: University Database Conceptual Schema: can be based on Entity-Relationship,Object-Oriented, or Relational of an Entity-Relationship schema:EnrolledcreditsStudentssidname dobmajoraddressCoursescidcnamegrade Relational schema:Students(sid:integer, name:string, major:string,address:string, dob:date)Courses(cid:integer, cname:string, credits:integer)Enrolled(sid:integer, cid:integer, grade:string) Physical Schema: Relations are stored as unordered files Index on first column of Students External Schemas (Views): CourseInfo(cid:integer, enrollment:integer)Dept.

9 Of Computer ScienceUC Davis1. IntroductionECS-165A WQ 1110 Data Independence Ability to modify definition of schema at one level withoutaffecting a schema definition at a higher level Achieved through the use of three levels of data abstraction(also calledthree level schema architecture) Logical Data Independence:Ability to modify logicalschema without causing application programs to berewritten Physical Data Independence: Ability to modify physicalschema without causing logical schema or applications to berewritten (occasionally necessary to improve performance)Dept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 1111 Database LanguagesA Database Management system offers two different types oflanguages Data Definition Language (DDL) Specification language (notation) for defining a databaseschema; includes syntax and semantics DDL compiler generates set of tables stored in the DBMS sdata dictionary(containsmetadata, data about data ) Data storage and definition language special type of DDLin which storage structures and access methods used bythe DBS are specified Data Definition in SQL (Structured Query Language):create table students(sidnumber,namechar(70),majorcha r(50),dobdate,address char(100)).

10 Data Manipulation Language (DML) Language for accessing and manipulating the data that isorganized according to underlying data model Two classes of languagesProcedural user specifies howrequired data is retrievedDeclarative user specifies whatdata is required withoutspecifying how to get those data(declarative =non-procedural)Dept. of Computer ScienceUC Davis1. IntroductionECS-165A WQ 1112 Transaction Management Atransactionis an atomic sequence of Database operationsthat perform a logical function in a Database application. Transaction-management component of DBMS ensures thatthe DB remains in a consistent (correct) state despite systemfailures ( , system crash, power failure) and transactionfailures.