Chapter One Introduction to Database Processing
·
u ANSWERS TO GROUP I QUESTIONS
1.1 Why is database processing more important today than ever?
In recent years the explosion of the Internet and the dramatic development of new technology for the Internet have made knowledge of database technology one of the hottest career paths.
1.2 State the purpose of a database.
The purpose of a database is to help people and organizations keep track of things.
1.3 Summarize problems that can occur when using a list to keep track of some-thing.
Problems occur when changes are made to the list. If multiple copies of data occur in the list, the change must be made to each copy of the data.
Problems occur when data is delete data. It is possible to delete data about one object in the list and loose data about a second or third object in the list.
Problems occur when we want to add data to the list. If some data in the list is missing, we may not know exactly how or where to store the data that we do have.
Lists can also contain inconsistencies. If data is stored twice but the data does not have the same value, we cannot tell which value is correct. Sometimes what appears to be an inconsistency may actually be data that represents two different meanings of the data.
When data is missing from a list we cannot tell if the missing data means that the data has no meaning for a particular row in the list of whether we simply do not know what the value of the data should be.
Problems also occur when we share data in a list. These problems involve such things as who determines the format for the list and even the data within the list, who is allowed to modify, delete, or add items to the list, and who is allowed to see the list.
Problems also occur in lists because lists typically contain data about many different themes.
1.4 Based on your answer to question 1.3, when do you think it is appropriate to use a list to keep track of something?
A list may be appropriate when the list will contain data about a single theme and the data is not shared between multiple users that with different objectives for the list.
Use the following list when answering questions 1.5 to 1.9:
Adviser Adviser Phone Student Major Dept Head
Jones 221.2345 Parks Accounting Greene
KwaiI 223.4444 Stein Info Systems Masters
Rosenbloom 281.3944 Johnson Accounting Greene
Rosenbloom 281.3944 Gonzales Accounting Greene
Jones 221.2345 Rickey Accounting Greene
1.5 Describe problems that can occur when an adviser changes his or her phone number.
Using Jones as an example, if you do not change Jones’ phone number in both the first and last lines, you cannot tell which phone number is the correct phone number for Jones. In this example, since the phone numbers would be different, you may not know that the Jones in the last line is the same Jones as the one in the first line.
1.6 Suppose student Stein drops out of school, and the second row is deleted. Describe the side effects of this deletion.
The first problem would be that you would loose advisor Kwail phone number. You would also loose the fact that there is an advisor name Kwail. It also appears that the Dept Head column contains the name of the department head for the Major. Since Stein is the only Info Systems major, you would lose the name of the department head for the entire major. (you would also lose the fact that the school offers an Info Systems major)
1.7 How do we use this list to record the fact that Smith is the department head of the computer science department?
To record this fact you would need to add a row of data to the list. The row would only contain data for the Major (Computer Science) and the Dept Head (Smith). Since, at this point, there are no students that you know of there would be no data for the Student column. Since there is no student there would also be no Advisor or Advisor Phone.
Even though this can be done to satisfy the requirement not that it may make the list appear to provide information that is incorrect. For example, it may make someone think that there are really six students instead of five, we just do not know the name of the sixth student.
1.8 Suppose the entry in the last row, last column is changed from Greene to Abernathy. What problems will then occur in interpreting this list?
As it currently stands, you should be able to determine that the department head for the Accounting major is Green. If you make the suggested change, it will appear that the Accounting major has two different department heads. Would this mean that Greene was the department head when Parks, Johnson, and Gonzales declared the Accounting major and Abernathy was the department head when Rickey declared the major? If so, who is the current department head or are there two department heads? If there is only one department head, which one is the correct department head, Greene or Abernathy?
1.9 Change this list to a set of three interrelated tables. Use the technique shown in Figure 1-4 for representing relationships.
1.10 Explain how none of the problems encountered in questions 1.5 to 1.8 can occur with the tables developed in your answer to question 1.9.
Since Jones is stored in the Advisor table Jones appears only once and can have only one phone number. If there are two different Jones’, they would both be in the advisor table with different phone numbers and different Ids. The different Ids would indicate that they are different people.
If Stein drops out of school you would delete Stein’s row in the student table. This deletion would have no effect on the Advisor table and the major table. No information from these tables would be lost.
You can add Smith as the department head of the Computer Science major by adding a row to the Major table. There would be no affect on the Student or Advisor table.
As the table currently stands, the change in question 1.8 would be made in the major table. Since the Info Systems major exists only once in this table there would be only one change to be made. Using these tables, the Dept Head column could only represent the current department head.
1.11 Using the SQL statement on page xxx as an example, guess at a similar statement for joining together the tables in your answer to question 1.9. If any of your column names have spaces in them, put the column names in brackets [ ]. Because there is no computation in your result, you will not need an expression like [Daily Rate]*[Days] As Charge in your answer.
SELECT ADVISOR.Advisor, ADVISOR.[Advisor Phone], STUDENT.Student, MAJOR.Major, MAJOR.[Dept Head)
FROM ADVISOR, MAJOR, STUDENT
WHERE ADVISOR.ID = STUDENT.AdvisorID
AND STUDENT.MajorID = MAJOR.ID
1.12 Name the four components of a database system.
Figure 1-6 User, Database Application, Database Management System, Database
1.13 List the functions of a database application.
• Create and Process Forms
• Create and Transmit Queries
• Create and Process Reports
• Execute Application Logic
• Control Application
1.14 What does DBMS stand for? List the functions of a DBMS.
DBMS stands for Database Management System
The functions of the DBMS are:
• Create Database
• Create Tables
• Create Supporting Structures
• Read Database Data
• Update Database Data
• Maintain Database Structures
• Enforce Rules
• Control Concurrency
• Provide Security
• Perform Backup and Recovery
1.15 Define the term database.
In the most general case, a database is a self-describing collection of related records. For all relational databases (almost all databases today, and the only type we consider in this book), we can modify this definition to say a database is a self-describing collection of related tables.
1.16 List the contents of a database.
• User Data
• Metadata
• Indexes and Related Structures
• Stored Procedures
• Triggers
• Application Metadata
1.17 What is metadata? Give an example.
Data about the structure of a database is called metadata. Examples of metadata are the names of tables, the names of columns and the tables to which they belong; properties of tables and columns; and so forth.
1.18 What is the difference between a stored procedure and a trigger?
A stored procedure is a program that is stored within the database. Some stored procedures are utility programs for the database.
A trigger is a procedure that is executed when a particular data activity occurs.
1.19 Explain the differences between personal, workgroup, and organizational databases.
Personal: Mary Richards Housepainting: single-user databases with a relatively small amount of data—say, fewer than 10 megabytes. The forms and reports for these databases are generally simple and straightforward.
Workgroup: Treble Clef Music; they have more than one user, but usually fewer than 20 or 30 users altogether. They contain a moderate amount of data—say, 50 or 100 megabytes. The forms and reports need to be complex enough to support several different business functions.
Organizational: The largest databases are like those in the auto registration case, which have hundreds of users and trillions of bytes of data. Many different applications are in use, with each application having its own forms and reports. The characteristics of these types of databases are summarized in Figure 1-16.
1.20 Summarize the tasks in the requirements phase of the development on a database system.
The tasks to be completed in the requirements phase of the development of a database are to:
Build data model
Specify data items
Define constraints and rules
Determine application requirements
1.21 What is a data model? Why are such models important?
A data model is a logical representation of the structure of the database. Data modeling is very important because the design of the database and all of its structures depend on the data model. If the data model is incorrect, the result will be waste, aggravation, delays, and frustration.
1.22 Summarize the tasks in the design phase of the development on a database system.
In the design phase of the development of a database you must design the:
Tables
Relationships
Indexes
Constraints
Stored procedures and triggers
Forms
Reports
Queries
Application code
1.23 Summarize the tasks in the implementation phase of the development on a database system.
In the implementation phase of the development of a database you must:
Create tables
Create relationships
Create constraints
Write stored procedures and triggers
Fill the database
Create forms
Create reports
Create queries
Write application code
Test
1.24 Name two data models that preceded the relational model.
IBM developed and promoted DL/I, or Data Language One, which modeled database data in the form of hierarchies or trees.
CODASYL, the group that developed the standards for the COBOL language, created a model in the 1970s called the DBTG (Data Base Task Group) Model. The DBTG model could readily represent both hierarchies and networks. This model was at one time proposed as a national standard, but was never adopted primarily because of its complexity.
1.25 Describe the disadvantages of the two data models in your answer to question.
The DL/1 model was not truly general-purpose. Representing non-hierarchical network data was cumbersome.
The CODASYL model’s primary disadvantage was its complexity.
1.26 Who first proposed the relational model?
E.F. Codd proposed the relational model in 1970. Codd worked for IBM and after 10 years of research, development, and corporate lobbying, he and others convinced IBM to develop DBMS products based on the relational model.
1.27 How did personal DBMS products influence the development of organizational DBMS products?
Because there was plenty of computing power available on personal computers, personal DBMS products were able to create more graphical user interfaces. Over time, the influence of these products changed the interfaces of larger organizational DBMS products as well.
1.28 Name three early personal DBMS products.
Three early personal DBMS products were dBase, a product marketed by the Ashton-Tate Corporation, R:base from the Microrim Corporation, and Paradox from Borland.
1.29 What is the purpose of an OODBMS?
The goal of OODBMS products was to be able to store object-oriented programming objects (such as those in C++ or Java) in a database without having to transform them into relational format.
1.30 Why have OODBMS not been commercially successful?
Their use requires organizations to transform their databases from relational to OODBMS format. Also, most large organizations have older applications that are not based on object-oriented programming. Somehow, the programs in these applications would need to be accommodated by the new OODBMS. Thus, the high cost of migrating existing databases and information systems from relational DBMS to OODMBS has prohibited their wide use.
1.31 What is an object-relational DBMS?
Object-relational DBMS products, such as Oracle 8i and 9i, have been developed that allow both relational and object views of data on the same database.
1.32 What technology is at the leading edge of the database field today?
Recent years have seen the introduction and use of XML, which is a technology that originated to support Web sites, but has since been extended to provide important solutions to database problems. The integration of database technology and XML is the leading edge of the database field today and will be important for many years in the future.
u ANSWERS TO GROUP II QUESTIONS
1.33 Suppose that the New Orleans Wooden Boat Society (a fictitious organization) publishes a monthly newsletter for which it charges $35 per year. Assume they keep the following data in the form of list in a spreadsheet:
Name, Street, ApartmentNo, City, State, Zip, StartDate, EndDate, AmountPaid
What problems are likely to occur when maintaining this data as a list?
Suppose a library wanted to purchase 3 subscriptions to the newsletter. You would need to make three different entries for the library to know that three copies must be sent and that the library paid $35 for each subscription. If you simply made one entry for $105 being the AmountPaid, you may not know that three copies of the newsletter must be mailed. If this were the only library in the list you may think that a subscription cost $105 when purchased by a library but everyone else only pays 35. If three entries were made and the Street name was changed, all three entries must be changed.