Knowledge in computer world or principles of creating a Database

lenchezzz

An important part of web developer’s world is working with the database. The word working also includes the creation of databases. Today I will discuss the basic principles and rules for creating the database and give an example based on already familiar gingerbread man.

The purpose of the development of any database is to store and use information about any subject domain.

The main objectives of the database design:

  1. Ensure of storage in a database of all relevant information.
  2. Ensure availability of data for all necessary requests.
  3. Reducing redundancy and duplication of data.
  4. Ensure the integrity of data (correctness of their contents): exclusion of contradictions in the content of the data, exception of losing data, etc.

 

In order to achieve these objectives the following tools can be used:

  • The relational data model – a convenient way to represent subject domain data.
  • SQL language – a universal method for the manipulation of such data.

 

Let’s look at the main stages of database design:

STEP 1 Conceptual (Infological) Design – construction of formalized domain model. The model like this is built using the standard language means, usually graphical, such as ER-diagrams (“entity-relationship”). This model is built without targeting any particular database.

The main elements of this model:

  • Description of the subject area objects and relations between them.
  • Description of user information needs (description of basic queries to the database).
  • Description of algorithmic relationships between data.
  • Description of integrity constraints, that is requirements for valid data values and relations between them.

 

STEP 2 Logical Design – Display Infological model data model used in a specific database, such as relational data model. For relational databases, this model – a set of tables, usually with key fields, relationships between tables. If infological model is constructed in the form of ER-diagrams, the logic design is a construction of tables by certain formalized rules, as well as the normalization of these tables.

 

STEP 3 Physical design – implementation of the previous model by means of a specific database, as well as the range of solutions related to the physical storage environment: the choice of disk storage management, data access methods, data compression techniques, etc. – These problems are solved mainly by means of the database and the database are hidden from the developer.

 

Let us consider an example of each of the steps. For example, we have a bakery that bakes gingerbread men only. However, they are all different and not one but a few bakers works in a bakery. For every cookie, a recipe is used.

STEP 1

From this simple description of the subject area – the bakery, we can distinguish several entities (tables) and with the data that will make up the database. I identified the following entities: Gingerbread Man, Baker, Recipe, Eyes color (since the cookies must be different).

Starting building of the database schema and arrive at the following.

1

 

STEP 2

In the next step we need to describe the selected entity, ie add attributes describing their characteristics and their data types. At me it turned the following:

2

But to perform point #3 of main objectives of database design, we need to follow the rules of normalization of tables (you can read about them here), ie it is necessary to add a few extra tables. We add Ingredient table since the table Recipe may contain a few ingredients, and if you do not add an extra table we will have a Many to Many relationship which is unacceptable during normalization. We will also add table Colors since the color of the eyes can be only in a certain range. Therefore with all relations database schema would look like this:

3

 

STEP 3

This step usually takes some time to reproduce exactly all that we have shown in the diagram. But now there are many tools to auto generate code to create the database. To construct the diagram I used a site DBdesigner, and based on my database schemaI got the following code:

CREATE TABLE `Gingerbread Man` (
`ID_cookie` INT NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Surname` varchar(50) NOT NULL,
`Gender` varchar(20) NOT NULL,
`ID_eyes_color` INT NOT NULL,
`ID_baker` INT NOT NULL,
`ID_recipe` INT NOT NULL,
PRIMARY KEY (`ID_cookie`)
);

 
CREATE TABLE `Eyes color` (
`ID_eyes_color` INT NOT NULL AUTO_INCREMENT,
`ID_color` INT NOT NULL,
PRIMARY KEY (`ID_eyes_color`)
);

 

CREATE TABLE `Colors` (
`ID_color` INT NOT NULL AUTO_INCREMENT,
`Name_color` varchar(50) NOT NULL UNIQUE,
PRIMARY KEY (`ID_color`)
);


CREATE TABLE `Baker` (
`ID_baker` INT NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Surname` varchar(50) NOT NULL,
`Gender` varchar(20) NOT NULL,
PRIMARY KEY (`ID_baker`)
);

 
CREATE TABLE `Recipe` (
`ID_recipe` INT NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`ID_ingredient` INT NOT NULL,
PRIMARY KEY (`ID_recipe`)
);


CREATE TABLE `Ingredient` (
`ID_ingredient` INT NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
PRIMARY KEY (`ID_ingredient`)
);

ALTER TABLE `Gingerbread Man` ADD CONSTRAINT `Gingerbread Man_fk0` FOREIGN KEY (`ID_eyes_color`) REFERENCES `Eyes color`(`ID_eyes_color`);

ALTER TABLE `Gingerbread Man` ADD CONSTRAINT `Gingerbread Man_fk1` FOREIGN KEY (`ID_baker`) REFERENCES `Baker`(`ID_baker`);
 
ALTER TABLE `Gingerbread Man` ADD CONSTRAINT `Gingerbread Man_fk2` FOREIGN KEY (`ID_recipe`) REFERENCES `Recipe`(`ID_recipe`);

ALTER TABLE `Eyes color` ADD CONSTRAINT `Eyes color_fk0` FOREIGN KEY (`ID_color`) REFERENCES `Colors`(`ID_color`);

ALTER TABLE `Recipe` ADD CONSTRAINT `Recipe_fk0` FOREIGN KEY (`ID_ingredient`) REFERENCES `Ingredient`(`ID_ingredient`);

Try to come up with your own subject area and create a database schema and code. Good luck!

© 2024 Helena Boitsova