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)

);