Database Systems Spring 2017 Organization Of The Course .

2y ago
96 Views
2 Downloads
7.48 MB
191 Pages
Last View : 19d ago
Last Download : 2m ago
Upload by : Asher Boatman
Transcription

Database SystemsSpring 2017Organization of the CourseIntroductionSL01IDatabase curricula at IfIIOrganization of the courseILiteratureIThe database field, basic definitionsILecturesIDB applications, functionality, users and languagesIExercisesIData models, schemas, instances, and redundancyIContentIMain characteristics of the database approachDBS 2017, SL011/68M. Böhlen, IfI@UZHAbout meI have been a database system person since more than 20 years.IMy previous affiliations (and the first example of a database table):InstitutionETH ZürichUniversity of ArizonaAalborg UniversityFree University of Bozen-BolzanoUniversity of ZürichCountryCHUSADKITCHIM. Böhlen, IfI@UZHSlides can be accessed through the course web BS.htmlIThe slides are designed as a working script.IThe textbook is Database Systems by Elmasri and Navathe.IUse the slides, and optionally the textbook, for preparationthroughout the semester.IDuring the lecture we will solve illustrative examples on the board.Interaction during class is welcome.IWhat is importantIIDBS 2017, SL012/68About the Database Systems CourseIAffiliationsStart End1990 19941994 19951995 20032003 20092009 nowDBS 2017, SL013/68M. Böhlen, IfI@UZHBeing able to apply your knowledge to relevant examples.Being able to be precise about the key concepts of database systems.DBS 2017, SL014/68M. Böhlen, IfI@UZH

About Database Sytems @IfILiterature and AcknowledgmentsReading List for SL01:IDatabase Systems (DBS), Spring, 4th semesterIPraktikum Datenbanksysteme (PDBS), Fall, 5th semesterIDistributed Databases (DDBS), Fall even years, 5th semesterISeminar Database Systems (SDBS), Spring, 6th or 8th semesterIXML und Datenbanken, (XMLDB), Spring, 6th or 8th semesterIData Warehousing, Spring (DW), Spring even years, 8th semesterITemporal and Spatial Data Management (TSDM), Fall oddyears, 9th semesterDBS 2017, SL015/68IM. Böhlen, IfI@UZHDBS Course/1IIIMichael Böhlen, University of Zürich, SwitzerlandIJohann Gamper, Free University of Bozen-Bolzano, ItalyThe slides are based on the following text books and associated material:IDatabase Systems, Sixth Edition, Ramez Elmasri and Shamkant B.Navathe, Pearson Education, 2010.IA. Silberschatz, H. Korth, and S. Sudarshan: Database SystemConcepts, McGraw Hill, 2006.DBS 2017, SL016/68M. Böhlen, IfI@UZHDBS Course/2Tuesday 10:15-12:00 in BIN-0.K.02Wednesday 12:15-13:45 in BIN-0.K.02IThe final exam is written and takes place Tuesday, June 20, 10:15 12:00 (check official web pages for details).IAll written material (slides, exercises, exam) is in English.The assessment consists of the completion of 9 out of 12 exercisesand the participation at the final exam. Both parts have to bepassed independently.IThese slides were developed by:Lectures:IIDatabase Systems, Chapters 1 and 2, SixthEdition, Ramez Elmasri and Shamkant B.Navathe, Pearson Education, 2010.Office hours after appointment with TAs (after exercise hour or byemail).IThe weekly exercises are a crucial part of the course.IThe exercises take place Tuesday 12:15-13:45. Start is February 28.During the first week there are no exercises.ITAs: Oksana Dolmatova (English), Yvonne Mülle (German), KevinWellenzohn (English).IPlease sign up for the exercise groups by the end of this week byfilling the Doodle (cf. course web page). We will balance the loadacross groups.IHand in of the exercises is Tuesday 12:15 in the exercise room orbefore to TA directly.IExercises are only valid for the current year.There is no re-exam.DBS 2017, SL017/68M. Böhlen, IfI@UZHDBS 2017, SL018/68M. Böhlen, IfI@UZH

