Modern database management 11th edition pdf download

If time permits, use Teradata University Network resources to demonstrate the structure and contents of a relational database for some of the textbook datasets. Demonstrate, or lead students through, some simple SQL retrieval exercises against the textbook databases.

Answers to Review Questions 1. Define each of the following key terms: a. Data that have been processed in such a way as to increase the knowledge of the person who uses it. Data that describes the properties or characteristics of end-user data and the context of that data. Database application. An application program or set of related programs that is used to perform a series of database activities create, read, update, and delete on behalf of database users.

Data warehouse. An integrated decision support database whose content is derived from the various operational databases. A rule that cannot be violated by database users. An organized collection of logically related data. Entity A person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. Database management system. A software system that is used to create, maintain, and provide controlled access to user databases.

Systems development life cycle SDLC. A traditional methodology used to develop, maintain, and replace information systems. Agile software development. An approach to database and software development that emphasizes individuals and interactions over processes and tools, working software over comprehensive documentation, customer collaboration over contract negotiation, and response to change over following a plan. Enterprise data model.

The first step in database development, in which the scope and general contents of organizational databases are specified. Conceptual data model or schema. A detailed, technology-independent specification of the overall structure of organizational data. Logical data model or schema. The representation of data for a particular data management technology such as the relational model.

In the case of a relational data model, elements include tables, columns, rows, primary and foreign keys, as well as constraints. Physical data model or schema. There is one physical data model or schema for each logical data model. Match the following terms and definitions: c data b database application l constraint g repository f metadata m data warehouse a information j user view k database management system h data independence e database i enterprise resource systems planning ERP r systems development life cycle o prototyping d enterprise data model q conceptual schema p internal schema n external schema 3.

Contrast the following terms: a. Data dependence; data independence. With data dependence, data descriptions are included with the application programs that use the data, while with data independence the data descriptions are separated from the application programs.

Structured data; unstructured data. Data; information. Data consist of facts, text, and other multimedia objects, while information is data that have been processed in such a way that it can increase the knowledge of the person who uses it. Repository; database. A repository is a centralized storehouse for all data definitions, data relationships, and other system components, while a database is an organized collection of logically related data.

Entity; enterprise data model. An entity is an object or concept that is important to the business, while an enterprise data model is a graphical model that shows the high-level entities for the organization and the relationship among those entities. Data warehouse; ERP system. Both use enterprise level data. Data warehouses store historical data at a chosen level of granularity or detail, and are used for data analysis purposes, to discover relationships and correlations about customers, products, and so forth that may be used in strategic decision making.

ERP systems integrate operational data at the enterprise level, integrating all facets of the business, including marketing, production, sales, and so forth. Two-tier databases; multi-tier databases. Both permit easier data sharing among multiple users than a PC-based database by storing the database and the DBMS on a centralized database server accessible via the network.

A two-tier database houses the business logic and the user interface on the client devices. Multi-tier databases tend to house the user interface on client devices and the business logic may be maintained on multiple server layers to accomplish the business transactions requested by client devices.

Systems development life cycle; prototyping. Both are systems development processes. The SDLC is a methodical, highly structured approach that includes many checks and balances. Consequently, the SDLC is often criticized for the length of time needed until a working system is produced, which occurs only at the end of the process.

Increasingly, organizations use more rapid application development RAD methods, which follow an iterative process of rapidly repeating analysis, design, and implementation steps until you converge on the system the user wants. Prototyping is one of them. In prototyping, a database and its applications are iteratively refined through a close interaction of systems developers and users. Enterprise data model; conceptual data model. In an enterprise data model, the range and contents of the organizational databases are set.

Generally, the enterprise data model represents all of the entities and relationships. The conceptual data model extends the enterprise data model further by combining all of the various user views and then representing the organizational databases using ER diagrams. Prototyping; Agile software development. Agile software development is a method that shares an emphasis on iterative development with the prototyping method yet further emphasizes the people and rapidity of response in its process.

Five disadvantages of file processing systems: a. Program-data dependence b. Duplication of data c. Limited data sharing d. Lengthy development times e. Excessive program maintenance 5. Nine major components in a typical database system environment: a. CASE tools: automated tools used to design databases and database applications.

Repository: centralized storehouse of data definitions. Database management system DBMS : commercial software used to define, create, maintain, and provide controlled access to the database and the repository. Database: organized collection of logically related data. Application programs: computer programs that are used to create and maintain the database. User interface: languages, menus, and other facilities by which users interact with the various system components.

Data administrators: persons who are responsible for the overall information resources of an organization. System developers: persons such as systems analysts and programmers who design new application programs.

End users: persons who add, delete, and modify data in the database and who request information from it. Relationships between tables: Relationships between tables are expressed by identical data values stored in the associated columns of related tables in a relational database.

Definition of data independence: Data independence refers to the separation of data descriptions from the application programs that use the data. Additionally, data independence allows changes to application programs without requiring changes in data storage structure. Program-data independence b.

