Data Base Design

What is Relation and Relationship?

Steps to Design the DataBase 

Before design the DB , We should finalize the Type Of DB ie whether its OLTP or OLAP .Based on that we will finalize the DB.

OLTP- Online Transaction which supports the ACID and also perform the CRUD Operations


OLAP  Online Analysis - Mainly for Analysis and forecasting in Future data.

Step 1: Identify the Entities

Identify the entity is key part of DB Design and it will fall under the following categories ie entities are mainly

1) People

2) Event

3) Things

4) Location


Step 2: Identify the Relationship between the Entities

1-1
1- M
M-1
M-M

Apart from above, We have finalize the Recursive and Redundant Relationship between the entities


Final step in the relationship part,We need to solve the M-N relationship . We are not able to create the M:N relationship between the two tables instead we need to introduce the third table to perform the M:N . Those new entity is called as associative entity which should be placed between those two main entities.


Step 3  :Identify the Attributes for Each Entity

Should not capture the Computational value in the table level.

Step4 : Assigning the Keys (Primary and FK)

Step 5: Finalize the Data Type for each Attribute


Come up with ER Diagram .


3) Explain the Cardinality with Sample

1-1

Create table Gov(GID number(6) primary key, 
Name varchar2(25), Address VarCHAR2(30), 
Term_begin date,Term_end Date); 


Create table State(SID number(3) primary key,
State_name varchar2(15), Population number(10),
SGID Number(4)  references GOV(GID), 
CONSTRAINT GOV_SDID UNIQUE (SGID)); 

1-M/M-1

 
create table vendor1 ( vendor_no    number(4) primary key,
 name varchar2(20),  address varchar2(20),  city        varchar2(15),  st varchar2(2),  zip varchar2(10),  contact  varchar2(16),  phone_no varchar2(12),  status  varchar2(8),
 stamp_date     date);

create table inventory1 ( item   varchar2(6) primary key,
 description  varchar2(30),  quantity_on_hand number(4) not null,
 vendor_no   number(2) references vendor1(vendor_no),
 reorder_qty  number(3) not null );

M-M

Create table Class (ClsID varchar2(10) primary Key, 
    title Varchar2(30),
    Instructor Varchar2(30), 
    Day Varchar2(15), 
    time Varchar2(10));

Create table Student (StudID varchar2(15) primary Key, 
    Name Varchar2(35),
    Major Varchar2(35), 
    Classyr Varchar2(10), 
    Status  Varchar2(10));  

Create table Stud_class (studid Varchar2 (15) not null,
ClsId varchar2 (14) not null,
Foreign key (Studid) references Student(Studid), 
Foreign key (ClsId) references Class(ClsID),
UNIQUE (Studid,ClsID)); 

4) What is the use of index and types ?


5) What is ACID?

6) What is difference between relational and non relational DB?


Relational DB

Records are stored in structured format like rows and column

Its supports ACID property

Its supports Referential Integrity  

Non Relational DB:

Records are in Json/CSV and document

Does not supports the ACID

Does not supports the Referencial Integrity

It does not supports Join to retrieve the records from Multiple tables


What is Referential Integrity ?

it mange the relationship between the multiple tables .

Rules:

if the rows are deleted in the Master table corresponding rows are in the reference tables also deleted though Cascade

Same rule apply for update also

If any changes in PK value of Master table corresponding FK values are updated in the Reference tables

It should not allow the user to add the new value in Reference which does not exists in master table

What is the use of Index and its types?

What is the View and types?

What is polyglot Persistence ?

Application dealing with multiple Data base depending upon the Data format and usage .Based on the usage and format ,it may be RDBMS/Key Value/Document/Graph and any other types of DB.

What is the difference between Normalization and its advantages?