create a database with tables based on this scenario. Requirement 1 The website will display information from
Question:
create a database with tables based on this scenario.
Requirement 1
The website will display information from the database as dynamic content, as follows:
1. Horses: Horse name, colour, sire, dam, year born, Trainer name. Results listed alphabetically by name.
2. Event winners: Event name, showsite name, judge name, horse name, place, prize money - for horses that have won a prize. Results listed alphabetically by event name and by place.
3. Best performing horses: Horse name, number of times placed - for horses that have been placed in the top three (that is, was placed 1, 2 or 3). Results listed by highest number of times placed and alphabetically by horse name.
Requirement 2
There will also be an administration area where staff members will be able to add, modify and delete records from the database tables through website forms, as follows:
1. Add new horse: name, colour, sire, dam, year born, Trainer.
2. Add new event: Show, event name, judge, prize money for each place (that is, 1, 2 or 3).
3. Add new event entry: Event, horse, place.
4. Modify judge: Judge name, address.
5. Modify prizes: Place, prize money.
6. Delete events, including associated prizes.
Part 1 : Identify SQL requirements
Analyse the client's requirements and the supplied csv files to complete the following tasks.
1. Produce an Entity Relationship Diagram (ERD) to determine the information in the database. It must indicate each of the entities and the relationships between them.
2. Produce a Relational Data Model (RDM) that indicates each table required, including:
a. its name
b. all attribute (or column) names
c. its primary key
d. its foreign key, indicating the referenced table.
1. Write the SQL statement to create a database called horsedb. Save your script file as Name.CreateWayOutDatabase.sql
2. Write the SQL statements to create each of the tables as identified in your RDM. The statements must identify the primary and foreign keys for each table. Append your statements to the same script file as Task 1.
3. Execute your script on the Database Server. Check and ensure that your script executes without errors and that it creates the database, tables and primary and foreign key constraints as required. If the database and tables have been created successfully, you can now proceed to the next task. Otherwise, correct any errors in your script before you proceed.
After creating the tables, upload the provided data from the client to populate the tables to be used as initial test data. You may use the DBMS to do this.
4. Create a test plan to test the SQL statements that you write to ensure they meet the specifications. Ensure the tasks that you are completing as part of your own workload are sequenced and prioritised effectively. Include:
a. sample test data for Requirement 2, ensuring you include both valid and invalid data
b. expected results for both Requirement 1 and 2
c. a column for actual results (to be completed when running the tests).
PICTURES BELOW TO MAKE TABLES
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi