Solution Manual for Database Systems: A Practical Approach to Design, Implementation, and Management, 6th Edition

Preview (31 of 470 Pages)

100%

Purchase to unlock

Loading page image...

SOLUTIONS TO REVIEW QUESTIONSAND EXERCISESFOR PART 1–BACKGROUND (CHAPTERS 1–3)

Loading page image...

Database Systems: Instructor’s Guide-Part III2Solutions to Review Questions and ExercisesChapter 1 Introduction to Databases................................................................................................................3Chapter 2 Database Environment.....................................................................................................................7Chapter 3 Database Architectures and the Web.............................................................................................11

Loading page image...

Database Systems: Instructor’s Guide-Part III3Chapter 1 Introduction to DatabasesReview Questions1.1List four examples of database systems other than those listed in Section 1.1.Some examples could be:•A system that maintains component part details for a car manufacturer;•An advertising company keeping details of all clients and adverts placed with them;•A training company keeping course information and participants’ details;•An organization maintaining all sales order information.1.2Discuss each of the following terms:DataFor end users, this constitutes all the different values connected with thevarious objects/entities that are of concern to them. See also Section 1.3.4.DatabaseSee Section 1.3.1Database Management SystemSee Section 1.3.2Database Application ProgramSee Section 1.3.3Data IndependenceThis is essentially the separation of underlying file structures from theprograms that operate on them, also called program-data independence. Seealso Sections 1.2.2 and 1.3.1.SecurityThe protection of the database from unauthorized users, which may involvepasswords and access restrictions. See also Section 1.6.IntegrityThe maintenance of the validity and consistency of the database by use ofparticular constraints that are applied to the data. See also Section 1.6.ViewsThese present only a subset of the database that is of particular interest to auser. Views can be customized, for example, field names may change, andthey also provide a level of security preventing users from seeing certaindata. See also Section 1.3.3.1.3Describe the approach taken to the handling of data in the early file-based systems. Discuss thedisadvantages of this approach.Focus was on applications for which programs would be written, and all the data required wouldbe stored in a file or files owned by the programs.See also Section 1.2.Clearly, each program was responsible for only its own data, which could be repeated in otherprogram’s data files. Different programs could be written in different languages, and would not beable to access another program’s files. This would be true even for those programs written in thesame language, because a program needs to know the file structure before it can access it. See alsoSection 1.2.2.1.4Describe the main characteristics of the database approach and contrast it with the file-basedapproach.

Loading page image...

Database Systems: Instructor’s Guide-Part III4Focus is now on the data first, and then the applications. The structure of the data is now keptseparate from the programs that operate on the data. This is held in the system catalog or datadictionary. Programs can now share data, which is no longer fragmented. There is also a reductionin redundancy, and achievementof program-data independence. See also Section 1.3.1.5Describe the five components of the DBMS environment and discuss how they relate to eachother.See Section 1.3.3.1.6Discuss the roles of the following personnel in the database environment:Data AdministratorSee Section 1.4.1Database AdministratorSee Section 1.4.1Logical Database DesignerSee Section 1.4.2Physical Database DesignerSee Section 1.4.2Application DeveloperSee Section 1.4.3End-UsersSee Section 1.4.41.7Discuss the three generations of DBMSs.The CODASYL and hierarchical approaches represented thefirst generationof DBMSs. Theywerebasedontheconcept thatsmallercomponents cometogetheras parts oflargercomponents, and so on, until the final product is assembled. This structure, which conforms toan upsidedown tree, is also known as ahierarchical structure.Relational DBMSs are referred to assecond-generationDBMSs.In 1970, E. F. Codd of theIBM Research Laboratory produced his highly influentialpaper on the relational data model(“A relational model of data for largeshared data banks,” Codd, 1970). This paper was verytimelyandaddressedthedisadvantagesoftheformerapproaches.Manyexperimentalrelational DBMSswere implemented thereafter.In response to the increasing complexity of database applications, two “new” systemshaveemerged:theobject-orientedDBMS(OODBMS)andtheobject-relationalDBMS(ORDBMS). However, unlike previous models, the actualcomposition of these models is notclear. This evolution representsthirdgenerationDBMSs.1.8Discuss the advantages and disadvantages of database management systems.See Section 1.6

Loading page image...

Database Systems: Instructor’s Guide-Part III5Exercises1.9Interview some users of database systems. Which DBMS features do they find most useful andwhy? Which DBMS facilities do they find least useful and why? What do these users perceive tobe the advantages and disadvantages of the DBMS?Select a variety of users for a particular DBMS. If the users are using different DBMSs, group theanswers for the different systems, which will give an overall picture of specific systems.1.10Write a small program (using pseudocode, if necessary) that allows entry and display of clientdetails including a client number, name, address, telephone number, preferred number of rooms,and maximum rent. The details should be stored in a file. Enter a few records and display thedetails. Now repeat this process but rather than writing a special program, use any DBMS thatyou have access to. What can you conclude from these two approaches?The programcan bewrittenin anyappropriateprogramminglanguage,suchas Pascal,FORTRAN, C. It should adhere to basic software engineering principles including being well-structured, modular, and suitably commented. It is important to appreciate the process involvedeven in developing a small program such as this. The DBMS facilities to structure, store, andretrieve data are used to the same effect. The differences in the approaches, such as the effortinvolved, potential for extension, ability to share the data should be noted.1.11Study the DreamHome case study presented in Section 10.4 and Appendix A. In what ways woulda DBMS help this organization? What data can you identify that needs to be represented in thedatabase? What relationships exist between the data? What queries do you think are required?It may be useful to review the file-based approach and the database approach here before tacklingthe first part of the exercise. Careful reading and thinking about how people might use theapplications should help in carrying out the rest of the exercise.1.12Study the Wellmeadows Hospital case study presented in Appendix B.3. In what ways would aDBMS help this organization? What data can you identify that needs to be represented in thedatabase? What relationships exist between the data?The approach used for Exercise 1.10 should be used for this exercise also.1.13Discuss what you consider to be the three most important advantages for the use of a DBMS for acompany likeDreamHomeand provide a justification for your selection. Discuss what youconsider to be the three most important disadvantages for the use of a DBMS for a company likeDreamHomeand provide a justification for your selection.Students should review section 1.6 to make a list of advantages and disadvantages of using aDBMS.1.14Using any Web browser, look at some of the following Web pages and discover the wealth ofinformation available there:

