SIT772 Database And Information Retrieval

Assignment

DBMS

Database Model and implementation using SQL

Table of Contents

Concept of Data Modelling and use Entity-Relationship (ER) models to represent data.

Concept of Data Modelling

The method via which the data model could be created so that the database could be used for storage purposes. In other words, a specified set of rules, association between the different data objects and the representing the data conceptually is termed as data modelling. Data modeling enables visible records planning and enforces enterprise policies, compliance laws, and government information policies. Data models ensure consistency in design ideas, default values, semantics, protection even as making sure information exceptional.

Data Model

Data model could be termed as an abstract model that organizes information definition, statistics semantics, and records consistency limits. The facts version emphasizes what facts is needed and how it should be prepared as opposed to what will be executed with the information. The Data Model is similar to the architectural design program, which enables to construct sensible models and to set up relationships between statistics items.

Necessity of Data Model

Data Model is essential due to the following goals which it could fulfil. The goals are listed below:

• Ensures that each one information items required by the website are appropriately represented. Data breach will lead to the advent of mistakes reports and produce incorrect effects.
• The information model allows to layout a website on the intellectual, bodily and intellectual tiers.

• The Data Model structure enables outline associated tables, key and external keys and stored tactics.
• It offers a clear image of primary facts and may be utilized by net developers to build a transportable internet site.
• It is likewise beneficial to perceive missing and undesirable data.
• Although to start with constructing a statistics version is labor-in depth and time-consuming, over time, it makes upgrading your IT infrastructure and preservation cheaper and faster.

Pros of Data Model

• The important intention of the design statistics model is to make sure that the data gadgets provided by the working group are appropriately represented.
• The data version need to be designated enough to be used to build a transportable database.
• The facts in the information model can be used to describe the relationships between tables, key and external keys, and saved procedures.
• The Data Model allows the business to communicate within and between corporations.
• The information model facilitates to bring together records maps within the ETL process
• Help pick out the right facts assets to complete the model

Cons of Data Model

• To improve the Data version one has to recognize the bodily capabilities saved within the information.
• This navigational system produces complex utility development, management. Therefore, it requires know-how of biographical data.
• Even minor modifications made to the shape want to be changed all through the application.
• There isn’t any set language to manipulate facts in DBMS.

Interpretation of Data Modelling

• Data modelling is the manner of developing a information model so that data is saved on a database.
• Data models make sure consistency in layout ideas, default values, semantics, security while making sure statistics exceptional.
• The Data Model structure helps define associated tables, key and external keys and saved procedures.
• The main purpose of the idea model is to establish businesses, their attributes, and their relationships.
• A logical facts model defines the shape of statistics items and units the relationships among them.
• The Physical Data Model describes the direct use of a information version website.
• The fundamental goal of the design records model is to ensure that the statistics items provided with the aid of the working team are as it should be represented.
• The major disadvantage is that even minor modifications made to the structure need to be modified for the duration of the application.

ER Model

The ER model represents the Entity Relationship Model inside the Database Management System (DBMS). It is step one in design to offer the flow of thoughts and is a demand of the organization’s DFD (Data Flow Diagram). It is the basis for constructing related models. It isn’t always that a lot training is needed to design a database assignment. It could be very smooth to transform an E-R model into associated tables or make trendy tables.

It is a diagram of a high-degree statistics version that describes the conceptual concept of a database.

Data Representation using ER Model

As the data is not given in the question, we will take into consideration the case study of city jail from SQL programming. Before drawing the actual ER Model, the steps are shown below:

Implementation of Relational Database using SQL.

Before going to actual implementation of Relational Database using SQL, let us get some description regarding its background.

Background of Relational Database

Database facts control device overcomes the various commonplace document system machine shortcomings. One of the important thing features of a database machine is that facts is stored as one logical unit. The implication of that is that despite the fact that the records can be distributed to all a couple of transportable documents, the website transfers the statistics as to be had in a unmarried facts repository. Editing data in a single logical repository allows for easy deception and data retrieval, not like traditional document systems where the editor need to specify what and the way to retrieve information.

For database programs, best want specification to be finished, DBMS (Database Management System) does something else. Another gain of the archive approach is that, due to the fact the statistics is to be had on the same website online, the information in one-of-a-kind seen regions need to not be duplicated. Data duplication is one manner to maintain facts integrity. If statistics is authorized to be repeated, mistakes can occur if one information event is changed and every other situation remains the same. If information is permitted to be duplicated, additional care and device resources are required to make sure that the information remains compact.

Implementation

For implementing the relational database, we will consider the following case study given below:

The requirements database set is shown below:

Next step deals with drawing the ERD Diagram, The ERD Diagram is shown below:

The above diagram is just a mere example of conceptual model design. It does not provide details regarding the actual implementation design Phase.

Design Phase (Actual Implementation)

For this case, the translation of ERD into the data dictionary takes place. The translation process is shown below:

SQL Statements

The SQL Statements are used for table creation. These statements are listed below:

Conclusion

This assignment deals with the description of Conceptual Data Model in brief and implementing the relational database model using SQL. For this case, the concept of data model along with its features, pros and cons are discussed in detail and for implementation purposes, as the case study was not given, it is taken and implemented using SQL.

References

Caldeira, C. P. (2008). Teaching SQL. ACM SIGCSE Bulletin, 40(3), 340–340. https://doi.org/10.1145/1597849.1384382

Guru99. (2018, December 24). What is Data Modelling? Conceptual, Logical, & Physical Data Models. Guru99.com. https://www.guru99.com/data-modelling-conceptual-logical.html

Tutorial: Step by Step Database Design in SQL. (2015). Linkedin.com. https://www.linkedin.com/pulse/tutorial-step-database-design-sql-david-mccaldin