Sign Missouri Assignment of Partnership Interest Computer

Document type sign assignment of partnership interest missouri computer

okay Richard aalverson here this is ICS 321 and today is September 18th Wednesday and I'm sitting here in my office here and in 303 G Post building and so I've just I've just posted assignment 1 the first assignment it's a pretty long assignment and you haven't long time to do it I thought I'd posted earlier though and kind of go over it a little bit so let me share my screen and I think I'm gonna probably share this first screen here ok here we go so I have my I have laulima up here and show system run so yeah so we're in ICS 321 and if we look at the overview we are someplace down here chapter 4 in chapter 4 I'm giving you assignment 1 right now and it's it's we're gonna get into chapter 10 SQL tables next next week but so this isn't this is an assignment where we're creating SQL tables and then we're gonna write a bunch of SQL statements and we're gonna learn about the SQL statements over the next couple weeks after this maybe the assignment is not due till until the 19th of October so so let's look at the assignment it's posted here and it's called morning it's called Golden Glory okay so let me make this little bigger and it's due the 19th of October and you can you can Excel except resubmission until this day here and so what I've done is I've given everybody in SQL server and so first what I'm going to do is I'm going to show you how to sign in log into your SQL Server and this is something that you can do to make sure that when you go to it on the web but always defaults to HTTPS that's not it's not that important to do that but you can do it if you want to this is how you do it and then I'll get into the assignment a little bit there's two parts of the assignment the first part is simply creating this database and it's got a few tables here 1 2 3 4 5 tables and some pictures here and I actually give you on the bottom here I give you the SQL - to load up into the table so you don't have to type this data again but you have to create the table first you have to create it so it will accept the SQL and then after that there are a bunch of SQL statement that I'd like you to write and we'll go over all these so this is just giving you a heads up on what's coming and but I'll go over each one of these with you and help you out and we can work through these together you can also find a partner and work on these assignments with somebody else and and I don't want I'm gonna set up something I'm going to do that this week I'm going to set up a forum or you gonna just enter these SQL statements because I'm I'm working actually working on a system to support or to automate partial grading of these this assignment and so this is going to be a good test case for me and so I'd like you to enter them into a particular form it'll be similar to a Google Form but right now you can spend your time getting this database setup so so let's go over this so you have a web server it's it's your us user named ICS 321 com for example my you H user name is Richard H so this is what mine is I can open this up in a new link and really I actually have a de leus here so it's got some returning costs on this server okay so it when you go into it it's going to look like this and it's just going to just a blank web page and yours and you have to go to cPanel this that's where you can get to the backend and get to your your your control panel for your web server and so it looks like this see what happens when I hit this okay I get the same thing let me so you log in it looks like this and you enter in your username which is you know your your us username and your password is your last name it the first letter of your last name capitalized the first letter of your last name capitalized followed by the first letter of your first name not capitalized followed by your 8-digit you hid followed by these three characters so mine is for example capital H lowercase R and then my you hid and then which is a digits you have eight you have an A to G you hid followed by this % hash tag exclamation point and so that's what is typed in here and you log in and you can say that if you want to and so your your cPanel is gonna look like this you got a file manager here database is here you've got some other stuff you can look at email accounts whatever you can you could check all this stuff out if you want to if you purchase if you you know purchase a website on an Hostgator or Bluehost or any one of these hosting companies even Amazon as CPSC panel you can get see panel and it looks just like this alright so so I've logged in and so if you want you can set up the secure server default and and what you're doing is you create an HT Access file and inside the htaccess file you can put these three statements these three commands so I'm going to copy these and I'm gonna go back to here and I'm gonna go to my file manager and I'm gonna go into public underscore HTML right here that will click and this is my web page I can make make a hello page hello world page in here if I wanted to if we look at the index of it let's see there's the CGI bin directory and that's that and so I'm going to create a folder a file a new file and I'm going to name it period htaccess period htaccess create a new file it exists already okay let's look and see what it looks like on the reason I can't see it is because we don't have this set up to see hidden files so to do that I got to click the settings up here and I want to look at show hidden files there it is say oh there already is an HT Access file there let me the concealer it is today okay let me see what it looks like click on it click Edit setting up for PHP well anyway I still should be able to add this in here I'm going to do a paste right here paste and so those are there I hope this works okay Save Changes and now if I just choose to go to let me put up a pull up another browser here if I just go to our [Music] jcharwell enter and it automatically defaults as you can see the lock is there it automatically defaults to HTTPS alright cool so so now let's go back to assignment and we have this assignment here and the problem is it was adapted by this old textbook I used to use gold garden or did I split gold I did that golden glory what was I thinking I mean this is I'm just gonna fix it right now mind it's called garden glory garden ard garden that's funny guard ard garden glory garden glory art to ard alright let me save this okay that's better garden and glory assignment or what okay garden glory is a lawn service assume that garden glory designs a database with following tables each tables fields are in parentheses underlined fields are primary keys italicized fields or foreign keys so we've got five database tables or five relations you think them that way we've got an owner relation and hold property relation garden at glory service relation employee relation and a property service relation and these are the attributes and these are the primary keys right here for each of these relations and it looks like we've got some foreign keys here foreign keys alright and this is what the data looks like so I've got some owners here mary jones whatever got some properties and who owns them that's this owner ID here and we've got some employees and some services and some property service table when things were done all right so we're going to implement this database and I've given you some parameters here I'd like you to define all character fields using varchar' and variable character variable Inc characters I'd like the zip code to be able to account for leading zeros that means it cannot integers varchar' characters defined keys as integers define the hours and the cost as decimal numbers with two decimal points dates dates should be the date type and phone numbers should be stored stored as shown you notice they've got some hyphens in there and and the referential integrity constraints are this our own property must exist in owner ID so so in other words you can't have any you know you can't have an owned property in here linking off to an owner that doesn't exist and you can't have property ID and property service must've must exist you know they're there for probably the property service table you need to have if it's referring to a property and a property ID that property has got to exist the service has got to exist in the service relation and the employee ID has to exist in the employee table okay so that's that justifies the referential integrity and then we're going to use these surrogate keys for the primary keys and all of these will become you know clear if they're not if you haven't gotten that far in the textbook yet I think you maybe have but if you haven't these will become clear very soon and so all we have to do in this part one is we give to create the database make sure it's named exactly as stated above implement the foreign key constraints for the for the relationships and make your own assumptions about updates and deletes and talk more about that when I do it and execute this MySQL script attached to a populate the database so so let's get started that's good I'm going to open up I'm gonna go back to and close this I'm gonna go back to my cPanel here and the first thing I'm going to do is I'm going to click on MySQL databases and so I'm gonna create a new database here let's see here's the instructions the database the database is called golden underscore glory I'm gonna set this aside here okay so I'm gonna create a new database called not golden glory garden glory garden underscore glory garden glory so that's my new database click create database go back and so now I have a database here called garden glory I don't need to worry about users yet or anything worry about that later let's go back to my seat panel and now I can click on this PHP myadmin here and now I'm in my database management system web based here so here's my database here on the left I'm going to click on it and we see that it has no tables the tables would be listed here and so I'm going to start creating some tables here the first thing first thing I'm going to create is let's see let me do something I can see both this this you put this back up and I'm gonna put this over here like this this put this up here and I'm going to take this and put this down right here and basically I just want to be able to see this slide right here start with this owner ID here right there okay alrighty so let's create a new table let's see how do we do that to name let's name it owner o W capital o W and E are trying to make it exactly the same it's got one two three four columns go and the first one is going to be owner owner ID and it's going to be an integer and that's fine and let me pop this over there integer is fine and over here let me see oh it's gonna be primary primary key and it's going to be Auto increment Auto increment that's what this stands for Auto increment that that's our surrogate key and each time we add a new record and the owner in there it's gonna automatically increment that owner idea so we'll always have a unique primary key for each other the next one is gonna be owner name and I'm gonna make that a bar char fine big name the next one is going to be owner email and that can also be varchar' 50 probably enough and owner type o W and E are type and what do what does owner type look like it's just a character string so I'm gonna make this 20 character character string that's fine a 20 bar char 20 all right and none of these can be you know let's make it let's make it so none of them can be known and the storage engine we want to have it be an eye in and odv the reason we want I and nodb is because that it that storage engine will that will enforce referential integrity when you set it up this other one that's default this my is am that does not enforce referential integrity why would you not want to force referential integrity well it's a it's more it's more expensive to really enforce referential integrity and referential integrity might be something that you want to enforce at the beginning when you're just defining your database but after you get there you got everything work anyone you know everything works right and you're you're interested in speed then you can switch from imn ODB to my is a.m. and it'll be it should perform faster all right but for now we do i NN ODB and let me save this okay now we've got a table here a nice table let's go back to the database itself and we see the table listed here let's create the next one which is owned property so let's create a new table new table old old Oh WN EE D underscore property owned the property one two three four five six seven eight make an eight and the first one is going to be property ID property ID integer primary key auto increment and the next one is going to be property name this make that a varchar' fifty and the next one is going to be property type and what is property type let's look and see some examples oh it's just another character string to make that twenty characters probably make it 25 characters varchar' 25 characters and what's next street and that can be a varchar are 50 whatever that's the street address the next one can be a city that can be a varchar' 20 next to state that can be a bar chart to the next one is zip code zip zip and let's make it look just like the way we have it here and that can be a bar chart let's make it a bar chart ten case we decide to do a nine-digit zip code and it doesn't matter let's do our ID and that's going to be an integer and it's also because it's going to be a foreign key we have to index it so to index it we can go over here and we select the index so we're gonna make an index for it and we're gonna make make this an eye n n ODB all right save and now we've got a second table here let's go blonde and what's next we're gonna make a third table called GG service so let's go here new table called caps GG underscore service and that's gonna have one two three and so the first one is going to be service ID that's going to be an integer it's gonna be a primary key auto increment and the next one is going to be service description let's make that acquired char let's make it longer let's make it 200 in case you want to get elaborate and let's make this cost per hour and let's make that a decimal decimal and the decimal is there's two two parameters for the decimal type the first one is the total number of digits and so let's make it like nine and then the second comma is going to be the number of digits to the right of the decimal point so nine comma two is a good nice big number and we're gonna make this a 1000 DB and let's save this and let's go back to the database and we've got another table here called employee and so let's call it employee and that's gonna have one two three four five and the first one is going to be employee ID and that's going to be a primary auto incrementing field and the next one is going to be last name company varchar' no 50 next field can be first name mark Schauer 50 those are prefect names doesn't matter cell phone got some pie fence in there let's make it varchar' I don't know 3 3 4 is 7 10 plus a couple of just make it 16 15 is 5 and the last one is experienced level line and that's just a varchar' I think 20 or so let's see what employees yeah senior master senior Junior master senior ok that's fine and we're gonna make that and inm ODB and we're going to save and got one more table here this is property service property underscores service and that has one two three four five six fields okay the first field is property service ID it's an integer primary auto and current serve this second one is property ID and that's an integer and it's an index it's a foreign key so we got to make this an index and the third one is service ID and that is also in index it's a foreign key into the GG service Finkle great sinkholes and the next one is a service date and that don't just a date field and the next one is employee ID and that's an integer and that is a it's a foreign key into the employee table and the last one is hours worked and let's make that they worked in partial hours make that a decimal and what kind of examples do the effort hours worked okay it's 2-2 to 2.75 okay so I'll make it decimal nine comma two and make this an eye n mo DB and save okay and so so looks like we've created all of the tables 1 2 3 4 5 and we want to set up these we've done varchars reasonable the zip should be leading zeroes with it will define keys integer hours and cost can be decimal two decimal points date should be date phone numbers can be stored as shown referential integrity constraints okay so we have to make sure that when there's a foreign key it refers to or it references a record in a table that does exist so the way to do that is first of all let's just go one by one the foreign key so let's start the owner ID and owned property must exist in owner ID okay so owned property okay this is owned property and if you click on structure for owned property and we click up here relation view relation view we have that we don't need to fill in anything for a constraint name it will fill in a name for us and on delete we want to let's restrict delete let's let's uh if we try to delete an owned property now if we try to delete an owner if we try to delete an owner and this own property is refers to it okay this is I believe this is how I do it I'm going to start this now this is on an update on on restrict okay it's the column is owner ID this database table owner or ID okay so foreign key constraint so we've got a 1 right the is a foreign key into this column okay so save this okay I think I think I'm doing this right I get a mixed up which where you actually connect to which but that's so that's owner property what else do we have here property idea in property service ok property service so let's go to go back here and let's go to property service and we go to structure and go to relation view and leave these restrict and we should have that employee ID links to in the employee table employee ID and the property ID goes to property and property service ID that's this that's in this table so I don't that's not doesn't go anywhere service ID goes to GG service service NT okay I think that's it employee property ID service ID so we have this goes over to property owned property this goes over to GG service this goes over to employee ID and so property ID and property service service ID service and okay are there any other ones let's see let me look go back up here again owner ID I did that one and I did this one in this one okay so I think I have everything I think I've set this up right and so I'm gonna now try to implement the foreign key constraints so I did that make your loan assumptions okay so I just restricted the deletions I restrict the updates to that's fine and then I'm gonna execute the script so let me download the script here it is I'm gonna click on it and I'm gonna I'll save the file on the desktop so here it is let me open it up doesn't matter what I open it in here's here it is and it's a bunch of insert statements and there's inserting owner data inserting property data we've got insert the owners insert the property's sort the employees then we can start inserting well insert the service and then we can insert something that connects the properties to the property service the property service is going to be connected to the property the property and the GG service and the employee the property is is owned by an owner I see so first we got to put the owners in there and then once they're all in there then we can start putting these properties in there because the properties are going to refer to the owners and and we can't put a property in there we can't link it to an owner that doesn't exist so that's why owners have to go in first and then properties can go in there because all the owners are in there and then once the owners and the properties are in there and the employees are and they're going to put them in there too then we can put the rest of these in there so so the order looks correct so I tell you what I'm gonna do I'm just gonna I'm just going to select this whole thing copy and I'm gonna go back to my my database here and it doesn't really matter where I do this I just got to open up an SQL window it doesn't matter it right now I'm sort of in I am in the property service date I'll go up to the database here's the five five tables all i and and ODB and so let me open up an SQL window I'm just gonna click SQL here and I'm going to paste what I just copied to control V and here it is there's the inserts for the property service for the GG service which I think this'll figure EG service owned property okay so let me just execute this go and so I'll green all these were inserted very good so let's go back to our database and we see now there are these records here and that looks nice and let's see what they look like let's they should look like this this is what the owner the owner table should look like let's see what the owner table looks like looks pretty good let's see if I can make this little smaller okay the owner table Mary Jones that looks so good individual okay and what's next the next table is the owned property table this little smaller owned property table so if we go back here your own property looks pretty good Eastlake office Eastlake 492 and so on and next we have employee table let's see what the employee table looks like employee and Smith Evanston Marie Murphy that looks pretty good and Gigi service Milan plant annuals weed garden trim hedge prune small tree and so on and we have the property service table which looks like this you notice with this I know di and nodb that these little foreign keys are links so if we click on employee ID here we click on the one here it takes us to employee number one all right so now let's test these in these constraints alright so if in property service I'm referring to employee ID number one so that means if I go over here and I try to delete employee ID number one delete it doesn't let me do it cannot delete or update a parent role foreign key constraint fails so it tells you why I can't do it if I won't want it to delete if I wanted to delete this role here I have to first make sure that property there's property ID this that this properties go on to make sure that's gone I'd have to make sure the service is gone deleted out and I'd have to make sure that the employee is deleted out so that's these foreign key constraints these integrity constraints that it forces you to do things right don't delete anything that's that's still being referenced and it maintains the integrity of the database okay so that should take care of the first part of assignment one and and we'll next week we will cover the rest of the 7-1 but you should be able to get started with this now alright thanks for watching