Database Design And Sql

[Prisc Chandola]

Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.[1] A database management system manages the data accordingly.

In a majority of cases, the person designing a database is a person with expertise in database design, rather than expertise in the domain from which the data to be stored is drawn e.g. financial information, biological information etc. Therefore, the data to be stored in a particular database must be determined in cooperation with a person who does have expertise in that domain, and who is aware of the meaning of the data to be stored within the system.

This process is one which is generally considered part of requirements analysis, and requires skill on the part of the database designer to elicit the needed information from those with the domain knowledge. This is because those with the necessary domain knowledge often cannot clearly express the system requirements for the database as they are unaccustomed to thinking in terms of the discrete data elements which must be stored. Data to be stored can be determined by Requirement Specification.[2]

(NOTE: A common misconception is that the relational model is so called because of the stating of relationships between data elements therein. This is not true. The relational model is so named because it is based upon the mathematical structures known as relations.)

ER models are commonly used in information system design; for example, they are used to describe information requirements and / or the types of information to be stored in the database during the conceptual structure design phase.[3]

Once the relationships and dependencies amongst the various pieces of information have been determined, it is possible to arrange the data into a logical structure which can then be mapped into the storage objects supported by the database management system. In the case of relational databases the storage objects are tables which store data in rows and columns. In an Object database the storage objects correspond directly to the objects used by the Object-oriented programming language used to write the applications that will manage and access the data. The relationships may be defined as attributes of the object classes involved or as methods that operate on the object classes.

The way this mapping is generally performed is such that each set of related data which depends upon a single object, whether real or abstract, is placed in a table. Relationships between these dependent objects are then stored as links between the various objects.

Each table may represent an implementation of either a logical object or a relationship joining one or more instances of one or more logical objects. Relationships between tables may then be stored as links connecting child tables with parents. Since complex logical relationships are themselves tables they will probably have links to more than one parent.

This step involves specifying the indexing options and other parameters residing in the DBMS data dictionary. It is the detailed design of a system that includes modules & the database's hardware & software specifications of the system. Some aspects that are addressed at the physical layer:

A database includes bulk information deposited in a framework, making it easier to locate and explore relevant details. A well-designed database contains accurate and up-to-date information for analysis and reporting. We cannot stress enough the importance of a database for a company dealing with heaps of data regularly. The database design can play a crucial role in efficiently executing queries and ensuring information consistency.

Database design defines the database structure used for planning, storing, and managing information. To ensure data accuracy, you must design a database that only stores relevant and valuable information.

The reliability of data depends on the table structure, whereas creating primary and unique keys guarantees uniformity in the stored information. You can avoid data replication by forming a table of probable values and using a key to denote the value. So, the alteration happens only once in the main table whenever the value changes.

As the general performance depends on its design, a good database design uses simple queries and faster implementation. Also, it is easy to maintain and update. On the other hand, when the database is poorly designed, even trivial interruptions may harm stored events, views, and utilities.

There are various stages in database development. However, it is not necessary to follow each of the steps sequentially. The life cycle can be divided into three phases: requirement analysis, database designing, and implementation.

Database designing generally starts with identifying the purpose of your database. The relevant data is then collected and organized into tables. Next, you specify the primary keys and analyze relationships between different tables for an efficient data design. After refining the tables, the last step is to apply normalization rules for table standardization.

The first step is to determine the purpose of your database. For example, if you are running a small home-based business, you can design a customer database that maintains a list of consumer info to generate emails and reports. Hence, understanding the importance of a database is vital.

At the end of this step, you will have a strong mission statement that you can refer to throughout the database design process. It will help you concentrate on your objectives when making important decisions.

The next step is to collect all kinds of information you might want to store in the database. Begin with the existing data and mull over the questions you want your database to answer. It will help you decide which data needs to be recorded.

Once you have amassed all the necessary data items, the next step is to divide them into main entities or subject areas. For example, if you are a retailer, some of your main entities could be products, customers, suppliers, and orders. Each entity will then become a separate table.

After determining the initial set of columns for every table, you can refine them. For instance, you can record customer names in two separate columns: first and last names. Likewise, you can store the address in five distinct columns based on address, town, state, zip code, and region. It will make it more convenient for you to filter information.

The next step to improve your database design is to select a primary key for every table. This primary key is a column or a set of columns that pinpoint each row distinctively. For instance, in your customer table, the primary key could be customer ID. It will allow you to identify unique rows based on the customer ID.

More than one primary key can also exist, called a composite key, including multiple columns. For example, the primary keys in your Order Details table could be order ID and product ID. The composite key can be made using fields with similar or varying data types.

After dividing data into tables, information needs to be brought together in a meaningful manner. So, you can explore each table and Identify the connection between the tables. If required, you can add fields or form new tables to simplify the relationship based on the types of information.

Now that you have all the required tables, fields, and relationships, the next step is to refine your database design by creating and populating your tables with mockup information. Experiment with the sample data by running queries or adding new items. It will help you analyze your design for faults, and you will be able to highlight possible errors. If needed, adjust your design to mitigate those problems.

The last step is to implement the normalization rules for your database design. A systematic approach removes redundancy and unwanted characteristics, such as Insertion, Update, and Deletion irregularities.

A good database design can help save disk storage space by reducing data redundancy. Along with maintaining data precision and reliability, it allows you to access data in various ways. Moreover, a well-designed database is easier to use and maintain, making integration a breeze.

A properly designed database provides you with access to up-to-date, accurate information. Because a correct design is essential to achieving your goals in working with a database, investing the time required to learn the principles of good design makes sense. In the end, you are much more likely to end up with a database that meets your needs and can easily accommodate change.

This article provides guidelines for planning a desktop database. You will learn how to decide what information you need, how to divide that information into the appropriate tables and columns, and how those tables relate to each other. You should read this article before you create your first desktop database.

Certain principles guide the database design process. The first principle is that duplicate information (also called redundant data) is bad, because it wastes space and increases the likelihood of errors and inconsistencies. The second principle is that the correctness and completeness of information is important. If your database contains incorrect information, any reports that pull information from the database will also contain incorrect information. As a result, any decisions you make that are based on those reports will then be misinformed.

Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.

To find and organize the information required, start with your existing information. For example, you might record purchase orders in a ledger or keep customer information on paper forms in a file cabinet. Gather those documents and list each type of information shown (for example, each box that you fill in on a form). If you don't have any existing forms, imagine instead that you have to design a form to record the customer information. What information would you put on the form? What fill-in boxes would you create? Identify and list each of these items. For example, suppose you currently keep the customer list on index cards. Examining these cards might show that each card holds a customers name, address, city, state, postal code and telephone number. Each of these items represents a potential column in a table.

c80f0f1006