ScenarioA local veterinarian clinic has contacted you to develop adatabase system. The clinic is small, but their manual system has become unmanageable. After going through their paper-based files, interviewing staff, and observing day-to-day tasks, you have put together enough data to develop the following requirements:Currently two veterinarians work at the clinic, but they are looking to expand.The system must store the following information about a veterinarian: license number, name, phone, address, and email.Owners bring their pets in for examinations with one of the available veterinarians.the database must store the following information about owners: name, address, phone, and email (email is optional).In the paper files, they had issues with owners with the same name, so this issue must be solved in the new database system (this problem also occurred with pets).An owner can have many pets, but each pet is associated with one owner.The clinic helps pets of many types (dogs, cats, birds, ferrets, and rabbits, etc.).The clinic keeps the following information about each pet: name, age, type of pet, and comments related to that pet such as behavior, fears, special diet, etc.When a pet has An exam at the clinic, the following information is recorded: the exam identification number, date of exam, and reason for the exam.One exam is attended by one veterinarian, and one veterinarian can have many exams with pets.An exam can result in no treatments or many treatments. Each treatment must have a treatment type, description, and cost. ???Design the veterinarian database following the rules set forth in this scenario. Step 1: Analyze the business problemRead the scenario provided in the document entitled, “Database Design Case Study Scenario”. Identify all relevant nouns as data elements.List all of these items in a word document using the file name, “Final Project – Data Item Template”.Subdivide each element into its smallest useful components.????????????????????????Step 2: Identify the relationshipsUsing this list of data elements, try to identify the entities and attributes of each entity. The entities become the database tables and each entity’s attributes become the columns for the table.Identify the potential primary keys that uniquely identify each row in a table.Capture the entities and attributes as tables and columns in a Word document called, Final Project – Data Tables.Identify the potential data types for each attribute paying particular attention to data type and size.Review the data structure that you are creating. Determine whether or not it is normalized to at least 3NF – third normal form. Redesign any tables and primary keys to resolve the data redundancy issues.Step 3: Model and Script the database Create a model of the design using MySQLWorkBench. Deliverable is the .mwb file.Create all of the “Create Table” scripts complete with columns, constraints and the indexes that you consider important given your database design. Having no constraints or indexes is wrong but so is having too many!Make sure that the script file is a .sql file and is syntactically correct. Step 4: Demonstration of SQL DMLInclude in the bottom of your script file, examples of:-??NSERTS for adding test data to all of your tables.-??PDATING data in your tables-??ELETING data from your tables-??ultiple SELECT statements using WHERE, Multi-table JOINS, SUMMARY QUERIES, SUBQUERIES?omputer ScienceEngineering & TechnologyMYSQL CETD 1211