Minimal data redundancy c. Improved data consistency d. Improved data sharing e. Enforcement standards g. Improved data quality h. Improved data accessibility and responsiveness i. Reduced program maintenance, and j. Improved decision support. Five additional costs or risks of the database approach are: a.

New, specialized personnel b. Installation, management cost, and complexity c. Conversion costs d. Need for explicit backup and recovery, and e. Organizational conflict. Three-tiered database architecture definition: A database architecture that allows the data for a given information system to reside in multiple locations or tiers of computers.

The purpose is to balance various organizational and technical factors. Also, the processing of data may occur at different locations in order to take advantage of the processing speed, ease of use, or ease of programming on different computer platforms. Performs sophisticated calculations and manages the merging of data from multiple sources across the organization—also called the data services tier Possibility of no database on a tier of 3-tiered database?

Yes, it is possible. The end user machine in the client tier — a PC, for example — might have presentation logic but no database installed on it. Five SDLC phases: a. Planning Purpose: To develop a preliminary understanding of the business situation and how information systems might help solve a problem or make an opportunity possible Deliverable: A written request to study the possible changes to an existing system; the development of a new system that addresses an information systems solution to the business problems or opportunities b.

Design Purpose: To elicit and structure all information requirements; to develop all technology and organizational specifications Deliverables: Detailed functional specifications of all data, forms, reports, displays, and processing rules; program and database structures, technology purchases, physical site plans, and organizational redesigns d.

Implementation Purpose: To write programs, build data files, test and install the new system, train users, and finalize documentation Deliverables: Programs that work accurately and according to specifications, documentation, and training materials e.

Maintenance Purpose: To monitor the operation and usefulness of a system; to repair and enhance the system Deliverables: Periodic audits of the system to demonstrate whether the system is accurate and still meets needs Activities and five phases of SDLC?

Database development activities occur in every phase of the SDLC. Actual database development is most intense in the design, implementation, and maintenance steps of the SDLC. The methodologies are considered to be different not because of what is done, but because the timing of the methodologies differ.

The SDLC methodology is methodical and thorough which makes it well-suited for systems that populate and revise databases. Agile software development emphasizes quick responses and rests on high-involvement from knowledgeable customers. A user view or external schema is also independent of database technology, but typically contains a subset of the associated conceptual schema, relevant to a particular user or group of users e. An internal schema consists of both a physical schema and a logical schema.

A logical schema consists of a representation of the data for a type of data management technology. For example, if the relational model is the technology used, then the logical schema will consist of tables, columns, rows, primary keys, foreign keys and constraints. Three-schema architecture: a. Phases and activities of SDLC within textbook scenario: Student answers may vary depending upon whether or not they read the section closely enough to realize that Chris is following a prototyping methodology approach to developing the database application for PVFC.

The prototyping methodology is shown in Figure 8, while the traditional development approach is shown in Figure 7. On the other hand, the Identify Problem phase involves sketching a preliminary data model, which is work that Chris clearly completes.

Designing the Database Develop initial prototype Logical Database Design, Physical database design and definition, database implementation - Chris takes the knowledge he has gained from the initial sessions with Helen and begins to build a functioning example of the database in an agreed-upon relational database management system. Using the Database Implement and use prototype; Revise and enhance prototype Database maintenance - Chris provided enough of a working sample database that Helen could use it and make suggestions about how to revise it.

Chris could iteratively make changes to improve the solution, and move some initial ad-hoc queries into more formal reports. Why does PVFC need a data warehouse? Pine Valley Furniture Company PVFC uses a database management system to support its operational functions but this database is not structured in a way that supports timely analysis of trends or historical patterns.

They are also being used to improve employee relationship management by tracking employee skills and sending notice when an internal job opportunity that needs a particular skill that the employee possesses is announced. Online shopping sites are able to carry a large virtual inventory stored in a database for the customer to peruse. Solutions to Problems and Exercises 1. Advanced data types have several special requirements: a.

Storage requirements — multimedia objects such as images, sound, and video clips require substantial storage capacity, which needs to be justified. Content management — this is the problem of storing, locating, and retrieving the multimedia objects.

This process requires specialized software not generally available in a relational DBMS or extra effort to create a means to rapidly access multimedia objects such as keyword indexes. Maintenance — while conventional relational data are easily updated, multimedia objects may require maintaining multiple versions of the data.

Usually the whole object needs to be restored because it is treated as a whole rather than a set of parts. Metadata for Class Roster: Please note that some columns have been omitted in order to save space.

Why do organizations create multiple databases? There are several reasons. First, because of resource limitations, organizations fund development of their information systems one application at a time. Second, organizations may acquire some of their information systems from outside vendors. This also results in a proliferation of databases.

Third, mergers and acquisitions generally result in multiple databases. What organizational and personal factors lead an organization to have multiple, independently managed databases? Perhaps the most common reason is that end-user groups develop their own database applications, rather than wait for the central IS organization to develop a centralized database. Also the pressures associated with rapid business change result in organizations taking a short-term, suboptimal approach rather than a careful, long-term strategy.