DBS ExercisesIDBS Syllabus/11. Database systems, chapter 1 and 16.523.530.5Relational algebraDomain relational calculusSQL (metadata, DDL, simple DML), PostgreSQLTransformations: RA - DRC - SQLAdvanced SQLFunctions and triggersRelational database designFunctional dependencies, multivalued dependenciesEntity relationship (ER) modelPhysical design and indexingQuery trees and plans, cost computationTransaction processingWrap upDBS 2017, SL019/68I2. Relational model, algebra, and calculus, chapter 3 and 6IIIIM. Böhlen, IfI@UZHDBS 2017, SL0110/68M. Böhlen, IfI@UZHRelational Algebra (RA):The design process, the entity-relationship model, entity-relationshipto relational model mappingPhysical Storage media, file and buffer manager, indices, B-trees,hashingMeasures of query cost, selection and join operation, transformationof relational expressions, evaluation plans9. Transactions, concurrency, recovery, chapter 20, 21, 22ACID properties, SQL transactions, concurrency protocols, log-basedrecoveryDBS 2017, SL01design goals, keys, functional dependencies, normal forms, losslessjoin decompositions, higher normal formsNotational Conventions/18. Query processing and optimization, chapter 18 and 19Icolumn constraints, table constraints, assertions, referential integrity,triggers, stored procedures5. Relational database design, chapter 14 and 157. Physical database design, chapter 16 and 17IData definition language, data manipulation language4. Constraints, triggers, views, DB access, chapter 5 and 126. Conceptual database design, chapter 7 and 8IThe relational model, relational algebra, relational calculus3. SQL, chapter 4DBS Syllabus/2IThe field, terminology, database system, schema, instance,functionality, architecture11/68M. Böhlen, IfI@UZHIconstant: ’abc’, 14, 3.14, .Iattribute: Name, X , . (upper case)Irelation name: Employee, R, . (upper case)Ituple: t, t1 , . (lower case)Irelation: emp, r , s, . (lower case)Ischema: sch(emp) Employee(Name, Addr ), .Idatabase: D, DB, . (upper case)Domain Relational Calculus (DRC), First Order Predicate Logic (FOPL):Iconstant: ’abc’, 14, .Ivariable: X , Y , . (upper case)Ipredicate: p, q, . (lower case)DBS 2017, SL0112/68M. Böhlen, IfI@UZH

Notational Conventions/2SQL:Iconstant: ’abc’, 14, .ISQL keyword: SELECT, FROM, . (all cap, blue, bold)Iattribute: Name, Salary , . (upper case)Itable: r , dept, . (lower case)The Database FieldEntity Relationship Model (ER Model):Iconstant: ’abc’, 14, .Iattribute: Name, Gender , . (green, upper case)Irelationship: workFor , . (red, lower case)Ientity: Company , Emp, . (blue, upper case)DBS 2017, SL0113/68M. Böhlen, IfI@UZHThe Field/1IIIIIIIProductsIActivities of Database PeopleIBasic Terminology and DefinitionsDBS 2017, SL0114/68M. Böhlen, IfI@UZH16/68M. Böhlen, IfI@UZHThe Field/2SIGMOD/PODSVLDBICDEEDBT/ICDTACM Transaction on Database System (TODS)The VLDB Journal (VLDBJ)Information Systems (IS)IEEE Transactions on Knowledge and Data Engineering (TKDE)DBLP Bibliography (Michael Ley, Uni Trier, Germany)IIIJournal PublicationsIIProfessional ResourcesConference PublicationsIIIhttp://dblp.uni-trier.de/db/DBWorld mailing listIhttp://www.cs.wisc.edu/dbworld/DBS 2017, SL0115/68M. Böhlen, IfI@UZHDBS 2017, SL01

ProductsICommercial ProductsIIIIIIIOracle’s Solution StackOracleDB2 (IBM)SQL Server (Microsoft)TeradataSAP HANA.Open Source ProductsIIIIIIIPostgreSQLMySQL (Oracle), MariaDBMongoDB (NoSQL)CouchDB (NoSQL, JSON, MapReduce)CassandraMonetDB.We will use PostgreSQL for this course.Image: Roger Wullschleger, Oracle @ DBTA Workshop on Big Data, Bern, 2012DBS 2017, SL0117/68M. Böhlen, IfI@UZHBasic Definitions/1DBS 2017, SL0118/68M. Böhlen, IfI@UZHBasic Definitions/2About, data, information, and knowledge:IMini-world: The part of the real world we are interested inData are facts that can be recorded:IData: Known facts about the mini-world that can be recordedIDatabase (DB): A collection of related dataIDatabase Management System (DBMS): A software package tofacilitate the creation/maintenance/querying of databasesIDatabase System (DBS): DB DBMSIMeta Data: Information about the structure of the DB.IIIInformation data meaningIIIIIbook(’Lord of the Rings’, 3, 10)book:title ’Lord of the rings’,volume nr 3,price in USD 10Knowledge information applicationIIDBS 2017, SL0119/68M. Böhlen, IfI@UZHwhich tables? which columns? which users? which access rights?Meta data is organized as a DB itself.DBS 2017, SL0120/68M. Böhlen, IfI@UZH