Loading page image...

Database Systems: Instructor’s Guide-Part III6(a) http://www.oracle.com(b) http://www.microsoft.com/sql and http://www.microsoft.com/access(c) http://www.ibm.com/db2(d) http://www.mysql.com(e) http://en.wikipedia.org/wiki/database andhttp://en.wikipedia.org/wiki/DBMSStudents should visit the Web pages listed above to gainunderstanding aboutthe type ofinformation covered on each Web site.Pagesare the major database vendor Web sites while thefinal two links provide information on databases and DBMS.

Loading page image...

Database Systems: Instructor’s Guide-Part III7Chapter 2 Database EnvironmentReview Questions2.1Discuss the concept of data independence and explain its importance in a database environment.See Section 2.1.52.2To address the issue of data independence, the ANSI-SPARC three-level architecture wasproposed. Compare and contrast the three levels of this model.See Section 2.12.3What is a data model? Discuss the main types of data models.An integrated collection of concepts for describing and manipulating data, relationships betweendata, and constraints on the data in an organization. See Section 2.3.Object-based data models such as the Entity-Relationship model (see Section 2.3.1). Record-based data models such as the relational data model, network data model, and hierarchical datamodel (see Section 2.3.2). Physical data modelsdescribe how data is stored in the computer (seeSection 2.3.3).2.4Discuss the function and importance of conceptual modeling.See Section 2.3.4.2.5Describe the types of facility you would expect to be provided in a multi-user DBMS.Data Storage, Retrieval and UpdateAuthorization ServicesA User-Accessible CatalogSupport for Data CommunicationTransaction SupportIntegrity ServicesConcurrency Control ServicesServices to Promote Data IndependenceRecovery ServicesUtility ServicesSee also Section 2.42.6Of the facilitiesdescribed in your answer to Question 2.5, which ones do you think wouldnotbeneeded in a standalone PC DBMS? Provide justification for your answer.Concurrency Control Services-only single user.Authorization Services-only single user, but may be needed if different individuals are to use theDBMS at different times.Utility Services-limited in scope.Support for Data Communication-only standalone system.

Loading page image...

Database Systems: Instructor’s Guide-Part III82.7Discuss the function and importance of thesystem catalog.See Section 2.4, Service (2)–User-accessiblecatalog.2.8Discuss the differences between DDL and DML? What operations would you typically expectto be available in each language?DDL-A language that allows the DBA or user to describe and name the entities, attributes,and relationships required for the application, together with any associated integrity andsecurity constraints.DML-A language that provides a set of operations to support the basic data manipulationoperations on the data held in the database.See Section 2.2.2.2.9Discuss the differences between procedural DMLs and nonprocedural DMLs?Procedural DML-A language that allows the user to tell the system what data is needed andexactly how to retrieve the data.Nonprocedural DML-A language that allows the user to state what data is needed rather thanhow it is to be retrieved.See Section 2.2.2.2.10Name four object-based data models.•Entity-Relationship (ER)•Semantic•Functional•Object-oriented.See Section 2.3.1.2.11Name three record-based data models. Discuss the main differences between these datamodels.•relational data model-data and relationships are represented as tables, each of whichhas a number of columns with a unique name•network data model-data is represented as collections ofrecords, and relationships arerepresented bysets. Compared with the relational model, relationships are explicitlymodeled by the sets, which become pointers in the implementation. The records areorganized as generalized graph structures with records appearing asnodes(also calledsegments) and sets asedgesin the graph

Loading page image...

Database Systems: Instructor’s Guide-Part III9•hierarchical data model-restricted type of network model. Again, data is represented ascollections ofrecordsand relationships are represented bysets. However, the hierarchicalmodel allows a node to have only one parent. A hierarchical model can be represented asa tree graph, with records appearing asnodes(also calledsegments) and sets asedges.See Section 2.3.2.2.12What is a transaction. Give an example of a transaction.A transaction is a series of actions, carried out by a single user or application program, whichaccesses or changes the contents of the database.See Section 2.4.2.13What is concurrency control and why does a DBMS need a concurrency control facility?A mechanism to ensure that the database is updated correctly when multiple users areupdating the database concurrently.This avoids inconsistencies from arising when two or more transactions are executing and atleast one is updating the database.See Section 2.4.2.14Define the term "database integrity". How does database integrity differ from databasesecurity?“Database integrity” refers to the correctness and consistency of stored data: it canbeconsidered as another type of database protection. Although integrity is relatedto security, ithas wider implications: integrity is concerned with the quality of dataitself. Integrity isusually expressed in terms ofconstraints, which are consistencyrules that the database is notpermitted to violate.See Section 2.4.Exercises2.15Analyze the DBMSs that you are currently using. Determine each system’s compliance with thefunctions that we would expect to be provided by a DBMS. What type of language does eachsystem provide? What type of architecture does each DBMS use? Check the accessibility andextensibility of thesystem catalog. Is it possible to export thesystem catalogto another system?To do this you will need to obtain appropriate information about each system. There should bemanuals available or possibly someone in charge of each system who could supply the necessaryinformation.

Loading page image...

Database Systems: Instructor’s Guide-Part III102.16Write a program that stores names and telephone numbers in a database. Write another programthat stores names and addresses in a database. Modify the programs to use external, conceptual,and internal schemas. What are the advantages and disadvantages of this modification?The programs can be written in any suitable language and should be well structured andappropriately commented. Two distinct files result. The structures can be combined into onecontaining name, address, and telNo, which can be the representation of both the internal andconceptual schemas. The conceptual schema should be created separately with a routine to mapthe conceptual to the internal schema. The two external schemas also must be created separatelywith routines to map the data between the external and the conceptual schema. The two programsshould then use the appropriate external schema and routines.2.17Write a program that stores names and dates of birth in a database. Extend the program so that itstores the format of the data in the database; in other words, create asystem catalog.Provide aninterface that makes thissystem catalogaccessible to external users.Again, the program can be written in any suitable language. It should then be modified to add thedata format to the original file. This should not be difficult, if the original program is wellstructured. The interface for other users operates on the data dictionary and is separate from theoriginal program. A menu-based interface is adequate.

