Design a Database;Need perform the following steps;1.Finalize the database design from short-listed database schemas from the previous week. (see next page for refresher on previous week database schemas);2.Finalize the frequent query set.;3.Incorporate MySQL data backup and recovery methods.;Your response should contain;?The final database schema.;?The frequent query set.;?Backup methods and scripts.;Database Application Design;Create a food store selection survey database that exists in three different states. Include questions allowing the user to select one of three different food stores and possible answers. Include information about the users such as name, location and age. You should also include any other pertinent user data and possible responses, and administration data. Be sure to include at least five important user data attributes required for storage and retrieval of survey information. The survey administration information organization exists in one of the three states. The data is collected in one state, modified in another state and stored in all three locations. Design the database application required to achieve these processes. Include diagrams, code, and descriptions of your approach.;(Previous weeks database schemas);Transfer of Data;John needs to transfer a data set of 50,000 tuples from database schema1 to database schema2. Database schema1 contains table1, which has seven attributes and database schema2 contains table2, which has five attributes. How will he perform the transfer of the data set of 50,000 tuples from database schema1 to database schema2 at the database level? Which fields will be kept, lost, or combined?;Please provide example SQL diagrams and show all design work to support your response. Among all techniques available which one is the most efficient? Why?;Answer;Suppose schema1.table1 has following columns;table1(col1,col2,col3,col4,col5, updatedBy, updatedAt);and schema2.table2 as following columns;table2(col1,col2,col3,col4,col5);We can insert the data from table2 to table1 using programming language. But it will be slow as there a loop will may be used to iterate over the records of one table and inserting that record to another table.;Insert select query can be written to perform this operation at database level if the col1 to col5 of table1 have same datatypeand length as of table2 to avoid data loss.;It is efficient and it can be executed in single query.;Following is the query to use insert select.;Insert into schema1.table1(col1,col2,col3,col4,col5, updatedBy, updatedAt);select;t2.col1, t2.col2,t2.col3,t2.col4,t2.col5, ?user1?, now());If required, any column can be left. It can be omitted from insert statement and select statement but the number of columns mentioned in insert and select statements should be same and columns should have same data type.
Paper#69369 | Written in 18-Jul-2015Price : $27