Search the Community

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