Basic Definitions/3DBMS Languages/1IA DBMS offers two types of languages:IIIThe standard language for database systems is SQLIIIIIDBS 2017, SL0121/68M. Böhlen, IfI@UZHDBMS Languages/2IIISQL offers a DDL and a DML.DBS 2017, SL0122/68M. Böhlen, IfI@UZH1. Give examples of declarative and procedural approaches from thereal world.High level or declarative (non-procedural) languagesLow level or procedural languagesHigh level or declarative language:IIIISQL stands for Structured Query LanguageExample SQL query: SELECT * FROM rthe original name was SEQUEL“Intergalactic data speak” [Michael Stonebraker].Review 1.1We distinguish betweenIdata definition language (DDL) to create and drop tables, etcdata manipulation language (DML) to select, insert, delete, andupdate dataFor example, the SQL languageSet-oriented (retrieve multiple results)Specify what data to retrieve and not how to retrieve itLow level or procedural language:IIIRetrieve data one record at a timeSpecify how to retrieve dataConstructs such as looping are needed to retrieve multiple records,along with positioning pointers.DBS 2017, SL0123/68M. Böhlen, IfI@UZHDBS 2017, SL0124/68M. Böhlen, IfI@UZH

Applications of Database SystemsIApplications, Functionality, Usersand InterfacesTraditional ApplicationsIIMore Recent Applications:IIIIIIIApplication Areas of Database SystemsIFunctionality of Database SystemsIUsers of Database SystemsIDBMS InterfacesIIIIII25/68M. Böhlen, IfI@UZHTypical Activities/Jobs of Database PeopleIData modeling (e.g., UZH)IHandling large volumes of complex data (scientific data,astrophysics, genome data, etc)IDistributed databasesIDesign of migration strategiesIUser interface designIDevelopment of algorithmsIDesign of languagesNew data models and systemsIIIIIIMultimedia DatabasesGeographic Information Systems (GIS)Data WarehousesReal-time and Active DatabasesMany other applicationsExamples:IDBS 2017, SL01Numeric and Textual DatabasesBank (accounts)InsurancesStores (inventory, sales)Reservation systemsUniversity (students, courses, rooms)online sales (amazon.com)online newspapers (nzz.ch)DBS 2017, SL0126/68M. Böhlen, IfI@UZHFunctionality of Database Systems/1Typical DBMS functionality:IDefine a particular database in terms of its data types, structures,and constraintsIConstruct or load the initial database contents on a persistentstorage mediumManipulating the database:IIIIXML/semi-structured databasesStream data processingTemporal and spatial databasesGIS systemsIRetrieval: Querying, generating reportsModification: Insertions, deletions and updates to its contentAccessing the database through Web applicationsSharing by a set of concurrent users and application programswhile, at the same time, keeping all data valid and consistentetc.DBS 2017, SL0127/68M. Böhlen, IfI@UZHDBS 2017, SL0128/68M. Böhlen, IfI@UZH

Functionality of Database Systems/2Users of Database Systems/1Database users have very different tasks. There are those who use andcontrol the database content, and those who design, develop andmaintain database applications.Additional DBMS functionality:IOther features of DBMSs:IProtection or security measures to prevent unauthorized accessIActive processing to take internal actions on dataIPresentation and visualization of dataIIIMaintaining the database and associated programs over the lifetimeof the database application (called database, software, and systemmaintenance)DBS 2017, SL0129/68M. Böhlen, IfI@UZHUsers of Database Systems/2IICasual: access database occasionally when neededNaïve: they make up a large section of the end-user population.IIIIIThey use previously well-defined functions in the form of “cannedtransactions” against the database.Examples are bank-tellers or reservation clerks.Sophisticated:IThese include business analysts, scientists, engineers, othersthoroughly familiar with the system capabilities.Many use tools in the form of software packages that work closelywith the stored database.Stand-alone:IIDBS 2017, SL01Mostly maintain personal databases using ready-to-use packagedapplications.An example is a tax program user that creates its own internaldatabase or a user that maintains an address book31/68IResponsible for authorizing access to the database, for coordinatingand monitoring its use, acquiring software and hardware resources,controlling its use and monitoring efficiency of operations.Database Designers:IResponsible to define the content, the structure, the constraints, andfunctions or transactions against the database. They mustcommunicate with the end-users and understand their needs.DBS 2017, SL0130/68M. Böhlen, IfI@UZHDBMS Interfaces/1End-users: They use the data for queries, reports and some of themupdate the database content. End-users can be categorized into:IDatabase administrators:M. Böhlen, IfI@UZHIUser-friendly interfacesIIMenu-based, forms-based, graphics-based, etc.Stand-alone query language interfacesIExample: Entering SQL queries at the DBMS interactive SQLinterface (e.g. psql in PostgreSQL, sqlplus in Oracle)IProgram interfaces for embedding DML in programming languagesIWeb Browser as an interfaceISpeech as Input and OutputIParametric interfaces, e.g., bank tellers using function keys.Interfaces for the DBA:IIIICreating user accounts, granting authorizationsSetting system parametersChanging schemas or access pathsDBS 2017, SL0132/68M. Böhlen, IfI@UZH

