CPS352 Lecture - Course Introduction; Fundamental DBMS ConceptsLast revised July 24 , 2008Objectives:1. To introduce the syllabus and key issues of the course - showing how they are related.2. To contrast file-processing systems and DBMS-based systems.3. To introduce key DBMS concepts and terminologyMaterials:1. Course syllabus2. Example database (intro); handout of SQL used for it [ test run examples on it ]Course IntroductionDistribute, go over syllabus. Mention that we will be relating key ideas to the course schedule as we go through this lecture.I. File Processing Systems vs Database Management SystemsA. At the outset of our course on database management systems, we want to carefully distinguish two different approaches to meeting the information processing needs of an organization: a file-processing approach versus a database management system approach.1. Historically, these two approaches evolved successively. a) Early computer applications were always developed using the file processing approach, because that was the only approach known.b) The DBMS approach was developed in the 1960's, and has come to be used in a variety of application areas - many falling into the broad category of "business data processing", but for other areas as well.2. Though we will distinguish these approaches, we don't want to treat them as totally mutually exclusive. In fact, for many applications, the file processing approach is the best way to go. (Word processing and spreadsheets, for example, work this way) Both approaches can be used at various points in the information processing activities of an organization; each is legitimate in its own proper domain.1B. Regardless of which approach is used, an organization's needs are going to be met by a collection of files containing its operational data and programs that manipulate this data.1. We distinguish between operational data that is relatively permanent and transient input/output data.Example: In a payroll application, the operational data includes the basic employee data such as name, SSN, rate of pay etc., plus year-to-date totals for income, taxes etc. This data is retained in storage permanently. (This is distinct from transient data like weekly time card information, which is discarded once processed.)2. In either case, we call the collection of files that together contain the organization's operational data its DATABASE.3. What distinguishes the file processing approach from the DBMS approach is how the programs relate to the operational data. (A file processing approach is often still used with transactional data.)C. We might say that a file-processing approach is characterized by a close relationship between programs and data.1. Each program is written to process a certain file or group of files and must embody detailed knowledge about the structure of each file it uses.Example: Consider a program that prints mailing labels to send a mailing to each customer on an organization's customer list. This program presumably works with a customer file, and must, therefore, incorporate knowledge about how the customer file is structured.a) Suppose that the customer file consists of one record per customer, with each record containing customer name, address, city, state, ZIP, plus the name of the salesman handling the account, the total sales to the customer this year to date, and a flag indicating what kind of mailings the customer might receive.b) Each program accessing this file would have to incorporate this information in some language-appropriate way - e.g. as a C/C++ struct declaration:2struct{ char[30] name; char[30] address; char[20] city; char[2] state; int zip; char[20] salesman; float ytd_sales; char mail_code;} Customer;2. As a corollary, any change in the structure of the file will necessitate a change in the program.Example: Suppose the company decides to adopt a policy of giving different discounts to different customers. This might call for adding a discount field to the customer file. Of course, this change does not affect the mailing list directly; nonetheless, the mailing list program must be modified to reflect the changed file structure.3. To avoid this unintended coupling between unrelated programs, it is common to design file-processing type systems so that each application area “owns” its own files.Example: We might maintain separate customer files for the mailing list application and the billing applications. The former would include only customer name, address, city, state, ZIP, and mailing code. The latter would certainly include name, salesman, YTD sales, and the discount. It might also include the address information so that the bills can be mailed. But it would not include the mail list code.4. File-processing based systems, then, tend to be characterized by a proliferation of application-specific files, each with its own format. Certain data items are stored redunantly - i.e. in more than one place in the database. This, however, creates new problems:ASK CLASSa) Wasted storage (becoming less of a problem as storage costs go down, but still a concern, especially when one thinks of backup using a network.)3b) Update problems: when an item of information has to be changed, it may need to be changed in several different places in the database. This means extra work each time an update has to be done.c) Inconsistency problems: over time, it is possible that the database may contain two different values for the same data item in two different places, because some update operation did not catch all of the places that need to be changed. This causes confusion.Example: Gordon's first computerized registration system maintained a separate student file for each academic term. Each file contained various personal data on the student, the name of his advisor, and a list of the courses he/she was enrolled in that term. The file also contained space to record the grades for each course taken, though of course these slots would not be filled in until after the end of the term.(1) As registration time for a new term approached, the computer center would copy data from the current term's file into a file for the new term, blanking out the list of courses registered for but leaving all else intact.(2) At some point in time, the registrar's office could have three different files active:(a) The term just completed, awaiting the posting of grades and printing of