Database System Concepts, 6th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use Chapter 1: Introduction Silberschatz, Korth and Sudarshan1.2Database System Concepts - 6th EditionDatabase Management System (DBMS)nDBMS contains information about a particular enterpriselCollection of interrelated datalSet of programs to access the data lAn environment that is both convenient and efficient to usenDatabase Applications:lBanking: transactionslAirlines: reservations, scheduleslUniversities: registration, gradeslSales: customers, products, purchaseslOnline retailers: order tracking, customized recommendationslManufacturing: production, inventory, orders, supply chainlHuman resources: employee records, salaries, tax deductionsnDatabases can be very large.nDatabases touch all aspects of our livesSilberschatz, Korth and Sudarshan1.3Database System Concepts - 6th EditionUniversity Database ExamplenApplication program exampleslAdd new students, instructors, and courseslRegister students for courses, and generate class rosterslAssign grades to students, compute grade point averages (GPA) and generate transcriptsnIn the early days, database applications were built directly on top of file systemsSilberschatz, Korth and Sudarshan1.4Database System Concepts - 6th EditionDrawbacks of using file systems to store dataDrawbacks of using file systems to store datalData redundancy and inconsistency4Multiple file formats, duplication of information in different fileslDifficulty in accessing data 4Need to write a new program to carry out each new tasklData isolation multiple files and formatslIntegrity problems4Integrity constraints (e.g., account balance 0) become “buried” in program code rather than being stated explicitly4Hard to add new constraints or change existing onesSilberschatz, Korth and Sudarshan1.5Database System Concepts - 6th EditionDrawbacks of using file systems to store data (Cont.)Drawbacks of using file systems to store data (Cont.)lAtomicity of updates4Failures may leave database in an inconsistent state with partial updates carried out4Example: Transfer of funds from one account to another should either complete or not happen at alllConcurrent access by multiple users4Concurrent access needed for performance4Uncontrolled concurrent accesses can lead to inconsistenciesExample: Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same timelSecurity problems4Hard to provide user access to some, but not all, dataDatabase systems offer solutions to all the above problemsSilberschatz, Korth and Sudarshan1.9Database System Concepts - 6th EditionData ModelsnA collection of tools for describing lData lData relationshipslData semanticslData constraintsnRelational modelnEntity-Relationship data model (mainly for database design) nObject-based data models (Object-oriented and Object-relational)nSemistructured data model (XML)nOther older models:lNetwork model lHierarchical modelSilberschatz, Korth and Sudarshan1.10Database System Concepts - 6th EditionRelational ModelnRelational model (Chapter 2)nExample of tabular data in the relational modelColumnsRowsSilberschatz, Korth and Sudarshan1.11Database System Concepts - 6th EditionA Sample Relational DatabaseSilberschatz, Korth and Sudarshan1.13Database System Concepts - 6th EditionData Definition Language (DDL)nSpecification notation for defining the database schemaExample:create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2)nDDL compiler generates a set of table templates stored in a data dictionarynData dictionary contains metadata (i.e., data about data)lDatabase schema lIntegrity constraints4Primary key (ID uniquely identifies instructors)4Referential integrity (references constraint in SQL)e.g. dept_name value in any instructor tuple must appear in department relationlAuthorizationSilberschatz, Korth and Sudarshan1.14Database System Concepts - 6th EditionSQLnSQL: widely used non-procedural languagelExample: Find the name of the instructor with ID 22222selectnamefrominstructorwhereinstructor.ID = 22222lExample: Find the ID and building of instructors in the Physics dept. select instructor.ID, department.buildingfrom instructor, departmentwhere instructor.dept_name = department.dept_name and department.dept_name = Physics nApplication programs generally access databases through one oflLanguage extensions to allow embedded SQLlApplication program interface (e.g., ODBC/JDBC) which allow SQL queries to be sent to a databasenChapters 3, 4 and 5Silberschatz, Korth and Sudarshan1.16Database System Concepts - 6th EditionDatabase Design?nIs there any problem with this design?Silberschatz, Korth and Sudarshan1.17Database System Concepts - 6th EditionDesign ApproachesnNormalization Theory (Chapter 8)lFormalize what designs are bad, and test for themnEntity Relationship Model (Chapter 7)lModels an enterprise as a collection of entities and relationships4Entity: a “thing” or