Write the relational schema and draw dependency diagram to meet 3nf

write the relational schema and draw dependency diagram to meet 3nf

Create a database whose tables are at least in 3NF, showing the Write the relational schema, draw its dependency diagram and identify all. write the relational schema and draw its dependency diagram. Using the answer to Problem 8, write the relational schema and create a set of dependency diagrams that meet 3NF requirements. Rename attributes to meet the naming. Identify all dependencies, including all transitive dependencies. b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements.

Next Given that information, write the relational schema and draw the dependency diagram. The answers to this problem are shown in Figure P5. Initial Dependency Diagram Problem 8 Solution: The dotted transitive dependency lines indicate that these transitive dependencies are subject to interpretation. The relational schema may be written as follows: Using the answer to Problem 8, create the relational schemas and create a set of dependency diagrams that meet 3NF requirements.

Rename attributes to meet the naming conventions, and create new entities and attributes as necessary. The dependency diagrams are shown in Figure P5. We have added a sample relational diagram to illustrate the relationships at this point.

The relational schemas are written below Figure 5. The dependency diagrams in Figure P5. A building can have many rooms, so knowing the building code will not tell you what the room in that building is. Therefore, the transitive dependency is marked properly in the Problem 8 solution.

Once again, the nature of this relationship is not and cannot be reflected in the dependency diagram. NOTE We also assume here that each item has a unique item code and that, therefore, an item can be located in only one place at a time. However, we demonstrate in Appendixes B and C that inventory control requirements usually cover both durable and consumable items. Although durables such as tables, desks, lamps, computers, printers, etc.

Therefore, a given inventory description such as "8.

write the relational schema and draw dependency diagram to meet 3nf

We demonstrate in Appendixes B and C how such a condition may be properly handled. Also, keep in mind that a room may be related to the building in which it is located. Each ROOM is located in a single building.

Therefore, you can expand the design shown in Figure P5. This solution assumes that a room is directly traceable to a building. Although optionalities make excellent default conditions, it is always wise to establish the optionality based on a business rule.

NOTE Problems 11 — 14 may be combined to create a case or miniproject. The table structure shown in Table P5. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic. The dependency diagram is shown in Figure P5. Note that the order of the attributes has been changed to make the transitive dependencies easier to mark.

In any case, the order in which the attributes are written into a relational database table is immaterial. The relational schema is written below Figure P5. Break up the dependency diagram you drew in Problem 11 to produce dependency diagrams that are in 3NF.

write the relational schema and draw dependency diagram to meet 3nf

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 there are no multivalued attributes, that the naming conventions are met, and so on.

Dependency diagrams have no way to indicate multi-valued attributes, nor do they provide the means through which such attributes can be handled. Therefore, the solution to this problem requires a basic knowledge of modeling concepts, once again indicating that normalization and design are part of the same process. We have created the appropriate entities to avoid the use of multi-valued attributes. Overall employment longevity is likely to be included, too … employers often find it useful to keep expensive job turnover rates low.

This problem is a great source of discussion material! Using the results of problem 12, draw the relational diagram. The relational diagram is shown in Figure P5. Suppose you are given the following business rules to form the basis for a database design.

For example, a dinner may be composed of a fish entree, rice, and corn. Or the dinner may be composed of a fish entree, a baked potato, and string beans. This structure uses a composite primary key. Break up the dependency diagram you drew in Problem 15 to produce dependency diagrams that are in 3NF and write the relational schema. 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.

Actually, there is no way to prevent the existence of multi-valued attributes by merely following normalization rules. Instead, knowledge of E-R modeling concepts will help define the environment in which the multi-valued attributes are dealt with. Although we keep repeating the message, it is worth repeating: The design process can be described thus: Such a process will yield the dependency diagrams shown in Figure P4.

After all, zip codes tend to be thought of as part of the address. Such a solution would merely add complexity without adding functionality. Getting the number of acceptances for a given dinner by a given date would be simple, thus enabling the catering service to plan the dinner better.

The relational schemas follow: One would also expect that the manager would want to track YTD expenditures of the members and, of course, there would have to be an invoicing module for billing purposes.

And what about keeping track of member balances as the members charge meals and make payments on account? The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P5.

Carson Midwest 56 Karl M.

Learning Journal : May

