Lesson 1 - Oracle Server Technologies and the Relational Paradigm

okay chapter one is oracle server technologies in the relational paradigm fancy top title for a chapter and the certification objective the objectives of this chapter are to talk about the oracle server talk about their technologies understand what a relational database is and oracle is a relational database management system there are other types of databases besides relational is hierarchical databases okay there's network databases there are object oriented databases so but Oracle is the typical one that's used in most environments today for commercial applications or relational databases and Oracle is the premier relational database in existence today but we have to talk about what that means and so on so we'll go over what are relational databases but relational structures are then we're going to go over a summary of the sequel language and talk about some of the client tools that are available and when you install Oracle you get demonstrations emails the two main scheme is a schema is basically and a owner of objects so there's two schemers one is called hrf is human resources and one is called OE for the entry and this book uses those two schemers the HR stuff is mostly for things in the book where they giving you examples and the OE schema is mostly for anything that they have you do as as lamps at the end of the chapter I'm going to have you two lives in both the HR and the OA scheme is mostly vhr scheme the lips Alice idea so you're going to need to get on to both of those schemas and we'll talk about that and at the end to this chapter will actually get onto the server which is up in the cloud make the connection make sure you can get on and and set up those two schemas plus one additional scheme that you'll use the wrong word ok so that's the purpose of this chapter server technologies there are basically for things we're going to talk about here as far as oracle server and what the technologies are the first is the database server and that's the main thing that's what you're going to be working with oracle also has an application server will talk about it briefly it has an enterprise manager which man it which manages the application service the database service and a lot of other stuff anna has development tools such as Siebel plus single developer and sequel loader a number of different things the Oracle provides to help manage databases can use the database cover so chat about those but we're going to focus on the database server okay first question is what is what is an Oracle database well it's really just a set of files a set of files that are on disk when you talk upon Oracle talks about an Oracle database they're talking about the physical files that are on to this and the nice part about Oracle is that there's no limit to the number of files you can have and no limit to the size of each file so the size of the database is basically I'm basically unlimited okay so you can have as many files as you want and you can split your database over a whole host of files it can be an ASP you number of very large files or a large number of small files or some combination the access to the database the access to this sets of files that are out on disk is via an Oracle instance so when you hear of an Oracle instance but is that what are they talking about they're talking about two things they're talking about background processes that do the practicing and do all the work to go get the data go save the data back out the disk and so on and memory structures that are what's stored in memory to make to manage this whole process and these database instances they run exist on CPUs of the database server so kind of one way to think of this the easy way to think of this is uh compared to Microsoft earn in microsoft word the actual word document is stored out on disk that would be a more equivalent to the oracle database which is stored out on disk of course the one where documents act of a lot smaller than your oracle database but you get the idea an instance would be equivalent to AA two or go to microsoft words program the word program when you start the program up fires upward and then you do file open and all of that and you bring in the database and it's the combination of those two things that allows you to work with word files and modify and change them and it's a combination of the oracle database and the oracle instance that allows you to store the data out on disk and bring it in and manipulate the data okay the instance exists on cpus you can have single instance or multiple instances and how to users connect well they use it does not a use it does not directly read anything like this a user connects to an instance the same way to get to manipulate a word document you don't manipulate it directly in any way you what you work with word and you know word what you want to do I want to change this to bold I want to cut copy and paste this and then it does all the work so users connect to the database via the instance and when the user connects to the database via the instance that is called a single session a session is one user connecting through the data database through an instance and the instance manages all the database access getting the data manipulating theater and so on and as I said before other combination of the Oracle instance and the Oracle database together is what is known as the Oracle database server that's how they operate okay the simplest situation is a single instance connected to one database single instance manages one database that's the simplest Oracle database server oracle has a much more complex environment called rack real application clusters and what that is is multiple instances connected to a single database anyone know if you if you use a rack in your environment or use single instances how does your environment work does anyone know that what do you mean by instance an instance is a cert a oracle program running the software running on an oracle server just like Microsoft Word running on your work on your workstation would it's kind of equivalent to one program running on the server the Oracle program of course is much more complex but the program that runs is called an Oracle instance I want to say that we do have most multiple instances but i am not a hundred percent sure about the way you're describing that seems like how our system runs but i like i said i can hundred percent vouch for that okay well that's more on the deep the DBA kind of side anyway for a developer it's kind of irrelevant but we're going over you know the whole technology and how oracle works so just to let you know that it can be multiple instances so the question is well what's the advantage of having multiple instances what would you think would be an advantage to having multiple instances rather than a single you could have a number of people doing the number of different tasks that's true but a single instance can connect multiple users at the same time whereas my Priscilla cover version your oh that's not so much version control as far as you know the versions of the database and changes that's a different thing but let's say you have a single instance and you can have hundreds of users connected to that one instance running on one database server so it's running on one physical server and you got hundreds of users but now you're increasing it and you're overloading that server so things are starting to slow down because the server is just too busy we have a choice you can get a bigger server and run a single instance or you can put another server bots another server box in there and run another instance on the other server box and now you can split it split your workload so some users connect to one instance some connect to the other though buddy both instances are going to the same disk files to go get the data but you split the work among two machines rather to have to get rid of the first machine by a bigger one and a bigger one in a bigger one so you can have multiple instances two or three more instances all running and your workload is split among those machines so one that's one big big advantage of having rack anyone think of another advantage of having multiple physical servers running at the same time um could if one crashes there's all their servers to back that off that the workload can get switched to a different server that's exactly it thank you if you have if you have only one physical box if that box crashes for any reason you're down but if you have multiple servers multiple instances running on different physical servers maybe even in different one in New York one in Chicago though I'm doing different electrical grid so if they have a power outage or whatever what you still have some server running somewhere and people can autumn will automatically can reconnect to the other server and continue operating so that's another big advantage okay how relational databases work in databases in general general most of them are what's called client server which is two-tier you've got a two-tier meaning you have a client and you have the server your client is is your workstation running sequel developer or somebody else running super plus so someone else running an application that connects to the server so it's client server is to tears the client is the person running the program the server is the database server that that has the connects to the database and answers the clients requests well how to client requests come in they come in through simple comments so whatever client you're running the user process which is running for the client it basically generates sequel commands the sequel commands goes to goes to goes to the server and the server execute that and goes and gets it off the disk and returns it back to the client everything has to be processed through with communications network and has to be a network protocol and Oracle's network protocol is proprietary to Oracle it's called Oracle net and it handles all the communications between the client and the instance running on a server and we're and it gets the it gets a client the client makes action oracle net handles the connection once you say i'm a client i want to connect to that database well oracle net will say okay great I'll connect you to the database I'll create a session there's an hour session running from your plan connected to the database doing all the work and work on that is handling all of that then you send it a simple command and Oracle net census people command from your client to the instance the instances okay I'm going to do all the work for this I'm going to go out to the disk if the data you wanted I'm going to pass it back alright so I think we have a a little bit more here that we can show you of maybe a little bit clearer yes user process to serve a process so here's the client site there's a service like it's a client server environment the client side the user is running some user process sequel developer an application whatever it is and that sets up a session between the user process and the server process and they send a sequel command over and now this is the physical server here's the incidents running on the server the instance will go through all the work and get the data from the database or update the database or whatever has to do so that's that's the whole process two sides client and server the instance controls everything to the database the client over here does not connect to the database in any way okay so you know there is no connection here from the user over to the database okay that it that has to be done that's a no no that's not done that has to be gone going through this process where it goes over to the server and the instance is the thing to physically connect to the database any question so far actually I don't understand your last comment about the client is not connected to the database okay a client sometimes a client in certain environments can physically connect like if you run your workstation and you had a file on the workstation you can go to that file and find the database that file at the a data file it it ends in txt let's say and you can double click on it and open it directly so you are physically manipulating that data you could delete that file it's on your desk that would be a direct connection between you and the database but you can't do that in Oracle you can't go into loser operative access I'm Sergeant route but if you have writing access control in that database in the summer to you if you're a DBA and or you're a administrator of the database server yes you could but users don't have those administration administrator privileges don't you use it just has the privilege to select from a database or insert to and select from a certain table insert and so good good question any other questions okay so use a processes any client that can connect to the Oracle server and start a user process it could be super plus people developer toad and user applications any of those things alright so that's the Oracle database server there is a separate server called via oracle application server for basically managing and deploying web type applications web based applications so this and there are a lot of you don't have to use the oracle application services that could use any of this earth instead of being client server which is to teeter but oracle database with a client might sequel developers to to you that's equal developer you got the database they they talk to one another through the instance and so on so there's two different areas with an application so every at oracle of somebody else's application server becomes multi-tier the simplest form is a three tier one expert here you got the client here and that's usually in this case because you're a web application the client here is managing the user interface through a browser so your browser is now basically on your workstation that you're planning your middle tier which is running oracle application server or some other kind of application server it's what the browser is talking to the browser talks to a middle tier instead of sheep will develop a point directly to the date to the database there's a middle tier it manages that whole thing and you're awful application server is one of those it happens to be written in Java and if this little Tia that generates the sequel in your dueling in Siebel developer is only two tier and its people developer that generates the sequel commands but if someone's running a web-based application is generally a middle tier and the middle tier generates a sequel States a client just manages the user interface puts up the screen you know puts up the Google screen or the New York Times screen or the whatever swing you have let you do your you say what you want it's interviews enter the customer number you the customer number then you click the button view actually use the screen what the user screen does is it 12 the toast of the middle tier and the middle tier based on the user ID that you put in generates a sequel to select saw from customer cus no equal whatever the customer number was that you put it that generates a seat escape the middle tier talks to the database which is the instance says he is my seat will command the sequel command returns data back to the middle tier and the middle tier generates the generates the whatever has to be done and sends the result back to the client and sends the HTML back to the client and the client displays the use my ex so that you difference here to tier 15 sequel developer is equal plus multi-tier in a web-based application any questions on difference there and how that works okay Oracle Enterprise Manager is generally a fun by the EDA whoever is administering the whole database and then several different versions of it there's something called Abe is control it manages a single database there's applications for control managers an application server database control manage the database sir grid control is the more sophisticated one and it manages both total environments many different day this is any different instances many different applications as well as also the third party products again this is mostly to the DBA they handle it and enterprise manager has a repository if you're going to manage many databases you have to have information about all those databases where is the information about all those databases stored in an Oracle database so the repository the information about all the databases that Enterprise Manager is going to store is going to manage is in an Oracle database and all of these all three forms of enterprise manager are managed from the browser so you've got a browser environment unlike super all developer which is not a browser to buy environment I mean that's an actual application that runs on your workstation unlike that Enterprise Manager runs from a browser so ebas can manage it from any machine they have anywhere in the world they have access to it and they have a browser okay Oracle's supports three development languages three different develop at languages are supported by Oracle sequel PLC people and Java those are the three languages that are built right into the Oracle database other languages can be supported from outside the database they are not supported directly in the database jonathan is supported from inside or outside but see most of the three deals three generation languages like a COBOL and things like that and older day older languages can be supported also through something called an oci Oracle all in space say it's an interface that they have to support all these other landed but only these three are inside the database and someone asked about feel sequel or someone asked about said that they would like to know more about stored procedures in PLT well unfortunately I'm looking and I don't see Bree I don't see who Lester but I know someone did and unfortunately we'll have to disappoint you early support procedures are not part of the sequel language you can have simple commands in a stored procedure with my party people which is part of the PLC black and the database support people which is the main ansi standard way of getting data out of the database and putting data into the database PL sequel is more like a third-generation language which has looping and if then else in case statements and variables and all that stuff seep world does not have all of that plc growth is robust in that way but less robust in other ways which we won't bother talking about now but plz ball is covered in a completely separate class in a PLC book not in a sequel so we're really not going to get into how you create stored procedures money like so sorry about that's not part of this place all the other all the other special requirements of people mentioned that they would like to do there are many but all the other things we can't support and we will be going over this in the in this place okay let's talk about relational instructors this number by the way whenever you see a number up here it references about where in the book uh this this slide is it is pulled from so if you look at page 10 in chapter one on the bottom it says understanding relational structure so that's where I'm pulling this from and I'm using this to just summarize and we'll go back to the book from time to time to go into details when they have examples or whatever that we want to go into okay a relational structure of sequel any language and evil ational database management system is based on something called a relational structure and there were three main concepts here the first concept with relational structures is two-dimensional tables everything is stored in relational databases in two-dimensional tables rows and columns each one is kind of like a sequel scratch AAA Excel spreadsheet so two-dimensional tables second thing is a concept called data and normalization well if you're going to break your database in the two-dimensional tables and the question is how do I design these two-dimensional tables well there were a bunch of rules the rules of data normalization that hell with how to design that is okay one we have a bit it's designed according to the rules of Asia normal is fiction how do we data health and we need a language the language people that's cool we're alike I'm your mics breaking up okay I telling me us get that closer and is it is it okay now or is it so way that's perfect nope it's gone okay great thank you if you do have problems with it let me know sometimes you know I'm on I'm on a I'm not on the voice over IP because I usually find the phone connection is better for me but if the phone connection goes out or is a problem I will resign in with another phone connection or in worst case if the phones are bad I will go in with voice over IP and it's still pretty good ok so those are the three three key concepts that we want to cover here first thing is two-dimensional tables well is this really wonderful I table I mean two-dimensional table means every set of data is split into a table which is two-dimensional Rosen but if you read any of the literature they will sometimes refer to a table as a table so sometimes refer to it as an entity and they'll sometimes refer to it as a relation a table and to be a relation that's all the same concept unfortunately it's the same thing for Rose rose or sometimes called couples and sometimes both records and columns are sometimes called attributes and fields so as you read through a book punjab one page for common sense the next sentence going to refer to them as different things table is an entity relation row is a regular couple column is an attribute of field every people generally has a primary key the purpose of primary key is uniquely define a row in a table or what why would we need to uniquely define a row and until you do a select because you all the runs you say slept with departmental tanika's you all of employees and department and why do I have to uniquely be able to define every row in the employees it would we think I'm gonna start calling on people hopefully if they're around here today John ah why this why does one uniquely have to define every poet employees well I know in our database the primary keys are usually on that field that's index so when you do joins and it goes a they go they were on a lot faster I don't know if that's what you're looking for but ok that that is one of the key things primary keys are always indexed and if you say where employee number equal for one through seven where person or equal twelve thousand 852 it can find it quickly because they're indexed um let let's go beyond that and ask that question is why do we have to be able to find one row and you could have indirectly into that we are we are uniquely identify a row because we want to be able to update a single row what the leaders in and if you didn't have a unique key and they might just have believed you know Philippe John Smith where personally macol journalist and we will smith you might be deleting multiple John Smith's you only want to delete one you have to be able to uniquely define each row and that's the purpose of the primary uniquely define a row at a secondary value to having a unique identification now you can also have an index for that and quickly find it okay turns out that although almost little tables have a primary key the ANSI standard sequel does not require that every table has a primary so therefore Oracle does not require if you can clear the table with not a primary key but almost everyone your tables will have a primary anyone you guys have any tables that you know without a primary key I can't think of any I have some personal ones I work on a project for our cap team which is one of our like subsidiary teams that deals with a lot of databases and it's more of instances of when people do work in an Access database it's logging records of what people have changed so there's no primary key on that table okay good very good good example if you're just walking something you may need a not need a primary key just to write it out and log it I see that sometimes on it imports someone's giving us a lot of data that we have to import into the table well they're providing us the raw data and we might load that into a transaction file that you depending we're importing and when i import it I then insert that into tables and then give it primary so a couple of reasons why you might not have it in certain situations good okay so there are primary keys and there's something else called a foreign key anyone know what a foreign key is and give us an example from money your environments or what a foreign key is for concepts relational database so foreign key is something that you're looking at say like a descriptive table so we have our claims table that might have say a member key field in our claims data and that foreign key in the claims table will link to the member key in the actual member table which in the member table that member key is the primary key so it's pulling more descriptive data into the our actual claims data if you want to know more about the number okay great that's good example so basically if you have linking between tables which you almost always have in a real environment so I'll take a simple example of a employee table and a department table well the employee table as employee number of first name last name salo DHS and it has a column called department number you know which department the employees in that is another table go to the Prime and table it has department number department name managers managers name that their part and things like that so for every employee they have to belong to a valid department and therefore the department number in the employees it is a farm keep pointing to the department table and whenever you put in an employee and you assign the employee of the proper number if it's a foreign key must exist in the department table if it doesn't exist in the department table you can hear that employee all right so that that that's a and and it's like a two-way check if you if you add an employee with the department number it must exist in department table if you try to delete a department and that department has employees in the employee table it will not let you delete that the part that's the general rule there are a couple of ways to get around these things but that's the general rule for primary keys and and foreign and what that does is it keeps your database consistent so let's say you have customers and orders I got a customer table or custo as my customer as my primary key and last name and first name and company name and company address and so on and when I have an order table and every customer can have multiple orders or I have the order number in the order date and you go to remount and then I also have the custom image select know which customer that belongs to Cosmo column in the order table is a Florin key and it must point to a valid customer in the customer game because otherwise allowed you to put item number 75 water table and it was no customer 75 how would you know ability I mean it doesn't make sense you've had off in the order so a hat so a foreign key makes it a base consistent and do a lot of orders the first number 75 and you try to delete custom of 75 or if it will not let you do it because you wrote a foreign key in the other table and says hey you've got orders not going to let you open those orders by deleting customer 75 so that's the whole the whole concept component primary keys and foreign keys from key is in the child table pointing to a valid record in the parent a blitz parent-child relationship any questions on that and that is described in more detail in page 11 so when you get this summary at the end of the class that you know you can go back and find find these things easily okay so now we have this these two-dimensional tables and as rows and columns and of course the customer table had one vote for every customer and one column for every attribute for every field within the custom so one color for the customer one for the customer name onto the street address one for the city state zip so you've got these blue dimensional tables and we want to design our database we have a lot of data we have to figure out how I'm going to design this database will make many two-dimensional tables out of it many relational tables out of it how do I do that well oracle and and not just oracle but people the rules / sequel have have rules both data and normalization and oracle follows that data normalization rules to see and there's multiple levels there's really five levels but a standard relational database supports the burst three levels of data normalization the first if first normal form is remove repeating groups so if you have a repeating group you want to remove and where we know about these repeating groups well we'll see that coming up in in the book I'll jump to the book in a bit the second rule for the second normal form is remove columns that are not dependent on the primary key and the third normal form is remove all columns are interdependent so if you have a repeating group within a some kind of structure that you created well you now know that I can't keep that in my structure I ever table but there's a repeating Ruben man I can't i gotta pull that repeating group fade out and probably create another table song if I have columns that are not dependent on the primary key that's dependent on something else I remove those columns and put them in another table and if I have certain columns that are interdependent I don't need all of those I only one of them and I have to put the rest of them in another table so that's what these these rules are all about it helps you design your going to have first second and third normal form and helps you design and I'm not going to go into example of that but if you look at starting a page 14 in chapter one let's look here here's where it starts the rules of data normalization and if you if you're interested you can go through this process and it shows you things like like here's an enormous version someone created a table and it has one column is beer and another column title and then it could have multiple offices so I have a column or for one or the two or three or four for four that's a repeating group it repeats and therefore you would have to take these authors out of this table and put it into another table that's the issue here okay see the issue we're having a rotating group where where the ortho repeats many times and the only way to do that reason it leaders have a column or 41 or 42 or 43 or 44 before you don't want to do that you don't know how many's in the need and it turns out it's very hard to write simple commands against that kind of structure so you're going to pull it out and you're going to create another table with probably called your authored table and it's going to have it's a home data so I'm going to now have my ISBN my title of my publisher but I pulled out the authors and I put it in a separate table holding off the table so I have my office John Watson Andrew question Sam and I'm telling it John and rupesh where authors for both one two three four five so this one two three four five is a is a foreign key clicking back to the primary key in the books table instead of this structure which had multiple authors because that violated the that violated the first normal form remove repeating groups and if you're interested in this I mean this is more fit designers but I want you to understand the concepts of of the fact that these things that have to be pulled out and yet the design it properly without repeating groups without columns at a dependent they're not and if they have to be dependent on the primary key and and and you don't need multiple ended into the end columns so you can go back to the next few pages here and they show you examples of that but I don't want to take the time to do that I'm just letting you know those are the rules that are required ok now for documentation any questions on that three normal forms that i'm talking about what what the concept is here helps you design your two-dimensional tables so that it makes sense it's easy to use and and it also doesn't allow duplicate data because other forms some of the other forms if you violate these rules you're going to have data in multiple tables but it's the same data and you don't want the same data in in multiple places ok why do you not want the same data in multiple places two main reasons and you want to think of two main reasons let's see let's try David David why would you not want to have multiple data in or or data in more than one place the main reasons you want one has one for space that it would take up within a database absolutely one of them is the space because if something of hers many many times you're taking up many much more space okay any idea David what the second one is what's the second reason for not wanting it duplicated um nothing comes straight to my head no okay anybody else want to jump in another reason why you don't want to duplicate data multiple times on different places yeah all right say that um insert update one Megan I'm making the change you got it yeah if it appears in many places let's say a customer name is in the customer table but it's also located in every order you could put the customer name in every order on network any address well now let's say a customer changes that are dressed you not only have to change it the customer table you got to change it in roll 47 of their orders it's very hard to keep the data consistent and in sync because you learn that changing too many places you don't want to have to change it and that's and that's a maintenance nightmare and then everything gets out of thing so you really do you almost never want to have duplicate data in your database the only time you violate that rule in the design is if it's the data is to speed up things if things are useful because to go get the day these people will run somewhere else in to another table or several other tables to get what you need you might to placate it in a place where you eat it and that happens a lot in what we call data warehouses were you doing an analysis and reports and I are you folks working from a lot of data warehouses does anyone know that the time data warehouse versus oltp online transaction processing oltp is applications where you're like a like a ordering application where you're inserting orders updating what is looking up on order and so on in a warehouse is like a situation where all my orders for the month are stored in one place at the end of the month I store it all together and now at people are just doing analysis on that throughout the first two days of the month to do all their monthly reports that's a data warehouse in a data warehouse that's where you might duplicate data because you're running with big reports against a large volume of data and they're running through slowly and you may want to duplicate data see in a big price of having duplicate data so it's going to storm worth more space on this but the reports are going to run fast and the maintenance is not an issue because I created a warehouse maybe the the first day of every month and then everybody uses it for the month because it's it's fairly static it doesn't change oltp orders are coming in and changing every day data warehouse is fairly spec so in general in general you're not going to have duplicate data there are exceptions the data houses in other places where it has to you need it for efficiency to run past any questions on that okay all right there is a there are several rules for documenting tables and they're documenting the structure and replication and documenting all the tables in your application one is you list all the attributes the attitude to the columns you list every table and all the attributes all the columns and when you list all the columns you can play they generally put a hash sign next to the primary key column or columns a backslash next to the foreign key column or columns an asterisk if it's mandatory column or columns and and oh if it's little oh if it's optional certain columns when you design a table a mandatory for instance in the customer table you might say that the customer number is mandatory and the customer name is mandatory maybe that's all you have it's mandatory in your order table you may have the order number and the order date you might say I must have and the order of fries I must have and the customer number those may all be mandatory and a mandatory field means you cannot leave it cannot leave it without a value without a value there's another way there's another term for without a value column is what if it doesn't have any value we know that term missing no no no I'm having a hard time actually hearing you okay is anyone else having all the time hearing me as well yeah the preview the only one yes this yeah this is Grace you're coming in and out I am coming in and out it's not good we raise the volume a little is that any better I think I hear every other word like the words get out the words get Kara all right here's what I'm going to do I've been seeking quickly I'm gonna speak a little slower and see if that helps if that doesn't help i will read aisle in okay so let's peek a little well as usual mr. reed island say that again joepa okay i'm gonna i was going to say there's almost a laga between there's almost at wing almost at wing following your word so that it has a very mechanical sound and the fact that the words are going cut off shut up overlapping okay i'm going to hang this up in and i'm going to come in give me about 30 seconds and i'll come in on voice over IP and we'll see if that's any better I'll be right back don't go away okay testing testing 123 hello everybody can you hear me now yes yep is that better yes okay well we'll stick with voice over IP if we'll stick with voice over IP if that's better good thank you for letting me know okay alright so we had for each attribute we flagged them with various symbols yeah so if you're going to document it that that's how you might document that if you look at page 18 and 19 it reviews reviews or all of these symbols that we're talking about here and we said that if I column is optional it can be no it can be left without a value null is not the same as 0 okay it's different no means it has no value so if it's optional it can have no value but if it's mandatory it has to have a value and the let the second thing we're going to do besides listing the columns indicating this these pieces of information which is the primary key foreign key is it mandatory or optional we also can have an entity relationship sometimes called er entity relationship diagram to describe the primary keys and foreign keys and how they relate so here's an example of I have my books table and there can be multiple authors you see this little symbol here with it looks like crows feet that's the multiple if it's if it doesn't have that it's a one-to-one relationship if it does have that it's a one-to-many so one book can have many authors but one author only has one address one book can have many publishers excuse me one publisher can have many books but one publisher only has one address that's a very simplified view oversimplified but it's a simple my view and that's an ER entity relationship diagram so it's incomplete but it gives you a picture of what an ER diagram is like so what we did here was one to one and one too many there are relationships that are many to many most relationships in in relational debated databases are one too many and they're represented by a primary key to foreign key link one customer many orders primary key to funky one department many employees primary heat of foreign key there are also many to many relationships a one-to-many relationships is directly represented by primary key and foreign key in relational model many to many relationships cannot be represented directly in a relational model there is no way to do it it's not supported directly the solution is you have to create multiple one-to-many relationships and to do that you need an intermediate table so let's look at this example for our example before remember we looked at a book it could have many authors but can have multiple orders and author can also have many books so the true relationship is a many-to-many relationship that's why we have crow's feet on both sides one book can have many authors wonder if it can have many books you cannot represent that with the primary key foreign key because it's two-way it just doesn't work so this is unnormalized because you're not allowed to have many to many relationships and in normalized database how do you get a wrap we've got somebody who is not muted so please mute yourself I just heard a lot of noise in the background thank you okay so how do you get around that the way you get around that is you create two one to many relationships and you create an intermediate table so here i have a one-to-many from my books to my book authors and a one-to-many from my authors to my book authors and I have to create this intermediate table that really is only here because relational databases don't support many to many relationships they do not have that level of support and let's see this is on page 22 22 and so it shows you that relationship and you're going to have to actually create this and the dba's when they set up your database they do create these relationships so they will show here's my books book a book be booked see with all the names and so on my book authors will say book a has three rows to three authors that's your one to many relationship okay but authors can have many books so here will show that author jeff has rolled for books and it will have all of the book IDs for that so this has for a single book all the authors and it's got two columns it's got the book ID and it has the author ID so for a single book there's multiple rows with multiple authors for single author there's multiple rows with multiple books and now if you want to link or thurs in books you're going to do a three table join for those of you that know joins already you can have to join all three tables not just two tables we'll get into that one we do joins okay so we've covered all the base the main requirements of a relational databases of relational database all right we talked about how you can have one to one and one too many but if you have many in many relationships you've got to create an intermediate table and you'll see that often when you have to do a join you got to do create connect one table to another table and there's no direct connection there's an intermediate table and you got to join them through that intermediate table and we'll be getting into examples of what we do that later if you don't have any if you don't have any experience with this so don't worry about it we'll get into more details there and we talked about how you have primary keys and foreign keys in in a table and how they're linked and that's for your one-to-many relationships also one to one could use primary in foreign key but it's usually one too many okay now we get into the sequel language itself so we now have talked about you're designing your database you have these two dimensional tables and how we design them with normalization and with one to one or one too many but now we have to go get the data added a database and put data into the database so that's where the sequel language comes in sequel language is a set based language it is not procedural meaning you don't tell Oracle how to go get the data you tell Oracle what data you want select star from customer where a state equal New York go get me all the customers in New York State you don't tell Oracle how to go get it Oracle will figure out how to go get it it's a set based language and asset because it's going to return to you a set of Records it'll turn all records for all oil customers that are in New York so it's not procedural procedural is a language that loops through and said you're going to write a loop and going to say go get the first record check to see if it's New York if it is return it if it's not don't returning loop back around it's not procedural for procedural language that's PL sequel which we're not doing in this class sequel is a set based language returns a set of rows with a single command so you tell Oracle what to do not how to do it the database figures out the best way to do it so when you do a complex select statement Oracle goes through a rigorous algorithm to try to figure out the most efficient way to get the data that you're asking for it to retrieve now I mean you may look at it and say well you know too if I want one customer it's going to use an index the customer index sure it'll probably do that but Oracle will go through thousands of iterations trying to figure out the most efficient way because when you have a complex requirement in your sequel i'm joining three tables or four tables I have a where clause with many many conditions in it so I have a lot of indexes I could use this index first then that index or I could use the second index first endless than the first index well which one should i use first I'm joining three tables Oracle as many ways to join tables how should I do it oracle has something called a CBO course based algorithm see so its course based and oracle will go and figure out the best way to do it kinda I I like to compare this to a chess-playing program where you make a move and then the computer says okay I can make this counter move but let me follow the chain what how's everybody gonna make a move no that's not a bad move three moves down the line I'm going to get hurt then it backs up it finds another way it finds another way it tries many different paths down based on what what are the possible things that it could do to get your data I mean one thing i could do is read the whole table i'm going to read the whole table and search the whole thing if i have an index i could go get it faster using an index if i have two different indexes i gotta decide which index is better so it comes down and it comes back back with the best algorithm how does it determine the best algorithm it's a proprietary oracle program that goes through and assigns costs to everything and comes up with the least cost algorithm and that's how what oracle goes and gets the data sequel is purely a database access language it goes and gets the data and puts the data in procedural languages it does not do procedural languages which you need for application development all they have to call sequel those languages call sequel and sequel goes and gets the database the main purpose of sequel is going and getting records out of the database putting records into the database creating tables managing the database set based language primary commands there are categories of commands okay that what are the categories of commands the first one is DML commands DML stands for data manipulation language select insert select insert update to lead and so on and merge is another one emerges a leaf less frequently used command it goes and gets aight added a database put state in ddl commands are dated definition the mls data manipulation dbl is data definition okay how defining my database create a table alter a table drop a table rename a table there's also two other categories data control and transaction control they're often all combined into ddl data definition most people consider all of these ddl data definition language don't mind my p over here it's really a d these are data definition language commands they define the database and who has access to things that's grant and revoke and transactions and so on DML is data manipulation those are the two primary types of sequel commands Jeff of a question yes um so would you would you say that SAS is a procedural language I wouldn't really say that SAS is a procedural language no I'd spent a long time since I use SAS but with SAS you basically go in and tell it you want to do a some sort of an analysis and get into a regression analysis on something or whatever and you pass it all the data and it goes and figures out how to do that analysis so I would now I would call that more of a not a procedural language so much on because you're not telling it every step if you have to go compute something you don't if I'm correct is that right and SAS you don't go tell it every step how to do it go get this add this to this you know / that you don't do all that you just say go get me the result is that correct not usually I don't think so so maybe what it would be is it's like a set or oriented language but it's just more complicated because I was kind of surprised to hear that sequel only had 16 commands so I mean there's like a whole lot in SAS so it's just a more complex set through language it the way the way I think about it is for example C++ you can do it erations and you can say do this and then if done it then do this and then loop and if you find this then go to the next step and so on until you optimize for so so you can really write I'm sorry I'm sorry no I'm sorry please go so I'm sorry please continue so 44 says well you can really change the the algorithm that does for example that look for you know for for what is the best you know not work or something like that so it's already those our procedure that's stored that you really cannot change it so it's an algorithm that already exists that does a nearest-neighbor whatever the heck is doing so that's to me that's how I think about my different you know so procedure is when you can really write all the iterations yourself and in and compile you know through you know through an algorithm compiler or something like that that's how I think of it okay that's helpful thank you good good good description thank you and by the way these are not the only these are the primary sequel commands there are hundreds of sequel commands most of them are ddl managing the database these are just the key ones there are not a lot of DML commands there may be a few more beyond this but not a lot maybe a dozen DML commands but hundreds of ddl commands with and you know we just haven't gone into all of them okay uh and and let's see okay sequel plus is your basic command level type tool it's written in C you're not going to be using sequel plus so we can skip over that let's skip over these things prop but sequel developer is a graphical tool it's written in Java requires the Java Runtime environment you can stall it install it anywhere in any directory on your workstation and sequel developer you must create a database connection which you you generally do and we're going to create database connections to connect to our cloud database if you have and will show you how to do that if you haven't already done it and you can connect to multiple Oracle versions using a host two different protocols with this with sequel developers not just with one protocol so depending on what protocol your setup you can you can connect from sequel developer and that's how you're going to do it and see it is Jeff yes what kind of place is this a good time to take a break oh good good point yeah we're going to till five o'clock i usually take a break around three but uh let me let me see we're almost done with this so let me finish this chapter and we'll take a break in a couple of minutes just and before we do the lab how is that ok sounds good thank you ok good yeah if I if I keep going too long sometimes you know I get carried away here so just if I'm going past any certainly if I'm going past three o'clock and in the afternoon halfway through and then I appreciate that anyone letting me know okay a sequel developer you're going to create a you're going to create a connection and when you can reconnection you give it a connection name a username and a password you generally save the password and for ours we're going to give you host names you you all have a hostname and we'll go over those if you don't already have it or you don't have that email and your port is going to be 1521 your cities or CL so that's how you're going to set it up for here you're going to need three connections one to the HR schema one to the OE schema and went to a new schema that you're going to create on your server each of you have your own database server in the cloud that you can connect to so you'll connect the three schemas and then you'll have three connections and when you have to do is a lab in the hrs gamer you just connect to the hrs gamer and do the lab there Oh a schema you'll connect to that and towards the end of the class you are we do a couple things with your own scheme I'm almost everything is with the HR and yoli schemas okay so let's talk a little bit about users and schemas let me make sure how much how many slides do I have you go in here yep just two more okay there is a user is a person who can log on to the database and make a connection okay that's the end user a schema is different a schema is an owner of objects so we have two different concepts here one is a user who can log on to the database and make a connection one is a schema that is an owner of objects in a lot of cases you'll see people using schema and user interchangeably but the key is that a schema contains the objects in the database a specific scheme it contains the objects in the database it's owned by one user so I if I create a user called jeff i can connect the database with jeff with the appropriate password and jeff will have a schema that contains all the objects to jeff owns but for whatever reason Oracle considers them differently schema is the owner of the objects and a user is a user the connects to the database but when you create a user so that someone can connect to the database you create a new user name or whatever that user has a schema associated with it and they are linked you can't pull those apart you can't take a you can copy tables from one user to another you're copying it from one user schema to another but you can't copy the schema because the schemers you can't move this game and the skimmer is directly tied in and associated where they use it so if you want to copy a schema that's fine you're copying all the objects in that user schema to another user schema but you can't physically move a schema the user is created with a create user command will be doing that in the lab and the Ukraine user command creates a user with an empty schema for that user okay so that's just the difference between the concepts of a user and a schema and there our demonstration scheme is we won't be using the sky tiger it's an old demonstration schemer we will be using the HR schema and we will be using the OE schema and they are in your database they're locked so as part of our first lab after we take a break we're going to the instructions will show you an altar command so that you can alter that schema and you will not need the creation script because the objects are already created on that schema so we're just going to have to unlock want both of these schemas and that's part of our first lab let's just go over those that lab the solutions or the lab questions here let me show you you should see the self-test now what components of the IT developed environment can an Oracle Enterprise Manager grid control manage and if you recall the grid control was the more sophisticated one it actually manages everything all of the above it manages Oracle databases application servers third-party products all works so all of the above what language is run within the database so we're only three sequel PL sequel and Java now you can run c and call sequel no problem but you have to use something called the oci libraries so C is C or other other third-generation languages Pascal and other things like that you have to go and use this external thing and it doesn't run within the database PL sequel definitely runs within a database and Java can also run within the database next one is understand relational structures ah the data data is modeled into a relational database and what do we typically use we use the third normal form okay those three rules that we gave you no duplicates no duplicate fields and so on all those three rules we do not use the first normal form we don't use the fifth we certainly known use the abnormal apparel normal forms I don't know where they get these answers from all right entity relationship diagram as data modeled into everything in relational databases are two-dimensional tables so the ER diagram shows your model as two-dimensional tables number five is equal to the set oriented language which of these features is a consequence of it being set oriented so more feature and what's the best answer so individual rows may have a unique identifier that's not actually required usually it's there but we talked about that some of your tables don't have unique identifies meaning primary keys sets can be managed in groups that's true but it's not a consequence of the fact that it's a set oriented language and it's not the best answer see a sequel statements can be placed within blocks of code in other languages such as Java and PLC well that's also true but it's not a consequence of it being a set set language one statement can affect multiple rows that's the consequence of it being a step oriented language which of these constructs is not part of the sequel sequel language do you cannot do do while in sequel you can't do for next you can't do if then else the only two here you can do are commit and rollback those are data control languages which are usually called DML but sometimes they call transaction control language you would need PL sequel to do do while for next if then else client tools which are the following which of these statements regarding sequel developer is correct sequel developer cannot be connected to the database early that's what we didn't talk about that but it can be connected to any of the earlier databases can be installed outside the sequel Oracle home remember sequel developer can be installed anywhere you want on your workstation it's irrelevant it cans to a password so that's also true so B and C or true and sequel developer does not rely on one technique the name resolution that can handle multiple techniques which are the following that you are requirements for sequel developer we mentioned that it does need a java runtime it's written in Java the only other requirement since it's a graphical environment it needs to have a graphical terminal their friend UNIX or Linux or something like that doesn't need the sequel plus libraries sequel plus needs those but not sequel developer where can the demonstration schemas be created that was kind of an interesting question and we're talking about this schemer so it's a little tricky this question the demonstration scheme is do not have to be in a demonstration database ah they can be created in a production database so those first two are out the demonstration schemas can be created in any database that's the answer now this is a little misleading the demonstration schemas can be created in any database if the demonstration user is created first well that kind of implies that you need to create the user and then the schema but remember that's not the process when you create a user you automatically get a schema they're linked together so it's a fine difference here but the point they're trying to make is that the user and the schema are intimately linked together you don't create a user and then a schema when you create a user you get a schema for that user it comes with it and how can you move a schema from one user to another again they're trying to emphasize that they are intimately linked you cannot move a schema from one user to another you can copy all the tables if you want copy all the indexes from one to another but the schema is tied to a user you can't move it from one to another and they ask you to design this and you probably took a look at the answer here here's a simple answer from that from that description but one customer has multiple orders one order has multiple order items goes to these crow feed a product can be that there are multiple order items can reference the same product no problem the product can be in can be and a product can be in multiple warehouses so one warehouse can have multiple products but a product can also be stored in multiple browsers which is the problem we had with getting your books in time because amazon tried to get all of those books and they were stored multiple multiple different warehouses so they have to have pieces of it going coming from all over the country or whatever okay so the problem was that's the many-to-many one warehouse has multiple products one product can be in multiple warehouses and therefore we have to create a separate in intermediate table called an inventory table and that showed this warehouse can have this product this is the same warehouse can have the state a second product the same way a house can have a third product but it also showed that this product can be in this warehouse this product to me in that way Alice the product can be in multiple browsers we needed an intermediate table because you cannot represent a many-to-many in a normalized solution in relational databases you have to have only one to one and one too many and product information there can be multiple product descriptions for product information they allow that also

2021-05-15 17:14

Other news