Chapter 1 Databases and Database Users CS 6360 Database Systems Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 Outline 1 1 Introduction 1 2 An Example 1 3 Characteristics of the Database Approach 1 4 Actors on the Scene 1 5 Workers behind the Scene 1 6 Advantages of Using the DBMS Approach 1 7 A Brief History of Database Applications 1 8 When Not to Use a DBMS 2 1 1 Introduction What is a Database Database Collection of related data Known facts that can be recorded and that have implicit meaning Miniworld or Universe of Discourse UoD Represents some aspect of the real world Logically coherent collection of data with inherent meaning semantics Built for a specific purpose 4 Examples Traditional database applications Store textual and or numeric information such as the Medicare database Multimedia databases Store images audio clips and video streams digitally Most modern DBMSs can do this Geographic information systems GIS Store and analyze maps weather data and satellite images 5 Examples eCommerce eBay Amazon etc Data warehouses and online analytical processing OLAP systems Extract and analyze useful business information from very large databases Support decision making Real time and active database technology Control industrial and manufacturing processes 6 Database Management System DBMS A suite of programs that allows a user to create and maintain a database You can specify the types of data relationships between various data elements and constraints on what can be stored Metadata describes the data and is stored in the database Also called a data dictionary A schema is the structure of the database 7 Database Management System cont d DBMSs allow you to manipulate the database both the schema and the actual data You can query the database to retrieve information based upon criteria You can update the database which includes adding new information changing existing information and removing records 8 Database Management System cont d DBMSs are generally multi user they support requests from large numbers of users DBMSs generally provide security both in terms of encrypting sensitive data and limiting access to authorized users DBMSs provide a programming interface API to allow you to write programs to interact with the database 9 Practical Stu Available DBs MySQL from Oracle Free upgrade path to Oracle Server You will be using for your project Most lecture examples Microsoft SQL Server The professional version is free to you as a UTD student while the express edition is free to everyone PostgreSQL SQLite 10 Implementation Terminology Table A set of data records of the same format divided into columns that all contain the same kind of data and rows of related records View A composite of a subset of columns from one or more related tables Stored Procedure A set of SQL statements that can be parameterized and executed macro 11 Terminology Relational Algebra Database Relation Table Field Column Tuple Row 12 Data Types Each column field attribute is of a specific data type Data types can include Integers Fixed length strings Variable length strings Floating point numbers Decimals Dates Times BLOBs binary large objects 13 1 2 An Example An Example UNIVERSITY database Information concerning students courses and grades in a university environment Data records tables STUDENT COURSE SECTION GRADE REPORT PREREQUISITE 15 An Example cont d Construct UNIVERSITY database Store data to represent each student course section grade report and prerequisite as a record in appropriate file Relationships among the records Manipulation involves querying and updating 16 An Example cont d A good question to ask when designing a database is What will you want to know from it For example Retrieve the transcript List the names of students who took the section of the Database course offered in fall 2013 and their grades in that section List the prerequisites of the Database course 17 An Example cont d Examples of updates Change the class of Smith to sophomore Create a new section for the Database course for this semester Enter a grade of A for Smith in the Database section of last semester 18 University Database 19 Phases for Designing a Database Requirements specification and analysis Does this sound like software engineering Conceptual design ER Model Chapter 7 EER Model Chapter 8 Logical design Relational Model Chapter 3 Physical design 20 1 3 Characteristics of the Database Approach The Traditional File Approach In traditional file processing Each user defines and implements the files needed for a specific software application This is not strictly true usually we designed suites of applications that used common files However the layout of the files was defined in the code not in the file e g preferences files configuration files 22 The Database Approach Single repository maintains data that is defined once and then accessed by various users and the database contains the layout Self describing nature of a database system Insulation between programs and data and data abstraction Support of multiple views of the data Sharing of data and multiuser transaction processing 23 Self Describing Database Database system contains complete definition of structure and constraints Meta data Database Catalog Describes structure of the database Database catalog used by DBMS software Database users who need information about database structure 24 Database Catalog i e Meta data Example 25 Insulation Between Programs and Data Program data independence Structure of data files is stored in DBMS catalog separately from access programs Program operation independence Operations specified in two parts Interface includes operation name and data types of its arguments Implementation can be changed without affecting the interface 26 Support of Multiple Views of the Data View Subset of the database Contains virtual data derived from the database files but is not explicitly stored Multiuser DBMS Users have a variety of distinct applications Must provide facilities for defining multiple views 27 Sharing of Data and Multiuser Transaction Processing Allow multiple users to access the database at the same time Concurrency control software Ensure that several users trying to update the same data do so in a controlled manner Result of the updates is correct Online transaction processing OLTP application e g filling in forms 28 Sharing of Data and Multiuser Transaction Processing Transaction Central
View Full Document
Unlocking...