Loading page image...

Database Systems: Instructor’s Guide-Part III11Chapter3DatabaseArchitectures and the WebReview Questions3.1What is meant by the term ‘client-server architecture’ and what are the advantages of thisapproach? Compare the client-server architecture with two other architectures.The client is a process that requires some resource, and the server provides the resource. Neitherneed reside on the same machine. Advantages include:•Better performance•Likely reduction in hardware costs•Reduction in communication costs•Better consistencySee also Section3.1.3.2Compare and contrast the two-tier client-server architecture for traditional DBMSs with thethree-tier client-server architecture. Why is the latter architecture more appropriate for the Web?See Figures3.5and3.6. Architecture maps quite naturally to the Web with a Web browser actingas ‘thin’ client and Web server acting as an application server (with database server as third layer).3.3What is ann-tier architecture?The three-tier architecture can be expanded tontiers, with additional tiers providing moreflexibility and scalability.3.4What is middleware? Provide a classification service for middleware.Middlewareis a generic term used to describe software that mediates with other software andallows for communication between disparate applications in a heterogeneous system. The needfor middleware arises when distributed systems become too complex to manage efficientlywithout a common interface. The need to make heterogeneous systems work efficiently across anetwork and be flexible enough to incorporate frequent modifications led to the development ofmiddleware, which hides the underlying complexity of distributed systems.3.5What is a TP Monitor? What advantages does a TP Monitor bring to an OLTP environment?A TP Monitor forms the middle tier of a three-tier architecture. TP Monitors provide significantadvantages, including:•Transaction routing:The TP Monitor can increase scalability by directing transactions tospecific DBMSs.•Managing distributed transactions:The TP Monitor can manage transactions that requireaccess to data held in multiple, possibly heterogeneous, DBMSs. For example, a transaction

Loading page image...

Database Systems: Instructor’s Guide-Part III12may require to update data items held in an Oracle DBMS at site 1, an Informix DBMS atsite 2, and an IMS DBMS as site 3. TP Monitors normally control transactions using theX/Open Distributed Transaction Processing (DTP) standard. A DBMS that supports thisstandard can function as a resource manager under the control of a TP Monitor acting as atransaction manager.•Load balancing:The TP Monitor can balance client requests across multiple DBMSs on oneor more computers by directing client service calls to the least loaded server. In addition, itcandynamicallybringinadditionalDBMSsasrequiredtoprovidethenecessaryperformance.•Funneling:In environments with a large number of users, it may sometimes be difficult forall users to be logged on simultaneously to the DBMS. In many cases, we would find thatusers generally do not need continuous access to the DBMS. Instead of each user connectingto the DBMS, the TP Monitor can establish connections with the DBMSs as and whenrequired, and can funnel user requests through these connections. This allows a largernumber of users to access the available DBMSs with a potentially much smaller number ofconnections, which in turn would mean less resource usage.•Increased reliability:The TP Monitor acts as atransaction manager, performing thenecessary actions to maintain the consistency of the database, with the DBMS acting as aresource manager.If the DBMS fails, the TP Monitor may be able to resubmit thetransaction to another DBMS or can hold the transaction until the DBMS becomes availableagain.3.6What is a Web service?Web services allow applications to integrate with other applications across the Internet and maybe a key technology that supports B2B (Business to Business) interaction. Unlike other Web-based applications, Web services have no user interface and are not aimed at Web browsers. Webservices instead share business logic, data, and processes through a programmatic interface acrossa network. In this way, it is the applications that interface and not the users. Developers can thenadd the Web service to a Web page (or an executable program) to offer specific functionality tousers.3.7What technologies and standards are used to develop Web services and how do they relate toeach other?Key to the Web services approach is the use of widely accepted technologies and standards, suchas:•XML (extensible Markup Language).•SOAP (Simple Object Access Protocol) is a communication protocol for exchangingstructured information over the Internet and uses a message format based on XML. It is bothplatform-and language-independent.•WSDL (Web Services Description Language) protocol, again based on XML, is used todescribe and locate a Web service.•UDDI(UniversalDiscovery,Description,andIntegration)protocolisaplatform-independent, XML-based registry for businesses to list themselves on the Internet. It wasdesigned to be interrogated by SOAP messages and to provide access to WSDL documents

Loading page image...

Database Systems: Instructor’s Guide-Part III13describing the protocol bindings and message formats required to interact with the Webservices listed in its directory.Figure 3.9 illustrates the relationship between these technologies. From the database perspective,Web services can be used both from within the database (to invoke an external Web service as aconsumer)and the Web service itself can access its own database (as aprovider)to maintain thedata required to provide the requested service.3.8What is a service-oriented architecture?The SOA approach attempts to design loosely coupled and autonomousservicesthat can becombined to provide flexible composite business processes and applications. SOA principlesprovide a unique design approach for building Web services for SOA:•loose coupling:services must be designed to interact on a loosely coupled basis;•reusability:logic that can potentially be reused is designed as a separate service;•contract:services adhere to a communications contract that defines the information exchangeand any additional service description information, specified by one or more servicedescription documents;•abstraction:beyond what is described in the service contract, services hide logic from theoutside world;•composability:services may compose other services, so that logic can be represented atdifferent levels of granularity thereby promoting reusability and the creation of abstractionlayers;•autonomy:services have control over the logic they encapsulate and are not dependent uponother services to execute this governance;•stateless:services should not be required to manage state information, as this can affect theirability to remain loosely-coupled;•discoverability:services are designed to be outwardly descriptive so that they can be foundand assessed via available discovery mechanisms.3.9Provide an example of a service-oriented architecture.An example of SOA is shown in Figure 3.10(b).3.10What is Cloud computing?Cloud computing: A model for enabling ubiquitous, convenient, on-demand network accessto a shared pool of configurable computing resources (e.g., networks, servers, storage,applications,andservices)thatcanberapidlyprovisionedandreleasedwithminimalmanagement effort or service provider interaction.See Section 3.5.3.11Discuss the five essential characteristics of cloud computing.The essential characteristics are:

