![\"Oraforbeg1\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2013\/12\/Oraforbeg1.jpg\")
<\/p>\nA wise man* once said, an expert is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalisation.<\/p>\n
[* that wise man was me<\/i>.]<\/p>\n
So now that I\u2019ve established my credentials by bamboozling you with arcane words and capital letters, let me tell you what the purpose of this series of articles is. By the end of it, you will be able to re-read that first paragraph and understand every word; or, if you would prefer that in more practical terms, you will be able to read \u2013 and write \u2013 \u00a0SQL, which is the programming language of databases.<\/p>\n
Let\u2019s meet the main characters of our story: I\u2019ll give you a couple of definitions; one building on the other.<\/p>\n
A Database<\/b> is an organised collection of data<\/i>. Not yet sure what that means? Well, do you own an address book, either on your phone or in a physical book? That\u2019s a database. After all, the addresses and phone numbers are organised<\/i> \u2013 with all friends whose names start with A<\/i> being grouped separately from people whose names start with B or C or D.<\/p>\n
A Relational Database<\/b> is a database in which the data is organised according to type with the relationships being maintained between the differing types<\/i>. Okay, that sounds a bit like Greek (or Dutch, if you\u2019re Greek; or German, if you\u2019re Dutch; or Xhosa if you\u2019re German\u2026), but it makes sense if you let me explain.<\/p>\n
Dig out your address book again. Imagine all the names grouped together; and all the phone numbers grouped together in another list; and all the addresses in a third. On their own these individual lists might be interesting but not useful; but if we establish the relationship<\/i> between the lists \u2013 this address is where that person lives and that\u2019s their phone number \u2013 then our database takes shape.<\/p>\n
Make sense? Don\u2019t worry about it too much if it doesn\u2019t; we\u2019ll come back to it a little later. Let\u2019s talk about Oracle now.<\/p>\n
You\u2019ve probably heard the word Oracle mentioned in discussions about databases, but you possibly do not know that Oracle is a corporation. It makes software to create and manage databases \u2013 so-called Database Management Systems. That\u2019s the DBMS acronym from way back in paragraph 1; and an RDBMS is, of course, a Relational<\/i> Database Management System.<\/p>\n
Oracle began making RDBMS in the 70s. Today, the Oracle database is, by most metrics, the most popular in the world (it does have some strong competition; we\u2019ll talk about them later. This isn\u2019t like football; now you\u2019re part of Team Oracle, it doesn\u2019t mean you have to hate the opposition. Not much, at least). The latest version of the database is Oracle 12c. You don\u2019t particularly need to remember that now \u2013 in fact, you don\u2019t particularly need to remember anything from this section. We\u2019re just painting in the background; the juicy stuff is what comes next.<\/p>\n
It\u2019s time to roll up our sleeves and get our hands dirty. Go get your address book again.<\/p>\n
Remember I\u2019d said the data in databases is organised in groups \u2013 all the names over here, the phone numbers over there, the addresses over in that other place? Well, those groupings are called tables.<\/p>\n
So in our little database we have a FRIEND_NAME table, a PHONE_NUMBER table, and an ADDRESS table. Got that? Cool.<\/p>\n
Tables are made up of vertical columns and horizontal rows. The columns contain data of the same type; while rows contain the data that makes up an item. In our example FRIEND_NAME table, the Last_Name column contains all the surnames \u2013 Geller, Bing, Tribiani, Geller-Bing, Green and Buffay \u2013 while the rows contain the full names, such as Ross Geller.<\/p>\n
<\/p>\n
Our database will be pretty boring \u2013 and not relational \u2013 if it contained only one table. Let\u2019s knock up our PHONE_NUMBER and ADDRESS tables.<\/p>\n
![\"Oraforbeg2\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2013\/12\/Oraforbeg2.jpg\")
<\/p>\n
Figure 1: PHONE_NUMBER<\/strong><\/p>\n Figure 2: ADDRESS<\/strong><\/p>\n You will have noticed that we\u2019ve got different types of data in our tables \u2013 from the PHONE_NUMBER table that contains nothing but numbers to FRIEND_NAME and ADDRESS that both contain character strings, numbers and, in the case of the ZIPCODE column, a combination of both.<\/p>\n The Oracle database needs to know the types of all the data you keep. (That way, for instance, if you ask it to subtract the value in the ADDRESS.CITY column from the value in the ADDRESS.HOUSE_NO column, it\u2019ll be able to tell you that you\u2019re crazy.) There is a long list of data types that Oracle recognises, but we\u2019ll only focus on the 3 main types.<\/p>\n NUMBER<\/b>: This one\u2019s self-explanatory. If a column is created as a NUMBER column, only numbers can be stored in it. It can be whole numbers, decimals, negative or positive.<\/p>\n VARCHAR2<\/b>: Okay, this one\u2019s a little weird. There\u2019s a lot of history packed into the name of this data type; however, it\u2019s mostly boring, so I won\u2019t go into it. What you need to know is that it stands for VARiable CHARacter and is the data type required to store character strings, such as the data in FRIEND_NAME.FIRST_NAME, FRIEND_NAME.MIDDLE_NAME and FRIEND_NAME.LAST_NAME.<\/p>\n There is one interesting difference between the VARCHAR2 and NUMBER data types, and that is that you can only store numbers in NUMBER columns; however, you can record any string of alphanumeric characters in VARCHAR2 columns. For example, with its combination of numbers and letters, we cannot record ADDRESS.ZIPCODE in a NUMBER column, but we can save it as a VARCHAR2.<\/p>\n DATE<\/b>: Another self-explanatory data type. We haven\u2019t used any dates in our hypothetical database thus far \u2013 but we will; I\u2019m saving that pleasure for later.<\/p>\n I\u2019ve got another term for you: Primary Key. A primary key is a key \u2013 a column or combination of columns \u2013 that uniquely identifies a row<\/i>.<\/p>\n Let me explain. Say one day, you\u2019re chilling out at a caf\u00e9 and you start chatting with a stranger. Turns out you\u2019ve got lots in common and you really like that joke they told about a politician, a monkey and a water pistol. When eventually, you rise to leave, you exchange names and numbers and promise to stay in touch. They say their name is Ross Geller. You add it to your address book. But you already had a friend named Ross Geller! How will you know which is which when you want to phone them up and laugh about the monkey joke again?<\/p>\n That\u2019s where primary keys come in. Names \u2013 even rare ones like Ross Geller –\u00a0 do not uniquely identify a record, so we need something that does. In our NAME table it is the number in FRIEND_ID. We simply need to give the new row, the new Ross, a new \u2013 unique \u2013 number in the FRIEND_ID column.<\/p>\n Databases rule the world, and thus, primary keys are all around us. Your passport number, your social security number, the number on your driving license \u2013 they\u2019re all primary keys.<\/p>\n We now have all the pieces of the puzzle. We can now redefine \u2013 and understand \u2013 relational databases. A Relational Database<\/b> is a database in which the data is organised in tables with the relationships being maintained between the different tables<\/i>.<\/p>\n Our database has a table for names, another for phone numbers, and a third for addresses. However, there is no way of knowing which of our friends lives at what address and when, or what their phone number might be. We\u2019ve built a database, but it\u2019s not yet relational. Let\u2019s create two further tables that address that problem.<\/p>\n Figure 3: FRIEND_ADDRESS<\/strong><\/p>\n Figure 4: FRIEND_PHONE<\/strong><\/p>\n Take a minute to study the tables. Notice how useful primary keys are? Instead of typing out the friend\u2019s name in full or typing the full address, all we need is the primary keys. And so, armed with our burgeoning knowledge of databases, we can look at the following:<\/p>\n And after relating this table to the FRIEND_NAME and ADDRESS tables, we know that it is saying between September 1994 and October 2000, Chandler Bing lived at Apartment 19, 90 Bedford Street, New York, NY10014.<\/p>\n And the reason we know that is because we now implicitly understand the concept of foreign keys. Here\u2019s a definition: A Foreign Key is a column (or combination of columns) that uniquely identifies a row in another<\/span> table.<\/i><\/p>\n Foreign keys are the invisible threads that knit all the tables in our database together.\u00a0 It is the foreign keys, telling us how the rows in one table are related to the rows in another table, that turn a database into a relational database.\u00a0 It is the foreign key that takes data and begins to turn it into information.<\/p>\n Without foreign keys, a database is like a dull room, full of bored people.<\/p>\n With foreign keys, it\u2019s a party.<\/p>\n Here\u2019s what I would like you to remember: what is a database? What is a relational database? What are tables, columns and row? What are the main data types? What are primary keys and foreign keys?<\/p>\n Got that? Great.\u00a0 In the next article, we\u2019ll be learning SQL, the language of databases.<\/p>\n A wise man* once said, an expert is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalisation. [* that wise man was me.] So now that I\u2019ve established my credentials by bamboozling you with arcane words and capital letters,…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48366,124952],"coauthors":[48557],"class_list":["post-73217","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-beginners-guide","tag-redgate-deploy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73217"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73217\/revisions"}],"predecessor-version":[{"id":88957,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73217\/revisions\/88957"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73217"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"Oraforbeg3\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2013\/12\/Oraforbeg3.jpg\")
<\/a><\/p>\nData Types<\/h2>\n
The One About Primary Keys<\/h2>\n
<\/a><\/p>\nRelational Databases<\/h2>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nRecap<\/h2>\n
Next Article:\u00a0Oracle for Absolute Beginners: Part 2 \u2013 SQL<\/a><\/h3>\n","protected":false},"excerpt":{"rendered":"