Sema baze podataka i relacije (Database schema and relations)

Sema je skup svih struktura svih tabela u bazi podataka (en. schema). Sema je nacrt ili plan baze podataka. 

Sema se sastoji od:

• opisa tabela
• kolona
• tipova podataka u tim kolonama
• primarnog kljuca svake tabele
• spoljnih kljuceva (ako ima potrebe za njima)

Sema se moze prikazati u vise oblika a mi cemo koristiti tekstualni.

Primer: primarni kljucevi su podebljani, a spoljni kluc je podvucen.

Kupci (SifraKupca, Ime, Adresa, Grad)

Porudzbine (BrojPorudzbine, SifraKupca, Iznos, Datum)

Spoljni kljucevi su relacije (veze) izmedju podataka u dve tabele. U nasem primeru to je SifraKupca.

Tipovi relacija u relacionim bazama podataka:

• jedan prema jedan
• jedan prema vise
• vise prema vise

Jedan prema jedan povezuje po jedan red u obe tabele. 

Primer: ako iz tabele Kupci izdvojite adrese i stavili ih u posebnu tabelu.

Jedan prema vise - jedan red u jednoj tabeli povezan je s vise redova u drugoj tabeli.

Primer: iz tabele Kupci, jedan kupac moze da ima vise porudzbina iz tabele Porudzbine. Sto znaci da tabela na strani "vise"povezana je preko spoljnog kljuca s tabelom na strani "jedan".

Vise prema vise - vise redova u jednoj povezano je sa vise redova iz druge tabele. 

Primer: ako imate dve tabele Motori i Radnje, moze se desiti da jedan motor prodaju vise radnji, od kojih svaka radnja prodaje i druge motore. Ovakav tip veze se obicno predstavlja sa drugom tabelom. Tako da dobijete sledece tabele. ImeMotora, Radnja, ImeMotora_Radnja. Treca tabela ImeMotora_Radnja sadrzala bi samo parove povezanih spoljnih kljuceva  iz prve dve  i oznacavala bi koje radnje prodaju koje motore. 

Schema is a collection of all the structures of all tables in the database. Schema is a blueprint or plan database.

Schema consists of:

• description table
• column
• data types in these columns
• primary key of each table
• foreign keys (if any need for them)

Schema can be displayed in several forms and we will use the text.

For example: primary keys are bold and external keys are underlined.

Customers (CustomerID, Name, Address, City)
Orders (OrdersID, CustomerID, Amount, Date)

Foreign keys are the relation (connection) between the data in two tables. In our example it is CustomerID.

Types of relations in relational databases:

• one to one
• one to more
• more to more

One-to-one connects one row in both tables.

For example: if you extract addresses from the table Customers and put them in a separate table.

One the more - one row in one table is associated with more rows in another table.

For example: from the Customers table, one customer can have more orders in the table Orders. Which means that the table on the side of "more" is connected via an foreign key to the table on the side of "one".

More to more - several rows in one table is associated with more rows from another table.


For example: if you have two tables Motobikes and Stores, it can happen that more than one stores sells one motobike, from which every stores sells other motobikes. This type of connection is usually presented with the second table. So you get the following tables: MotobikeName, Store, MotobikeName_Store. The third table MotobikeName_Store would contain  only pares connected foreign keys from the first two and would denote what stores sells what motobikes.