Database normalization is a database design technique that eliminates undesirable characteristics such as Insertion, Update, and Deletion Anomalies and reduces data redundancy. Normalization rules divide larger tables into smaller tables and use relationships to connect them. SQL normalization aims to eliminate redundant (repetitive) data and ensure logical data storage. This essay explains the database version and provides more information on the miniature database choice of dependencies, keys and its level of normalization applied.
A relational database is a collection of data items linked together by pre-defined relationships. These items are laid out in a table format with columns and rows. Tables store data about the objects that will be represented in the database. A field keeps the actual value of an attribute, while each column in a table holds a specific type of data. The table’s rows represent a group of related matters for a single object or entity (Kalinin and Severing, 2019). A primary key is a unique identifier assigned to each row in a table, and foreign keys link rows from different tables. Without reorganizing the database tables, data can be accessed in various ways.
The database contains several tables: a produce table, Animal product table, grains table, suppliers table, and a purchases table. The tables have various attributes, including ITEMID, SUPPLIERIP, GRAINED, TYPE, TYPE, STOCK QTY, and NEXT DELIVERY for the Grains table. The suppliers’ table contains SUPPLIERID, LAST DELIVERY, SPECIALITY, and ACTIVE attributes. The purchases Table has ITEMID, TOTALBOUGHT, TOTAL SOLD, TOTALREV, and MARGIN attributes. The produce table contains the various details ITEMID, SUPPLIERID, PLU CODE. PRODUCT NAME, TYPE, STOCK QTY, and NXTDELIVERY. Lastly, the animal products table has the following attributes ITEMID SUPPLIERID. ANPRDNAME, TYPE, STOCK QTY, and NXTDELIVERY. The data storage format that can hold a specific type or range of values is referred to as database data types. When data is stored in variables in computer programs, each variable must have its data type. The database data types include char, number, date, which define the attributes in various entities.
In normalization, their three standard levels are commonly known as rules; a “normal form” is the name given to each level. The database is said to be in “first normal form” if the first rule is followed. The database is said to be in “third normal form” if the first three rules are followed (Kalinin and Severin, 2019). The database belongs to the third level of normalization because it observes all three directions. For example, it does not use a single table to store similar data; tables use foreign keys to relate and fields that do not depend on the key.
A constraint that applies to or defines the relationship between attributes is called a dependency key. The SUPPLIERID in the supplier’s table is a dependence attribute. The relation makes the supplier’s table dependent on the other table entries. Lastly, since this is a rational database, the table is highly dependent on primary and foreign keys for relation; for example, the ITEMID is a primary and a foreign key. The miniature database also demonstrates use the NULL query that is satisfied if the column contains a null value or if a condition is evaluated for holding one or more NULL values. The database contains most fields that are allowed to be empty and are indicated in the NULLABLE {NO} column of the table. The language used for scripting the database is SQL (Structured Query Language).
Reference
Kalinin, I. V., & Severin, M. V. (2019). Evaluating the effectiveness of database normalization constraints in media stream processing applications. International Multidisciplinary Scientific GeoConference: SGEM, 19(2.1), 257-261.