Loading page image...

Database Systems: Instructor’s Guide-Part III14•On-demand self-service:consumers can obtain, configure and deploy cloud servicesthemselves using cloud service catalogues, without requiring the assistance of anyonefrom the cloud provider.•Broad network access:it is network based, and accessible from anywhere, from anystandardized platform (e.g. desktop computers, laptops, mobile devices).•Resource pooling.The cloud provider’s computing resources are pooled to serve multipleconsumers,withdifferentphysicalandvirtualresourcesdynamicallyassignedandreassigned according to consumer demand. Examples of resources include storage,processing, memory, and network bandwidth.•Rapidelasticity.Resourcepoolingavoidsthecapitalexpenditurerequiredfortheestablishment of network and computing infrastructure. By outsourcing to a cloud,consumers can cater for the spikes in demand for their services by using the cloudprovider’s computing capacity and the risk of outages and service interruptions aresignificantly reduced. Moreover, capabilitiescan be elastically provisioned and released,in some cases automatically, to scale rapidly based on demand. To the consumer, thecapabilities available for provisioning often appear to be unlimited and can be called on inany quantity at any time.•Measured service.Cloud systems automatically control and optimize resource use byleveraging a metering capability at some level of abstraction appropriate to the type ofservice (e.g., storage, processing, bandwidth, and active user accounts). Resource usagecan be monitored, controlled, and charged for.See Section 3.5.3.12Discuss the three main service models of cloud computing.•Software as a Service (SaaS):software and associated data are centrally hosted on thecloud.•Platform as a Service (PaaS):a computing platform that allows the creation of webapplications quickly and easily and without the complexity of buying and maintaining thesoftwareandinfrastructureunderneathit.Sometimes,PaaSisusedtoextendthecapabilities of applications developed as SaaS. While earlier application developmentrequired hardware, an operating system, a database, middleware, Web servers, and othersoftware, with the PaaS model only the knowledge to integrate them is required. The restis taken care of by the PaaS provider.•Infrastructure as a Service (IaaS):deliversservers, storage, network and operatingsystems–typically a platform virtualization environment–to consumers as an on-demand service,in a single bundle and billed according to usage.A popular use of IaaS isin hosting websites, where the in-house infrastructure is not burdened with this task butleft free to manage the business.3.13Compare and contrast the four main deployment models for the cloud.The four main deployment models for the cloud are:

Loading page image...

Database Systems: Instructor’s Guide-Part III15•Private cloud:cloud infrastructure operated solely for a single organization, whethermanaged internally by the organization, a third party, or some combination of them, and itmay be hosted internally or externally.•Communitycloud:cloudinfrastructureissharedforexclusiveusebyaspecificcommunity of organizations that have common concerns (e.g., security requirements,compliance, jurisdiction). It may be owned, managed, and operated by one or more of theorganizations in the community, a third party, or some combination of them, and it maybe hosted internally or externally.•Public cloud:cloud infrastructure is made available to the general public by a serviceprovider. These services are free or offered on a pay-per-use model. It may be owned,managed, and operated by a business, academic, or government organization, or somecombination of these. It exists on the premises of the cloud provider.•Hybrid cloud: cloud infrastructure is a composition of two or more distinct cloudinfrastructures (private, community, or public) that remain unique entities, but are boundtogether by standardized or proprietary technology, offering the benefits of multipledeployment models.3.14What are the difference between Data as a service (DaaS) and Database as a service(DBaaS)?•DaaS: offers the ability to define datain the cloud and subsequently query that data ondemand. Unlike traditional database solutions, DaaS does not implement typical DBMSinterfaces such as SQL (see Chapter 6). Instead, the data is accessed via a common set ofAPIs.DaaS enables any organization with valuable data to create new revenue lines basedon data they already own. Examples of DaaS areUrban Mapping, a geography dataservice, provides data for customers to embed into their own websites andapplications;Xignite makes financial data available to customers; and Hoovers, a Dun & Bradstreetcompany, provides business data on various organizations.•DBaaS:offers full database functionality to application developers. In DBaaS, amanagement layer is responsible for the continuous monitoring and configuring of thedatabase to achieve optimized scaling, high availability, multi-tenancy (that is, servingmultiple client organizations), and effective resource allocation in the cloud, therebysparing the developer from ongoing database administration tasks.See Section 3.5.2.3.15Discuss the different architectural models for Database-as-a-service.•Separate servers•Shared Server, Separate Database Server Process•Shared Database Server, Separate Databases•Shared Database, Separate Schema•Shared Database,Shared SchemaSee Section 3.5.2.

Loading page image...

Database Systems: Instructor’s Guide-Part III163.16Describe the main components in a DBMS.The major software components in a DBMS environment are depicted in Figure 3.20.The maincomponents in a DBMS are:•Query processor.This is a major DBMS component that transforms queries into a series oflow-level instructions directed to the database manager.•Database manager (DM).The DM interfaces with user-submitted application programs andqueries. The DM accepts queries and examines the external and conceptual schemas todetermine what conceptual records are required to satisfy the request. The DM then places acall to the file manager to perform the request.•File manager. The file manager manipulates the underlying storage files and manages theallocation of storage space on disk. It establishes and maintains the list of structures andindexes defined in the internal schema. If hashed files are used, it calls on the hashingfunctions to generate record addresses. However, the file manager does not directly managethe physical input and output of data. Rather, it passes the requests on to the appropriateaccess methods, which either read data from or write data into the system buffer (orcache).•DML preprocessor. This module converts DML statements embedded in an applicationprogram into standard function calls in the host language. The DML preprocessor mustinteract with the query processor to generate the appropriate code.•DDL compiler. The DDL compiler converts DDL statements into a set of tables containingmetadata. These tables are then stored in the system catalog while control information isstored in data file headers.•Catalog manager. The catalog manager manages access to and maintains the system catalog.The system catalog is accessed by most DBMS components.3.17Describe the internal architecture of Oracle.Oracle is based on the client–server architecture. The Oracle server consists of thedatabase(theraw data, including log and control files) and theinstance(the processes and system memory onthe server that provide access to the database). An instance can connect to only one database. Thedatabase consists of alogical structure, such as the database schema, and aphysical structure,containing the files that make up an Oracle database.Exercises3.18Examine the documentation sets of Microsoft SQL Server, Oracle, and IBM’s DB2 system toidentify their support for the following:(a) client–server architecture(b) Web services(c) service-oriented architectureMicrosoft SQL ServerMicrosoft SQL Server is designed to work effectively in a number of environments:•As a two-tier or multitier client/server database system•As a desktop database system

