Define: data
known facts that can be recorded and that have implicit meaning
Define: database
a collection of related data
Define: DBMS
DataBase Management System; a computerized system that enables users to create and maintain a database; a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications
Define: database system
the database and the DBMS software together
Define: database catalog
the part of a DB system that contains a complete definition or description of the DB structure and constraints (meta-data);
contains info such as:
* structure of each file
* type and storage format of each data item
* various constraints on the data
Define: program-data independence
a property that describes how the structure of data files can be stored in the DBMS catalog separately from the access programs
Define: user view
a perspective or view of the DB tailored to meet the requirements of a specific user; a view may be a subset of the DB or it may contain virtual data that is derived from the DB files but is not explicitly stored
Define: DBA
DataBase Administrator; an ‘actor on the scene’ who is responsible for authorizing access to the DB, coordinating and monitoring its use, and acquiring software and hardware resources as needed; accountable for problems such as security breaches and poor system response time
Define: end user
the people whose jobs require access to the DB for querying, updating, and generating reports; the DB primarily exists for their use;
categories:
* casual end users: occasionally access DB, may need diff info each time, typically mid/high-level managers or occasional browsers
* naive/parametric users: main job function revolves around constantly querying/updating the DB using canned transactions; ex: bank customers/tellers, airline/hotel/rental car reservation agents/customers, employees at receiving stations for shipping companies, social media users
* sophisticated end users: engineers, scientists, business analysts, etc who thoroughly familiarize themselves with the facilities of DBMS in order to implement their own apps to meet their complex requirements
* standalone users: maintain personal DBs by using ready-made program pkgs that provide easy-to-use menu-based interfaces/GUIs
Define: canned transaction
a standard type of query or update used by naive or parametric end users; has been carefully programmed and tested; now often available as mobile app on mobile devices;
ex:
* check bank account balance, post withdraws/deposits
* check availability for airline/hotel/car rental request, make reservation
* update central DB of received/in-transit packages at shipping company receiving station
* post and read items on social media website
Define: deductive database system
a DB system that provides capabilities for defining deduction rules for inferencing new info from the stored DB facts (ex: triggers, sprocs, …active DB systems?)
Define: persistent object
a complex object (from an OO programming language [i.e. C++, Java]) that is stored permanently in an OO DBMS; survives the termination of program execution and can later be directly retrieved by another program
Define: meta-data
the info stored in a DB catalog; describes the structure of the primary DB; a complete definition or description of the DB structure and constraints (meta-data);
contains info such as:
* structure of each file
* type and storage format of each data item
* various constraints on the data
Define: transaction-processing application
an application that accesses the DB by sending queries or requests for data to the DBMS; (transaction: an executing program or process that includes one or more DB accesses such as reading or updating DB records); (DBMS must ensure that concurrent transactions operate correctly and efficiently; DBMS must enforce isolation and atomicity)
What four main types of actions involve databases?
???
- Defining a DB: specifying data types, structures, and constraints of the data to be stored
- Constructing the DB: process of storing the data on some storage medium that is controlled by the DBMS
- Manipulating a DB: includes functions such as querying the DB to retrieve specific data, updating the DB to reflect changes in the miniworld, generating reports from the data
- Sharing a DB: allows multiple users and programs to access the DB simultaneously
Discuss the main characteristics of the database approach and how it differs from traditional file systems.
main characteristics of DB approach:
- self-describing nature of a DB system; can access diverse DBs by extracting and using DB definitions from the catalog
- vs file processing: data definition is part of the app programs themselves, programs are constrained to work with only one specific DB
- insulation between programs and data, and data abstraction; structure of data files is stored in DBMS catalog separately from access programs; program-data independence; program-operation independence
- vs file processing: structure of data files is embedded in app programs, so any changes to structure of file can require changing all programs that access that file
- support of multiple views of the data
- vs file processing: ???
- sharing of data and multiuser transaction processing; requires DBMS concurrency control software to ensure that several users trying to update the same data do so in a controlled manner so that the result of the updates is correct; DBMS must ensure isolation and atomicity
- vs file processing: ???
What are the responsibilities of the DBA and the database designers?
DBA: DataBase Administrator; an ‘actor on the scene’ who is responsible for authorizing access to the DB, coordinating and monitoring its use, and acquiring software and hardware resources as needed; accountable for problems such as security breaches and poor system response time
Database Designers: ‘actors on the scene’ responsible for identifying the data to be stored in the DB and choosing appropriate structures to represent and store this data; communicate with all prospective DB users in order to understand their requirements and to create a design that meets these requirements; typically interact with each potential group of users and develop views of the DB that meet the data and processing requirements of these groups
both: choosing which indexes to create and maintain for query processing and optimization (part of physical DB design and tuning)
Explain the roles of a system analyst and an application programmer in a database system.
System analyst: determine requirements of end users, especially naive/parametric end users; develop specs for standard canned transactions that meet these requirements
Application programmer: implement specs from system analyst as programs; test, debug, document, maintain these canned transactions
system analysts, app programmers: aka software developers, software engineers; should be familiar with full range of capabilities provided by DBMS to accomplish their tasks
Discuss the advantages of using the DBMS approach.
- controlling redundancy
- restricting unauthorized access
- providing persistent storage for program objects
- providing storage structures and search techniques for efficient query processing
- providing backup and recovery
- providing multiple user interfaces
- representing complex relationships among data
- enforcing integrity constraints
- permitting inferencing and actions using rules and triggers
- additional implications of using the DB approach (can benefit most organizations):
- potential for enforcing standards
- reduced app development time (“Development time using a DBMS is estimated to be one-sixth to one-fourth of that for a file system.”)
- flexibility
- availability of up-to-date information
- economies of scale
Under what circumstances is it desirable to develop customized database applications?
- simple, well-defined DB apps that are not expected to change at all
- stringent, real-time requirements for some app programs that may not be met because of DBMS overhead
- embedded systems with limited storage capacity, where a general-purpose DBMS would not fit
- no multiple-user access to data
Identify some informal queries and update operations that you would expect to apply to the database shown in Figure 1.2.
- SELECT all students with a given major/year
- SELECT all courses in a given department
- SELECT all students in a given course
- SELECT all courses taught in a given semester in a given year
- SELECT all students with grade in (C, F) (academic probation)
- SELECT all the prerequisites for a given course number/all the courses for which a given course number is a prerequisite
- UPDATE class for all students after end of year
- UPDATE coursenumber when Department name changes
- UPDATE section IDs for given course number
- UPDATE grade for a student after they retake an exam
- UPDATE prerequisite course number when coursenumber changes
- INSERT new students, new courses, new sections, new grade reports for students in sections, new prerequisites when new course added or new prerequisite decided to count
Discuss the various storage structures and search techniques used for efficient query processing.
- indexes: usually tree or hash data structures designed to speed up disk search
- caching/buffering module: controls copying DB records from disk to main memory buffers for processing
- query processing and optimization module: chooses an efficient query execution plan for each query based on the existing storage structures
Specify all the relationships among the records of the database shown in Figure 1.2.
- student enrolled in a section (of a course)
- section group of a course
- student has a grade in a section (of a course)
- course can be a prerequisite of another course
- courses belong to a department
- student has a major with one of the departments
- student can enroll in a section of a course if they have already taken the prerequiste course(s)
- student can enroll in a section of a course if the section is offered during the current semester and year
Give some additional views that may be needed by other user groups for the database shown in Figure 1.2.
- SELECT current courses that a given student is taking
- SELECT all courses+sections offered during given semester/year
- SELECT all prerequisite courses for a given course
- SELECT all grades for all students in a given section of a course
- SELECT all grades for a given student
- SELECT all courses(+sections) from a given department
- SELECT all students with a given grade (A for honor roll, F for AP)