DBMS Interfaces/2DBMS Interfaces/3IIProgrammer interfaces for embedding DML in programminglanguages:IIIEmbedded Approach:embedded SQL (for C, C , etc.)SQLJ (for Java)Procedure Call Approach:JDBC for JavaODBC for other programming languagesDatabase Programming Language Approach:e.g., ORACLE has PL/SQL, a programming language based on SQL;language incorporates SQL and its data types as integral componentsDBS 2017, SL0133/68M. Böhlen, IfI@UZHDBMS Interfaces/4IIIOracle SQL Developer is a graphical tool for DB development.With SQL Developer you can browse database objects, run SQLstatements and SQL scripts, and edit and debug PL/SQLstatements.34/68M. Böhlen, IfI@UZH36/68M. Böhlen, IfI@UZHDBMS Interfaces/5pgadmin is the administration and development platform forPostgreSQL.The graphical interface supports all PostgreSQL features, fromwriting simple SQL queries to developing complex databases.DBS 2017, SL01DBS 2017, SL0135/68M. Böhlen, IfI@UZHICommand line tool psql:DBS 2017, SL01

DBMS Interfaces/6IThere are various database system utilities to perform certainfunctions such as:ILoading data stored in files into a database. Includes dataconversion tools.IBacking up the database periodically on tape.IReorganizing database file structures.IReport generation utilities.IPerformance monitoring utilities.IData ModelsOther functions, such as sorting, user monitoring, data compression,etc.IDatabase SchemaIDatabase InstanceIRedundancyIDBS 2017, SL0137/68M. Böhlen, IfI@UZHData ModelsI38/68M. Böhlen, IfI@UZHCategories of Data ModelsA set of concepts to describe the structure of a database, theoperations for manipulating these structures, and certainconstraints that the database should obey.IStructure and Constraints:IIIIDBS 2017, SL01Data Model:IIModels, Schemas, Instances andRedundancyIDifferent constructs are used to define the database structureConstructs typically include elements (and their data types) as wellas groups of elements (e.g. record, table), and relationships amongsuch groupsConstraints specify some restrictions on valid data; these constraintsmust be enforced at all timesOperationsIIOperations are used for specifying database retrievals and updatesby referring to the constructs of the data model.Operations on the data model may include basic model operations(e.g. generic insert, delete, update) and user-defined operations (e.g.compute student gpa, update inventory)DBS 2017, SL01Conceptual (high-level, semantic) data models:39/68M. Böhlen, IfI@UZHIPhysical (low-level, internal) data models:IIProvide concepts that are close to the way many users perceive data.(Also called entity-based or object-based data models.)Provide concepts that describe details of how data is stored in thecomputer. These are usually specified in an ad-hoc manner throughDBMS design and administration manualsImplementation (representational) data models:IProvide concepts that fall between the above two, used by manycommercial DBMS implementations (e.g. the relational data model isused in many commercial systems).DBS 2017, SL0140/68M. Böhlen, IfI@UZH