Loading page image...

Database Systems: Instructor’s Guide-Part III17In a two-tier client/server system, users run an application on their local computer, known as aclient, whichconnects over a network to the server running SQL Server. The client applicationruns both business logic and the code to display output to the user, and is also known as athick client.SQL Server 2008 enables data to be consumed from custom applications developed usingMicrosoft .NET and Visual Studio and from within service-oriented architectures (SOA) andbusiness processes through Microsoft BizTalk Server.Web services extend the World Wide Web infrastructure to provide the means for software toconnect to other software applications. Applications access Web services via ubiquitous Webprotocols and data formats such as HTTP, XML, and SOAP, with no need to worry about howeach Web service is implemented. Web services combine the best aspects of component-baseddevelopment and the Web, and are a cornerstone of the Microsoft .NET programming model.Microsoft System Center plays a central role in Microsoft's vision to help IT organizationsbenefit from self-managing, dynamic systems. System Center solutions are tuned to simplifymanagementofthesystemsandapplicationsyourcompanyalreadyhasimplemented,including Microsoft SQL Server, Microsoft Exchange Server, Microsoft Biztalk Server,Internet Information Services and the Microsoft .NET Framework.OracleIn the Oracle database system environment, the database application and the database areseparated into two parts: a front-end orclientportion, and a back-end orserverportion--hencethe termclient/server architecture.The client runs the database application that accessesdatabase information and interacts with a user through the keyboard, screen, and pointingdevice, such as a mouse. The server runs the Oracle software and handles the functionsrequired for concurrent, shared data access to an Oracle database.Although the client application and Oracle can be run on the same computer, greaterefficiency can often be achieved when the client portions and server portion are run bydifferent computers connected through a network. The following sections discuss possiblevariations in the Oracle client/server architecture.Most organizations implement service-oriented architecture (SOA) with the hope of gainingmore business agility through reuse of shared services. As reuse begins to take hold within theorganization, however, it becomes critical to manage consumption of services or your SOAcan quickly spin out of control. Oracle Web Services Manager provides a solution forgoverning the interactions with shared services through security and operational policymanagement and enforcement to ensure service reuse remains under control.Leading companies are gaining operational efficiencies and business agility through adaptable,re-usablebusinessprocessesandservicesbuiltonatrulyflexibleService-OrientedArchitecture (SOA) foundation. Oracle SOA products allow you to build, deploy, and manageSOA with integrated, best-in-class technology that provides:

Loading page image...

Database Systems: Instructor’s Guide-Part III18•Comprehensive and Pre-Integrated SOA Platform—Complete set of service andprocess infrastructure components for building, deploying, and managing SOAs•Closed-LoopGovernance—Comprehensive,end-to-endlifecyclegovernanceofservices•Extremeperformanceandscalability—In-memorytransactions,real-timeeventprocessing, and high-volume data transfer on top of a highly scalable applicationserver•Integrated Security—Centralized policy management, enterprise-grade, end-to-endsecurityOracle SOA Suite, which now includes the former BEA AquaLogic Service Bus, is OracleFusion Middleware's strategic product for SOA. Oracle plans to continue to develop andsupport Oracle WebLogic Integration, and expects to converge this product with Oracle'sstrategicsolutionsovertime.Existingdeploymentsofthisproductwillbenefitfromcomplementary products such as Oracle SOA Suite.DB2Localandremoteapplicationprocessescanworkwiththesamedatabase.Aremoteapplication is one that initiates a database action from a machine that is remote from thedatabase machine. Local applications are directly attached to the database at the servermachine.Webserviceproviders aredescribedbyWebServicesDescriptionLanguage(WSDL)documents. You can use the Web services wrapper to access Web service providers.A Web services client application can obtain access to a DB2® Version 9 database with aWeb services description language (WSDL) interface. You can create a WSDL interface toDB2®Version 9 data by using the Web services Object Runtime Framework (WORF), alsoknown as Document Access Definition Extension (DADX) files. After you define theoperations to access DB2 data with the DADX file, then you deploy the DADX file and itsruntime environment (IBM®Web Service SOAP provider or Apache Axis version 1.2) to asupported Java™Web application server environment (Apache Jakarta Tomcat or IBM®WebSphere® Application Server). After you have the DB2 Web service tested and deployed,any Web services client can start using the DB2 Web service.Web services consumer-the user-defined functionsWhen DB2 Version 9 becomes the consumer, Web services can take advantage of theoptimization that is built within the database. By using SQL statements, you can consume andintegrate Web services data. By using SQL to access Web services data, you can reduce someapplication programming efforts because the data can be manipulated within the context of anSQL statement before that data is returned to the client application. You can convert anexisting WSDL interface into a DB2 table or scalar function by using tools that are providedin WebSphere®Studio version 5 and later. During the execution of an SQL statement, youestablish a connection with the Web service provider, and then you receive a responsedocument as a relational table or a scalar value.

Loading page image...

Database Systems: Instructor’s Guide-Part III19Webserviceproviders aredescribedbyWebServicesDescriptionLanguage(WSDL)documents. You can use the Web services wrapper to access Web service providers.Figure 1shows the architecture of Web services.1.A Web service provider implements a service and publishes the WSDL information to aservice broker, such as UDDI.2.The service consumer can then use the service broker to find a Web service provider.3.When the service consumer finds a Web service provider, the service consumer binds tothe service provider so that the consumer can use the Web service.4.The consumer invokes the service by exchanging SOAP (simple object access protocol)messages between the requester and provider.Figure 1. Web services: a service-oriented architectureThe SOAP specification defines the layout of an XML-based message. A SOAP message iscontained in a SOAP envelope. The envelope consists of an optional SOAP header and amandatory SOAP body. The SOAP header can contain information about the message, such asencryption information or authentication information. The SOAP body contains the message.The SOAP specification also defines a default encoding for programming language bindings,which is called the SOAP encoding.3.19Search the Web for a number of Web services other than the ones discussed in Section 3.2.What do these services have in common? Identify whether the services access a database.The Google Maps API provides Web services to make use of the Google Maps application.The following Web site contains a number of examples:http://maps.forum.nu/The followingsitecontainsinformationonusingadatabasewithGooglemaps:http://groups.google.com/group/Google-Maps-API/web/using-databases-with-gmaps-appsAmazonprovidesanumberofWebservicesdiscussedonthefollowingWebsite:http://aws.amazon.com/

