The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P6.10. Table P6.10 Sample CLIENT Records Attribute Name Sample Value Sample value Sample Value CLIENT_NUM 298 289 289 CLIENT_NAME Marianne R. Brown James D. Smith James D. Smith CLIENT_REGION Midwest Southeast Southeast CONTRACT_DATE 10-Feb-2016 15-Feb-2016 12-Mar-2016 CONTRACT_NUMBER 5841 5842 5843 CONTRACT_AMOUNT $2,985,00.00 $670,300.00 $1,250,000.00 CONSULT_CLASS_1 Database Administration Internet Services Database Design CONSULT_CLASS_2 Web Applications Database Administration CONSULT_CLASS_3 Network Installation CONSULT_CLASS_4 CONSULTANT_NUM_1 29 34 25 CONSULTANT_NAME_1 Rachel G. Carson Gerald K. Ricardo Angela M. Jamison CONSULTANT_REGION_1 Midwest Southeast Southeast CONSULTANT_NUM_2 56 38 34 CONSULTANT_NAME_2 Karl M. Spenser Anne T. Dimarco Gerald K. Ricardo CONSULTANT_REGION_2 Midwest Southeast Southeast CONSULTANT_NUM_3 22 45 CONSULTANT_NAME_3 Julian H. Donatello Geraldo J. Rivera CONSULTANT_REGION_3 Midwest Southeast CONSULTANT_NUM_4 18 CONSULTANT_NAME_4 Donald Chen CONSULTANT_REGION_4 West Table P6.10 was created to enable the manager to match clients with consultants. The objective is to match a client within a given region with a consultant in that region, and to make sure that the client’s need for specific consulting services is properly matched to the consultant’s expertise. For example, if the client need help with database design and is located in the Southeast, the objective is to make a match with a consultant who is located in the Southeast and whose expertise is in database design. (Although the consulting company manage tries to match consultant and client locations to minimize travel expense, it is not always possible to do so.) The following basic business rules are maintained: • Each client is located in one region • A region can contain many clients. • Each consultant can work on many contracts • Each contract might require the services of many consultants. • A client can sign more than one contract, but each contract is signed by only one client. • Each contract might cover multiple consulting classifications. (For example, a contract may list consulting services in database and networking.) • Each consultant is located in one region. • A region can contain many consultants. • Each consultant has one or more areas of expertise (class). For example, a consultant might be classified as an expert in both database design and networking. • Each area of expertise (class) can have many consultants in it. For example, the consulting company might employ many consultants who are networking experts. a. Given that brief description of the requirements and the business rules, write the relational schema and draw the dependency diagram for the preceding (and very poor) table structure. Label all transitive and/or partial dependencies. b. Break up the dependency diagram you drew in Problem 10a to produce dependency diagrams that are in 3NF and write the relational schema. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.) c. Using the results of Problem 10b, draw the Crow’s Foot ERD.
> According to the data model, is it required that every entity instance in the PRODUCT table be associated with an entity instance in the CD table? Why or why not?
> List all of the attributes of a movie.
> What is the difference between partial completeness and total completeness?
> What is an overlapping subtype? Give an example.
> What is a subtype discriminator? Given an example of its use.
> What is a specialization hierarchy?
> What kinds of data would you store in an entity subtype?
> Why may the client/server evolution be characterized as a bottom-up change and how does this change affect the computing environment?
> What is the most common design trap, and how does it occur?
> Using an ER diagram, illustrate how the change you made in problem 1 affects the relationship of the USER entity to the following entities: LAB_USE_LOG RESERVATION CHECK_OUT WITHDRAW
> Verify the conceptual model you created in Appendix B, problem 3. Create a data dictionary for the verified model.
> Tiny College wants to keep track of the history of all administrative appointments (date of appointment and date of termination). (Hint: Time variant data are at work.) The Tiny College chancellor may want to know how many deans worked in the College of
> Research – and document -- the purchase of a new house. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was the information useful? Why or why not? d. What decision(s) did y
> Research – and document -- the purchase of a new car. Based on your research, explain why you plan to buy this car. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was the i
> Use the Internet at your university computer lab or home to research the scenarios described in Problems 1-10. Then work through the following problems: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did
> Using the contracting company’s ERD in Chapter 6, “Normalization of Database Tables,” Figure 6.15, create the equivalent OO representation.
> Using the ERD shown in Appendix C, “The University Lab Conceptual Design Verification, Logical Design, and Implementation,” Figure C.22 (the Check_Out component), create the equivalent OO representation.
> Convert the following relational database tables to the equivalent OO conceptual representation. Explain each of your conversions with the help of a diagram. ) Note: The Avion Sales database includes the tables shown in Figure PG.7). FIGURE PG.7 TheAvio
> Why may client/server computing be considered an evolutionary, rather than a revolutionary, change?
> Convert the following relational database tables to the equivalent OO conceptual representation. Explain each of your conversions with the help of a diagram. (Note: The R&C Stores database includes the three tables shown in Figure PG.6) FIGURE PG.6
> Assume the following business rules: • A course contains many Sections, but each Section references only one course. • A Section is taught by one professor, but each professor may teach one or more different Sections of one or more courses. • A Section m
> Given the information in Problem 1, define a superclass VEHICLE for the TRUCK class. Redraw the object space you developed in Problem 3, taking into consideration the new superclass that you just added to the class hierarchy.
> Using the data presented in Problem 1, develop an object space diagram representing the object's state for the instances of TRUCK listed below. Label each component clearly with proper OIDs and attribute names. a. The instance of the class TRUCK with TRU
> Using the tables in Figure PG.1 as a source of information: a. Define the implied business rules for the relationships. b. Using your best judgment, choose the type of participation of the entities in the relationship (mandatory or optional). Explain you
> Convert the following relational database tables to the equivalent OO conceptual representation. Explain each of your conversions with the help of a diagram. (Note: The RRE Trucking Company database includes the three tables shown in Figure PG.1).
> Using the information given in the physical design section (C.5), estimate the space requirements for the following entities: RESERVATION INV_TRANS TR_ITEM LOG ITEM INV_TYPE (Hint: You may want to check Appendix B's Table B.3, A Sample Volume of Informat
> Design and verify a database application for one of your local not-for-profit organizations (for example, the Red Cross, the Salvation Army, your church, mosque, or synagogue). Create a data dictionary for the verified design.
> Design (through the logical phase) a student advising system that will enable an advisor to bring up the student's complete performance record at the university. A sample output screen should look like the one shown in Table PC.6. Table PC.6 The Student
> Verify the conceptual model you created in Appendix B, Problem 7. Create a data dictionary for the verified model.
> What are some of the managerial and technical issues encountered in the implementation of client/server systems?
> Verify the conceptual model you created in Appendix B, Problem 6. Create a data dictionary for the verified model.
> Verify the conceptual model you created in Appendix B, Problem 5. Create a data dictionary for the verified model.
> Verify the conceptual model you created in Appendix B, Problem 4. Create a data dictionary for the verified model.
> You have just been employed by the ROBCOR Trucking Company to develop a database. To gain a sense of the database’s intended functions, you have spent some time talking to ROBCOR’s employees and you’ve examined some of the forms used to track driver assi
> Use the following brief description of operations as the source for the next database design: All aircraft owned by ROBCOR require periodic maintenance. When maintenance is required, a maintenance log form is used to enter the aircraft identification num
> Create an ER diagram for a hardware store. Make sure that you cover (at least) store transactions, inventory, and personnel. Base your ER diagram on an appropriate set of business rules that you develop. (Note: It would be useful to visit a hardware sto
> Suppose a manufacturer produces three high cost, low volume products: P1, P2, and P3. Product P1 is assembled with components C1 and C2; product P2 is assembled with components C1, C3, and C4; and product P3 is assembled with components C2 and C3. Compon
> Create the initial ER diagram for a video rental shop. Use (at least) the following description of operations on which to base your business rules. The video rental shop classifies movie titles according to their type: Comedy, Western, Classical, Science
> Create the initial ER diagram for a car dealership. The dealership sells both new and used cars, and it operates a service facility. Base your design on the following business rules: a. A salesperson can sell many cars but each car is sold by only one sa
> Suppose that you have been given the table structure and data shown in Table 6.9, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one jou
> What are the logical components of a client/server application and how are these components allocated in a client/server environment?
> Use the dependency diagram shown in Figure 6.8 to work the following problems. FIGURE P6.8 Initial Dependency Diagram for Problem 8 a. Break up the dependency diagram in Figure 6.8 to create two new dependency diagrams, one in 3NF and one in 2NF. b.
> Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinne
> The table structure shown in Table P6.6 contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic. Table P6.6 Sample EMPLOYEE Re
> To keep track of office furniture, computers, printers, and so on, the FOUNDIT company uses the table structure shown in Table P6.5. Table P6.5 Sample ITEM Records Attribute Name Sample Value Sample Value Sample Value ITEM_ID 231134-678 342245-2
> Using the STUDENT table structure shown in Table P6.4, do the following: Table P6.4 Sample STUDENT Records Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value STU_NUM 211343 200128 199876 199876 223456 STU_LN
> Using the INVOICE table structure shown in Table P6.3, do the following: Table P6.3 Sample INVOICE Records Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value INV_NUM 211347 211347 211347 211348 211349 PROD_NU
> Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.2 into a dependency diagram that is in at least 3NF.
> Given the sample records in the CHARTER table shown in Table P6.11, do the following: Table P6.11 Sample CHARTER Records Attribute Name Sample Value Sample Value Sample Value Sample Value CHAR_TRIP 10232 10233 10234 10235 CHAR_DATE 15-Jan
> Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a dependency diagram that is in at least 3NF.
> What, if any, client/server standards exist and how do such standards affect the client/server database environment?
> The Journal of E-commerce Research Knowledge is a prestigious information systems research journal. It uses a peer-review process to select manuscripts for publication. Only about 10 percent of the manuscripts submitted to the journal are accepted for pu
> “Martial Arts R Us” (MARU) needs a database. MARU is a martial arts school with hundreds of students. It is necessary to keep track of all the different classes that are being offered, who is assigned to teach each class, and which students attend each
> The FlyRight Aircraft Maintenance (FRAM) division of the FlyRight Company (FRC) performs all maintenance for FRC’s aircraft. Produce a data model segment that reflects the following business rules: • All mechanics are FRC employees. Not all employees are
> Some Tiny College staff employees are information technology (IT) personnel. Some IT personnel provide technology support for academic programs. Some IT personnel provide technology infrastructure support. Some IT personnel provide technology support for
> The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for the company’s training program. The HEG administrator gives you the description (see below) of the traini
> In Chapter 4, you saw the creation of the Tiny College database design. That design reflected such business rules as “a professor may advise many students” and “a professor may chair one department.” Modify the design shown in Figure 4.36 to include thes
> Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate. Granite Sales Company keeps information on employees and the departments that they work in. For each department, the department name, intern
> Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate. Tiny Hospital keeps information on patients and hospital rooms. The system assigns each patient a patient ID number. In addition, the patie
> Global Computer Solutions (GCS) is an information technology consulting company with many offices located throughout the United States. The company’s success is based on its ability to maximize its resources—that is, its ability to match highly skilled e
> Global Unified Technology Sales (GUTS) is moving toward a “bring your own device” (BYOD) model for employee computing. Employees can use traditional desktop computers in their offices. They can also use a variety of personal mobile computing devices su
> What is middleware and why is it a crucial client/server component?
> Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate. Two-Bit Drilling Company keeps information on employees and their insurance dependents. Each employee has an employee number, name, date of h
> Create an Order data-entry screen, using the ORDERS and ORDER_LINE tables in the RobCor database. To do this, you can use frames and other advanced ColdFusion tags. Consult the online manual and review the demo applications. NOTE The following pages show
> Modify the insert scripts (rc-5a.cfm and rc-5b.cfm) for the DEPARTMENT table so the users who can be manager of a department are only those who belong to that department.
> Create ColdFusion scripts to search, add, edit, and delete records for the VENDOR table in the RobCor database.
> Create ColdFusion scripts to search, add, edit, and delete records for the INVTYPE table in the RobCor database.
> Create ColdFusion scripts to search, add, edit, and delete records for the USER table in the RobCor database.
> You have some money to invest. Research – and document -- mutual funds information for investment purposes. Report your investment decision(s) based on the research you conduct. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B
> Vacation time is almost here! Research—and document—the destination(s) and activities of next summer’s vacation. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was the info
> Research – and document -- the purchase of a new computer. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was the information useful? Why or why not? d. What decision(s) di
> Research the purchase of a 20-year level term life insurance policy and report your findings. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was the information useful? Why
> Why is an initial ER model not likely to be the basis for the implementation of the database?
> You need to do your taxes. Download IRS form 1040 and look for online tax processing help, documenting your search. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was the i
> You are in the market for a new job. Search the web for your ideal job. Document your job search and your job selection. Requirements: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was
> The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players. Each team also has up to three coaches
> Create a complete ERD in Crow’s Foot notation that can be implemented in the relational model using the following description of operations. Hot Water (HW) is a small start-up company that sells spas. HW does not carry any stock. A few spas are set up in
> Use the following business rules to create a Crow’s Foot ERD. Write all appropriate connectivity’s and cardinalities in the ERD. • A department employs many employees, but each employee is employed by one department. • Some employees, known as “rovers,”
> Luxury-Oriented Scenic Tours (LOST) provides guided tours to groups of visitors to the Washington D.C. area. In recent years, LOST has grown quickly and is having difficulty keeping up with all of the various information needs of the company. The compa
> Create a Crow’s Foot notation ERD to support the following business operations: • A friend of yours has opened Professional Electronics and Repairs (PEAR) to repair smartphones, laptops, tablets, and MP3 players. She wants you to create a database to he
> Using the Crow’s Foot methodology, create an ERD that can be implemented for a medical clinic, using at least the following business rules: a. A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointmen
> United Helpers is a nonprofit organization that provides aid to people after natural disasters. Based on the following brief description of operations, create the appropriate fully labeled Crow’s Foot ERD. • Individuals volunteer their time to carry out
> What business rule(s) and relationships can be described for the ERD in Figure QB.4? Figure QB.4 PART PART VEND VENDOR PK PART CODE PK,FK1 VEND ID PK,FK2 PART CODE PK VEND ID PART PROD PK,FK1 PART CODE PK,FK2 PROD CODE PROD_CUST PRODUCT CUSTOMER PK
> Automata Inc. produces specialty vehicles by contract. The company operates several departments, each of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV. Before a new vehicle is built, the department places an order with
> What is the purpose of the conceptual design phase, and what is its end product?
> What is the difference between the database design scope and its boundaries? Why is the scope and boundary statement so important to the database designer?
> Why is the organizational structure relevant to the database designer?
> What factors relevant to database design are uncovered during the initial study phase?
> What are time-variant data, and how would you deal with such data from a database design point of view?
> When implementing a 1:1 relationship, where should you place the foreign key if one side is mandatory and one side is optional? Should the foreign key be mandatory or optional?
> What is a surrogate primary key, and when would you use one?
> Under what circumstances are composite primary keys appropriate?
> What primary key characteristics are considered desirable? Explain why each characteristic is considered desirable.
> What is an entity cluster, and what advantages are derived from its use?
> What is an entity supertype, and why is it used?
> The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for the company’s training program. The HEG administrator gives you the description (see below) of the traini
> Discuss the difference between a composite key and a composite attribute. How would each be indicated in an ERD?
> How would you (graphically) identify each of the following ERM components in a Crow’s Foot model?
> What is a recursive relationship? Given an example.
> Suppose you are working within the framework of the conceptual model in Figure Q4.5. Figure Q4.5- The Conceptual Model Given the conceptual model in Figure Q4.5: a. Write the business rules that are reflected in it. b. Identify all of the cardinalitie