Spenser Midwest 22 Julian H. Donatello Midwest 34 Gerald K. Ricardo Southeast 38 Anne T. Dimarco Southeast 45 Geraldo J. Jamison Southeast 34 Gerald K. Ricardo Southeast The table was created to enable the manager to match clients with consultants.

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: For example, a contract may list consulting services in database and networking. 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. 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.

Here is a perfect illustration of the value of business rules. If the business rules had not been available, the sample record would produce ambiguities. For example, if you only look at the sample data in the one available record, defining the relationships between client, contract, date, consultant, and expertise would have been difficult, at best.

The business rules augment the original data and their use removes the ambiguities. The business rules help establish that a client can sign more than one contract, so you need more than the client number to identify the remaining attributes. Also, the same client can sign multiple contracts on the same date or on different dates, using the same set of consultants for each contract or a different set of consultants for each contract.

Remember also that the consultants have more than one area of expertise, so the same consultant may work on different contracts for the same client or for different clients. Given the combination of the business rules and the sample record in the original problem — or given the use of the two records provided in the first part of this discussion -- the dependencies show up in Figure P5.

The REGION attribute has been duplicated to show all of the dependencies in a single diagram The relational schema is written as follows: Break up the dependency diagram you drew in Problem 18 to produce dependency diagrams that are in 3NF and write the relational schema.

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. Although the normalization procedure has left us with the 3NF system shown in Figure P5. It would be appropriate to point out that the multivalued attributes encountered in Problem 18's sample values are probably best handled through the use of composite entities. We will resolve those issues in the answers to subsequent problems.

Here is yet another indication that normalization, while very useful as a tool to help eliminate data redundancies, is incapable of serving as the sole source of good database design. At this point, the entities are properly identified and the attributes are all accounted for. However, several of these entities are related to each other through M: Normalization does not provide information about the nature of the relationships, thus illustrating the need for combining normalization and ER modeling techniques.

write the relational schema and draw dependency diagram to meet 3nf

The relational schemas are written as follows: For example, processing requirements usually dictate that the attributes be made Chapter 5 Normalization of Database Tables more atomic. Also, remember that this simple system lacks many important entities and attributes. For instance, at this point there's no way to contact the clients, nor can clients contact the consultants.

Clearly, we ought to add addresses and phone numbers. However, we have added some crucial relationships to enable us to track billing charges by class and to track billable hours by class, by consultant, and by class. You can let your students use database software such as Microsoft Access to implement this system.

Naturally, you can add tables and attributes to enable the system to handle invoicing and reporting of consulting activities by consultant, by type, by client, and so on. We have added a few of the appropriate entities and attributes in the answer to problem Whether or not optionalities are included in the ERD depends on the business rules and on the operational requirements. For example, you can infer from Figure P5.

It is again worth emphasizing that many optionalities exist for operational reasons. In any case, the database designer is obligated to develop precise business rules to make sure that the data environment is properly reflected in the design.

DM-Practice: Advanced Data Modeling

You can use this file as a point of departure for the addition of other entities that may be dictated by real world requirements. For example, ask your students if it would be useful to track payments on account by customers. Or if it would be useful to keep track of the remaining balances of the contract amount as the consulting work progresses. This basic model is a perfect vehicle for the development of a real world design that is useful and practical. Make sure that you label all dependencies.

Look at the data values to determine the nature of the relationships. For example, note that employee Melton has flown two charter trips as pilot and one trip as copilot. Decompose the dependency diagram in Problem 21 to create table structures that are all in 3NF and write the relational schema. The normalized dependency diagram is shown in Figure P5. Make sure that the ERD yields a database that can track all of the data shown in Problem Show all entities, relationships, connectivities, optionalities, and cardinalities.

This flaw has the following consequences: Many of the smaller aircraft that used in charter flying require only that a pilot and a functioning autopilot be used. In fact, the Federal Air Regulations FARs that govern charter aviation permit single pilot operations for aircraft that have less than a 12,lbs.

Note that this modification makes it possible to assign any Chapter 5 Normalization of Database Tables number of crewmembers. To ensure that the crewmembers are properly qualified, a job attribute can be added to the EMPLOYEE entity and the applications software can then assign crewmembers based on job classifications such a pilot, loadmaster, flight attendant, etc.

In this case, only two crew members are required, but the design permits the addition of many more crew members without making structural changes in the database tables. Such flexibility is the essence of good design. Please note that this database contains quite a few sample applications, but this application development example is only a partially completed prototype. Because our Database Systems book is not an applications development book, no attempt has been made to produce a fully functioning end user application.

