This project represents a significant portion of your grade and will show your ability to turn the material we have lear

[answerhappygod]

This project represents a significant portion of your grade andwill show your ability to turn thematerial we have learned from the text, lectures, and question andanswer sessions into a practicalreality.Value: This project is worth 100 points. Please see theDeliverables section for additionalinformation. Please read this entire document before you begin yourdesign work. This will minimizethe iterative process and make your work more logical andconsistent. You must provide all DDL andDML statements you used to create your database, tables, storedprocedures, functions, and requiredsql statements and the results that you received from thosestatements. A screen shot of your work isNOT acceptable. Once I receive your statements, I will run them onmy machine to prove that theywork. Do not submit a copy of any other student’s work as thisconstitutes plagiarism and will result in afailure for the course. Make sure all statements are free of syntaxerrors and work.Do not under any circumstance take any design from the internet forany part of this project. I amfamiliar with every iteration of posted material on the internetand if I see any of it in your project it willresult in a failure for the course.Premise:You have been hired as a database consultant because of yoursuccessful graduation fromNorthern Virginia Community College’s ITD256 curricula. The groupthat has hired you is a small familymedical practice comprising a small number of physicians and staff.The community that they serve hasgrown significantly over the last few years and now it has becomehard, if not impossible, to accuratelymanage office, patient, scheduling, medication, prescription,treatment, medical test results, billing,insurance, and hospitalization information. You have been hiredbecause the physicians and staffbelieve that you are the best person to handle the creation of adatabase to handle, at a minimum, theareas indicated above.Mission:You must produce a Data Flow Diagram (DFD) and an EntityRelationship Diagram (ERD) whichwill indicate how data flows into and through the existing officesystem and what tables you will createto handle all the information that the new system will handle.Since your application will have no webpresence, the only tools for interactivity are sql select, insert,and update commands. You fill all tableswith sample data. Your design must adhere to at least third normalform principles (3NF). You willproduce a series of reports, which must be approved by theinstructor, that utilize inner joins, outerjoins, single row functions, group functions, phonetic keylookup.You will demonstrate your ability to modify table design usingalter statements as appropriate.Using the material you learned in class, you will utilize blocks ofcode, stored procedures and functions,to add, change, and delete data rows in all tables.
1. A DFD of the work flow process within the medicalpractice.2. An ERD of the tables you will build to support your design. 15tables are required. Themore comprehensive the design the more tables will be required. Youshould, throughyour analysis, easily be able to create 40 tables. 15 to 40 tableswill be sufficient to supporta robust design that is normalized to at least 3NF.3. Create, drop, alter, select, insert, update, delete, andtruncate statements are required.4. The following are the MINIMUM queries that arerequired...additional queries thatdemonstrate that you have gone beyond the requirements will addextra credit points toyour project score.5. The minimum requirements for your doctor’s office automationproject are shown below---You must provide me with your DDL and your DML statements in orderto receive credit forthis you must also show the results of each query1. select * from one table2. select * with a where clause from one table3. select looking for null values4. select looking for non-null values5. select counting the number of rows in a table6. select count of a specific column as indicated in item 3above7. select using a single sort key8. select using multiple sort keys --- note one of the sort keysshould be inascending sequence and the other in descending sequence9. select the numerical average for an entire table10. select the numerical average for all groups of data within atable.11. select where you have joined two tables --- use both the oldform and the newform of the join12. select where you have joined three or more tables --- use onlythe new form ofthe join clause13. use any of the sql server functions we have studied in aquery14. take the query you have produced in item 13 above and add acolumn to theoutput (not to any of the tables) that shows the current date andtime as well asa calculated value from two numeric columns or the concatenation oftwocolumns one of which is a character data type and the other is anumeric datatype.15. do a query with a left outer join of two tables16. do a query with a right outer join of two tables17. do a cross join (Cartesian product) of two tables18. do one or more queries using any mysql functions...you may usefunctions ofinterest to you that we have not studied in class such as but notlimited tostr_to_date, soundex, avg, min, max, etc.
19. use the results of the soundex function to provide analternate key lookup toone or more of your tables.20. Create a temporary table and perform any activity you choose onthat table