Loading page image...

SOLUTIONS TO REVIEW QUESTIONSAND EXERCISESFOR PART 2–THE RELATIONAL MODEL AND LANGUAGES(CHAPTERS4–9)

Loading page image...

Database Systems: Instructor’s Guide-Part III2Solutions to Review Questions and ExercisesChapter 4 The Relational Model......................................................................................................................3Chapter 5 Relational Algebra and Relational Calculus....................................................................................6Chapter 6 SQL: Data Manipulation...............................................................................................................22Chapter 7 SQL: Data Definition.....................................................................................................................32Chapter 8 Advanced SQL..............................................................................................................................42Chapter 9 Object-Relational DBMSs.............................................................................................................52

Loading page image...

Database Systems: Instructor’s Guide-Part III3Chapter4The Relational ModelReview Questions4.1Discuss each of the following concepts in the context of the relational data model:(a)RelationAtable with columns and rows.(b)AttributeAnamed column of a relation.(c)DomainTheset of allowable values for one or more attributes.(d)TupleArow of a relation.(e)IntensionThe structure of a relation together with a specification of the domainsand any other restrictions on possible values.ExtensionAn instance of the tuples of a relation.(f)DegreeThe number of attributes in a relation.CardinalityThenumber of tuples in a relation.Each term defined in Section4.2.1.4.2Describethe relationship between mathematical relations and relations in the relational datamodel?LetD1,D2, . . . ,Dnbensets. Their Cartesian product is defined as:D1D2. . .Dn={(d1,d2, . . . ,dn) |d1D1,d2D2, . . . ,dnDn}Any set ofn-tuples from this Cartesian product isa relation on thensets. Now letA1,A2, . . .,Anbe attributes with domainsD1,D2, . . . ,Dn. Then the set {A1:D1,A2:D2, . . . ,An:Dn} isa relation schema. A relationRdefined by a relation schemaSis a set of mappings from theattribute names to their corresponding domains. Thus, relationRis a set ofn-tuples:(A1:d1,A2:d2, . . . ,An:dn) such thatd1D1,d2D2, . . . ,dnDnEach element in then-tuple consists of an attribute and a value for that attribute.Discussed fully in Sections4.2.2 and4.2.3.4.3Describe the differences between a relation and a relation schema. What is a relationaldatabase schema?A relation schema is a named relation defined by a set of attribute and domain name pairs. Arelational database schema is a set of relation schemas, each with a distinct name. Discussed inSection4.2.3.4.4Discuss the properties of a relation.A relation has the following properties:•has a name that is distinct from all other relation names in the relational schema;•each cell contains exactly one atomic (single) value;

Loading page image...

Database Systems: Instructor’s Guide-Part III4•each attribute has a distinct name;•the values of an attribute are all from the same domain;•each tuple is distinct; there are no duplicate tuples;•the order of attributes has no significance;•the order of tuples has no significance, theoretically. (However, in practice, the order mayaffect the efficiency of accessing tuples.)Discussed fully in Section4.2.4.4.5Discuss the differences between the candidate keys and the primary key of a relation. Explainwhat is meant by a foreign key. How do foreign keys of relations relate to candidate keys? Giveexamples to illustrate your answer.The primary key is the candidate key that is selected to identify tuples uniquely within a relation.A foreign key is an attribute or set of attributes within one relation that matches the candidate keyof some (possibly the same) relation. Discussed in Section4.2.5.4.6Define the two principal integrity rules for the relational model. Discuss why it is desirable toenforce these rules.Two rules are Entity Integrity (Section4.3.2) and Referential Integrity (Section4.3.3).4.7What is a view? Discuss the difference between a view and a base relation.View is the dynamic result of one or more relational operations operating on the base relations toproduce another relation. Base relation exists as a set of data in the database. A view does notcontain any data, rather a view is defined as a query on one or more base relations and a query onthe view is translated into a query on the associated base relations.See Section4.4.ExercisesThe following tables form part of a database held in a relational DBMS:-Hotel(hotelNo, hotelName, city)Room(roomNo,hotelNo, type, price)Booking(hotelNo,guestNo,dateFrom, dateTo, roomNo)Guest(guestNo, guestName, guestAddress)whereHotel contains hotel details and hotelNo is the primary key;Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms the primarykey;andGuest contains guest details and guestNo is the primary key.

Loading page image...

Database Systems: Instructor’s Guide-Part III54.8Identify the foreign keys in this schema. Explain how the entity and referential integrity rulesapply to these relations.For each relation, the primary key must not contain any nulls.Room is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Room shouldeither be null or contain the number of an existing hotel in the Hotel relation. In this case study, itwould probably be unacceptable to have a hotelNo in Room with a null value.Booking is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Bookingshould either be null or contain the number of an existing hotel in the Hotel relation. However,because hotelNo is also part of the primary key, a null value for this attribute would beunacceptable. Similarly for guestNo. Booking is also related to Room through the attributeroomNo.4.9Produce some sample tables for these relations that observe the relational integrity rules.Suggest somegeneralconstraints that would be appropriate for this schema.Student should provide somesample tables, observing entity and referential integrity. Inparticular, ensure the uniqueness for the composite primary keys of the Room and Booking tables.Somegeneralconstraints may be:•There can be no two bookings for the same room in the same hotel on the same day.•For the Booking relation, dateFrom must be before dateTo.•Room price must be greater than 0 and less than £200.4.10Analyze the RDBMSs that you are currently using. Determine the support the system provides forprimary keys, alternate keys, foreign keys, relational integrity,and views.This is a small student project, the result of which is dependent on the system analyzed.4.11Implement the above schema in one of the RDBMSs you currently use. Implement, wherepossible,theprimary,alternate,andforeignkeys,and appropriaterelationalintegrityconstraints.This is a small student project, the result of which is dependent on the RDBMS used. Ensure thatkeys have been implemented, and that relationships have been implemented if the RDBMSsupports this.