Instead, the forms, reports, and other components of this database may be used to illustrate the effect of database design decisions, which are the focal point of this book. The charter record shown in Figure P5.

Break up the dependency diagram to create two new dependency diagrams, one in 3NF and one in 2NF. Instead, C determines the value of a key attribute.

Modify the dependency diagrams you created in Problem 24 to produce a set of dependency diagrams that are all in 3NF. To keep the entire collection of attributes together, copy the 3NF dependency diagram from Problem 24 and then show the new dependency diagrams that are also in 3NF. The solution is shown in Figure P5. Note that the A, C, and E attributes in the first three structures can be used as foreign keys in the fourth structure.

Suppose that you have been given the table structure and data shown in Table 5. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal. Systems KDD, Asst. Draw the dependency diagram. However, if an office can be shared among two or more professors, the dependency shown here does not exist.

Because this dependency is not clear-cut, the dependency line is shown as a dashed line. If so, the office number identifies the professor who uses that office. This condition yields a dependency.

We have indicated this potential transitive dependency through a dashed dependency line. One could safely assume that administrators at all levels would not care to be tied by such a restrictive office assignment requirement. Therefore, we will remove this restriction in the remaining problem solutions. Also, note that there is no reliable way to identify the effect of multivalued attributes on the dependencies. Therefore, these attributes are not marked with a solid arrow line. But you do not have a unique identification for each of those attribute values.

Identify the multivalued dependencies. Review the discussion that accompanies the solution for problem 27a. Create the dependency diagrams to yield a set of table structures in 3NF. Note that we have assumed that it is possible that professors can share an office. Eliminate the multivalued dependencies by converting the affected table structures to 4NF. The structures shown in Figure 5.

Part 3.1 Functional Dependency in DBMS in HINDI - DATE BASE MANAGEMENT SYSTEM

Yet this normalization does not yield a viable database design. Here is another opportunity to stress that normalization without data modeling is a poor way to generate useful databases. Note that we have assumed that an advisee can have only one advisor, but that an advisor can have many advisees. A professor can write for many journals and each journal includes articles by many professors.

Similarly, a professor can serve on many committees and each committee is composed of several professors. The dependency diagrams shown in Figure P5. Unfortunately, while this solution meets the normalization requirements, it lacks the ability to properly link the professors to committees and journals. This solution would yield tables P5. The preceding table structures create multiple redundancies. Therefore, this solution is not acceptable. Here is yet another indication that normalization, while very useful, is not always usually?

For example, a dinner may be composed of a fish entree, rice, and corn, or the dinner may be composed of a fish entree, a baked potato, and string beans. Because the manager is not a database expert, the first attempt at creating the data- base uses the structure shown in Table P6. Given the table structure illustrated in Table P6.

This structure uses a composite primary key. Break up the dependency diagram you drew in Problem 7a to produce depen- dency diagrams that are in 3NF, and write the relational schema. Also, make sure that the new dependency diagrams contain attributes that meet proper design criteria; i.

Use the dependency diagram shown in Figure P6. Break up the dependency diagram shown in Figure P6. Modify the dependency diagrams you created in Problem 8a to produce a set of dependency diagrams that are in 3NF. Suppose you have been given the table structure and data shown in Table P6.

The data reflects that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal. Draw the dependency diagram. Identify the multivalued dependencies.

Create the dependency diagrams to yield a set of table structures in 3NF. Eliminate the multivalued dependencies by converting the affected table struc- tures to 4NF. You might have to create additional attributes to define the proper PKs and FKs. Make sure that all of your attributes conform to the naming conventions. The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P6. For example, if the client needs 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 manager 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: For example, a contract may list consulting services in database design and networking. For example, a con- sultant might be classified as an expert in both database design and networking.

For example, the con- sulting company might employ many consultants who are networking experts. Given this 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. Break up the dependency diagram you drew in Problem 10a to produce depen- dency diagrams that are in 3NF and write the relational schema. You might Copyright Cengage Learning. Also make sure that the new dependency dia- grams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes, that the naming conventions are met, and so on.

Write the relational schema and draw the dependency diagram for the table structure. Make sure that you label all dependencies. Look at the data values to determine the nature of the relationships. For example, note that employee Melton has flown two charter trips as pilot and one trip as copilot. Decompose the dependency diagram you drew to solve Problem 11a to create table structures that are in 3NF and write the relational schema. Make sure the ERD yields a database that can track all of the data shown in Problem