4. Physical modeling

In this stage, all the information from our database, we corresponded with SQL, in other words we write our code base. SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system. Based upon relational algebra and tuple relational calculus, SQL consists of a data definition language (DDL) and a data manipulation language (DML).

Using DDL we create a table, database, domains. Create a table with the keyword CREATE TABLE. This statement allows you to define name of the table, name of each column. We can in each table specify the correct values for the column. We can build this constraint using the CHECK keyword in a create table statement. As we define a class of data (numeric, character, bit strings, temporal data, boolean data);

Here we have created an all tables, and each attribute has a data class as shown here check command.

CREATE TABLE Address_emp(

emp_id varchar(25),

name varchar(25),

address varchar(25),

phone int,

email varchar(25),

primary key(emp_id)

);

CREATE TABLE Medications (

med_id varchar(15),

name_drug varchar(25),

brief_abstract varchar(25),

manufacturer varchar (25),

Primary key(med_id)

);

CREATE TABLE Parish(

med_id varchar(25),

date_receipt Date,

quantity varchar(20),

supplier varchar(25),

purchase_price int,

Primary key(med_id)

);

CREATE TABLE Flow (

med_id varchar(25),

date_receipt Date,

quantity int,

selling_price int,

Primary key(med_id)

);

CREATE TABLE Manufacturers (

med_id varchar(25),

name varchar(25),

city varchar(25),

Primary key(med_id)

);

CREATE TABLE Suppliers(

med_id varchar(25),

name varchar(25),

city varchar(25),

Primary key(med_id)

);

CREATE TABLE Customer (

cust_id varchar (25),

name varchar(25),

date_order Date,

date_completion Date,

phone int,

med_id varchar(15),

primary key(cust_id),

foreign key(med_id) references Medications(med_id)

);

CREATE TABLE Orders(

ord_id varchar (25),

name varchar (30),

orderr int,

supplier varchar(20),

cust_id varchar(25),

primary key (ord_id),

foreign key(cust_id) references Customer(cust_id)

);

CREATE TABLE Ingredients (

ing_id varchar(25),

name varchar(25),

ingredient varchar(25),

ord_id varchar (25),

primary key(ing_id),

foreign key(ord_id) references Orders (ord_id)

);

CREATE TABLE Employees (

emp_id varchar(25),

name varchar(25),

position int,

primary key(emp_id),

cust_id varchar (25),

foreign key(cust_id) references Customer (cust_id)

);

5. Content

Using DML we fill information in our tables, database. To do this, we use the keyword INSERT. We change the values in tables with the keyword UPDATE. We delete data with keyword DELETE.

Here we have filled our entire data base or rather all of our table.

insert into Address_emp values ('2536', 'noshpa','Karaganda dom13',2356632,'k.n@gmail.com');

insert into Address_emp values ('1563', 'mentol','Aktau dom45',2554565,'aktau@gmail.com');

insert into Address_emp values ('7526', 'spazmalgon','Atyrau dom15',123695,'a.s@gmail.com');

update Address_emp set name = 'noshpa' where name = 'beseptol';

delete from Address_emp where emp_id = '2536';

insert into Medications values ('002', 'analgin','ot boli golovy','Astana - Karaganda');

insert into Medications values ('003', 'citramon','ot boli golovy','Astana');

insert into Medications values ('004', 'furazalidon','ot boli zhivota','Almaty');

update Medications set name_drug = 'analgin' where name_drug = 'asperin';

delete from Medications where med_id = '0002';

insert into Parish values ('0002', '2014-02-2','2','Astana','10000');

insert into Parish values ('0003', '2014-01-1','2','Taraz','5000');

insert into Parish values ('0004', '2014-03-11','2','Semei','1000');

update Parish set quantity = '2' where quantity = '5';

delete from Parish where med_id = '0006';

insert into Flow values ('0002', '2014-02-2','2','10000');

insert into Flow values ('0003', '2014-01-1','2','5000');

insert into Flow values ('0004', '2014-03-11','2','1000');

update Flow set quantity = '2' where quantity = '5';

delete from Flow where med_id = '0006';

insert into Manufacturers values ('0002', 'aevit','Astana');

insert into Manufacturers values ('0003', 'almagel','Semei');

insert into Manufacturers values ('0004', 'citramon','Taraz');

update Manufacturers set name = 'aevit' where name = 'asperin';

delete from Manufacturers where med_id = '0002';

insert into Orders values ('1122', 'aevit',1,'Astana');

insert into Orders values ('1223', 'almagel',2,'Semei');

insert into Orders values ('1324', 'citramon',3,'Taraz');

update Orders set name = 'aevit' where name = 'asperin';

delete from Orders where ord_id = '1122';

insert into Suppliers values ('0002', 'aevit','Astana');

insert into Suppliers values ('0003', 'almagel','Semei');

insert into Suppliers values ('0004', 'citramon','Taraz');

update Suppliers set name = 'aevit' where name = 'asperin';

delete from Suppliers where med_id = '0002';

insert into Ingredients values ('1022', 'aevit','Astana');

insert into Ingredients values ('1023', 'almagel','Semei');

insert into Ingredients values ('1024', 'citramon','Taraz');

update Ingredients set name = 'aevit' where name = 'asperin';

delete from Ingredients where ord_id = '1022';