COMP9120 Database Management Systems

Assignment

Database Management System

Functional Dependency Diagram and decomposition into 3NF Relations.

Solution

This assignment deals with creation of functional dependency ER Diagram and decomposing it into the 3NF Relations. We will consider it for the following case given below (Kroenke et al., 2018):

To draw the Functional dependency diagram, its essential to get overview about the ER Diagram and concept of logical design because it depends on the above concept. So both the concepts are explained in brief below:

Overview of ERD Diagram

ER Diagram additionally known as ERD is a diagram displaying the relationships of commercial enterprise units saved on a website(Guru99, 2019). In other words, ER diagrams assist to explain the logical structure of data. ER diagrams are created primarily based on three basic concepts: enterprise, attributes and relationships. ER drawings contain diverse symbols that use rectangles to represent agencies, ovals to describe functions and diamond structures to represent relationships. It is a facts version of a excessive-stage idea. The ER model enables to systematically analyze information desires with the intention to produce a nicely-designed website. The ER Model represents actual-international organizations and relationships among them. Creating an ER Model in DBMS is taken into consideration the first-rate way before the use of your database.

ER Modeling enables you to analyze information wishes systematically if you want to produce a nicely-designed website. Therefore, it is considered the nice practice to complete ER modeling earlier than the usage of your database.

Reasons to use ERD Diagram(ANISAH MOHD SAAD & MUNIANDI, 2020)

• Helps you outline phrases associated with enterprise dating modeling
• Give a preview of how all your tables have to be connected, which fields can be in every table
• Helps to outline organizations, attributes, relationships
• ER diagrams are translated into related tables that let you fast create records
• ER diagrams can be utilized by internet designers as a statistics utilization plan for specific software program applications
• The database clothier gains a higher knowledge of the facts that have to be contained at the website with the help of ERP diagram
• The ERD Diagram lets in you to hook up with a logical internet site shape for users

The ER diagram in general describes how the consumer locations an order and the way the order is processed with the aid of the corporation. After placing an order with the organization the business enterprise offers a unique order id and symptoms the client agreement and fits the ac in the targeted region as described by using the purchaser by way of supplying the installation alternatives. After making the entire charge invoice is produced through the client. The system for processing ac application is much like the above.

Concept of Logical Design(Overview of Logical Design, 2019)

This type of design may be taken into consideration as a summary degree layout in a pc program wherein programmers prepare statistics into a chain of logical relationships referred to as attributes or corporations. Business refers back to the statistics component, even as the characteristic describes the precise functions of the business.

Creation of Logical Designs (Thiru, 2017)

It is a idea, an abstract type of design. You aren’t managing details of physical pastime but; it best works by means of defining the sorts of statistics you want.

The rational layout manner entails organizing records into a chain of logical relationships referred to as agencies and attributes. A enterprise represents a positive piece of information. In related databases, a business commonly creates a table map. An characteristic is part of a commercial enterprise and enables outline business variety. In related databases, column map attribute.

You can create a logical design using pen and paper, or you may use a layout device like Oracle Warehouse Builder or Oracle Designer. Although business relationship drawing is often related to more common models consisting of on-line transaction processing (OLTP) programs, the methodology continues to be useful in modeling size. You just have a look at it in another way. In dimensional simulation, as opposed to trying to find atomic gadgets of information and all the relationships between them, you are trying to identify which table statistics is inside the center proper and which table records is length related.

The most effective output of a logical design is a set of corporations and attributes that correspond to reality tables and tables of significance. Another way to map performance facts out of your source to difficulty-specific statistics on your warehouse facts schema. Identifies enterprise topics or facts fields, explains relationships among enterprise subjects, and specifies attributes for every topic.

Factors that assist you determine the warehouse schema data version in your source data and your consumer needs. In a few instances, you may find a source version to your corporation’s commercial enterprise data version and then re-engineer a logical statistics model on your statistics repository. The real implementation of a logical statistics repository version may also require some modifications due to your gadget parameters – system size, variety of users, storage capacity, network type, and software.

Functional Dependency Diagram

After reviewing the concept of ER Diagram and Logical Design, we can draw the functional dependency diagram as shown below:

Decomposition into 3NF Relations

The Decomposition are shown below:

Relational Schema

The Relational Schema is shown below;

ERD Diagram

The ERD Diagram is shown below:

Conclusion

This assignment deals with the creation of functional dependency diagram. The Functional dependency diagram belongs to the concept of logical design in DBMS. Hence, the assignment begins with the brief description about the concept of ERD Diagram and the logical design followed by the functional dependency diagram, decomposition into the 3NF relation from the functional dependency diagram, relational schema and the ERD diagram derived from the functional dependency diagram.

References

Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) From Relational Database Schem. International Journal of Database Theory and Application, 8(3), 15–26. https://doi.org/10.14257/ijdta.2015.8.3.02

ANISAH MOHD SAAD, N., & MUNIANDI, M. (2020). The Reflections on the using of Oracle Data Modeler in Creating Entity Relationship Diagram (ERD). International Journal of Research Publications, 66(1). https://doi.org/10.47119/ijrp1006611220201601

Czenky, M. (2010). Normalization based on dependency diagram. Teaching Mathematics and Computer Science, 8(1), 121–132. https://doi.org/10.5485/tmcs.2010.0245

Guru99. (2019, May 10). ER Diagram Tutorial in DBMS (with Example). Guru99.com. https://www.guru99.com/er-diagram-tutorial-dbms.html

Jou, J. H., & Fischer, P. C. (1982). The complexity of recognizing 3NF relation schemes. Information Processing Letters, 14(4), 187–190. https://doi.org/10.1016/0020-0190(82)90034-5

Kroenke, D. M., Auer, D. J., Vandenberg, S. L., & Yoder, R. C. (2018). Database processing : fundamentals, design, and implementation. Pearson.

Kwan, I., & Li, Q. (1999). A hybrid approach to convert relational schema to object-oriented schema. Information Sciences, 117(3-4), 201–241. https://doi.org/10.1016/s0020-0255(99)00009-2

Overview of Logical Design. (2019). Oracle.com. https://docs.oracle.com/cd/A87860_01/doc/server.817/a76994/logical.htm

Packman, J. C., & Kidd, C. H. R. (1980). A logical approach to the design storm concept. Water Resources Research, 16(6), 994–1000. https://doi.org/10.1029/wr016i006p00994