Loading page image...

Database Systems: Instructor’s Guide-Part III6Chapter 5 Relational Algebra and Relational CalculusReview Questions5.1What is the difference between a procedural and non-procedural language? How would youclassify the relational algebra and relational calculus?Procedural language: alanguage that allows user to tell the system what data is needed andexactlyhowto retrieve the data.Non-procedural language: alanguage that allows user to statewhatdata is needed ratherthanhowit is to be retrieved.Informally, we may describe the relational algebra as a (high-level) procedural language: itcan be used to tell the DBMS how to build a new relation from one or more relations in thedatabase. Again, informally, we may describe the relational calculus as a non-procedurallanguage: it can be used to formulate the definition of a relation in terms of one or moredatabase relations.5.2Explain the following terms:•relationally complete;A language that can be used to produce any relation that can be derived using therelational calculus is said to berelationally complete.•closure of relational operations.The relational algebra is a theoretical language with operations that work on one or morerelations to define another relation without changing the original relation(s). Thus, boththe operands and the results are relations, and so the output from one operation canbecome the input to another operation. This allows expressions to be nested in therelational algebra, just as we can nest arithmetic operations. This property is calledclosure:relations areclosedunderthealgebra,justas numbers areclosed underarithmetic operations.5.3Define the five basic relational algebra operations. Define the Join, Intersection, and Divisionoperations in terms of these five basic operations.Five basic operations are:•Selection and Projection (Unary)•Cartesian Product, Union, and Set Difference (Binary).There is also the Join, Intersection, and Division operations:•Can rewrite-Join in terms of the basic selection and Cartesian product operations:

Loading page image...

Database Systems: Instructor’s Guide-Part III7R3FS =F(RS)•Can express the intersection operation in terms of the set difference operation:RS = R-(R-S)•Can express the division operation in terms of the basic operations:T1=C(R)T2=C( (S x T1)-R)T= T1-T25.4Discuss the differences between the five Join operations: Theta join, Equijoin, Natural join,Outer join, and Semijoin. Give examples to illustrate your answer.Theta joinRFSProduces a relation that contains tuples satisfying the predicateFfromthe Cartesian product ofRandS.EquijoinRFSProduces a relation that contains tuples satisfying the predicateF(which only contains equality comparisons) from the Cartesian productofRandS.Natural joinRSAn Equijoin of the two relationsRandSover all common attributesx. One occurrence of each common attribute is eliminated.(Left) Outer joinRSA join in which tuples fromRthat do not have matching values in thecommon attributes ofSare also included in the result relation.SemijoinRFSProduces a relation that contains the tuples ofRthat participate in thejoin ofRwithS.5.5Compare and contrast the tuple relational calculus with domain relational calculus. Inparticular, discuss the distinction between tuple and domain variables.In the tuple relational calculus, we use variables that range over tuples in a relation. In thedomain relational calculus, we also use variables but in this case the variables take their valuesfromdomainsof attributes rather than tuples of relations.5.6Define the structure of a (well-formed) formula in both the tuple relational calculus anddomain relational calculus.Tuple relational calculusA (well-formed) formula is made out of one or moreatoms, where an atom has one of thefollowing forms:•R(Si), whereSiis a tuple variable andRis a relation.•Si.a1Sj.a2, whereSiandSjare tuple variables,a1is an attribute of the relation overwhichSiranges,a2is an attribute of the relation over whichSjranges, andis one of thecomparison operators (,,,,=,); the attributesa1anda2must have domainswhose members can be compared by

Loading page image...

Database Systems: Instructor’s Guide-Part III8•Si.a1c, whereSiis a tuple variable,a1is an attribute of the relation over whichSiranges,cis a constant from the domain of attributea1, andis one of the comparisonoperatorsWe recursively build up formulae from atoms using the following rules:•an atom is a formula;•ifF1andF2are formulae, so are their conjunctionF1F2, their disjunctionF1F2, andthe negationF1;•ifFis a formula with free variableX, then (X)(F) and (X)(F) are also formulae.Domainrelational calculusA (well-formed) formula is made out of one or moreatoms, where an atom has one of thefollowing forms:•R(d1,d2,…,dn), whereRis a relation of degreenand eachdiis a domain variable.•didj, wheredianddjare domain variablesandis one of the comparison operators (,,,,=,); the domainsdianddjmust have members that can be compared by•dic, wherediis a domain variable,cis a constant from the domain ofdi, andis one ofthe comparison operators5.7Explainhow a relational calculus expression can be unsafe? Illustrate your answer with anexample. Discuss how to ensure that a relational calculus expression is safe.See end of Section 5.2.1.ExercisesFor the following exercises, use the Hotel schema defined at the start of the Exercises at the end ofChapter 4.5.8Describe the relations that would be produced by the following relational algebra operations:a)hotelNo(price50(Room) )This will produce a relation with a single attribute (hotelNo) giving the number of thosehotels with a room price greater than £50.b)Hotel.hotelNo=Room.hotelNo(HotelRoom)This will produce a join of the Hotel and Room relations containing all the attributes ofboth Hotel and Room (there will be two copies of the hotelNo attribute). Essentially thiswill produce a relation containing all rooms at all hotels.

Loading page image...