Database SchemaIIAn illustrative display of (most aspects of) a database schema.Schema Construct:IA component of the schema or an object within the schema, e.g.,Student, Course.IThe database schema changes very infrequently.ISchema is also called intension.DBS 2017, SL0141/68M. Böhlen, IfI@UZHExample of a Database DescriptionIIPart of a UNIVERSITY environment.Some mini-world entities (an entity is a specific thing in themini-world):IIIIIIIISTUDENTsCOURSEsSECTIONs (of COURSEs)DEPARTMENTsINSTRUCTORsSome mini-world relationships (a relationship relates things of themini-world):IIIIIISECTIONs are of specific COURSEsSTUDENTs take SECTIONsCOURSEs have prerequisiteCOURSE INSTRUCTORs teach SECTIONsCOURSEs are offered by DEPARTMENTsSTUDENTs major in DEPARTMENTsDBS 2017, SL0143/68The actual data stored in a database at a particular moment in time.This includes the collection of all the data in the database.Also called database state (or occurrence or snapshot).The term instance is also applied to individual database components,e.g., record instance, table instance, entity instanceIInitial Database Instance: Refers to the database instance that isinitially loaded into the system.IValid Database Instance: An instance that satisfies the structureand constraints of the database.IThe database instance changes every time the database is updated.IInstance is also called extension.DBS 2017, SL0142/68M. Böhlen, IfI@UZHExample of a Database SchemaMini-world for the example:IDatabase Instance:IThe description of a database.Includes descriptions of the database structure, data types, and theconstraints on the database.Schema Diagram:IIIDatabase Schema:IIDatabase InstanceStudentName rtmentPrerequisiteCourseNr tudNr SectionIdM. Böhlen, IfI@UZHMajorDBS 2017, SL01SemesterYearInstructorGrade44/68M. Böhlen, IfI@UZH

Example of a Database Instancecourse(Course)CourseName’Intro to Computer Science’’Data Structures’’Discrete ’8135’A’DBS 2017, ��’CS1310’45/68M. Böhlen, IfI@UZHReview 1.2/1IDuring the design of a database the number of tables and theirschemas must be determined.IA key goal of database design is to avoid redundancy.IRedundancy is present if information is stored multiple times.IExample of redundancy: storing the same address multiple timesIRedundancy leads to update anomalies and inconsistent data (e.g., aperson has multiple and partially invalid addresses)IThe goal of database design, and specifically of databasenormalization, is to eliminate redundancy.IThe term controlled redundancy is used if duplication ofinformation is allowed and if the duplication is controlled by theDBMS.DBS 2017, SL0146/68M. Böhlen, IfI@UZH48/68M. Böhlen, IfI@UZHReview 1.2/2Consider the university database instance shown above.1. Expla

Reviews: 24 47 54 Database Systems Spring 2017 Introduction SL01 I Organization of the course I The database eld, basic de nitions I DB applications, functionality, users and languages I Data models, schemas, instances, and redundancy I Main characteristics of the database approach DBS 2017, SL011/68M. Böhlen, IfI@U

Related Documents:

Database Applications and SQL 12 The DBMS 15 The Database 16 Personal Versus Enterprise-Class Database Systems 18 What Is Microsoft Access? 18 What Is an Enterprise-Class Database System? 19 Database Design 21 Database Design from Existing Data 21 Database Design for New Systems Development 23 Database Redesign 23

Getting Started with Database Classic Cloud Service. About Oracle Database Classic Cloud Service1-1. About Database Classic Cloud Service Database Deployments1-2. Oracle Database Software Release1-3. Oracle Database Software Edition1-3. Oracle Database Type1-4. Computing Power1-5. Database Storage1-5. Automatic Backup Configuration1-6

real world about which data is stored in a database. Database Management System (DBMS): A collection of programs to facilitate the creation and maintenance of a database. Database System DBMS Database A database system contains information about a particular enterprise. A database system provides an environment that is both

The term database is correctly applied to the data and their supporting data structures, and not to the database management system. The database along with DBMS is collectively called Database System. A Cloud Database is a database that typically runs on a Cloud Computing platform, such as Windows Azure, Amazon EC2, GoGrid and Rackspace.

Creating a new database To create a new database, choose File New Database from the menu bar, or click the arrow next to the New icon on the Standard toolbar and select Database from the drop-down menu. Both methods open the Database Wizard. On the first page of the Database Wizard, select Create a new database and then click Next. The second page has two questions.

Physical Database Design and Performance Modern Database Management 6th Edition Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden Robert C. Nickerson ISYS 464 – Spring 2003 Topic 23 Database Development Process Database planning Database requirements analysis Conceptual database design

Database Management Systems UNIT-I Introduction RGMCET (CSE Dept.) Page 1 UNIT-I INTRODUCTION TO DBMS Database System Applications, database System VS file System - View of Data - Data Abstraction -Instances and Schemas - data Models - the ER Model - Relational Model - Database Languages - DDL - DML - Database Access for applications Programs - Database Users and .

The Question is, “Am I my brother’s keeper?” Am I My Brother’s Keeper, Bill Scheidler 4 Deuteronomy 25:5-10 – God challenges brothers to build up the house of their brothers. “If brothers dwell together, and one of them dies and has no son, the widow of the dead man shall not be married to a stranger outside the family; her husband’s brother shall go in to her, take her as his .