1. Answer the following two questions. a. Identify and describe two types of facility you would expect to be provided in a multi-user DBMS. Identify two types of facility that not be needed in a standalone PC DBMS? Provide justification for your answer. (5 points) Concurrency Control Services - only single user. Authorization Services - only single user, but may be needed if different individuals are to use the DBMS at different times. Utility Services - limited in scope. Support for Data Communication - only standalone system. b. Contrast the two-tier client-server architecture for traditional DBMSs with the three-tier client-server architecture. Why is the latter architecture more appropriate for the Web? (5 points) - Client -> Database Server vs. Client->Application Server (e.g. Webserver - For web application) -> Database Server - See Figures 3.5 and 3.6. Architecture maps quite naturally to the Web with a Web browser acting as thin client and Web server acting as an application server (with database server as third layer). 2. Assume that you are an employee of a consultancy company that specializes in the analysis, design, and implementation of database systems. A client has recently approached your company with a view to implementing a database system but they are not familiar with the development process. First Name Last Name . 2 a) Discuss what a user view represents in the context of a database system. Give two examples of user views for a bank database system. On what basis do you choose one of the main approaches you would use for managing the design of a database system that has these multiple user views?(10 points) b) Briefly explain the two purposes of data modeling and identify two criteria for an optimal data model. ( 5 points) c) Identify the stage(s) where is appropriate to select a DBMS and why? (5 points) d) How do the contents of a users requirements specification differ from a systems specification?(5 points) Discuss what a user view represents in the context of a database system. Give two examples of user views for a bank database system. On what basis do you choose one of the main approaches you would use for managing the design of a database system that has these multiple user views?(10 points) User view: defines what is required of a database system from the perspective of a particular job role (such as Manager or Supervisor) or enterprise application area (such as marketing, personnel, or stock control) (See Section 9.4.1). A manager view and a teller view are two examples for a Bank. One way is to examine the overlap in the data used between the various user views (see, for example, Table 11.7). Briefly explain two purposes of data modeling and identify two criteria for an optimal data model. (5 points) The two main purposes of data modeling are to assist in the understanding of the meaning (semantics) of the data and to facilitate communication about the information requirements (see Section 10.6.2). Criteria for an optimal data model are simplicity, validity, etc; page 273, Table 10.2 Identify the stage(s) where is appropriate to select a DBMS and why? (5 points) Physical database design is tailored to a specific DBMS; therefore it is essential the DBMS is determined before the physical design phase can begin. Physical database design is described in Section 9.6.3. First Name Last Name . 3 How does the content of a users requirements specification differ from a systems in the context of a database system specification? (8 points) Systems specification describes the any features to be included in the new database system such as networking and shared access requirements, performance requirements, and the levels of security required. On the other hand, the users requirements specification describes in detail the data to be held in the database and how the data is to be used (see Section 11.4.4). 3. Assume that you are an employee of a consultancy company that specializes in the analysis and design of database systems. A client has recently approached your company with a view to get answers to the questions listed below. Provide your answers in writing. a. Briefly describe the main phases involved in database design. (10 points) b. Discuss the important role played by users in the process of database design. (5 points) c. How would you identify attributes from a users requirements specification and then associate the attributes with entity or relationship types? (5 points) a) Three phases are conceptual database design, logical database design, and physical database design. Conceptual database design to build the conceptual representation of the database, which includes identification of the important entities, relationships, and attributes. Logical database design to translate the conceptual representation to the logical structure of the database, which includes designing the relations. Physical database design to decide how the logical structure is to be physically implemented (as relations) in the target Database Management System (DBMS). Conceptual database design constructs a model of the information used in an enterprise, independent of all physical considerations. Logical database design is based on a specific data model, but independent of all other physical considerations. Physical database design constructs a description of the implementation of the database on secondary storage (see Section 9.6.3). First Name Last Name . 4 b) The users involvement throughout the database design phase is critical to providing the correct system. In particular, the user should clarify any ambiguities in the specification that describes the required system and also review continually the development of the database design. The process of developing the database design is repeated until the user is prepared to sign-off the design as being a true representation of the part of the enterprise that is being modeling. See Sections 16.1.3 and 16.2 (Step 1.9). c) In a similar way to identifying entities, we look for nouns or noun phrases in the users requirements specification. The attributes can be identified where the noun or noun phrase is a property, quality, identifier, or characteristic of one of these entities or relationships. By far the easiest thing to do when we have identified an entity (x) or a relationship (y) in the requirements specification is to consider What information are we required to hold on x or y? The answer to this question should be described in the specification. However, in some cases it may be necessary to ask the users to clarify the requirements. Unfortunately, they may give answers to this question that also contain other concepts, so that the users responses must be carefully considered. 4. Prepare a conceptual data model for the following two cases. You may state any appropriate assumptions. For each case: a) Identify the main entity types. b) Identify the main relationship types. c) Determine the cardinalities and participations associated for each relationship type. d) List three attributes for each strong entity type identified in (a). Also, identify a primary key attribute for each strong entity identified in (a). Provide an example of a relationship type with an attribute. Indicate the attribute. e) Draw the ERD using the UML notation. i) Case 1: A real estate firm that lists property for sale. The following describes the firm operations and policies (20 points a to e has each 4 points): The firm has a number of sales offices in several states. Each sales office assigned one or more employees. An employee must be assigned to only one sales office. For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he or she is assigned. The firm lists property for sale. Each unit of property must be listed First Name Last Name . 5 with one and only one of the sales offices. A sales office may have any number of properties listed, or may have no property listed. Each property has one or more owners. An owner may own one or more properties. ii) Case 2: Students in BSU may engage in more than one activity. Each activity may be sponsored by one or more faculty. (15 points a to e has each 3 points) 4. Case 1: ABC). Sales Office 0..1 Manage 1..1 Employee 1..1 Assign 1..* SalesOffice 1..1 List 0..* PropertyforSale PropertyforSale 1..* Own 1..* Owner D). List = {Date listed, Comment} Manage ={Date Assigned, bonus} E) ERD diagram List Manages Assigns Own Sales Office Property for Sale Employee Owner First Name Last Name . 6 Case #2: A). Student, Activity, Faculty B). Engage, Sponsor C). Sponsor Student: 0.** -------- 0..* Activity Faculty 0..* ------------ 0..* Activity D). Student: FN, LN, ID (PK), Address Activity: ActivityNumber, Location, Type Faculty: FacultyID (PK), FN, LN, DoB, SNN(AK) 5. Explain the conceptual data model presented in Figure 1 to end users in plain English, using no more than three paragraphs. In your answers be as specific as possible by explaining the entity types and relationship types represented as well as the cardinalities and participations associated with each relationship type. (10 points) Figure 1. ERD diagram for an hypothetical company X An employee works in one department and a department may have one to many employees. An employee can be exclusively Secretary, Engineer, Technician or have Department DeptID Name Work 1..1 1..* {Optional, Or} Secretary TypingSpeed Technician SpecialtyArea Engineer NoOfCertification Employee EmplID DoB First Name Last Name . 7 other position. All employees have EmpID and DOB attributes. If an employee is a Secretary, s/he has typing speed score obtained during a test; etc.