Database Systems: Instructor’s Guide-Part III9c)hotelName(HotelHotel.hotelNo=Room.hotelNo(price50(Room)) )This will produce a join of Hotel and those tuples of Room with a price greater than £50.Essentially this will produce a relation containing all hotel names with a room price above£50.d)Guest(dateTo‘1-Jan-2007’(Booking))This will produce a (left outer) join of Guest and those tuples of Booking with an end date(dateTo) greater than or equal to 1-Jan-2007. All guests who don’t have a booking withsuch a date will still be included in the join. Essentially this will produce a relationcontaining all guests and show the details of any bookings they have beyond 1-Jan-2002.e)HotelHotel.hotelNo=Room.hotelNo(price50(Room)) )This will produce a (semi) join of Hotel and those tuples of Room with a price greaterthan £50. Only those Hotel attributes will be listed. Essentially this will produce a relationcontaining all the details of all hotels with a room price above £50.f)guestName,hotelNo(BookingBooking.guestNo=Guest.guestNoGuest)hotelNo(city=’London’(Hotel))This will produce a relation containing the names of all guests who have booked all hotelsin London.5.9Provide the equivalent tuple relational calculus and domain relational calculus expressionsfor each of the relational algebra queries given in Exercise 4.8.a)hotelNo(price50(Room) )TRC:{R.hotelNo|Room(R)R.price50}DRC:{hotelNo|(rNo, typ, prce) (Room(rNo, hotelNo, typ, prce)prce50)}b)Hotel.hotelNo=Room.hotelNo(HotelRoom)TRC:{H, R|Hotel(H)(R) (Room(R)(H.hotelNo=R.hotelNo))}DRC:{hNo, hName, cty, rNo, hNo1, typ, prce| (Hotel(hNo, hName, cty)Room(rNo, hNo1, typ, prce)(hNo = hNo1))}

Loading page image...

Database Systems: Instructor’s Guide-Part III10c)hotelName(HotelHotel.hotelNo=Room.hotelNo(price50(Room)) )TRC:{H.hotelName|Hotel(H)(R) (Room(R)(H.hotelNo=R.hotelNo)(R.price> 50))}DRC:{hotelName| (hNo, cty, rNo, hNo1,typ, prce)(Hotel(hNo, hotelName, cty)Room(rNo, hNo1, typ, prce)(hNo = hNo1)(prce > 50))}d)Guest(dateTo‘1-Jan-2007’(Booking))TRC:{G.guestNo, G.guestName, G.guestAddress, B.hotelNo, B.dateFrom,B.dateTo, B.roomNo|Guest(G)(B) (Booking(B)(G.guestNo=B.guestNo)(B.dateTo> ‘1-Jan-2007’))}DRC:{guestNo, guestName, guestAddress, hotelNo, dateFrom, dateTo, roomNo|(gNo1) (Guest(guestNo, guestName, guestAddress)(Booking(hotelNo, gNo1, dateFrom, dateTo, roomNo)(guestNo = gNo1)(dateTo‘1-Jan-2007’)))}e)HotelHotel.hotelNo=Room.hotelNo(price50(Room)) )TRC:{H.hotelNo, H.hotelName, H.city|Hotel(H)(R) (Room(R)(H.hotelNo=R.hotelNo)(R.price> 50))}DRC:{hotelNo, hotelName, city| (rNo, hNo1, typ, prce)(Hotel(hotelNo, hotelName, city)Room(rNo, hNo1, typ, prce)(hotelNo = hNo1)(prce> 50))}f)guestName,hotelNo(BookingBooking.guestNo=Guest.guestNoGuest)hotelNo(city=’London’(Hotel))TRC:{G.guestName|Guest(G)((H) (Hotel(H)(H.city= ‘London’)((B) (Booking(B)G.guestNo=B.guestNoH.hotelNo=B.hotelNo))))}DRC:{guestName| (gNo, gName, gAddress, hNo, gNo1, dFrom, dTo, rNo,hName, cty, hNo1, typ, prce) ((Hotel(hNo, hName, cty)(cty= ‘London’)Guest(gNo, gName, gAddress)Booking(hNo1, gNo1, dFrom, dTo, rNo)(gNo = gNo1)(hNo = hNo1)))}

Loading page image...

Database Systems: Instructor’s Guide-Part III115.10Describe the relations that would be produced by the following tuple relational calculusexpressions:(a){H.hotelName|Hotel(H)H.city= ‘London’}This will produce a relation containing the names of all hotels in London.(b){H.hotelName|Hotel(H)(R) (Room(R)H.hotelNo=R.hotelNoR.price50)}This will produce a relation containing the names of all hotels that have a room priceabove £50.(c){H.hotelName|Hotel(H)(B) (G) (Booking(B)Guest(G)H.hotelNo=B.hotelNoB.guestNo=G.guestNoG.guestName= ‘John Smith’)}This will produce a relation containing the names of all hotels that have abooking fora guest called John Smith.(d){H.hotelName,G.guestName,B1.dateFrom,B2.dateFrom|Hotel(H)Guest(G)Booking(B1)Booking(B2)H.hotelNo=B1.hotelNoG.guestNo=B1.guestNoB2.hotelNo=B1.hotelNoB2.guestNo=B1.guestNoB2.dateFromB1.dateFrom}This will produce a relation containing the names of guests who have more than onebooking at the same hotel, along with the hotel number and the dates of the bookings.5.11Provide the equivalent domain relational calculusand relational algebra expressions for eachof the tuple relational calculus expressions given in Exercise 4.10.(a){H.hotelName|Hotel(H)H.city= ‘London’}DRC:{hotelName| (hNo, cty) (Hotel(hNo, hotelName, cty)cty = ‘London’)}RA:hotelName(city=‘London’(Hotel) )(b){H.hotelName|Hotel(H)(R) (Room(R)H.hotelNo=R.hotelNoR.price50)}DRC:{hotelName| (hNo, cty, rNo, hNo1, typ, prce) (Hotel(hNo, hotelName, cty)Room(rNo, hNo1, typ, prce)(hNo = hNo1)(prce> 50)) }

Preview Mode

This document has 470 pages. Sign in to access the full document!

Report

Study Now!

Document Details

Related Documents

Test Bank for Current Medical Diagnosis and Treatment 2023 (Ie), 62nd Edition (Chapters 27)

Solution Manual For College Physics: A Strategic Approach, 4th Edition

Solution Manual for College Algebra, 4th Edition

Solution Manual For Cost Accounting, 14th Edition

AP Calculus AB Scoring Guide Unit 6 Progress Check

Clinical Psychologist

Module 4 Matrices

MATH 1324 Homework 5 Answers

Ap progress 1 5 all 5

The Fundamental Theorem of Calculus and Definite I

Company

Explore

Study Tools