To be completed in SQL

You are a small database consulting company who designs and develops databases for small to medium sized businesses. You have now been hired to design and implement a database system for the dream company of a new client. This project should be for a small company and your design must use the principles you have been taught in this class. It is critical that your design process includes the stages of the Database Life Cycle as outlined in the text excerpt provided in the Course resources section of e-classroom To summarize, the phases you must incorporate in your design are: 1. Database initial study - here you are required to give a description of the company for which you are designing your database. Include any problems the company now experiences which must be solved by your database e.g. reports to be produced, queries to be generated etc. Also clearly state the objective of the system you are to develop and the scope of your design e.g. how long it is expected to be a viable solution for the company. 2. Database design - Begin here by creating the conceptual model then logical model of your database by following the steps in the following table: Developing the Conceptual Model Using ER Diagrams TABLE 9.1 STEP 1 2 3 4 5 6 7 OUW ACTIVITY Identify, analyze, and refine the business rules. Identify the main entities, using the results of Step 1. Define the relationships among the entities, using the results of Steps 1 and 2. Define the attributes, primary keys, and foreign keys for each of the entities. Normalize the entities. (Remember that entities are implemented as tables in an RDBMS.) Complete the initial ER diagram. Have the main end users verify the model in Step 6 against the data, information, and processing requirements. Modify the ER diagram, using the results of Step 7. 8 a. Identify, analyze and refine the business rules - in this section it is important for you to thoroughly review the information collected during the database initial study, in order to articulate the functioning of the business as business rules. Remember that not all business rules will be structural. At this stage the rules do not have to fully comply to ERDish but should capture the main players/objects in the database and how they interact with each other. b. Identify the main entities - After thoroughly researching and reviewing the way your business functions, your business rules from step 1 should contain all our major entities. Extract these and review them to determine if any supertypes/subtypes, hierarchical, recursive or mutually exclusive relationships exist. List all these identified entities. C. Define the relationships you likely already identified many of these in step 1 as you were articulating your business rules. Now you must model them in the creation of your conceptual model. To achieve this, you can: i. Write out valid ERDish for each relationship then draw the model ii. Indicate transferability where appropriate on identified relationship lines, and ensure that you include the appropriate cardinality and optionality iii. Remember that 1 to Many is the ideal so if your emerging design contains any 1 to 1, or many to many relationships you must review or resolve these respectively. iv. Draw an updated model after resolving issues identified in step iii above. d. Define attributes and keys - Just like relationships, you likely have an idea of what attributes will be needed in each entity from your work in step 1 above. At this point you can include the attributes for each entity and note their optionality. Indicate primary keys for each entity. e. Normalization - Review the attributes in each entity in relation to the identified Primary Key (PK). You must ensure that each entity is in 3NF and record this in your documentation. If any tables need to be normalized proceed to do so here and update your ERD accordingly. f. Complete the initial ER Diagram - Review your design to ensure that you have considered and captured the presence of Arcs, Recursive relationships, or the modeling of historical data where necessary. Draw the updated ERD 8. Review the model with the client'- go back to your documentation for the Database initial study as well as your refined business rules. As you read each business need check that the database you designed can support the stated functionality. h. Update your ERD - If any necessary changes were identified during step 7 make the necessary updates to your model. Finally convert your design into a physical model by essentially describing a data dictionary for your database i.e. for all your entities list its attributes and their characteristics such as data type & length, description, format, constraints, keys, default value and referenced tables. 3. Implementation and loading - Create your database using MS Access by implementing your table structures and the constraints you identified in step 2. Finally load some sample data into your database - at least ten rows per table. 4. Testing and evaluation - here you must run sample queries against your database and record the output of each. The queries chosen must include those showing your database tables, the description of each of your tables, a sample row from each table and a sample join for each pair of related tables. Also include samples of queries which will be used most often in the company for which you have designed your database