3 Database Terms A data model is a collection of concepts for describing data. A schema is a description of a particular collection of data, using the a given data model. The relational model of data is the most widely used model today. Main concept: relation, basically a table with rows and columns. Every relation has a schema, which describes the columns, or fields. attribute column name projects relation name table name projectno manager description budget PJ23 Miller main bodywork team PJ15 Maynard specialized wings PJ47 Morris electronics tuple / row 3

4 Database Management Systems DBMS: A tool for creating and managing g large amounts of data efficiently and allowing it to persist over long periods of time, safely. (Garcia-Molina et. al., 2002) Levels of abstraction provide logical data independence as well as physical data independence Many external schemas (views) describe how users see the data. One conceptual schema defines logical structure One physical schema describes the files and indexes used. DBMS Database DBS 4

6 Structure of a DBMS A typical DBMS has a layered architecture. Each layer provides some kind of data abstraction and data mapping Concurrency control, recovery as well as transaction management have to be supported (within some layers). This is one of several possible architectures; each system has its own variations. DBM MS Query Optimization and Execution Relational Operators Files and Access Methods Buffer Management Disk Space Management DB 6

10 SQL Queries Example: List all parts of version 1.0 and the manager that is responsible for the corresponding project. SELECT partno, version, manager FROM parts, projects WHERE parts.projectno = projects.projectno AND version = '1.0' Example: Which project is responsible for more than two different parts? SELECT projectno, COUNT(DISTINCT partno) FROM parts GROUP BY projectno HAVING COUNT(DISTINCT partno) > 2 10

12 Other Important Concepts Some other important concepts in SQL:1999: View: A virtual table that is formed by a query expression and does not physically exist. Routine: A procedure, function or method that is known (in some cases also stored) by the system. It can be written in SQL or an external host language. Trigger: Allows actions to be taken when data is inserted, updated or deleted. Schema: A named collection of objects in the database. Catalog: A named collection of schemas in a database. User: Authorization identifier to control access to the database. Privilege: Defines the allowed operations for each user. 12

20 Classification: Training Phase Given: a set of training tuples carrying a class label Aim: a model (classifier) that assigns a class label to a given tuple by deriving the label from the values of the tuple s attributes training data classification algorithms name age income credit Mary low poor James low fair Bill low good John medium fair Marc high good Anne high good classifier (model) IF age = OR income = high THEN credit = good store in DB, e.g., XML 20

21 Classification: Test Phase test data classifier (model) prediction name age income credit Paul high good Jenny low fair Rick high fair credit fair fair good If there is a significant discrepancy between the expected (and by definition correct) result and the result predicted by the model it may be necessary to adapt/rebuild the model. 21

24 Geographic Information System Search for areas of high traffic and high emission? SELECT Position FROM Emission E, Traffic V Spatial Extender WHERE overlaps(e.polygon, V.polygon) Spatial Query Database System 24

34 XQuery: FLWOR Expression FOR_ clause RETURN_ clause LET_clause WHERE_clause ORDER_BY_clause FOR clause, LET clause generate list of tuples of bound variables (order preserving) by iterating over a set of nodes (possibly specified by a path expression), or binding a variable to the result of an expression ession WHERE clause applies a predicate to filter the tuples produced by FOR/LET ORDER BY clause imposes order on the surviving tuples RETURN clause is executed for each surviving tuple, generates ordered list of outputs 34

The following is an excerpt from a draft chapter of a new enterprise architecture text book that is currently under development entitled Enterprise Architecture: Principles and Practice by Brian Cameron

COMP 378 Database Systems Notes for Chapter 1 of Database System Concepts Introduction A database management system (DBMS) is a collection of data and an integrated set of programs that access that data.

Fundamentals of Database Systems, 4 th Edition By Ramez Elmasri and Shamkant Navathe Table of Contents A. Short Table of Contents (This Includes part and chapter titles only) PART 1: INTRODUCTION AND CONCEPTUAL

DBMS / Business Intelligence, SQL Server Orsys, with 30 years of experience, is providing high quality, independant State of the Art seminars and hands-on courses corresponding to the needs of IT professionals.

Foundations of Business Intelligence: Databases and Information Management Problem: HP s numerous systems unable to deliver the information needed for a complete picture of business operations, lack of

ORACLE DATABASE 10G ENTERPRISE EDITION OVERVIEW Oracle Database 10g Enterprise Edition is ideal for enterprises that ENTERPRISE EDITION For enterprises of any size For databases up to 8 Exabytes in size.

Chapter 6 FOUNDATIONS OF BUSINESS INTELLIGENCE: DATABASES AND INFORMATION MANAGEMENT Learning Objectives Describe how the problems of managing data resources in a traditional file environment are solved

FAQs Introduction to Database Systems and Design Module 1: Introduction Data, Database, DBMS, DBA Q2. What is a catalogue? Explain the use of it in DBMS. Q3. Differentiate File System approach and Database

DBMS Interview Questions 1. What is database? A database is a collection of information that is organized. So that it can easily be accessed, managed, and updated. 2. What is DBMS? DBMS stands for Database

UNIT-1 Ques 1. Define dbms and file management system? Ans- Database management system (DBMS) is a collection of interrelated data and a set of programs to access those data. Some of the very well known

14 Databases 14.1 Source: Foundations of Computer Science Cengage Learning Objectives After studying this chapter, the student should be able to: Define a database and a database management system (DBMS)

A Next-Generation Analytics Ecosystem for Big Data Colin White, BI Research September 2012 Sponsored by ParAccel BIG DATA IS BIG NEWS The value of big data lies in the business analytics that can be generated

Database Release 2 Product Family An Oracle White Paper January 2002 Database Release 2 Product Family INTRODUCTION Database Release 2 is available in three editions, each suitable for different development

History of Database Systems By Kaushalya Dharmarathna(030087) Sandun Weerasinghe(040417) Early Manual System Before-1950s Data was stored as paper records. Lot of man power involved. Lot of time was wasted.

Master Management MDM and Warehousing Complement Each Other Greater business value from both 2011 IBM Corporation Executive Summary Master Management (MDM) and Warehousing (DW) complement each other There

In This Chapter This chapter describes several issues that a database administrator (DBA) must understand to effectively plan for a database. It discusses the following topics: Choosing a data model for

Introduction to Oracle Business Intelligence Standard Edition One Mike Donohue Senior Manager, Product Management Oracle Business Intelligence The following is intended to outline our general product direction.

The Import & Export of Data from a Database Introduction The aim of these notes is to investigate a conceptually simple model for importing and exporting data into and out of an object-relational database,