yrstruly Posted May 28, 2014 Share Posted May 28, 2014 (edited) Does Anybody knows how to do the following,API's and Indexes etc?CREATE TABLE ¿tUSER¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿id§number¿ varchar(20) NOT NULL, ¿first§names¿ varchar(100) NOT NULL, ¿last§name¿ varchar(100) NOT NULL PRIMARY KEY (¿id¿), <<INDEXES>>)CREATE TABLE ¿tPROFILE¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿tUSER§id¿ bigint(20) DEFAULT NULL, ¿tTYPES§id¿ bigint(20) DEFAULT NULL, ¿value¿ varchar(100) NOT NULL, PRIMARY KEY (¿id¿), <<INDEXES>>)CREATE TABLE ¿tTYPES¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿type¿ varchar(100) NOT NULL DEFAULT '', ¿description¿ varchar(255) NOT NULL, ¿deleted¿ tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (¿id¿), <<INDEXES>>) Apply indexes to the above tables, and design a SINGLE query to retrieve a full list of user information based on: 2.A) an ID number 2. a cellphone number (¿Cellphone¿ is a record in the ¿tTYPES¿ table)3. Write a re-usable script for importing information into the above 3 tables, from the example dsv file below: - NB - All data of the same type MUST be stored in the same format - NB2- You may assume that if a column doesn't seem to exist for a piece of data, that data type is listed in the tTYPES table<<BOF>>record§numberöid§numberöfirst nameölast nameömsisdnönetworköpointsöcard numberögender312ö9101011234011öTest JunioröSmithö071 123 4321öMTNö73ö1241551413214444öM313ö9012023213011öBoböSmithö27743334321öVodacomö3ö1231233232323244öM314ö8706055678011öFranköFrankinsonö2771 156 1567ö8taö0ö1231123453214444öM315ö9102078765011öMaryöVan Niekerkö+27(0)711236677öCellCö2ö1278933213214444öF316ö9005074545011öSusanöWilsonö0821121124öCellCö705ö1231233216544444öF317ö9101013232011öKatherineöJeevesö+271233214ö8taö112ö1231233678214444öF318ö9101011234011öMatthewöMatthiasö0711111111öMTNöö1231555213214444öM319ö9103126666011öMichaelöBayö085-6122-161ö8taö63ö1231244413214444öM320ö7506023232300öTyroneöOlivierö711234322öCellCö89ö1234563213214444öM321ö8901020304055öBurtöJacksonö071 4566544öVodacomö1ö4567233213214444öM<<EOF>>4. After the data has been imported, Write a basic API to wrap the Database. Include the following function points: - Add a new User - Update a User's details - Delete a User - Search users5. Describe what the following bash statement does: grep ¿date +%Y-%m-%d --date='1 day ago'¿ /path/to/file/FILE§PREFIX§Ö¿date +%Y%m%d --date='1 day ago'¿.dsv ö grep -v 'ERROR' ö cut -d "ö" -f 2 ö sed 's/Ü0/27/'------------------------------------------------PART II: Interesting Stuff------------------------------------------------1. If a resturaunt serves: - 3 types of starters - 5 types of main - N types of drinks - 3 types of desserts + How many different meals are available, if you can order 1 item of each type? + How many different meals are available, if you can order 1 item of each type, BUT you can order 2 drinks as long as you do not order the same drink twice? + How many different meals are available, if you can only order a dessert OR a starter?2. If you need to profile 1000 users, each with 3 different attributes, and each attribute has 4 possible values - before parsing any of the date: + What can we guarantee about the resultset?3. A wild director appears. He uses "I want to profile my user database using an additional attribute!" - Describe a ¿super-effective¿ method which we can implement, which will allow us to handle an indeterminate number of this type of request.4. If we have two seperate tables, the first detailing a list of registered club members, and the second detailing a list of competition entrants (assuming we have a key we can join on), what do/could the following resultsets represent: - The INTERSECT of the tables - The MINUS of the tables - The UNION of the tables Edited May 28, 2014 by yrstruly Link to comment Share on other sites More sharing options...
thescientist Posted May 28, 2014 Share Posted May 28, 2014 if you're going to post homework, at least post an attempt. At the very least put some original effort into it instead of just providing the assignment. At least make an attempt on your own first to demonstrate you're willing to put some effort in. Link to comment Share on other sites More sharing options...
yrstruly Posted May 28, 2014 Author Share Posted May 28, 2014 Have no clue how to even start this... Link to comment Share on other sites More sharing options...
thescientist Posted May 28, 2014 Share Posted May 28, 2014 well, just follow the steps of your assignment then. look into how to create a table in database. http://www.w3schools.com/php/php_mysql_intro.asp https://www.google.com/search?q=create+database+mysql&oq=create+database+mysql&aqs=chrome..69i57j0l5.3679j0j7&sourceid=chrome&es_sm=119&ie=UTF-8 https://www.google.com/search?q=create+table+mysql&oq=create+table+mysql&aqs=chrome..69i57j0l5.3004j0j7&sourceid=chrome&es_sm=119&ie=UTF-8 You have a few options, depending on your setup / proficiencies use phpMyAdmin (most common) use the command line use a one off PHP script use a Database Mgmt. desktop client application (like MySQL Workbench) Link to comment Share on other sites More sharing options...
yrstruly Posted May 28, 2014 Author Share Posted May 28, 2014 will this www.w3schools.com/sql/sql_create_index.asp be sufficient to apply index and single query statement, also point 3 i dont understand or know where to search for? Link to comment Share on other sites More sharing options...
davej Posted May 29, 2014 Share Posted May 29, 2014 Have no clue how to even start this... Well then you don't have any chance of completing it. Have you studied databases or not? Link to comment Share on other sites More sharing options...
yrstruly Posted May 29, 2014 Author Share Posted May 29, 2014 Pls see my last Questions and correct me if that link are suitable to search for answers on single query and index? Link to comment Share on other sites More sharing options...
thescientist Posted May 29, 2014 Share Posted May 29, 2014 will this www.w3schools.com/sql/sql_create_index.asp be sufficient to apply index and single query statement Did you try it? You're going to have to try a lot of things before you will likely get it just right. This is a fact of programming. also point 3 i dont understand or know where to search for? Did you read my first link? http://www.w3schools...mysql_intro.asp Those are examples of using PHP to execute SQL statements. Typically you create databases and tables through an admin utility since you only need to do it once, but for practicing, you could do it with a PHP script. Link to comment Share on other sites More sharing options...
yrstruly Posted May 29, 2014 Author Share Posted May 29, 2014 Have tried the phpadmin, how do i import @Q3? Pls provide me with examples or links also API @Q4? Link to comment Share on other sites More sharing options...
justsomeguy Posted May 29, 2014 Share Posted May 29, 2014 We aren't here to do your homework for you. You need to show an effort. What have you done for Q3 and Q4 so far? What specific questions do you have about it? Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now