Data entities and Enterprise Data Model for student organization or group: This is a good in-class, interactive exercise for individuals or small groups. A photo image of the driver: unstructured data d.

The make and serial number of the scanning device that was used to scan the fingerprint: structured data f. The resolution in megapixels of the camera that was used to photograph the driver: metadata; fact describing context g. Great Lakes Insurance database suggestion: One suggested approach would be to create an enterprise database to contain all information about customers, policies, etc. The need for an enterprise database is clear, since policy information would need to be accessed not just by the sales team but also by the actuarial department and the claims department.

For inside agents, access to the database would be through an intranet, utilizing a browser-based application as the front- end. Each outside agent would have a personal database on his or her notebook computer with only information for his or her territory.

The personal database would then be synchronized periodically with the enterprise database through the use of an extranet. Pet Store data model questions: a. There could be a relationship between customer and store. It would be useful if the customer had never purchased a pet, so for example the store could send mailings to prospective customers.

Problems that could arise because of this duplication are that payments may not be properly matched to vendor orders, or that customer receipts are improperly matched to customer bills. These potential mismatches could cause issues in collection and payment of financial transactions for the organization, and may cause issues with relationships with customers and vendors. At a first glance, these duplications appear to violate the principles of the database approach outlined in this chapter. These checks-and- balances are not apparent on this Figure, and might compensate for the apparent violation of database approach principles.

Representation of SDLC: The representation of the systems development life cycle has changed from the original waterfall metaphor. While it is a more compact representation, there are still some problems.

For example, it is not purely linear. Also, it is possible to conduct steps in parallel due to time overlaps. One additional problem is the inability to go back from one step to another without completing the entire five-step process Consider Business Enterprise example: a. Benefits from a multiple-tiered architecture for data? Considerations for using a multi-tier architecture: Since much of the data may be updated from a large number of different functions, network traffic will be an issue of crucial importance.

Processing close to the source data could reduce network traffic. Client technologies however, can be mixed personal computers with Intel or Motorola processors, network computers, information kiosks, etc.

In addition, you can change technologies at any tier with limited impact on the system modules on other tiers. All this will allow for data consistency and maintaining academic standards — a critical success factor for the academic unit.

Contrasting database development during conceptual data modeling and logical database design phases: It is often said that conceptual data modeling is done in a top-down fashion, driven from a general understanding of the business area, not from specific information processing activities. Logical database design approaches database development from two perspectives.

First, the conceptual data model is transformed into a standard notation through normalization, based on relational database theory. This bottom-up analysis verifies exactly what data are to be maintained in the database and the nature of those data as needed for each transaction, report, and so forth. During logical database design, you combine or integrate the original conceptual data model more general information along with the individual user views more specific information into a comprehensive design.

Location of prototype database: Department databases are often developed in combination by end users, systems professionals working in business units, and central database professionals. A combination of people is necessary since a wide variety of issues must be balanced in the design of shared databases: processing speed, ease of use, differences in data definitions, and the like.

This answer assumes that the organization maintains a development and production server for the databases. Enterprise data models and conceptual data models: Enterprise data modeling results in a total picture or explanation of organizational data, not in the design for a particular database.

A particular database provides the data for one or more information systems, whereas an enterprise data model, which may encompass many databases, describes the scope of data maintained by the organization.

Therefore, the level of abstraction associated with an enterprise data model is higher; conceptual data models will be more detailed. Iteration between physical and logical database design: Database development activities occur in each of the SDLC phases, and feedback may occur which causes a project to return to a prior phase. SDLC activities may find missing elements or errors when designing specific transactions, reports, displays, and inquiries.

When a missing element is noticed, for example, it is necessary to revisit the logical database design. User views of organizational data: A good approach in developing this problem for a bank might be to carefully select the views to be developed by collecting a transaction slip, monthly statement representing each type of account , statement of earnings, etc.

Examples of data included in each message are customer information, bank information, and transaction data checks, deposits, service charges, maintenance fees, overdraft protection fees, and so forth. Students who do not have the prerequisite will be dropped from the course. However, you are advised to make an appointment by e-mail or by telephone before coming to ensure that the professor is not unavoidably absent. Ryerson requires that any official or formal electronic communications from students be sent from their official Ryerson E- mail account.

As such emails from other addresses may not be responded to. Normalization and Entity Relationship modeling are the two primary methods of analysis taught in the course. The course will utilize state of the art data base design tools and relational database software. To acquire a competency in developing SQL views and queries to extract relevant data to meet business information requirements. Understand and articulate the Relational Database concept and its role in business 2.

Analyze data requirements for the database 3. Construct an Entity Relationship Diagram 4. Understand and apply the rules of Normalisation 6. Develop query commands in SQL at an advanced level 7.

Design and implement Views using SQL 8. Be competent in the use of relevant industry representative software 6. In addition, in order to pass this course, students must pass the final examination. Grades on final exams must be posted.

However, as there may be other consideration in the determination of final grades, students will receive their official final grade in the course only from the Registrar. If grades are posted in hard copy they must be posted numerically sorted by student identification number after at least the first four digits have been removed.