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.
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
1 Show_id Name 2 1 Dubbo B 4 5 5 7 3 3 0 4555W NO 2 3 Address 38 Un Road 2 Young 13 Cherry Lane, Young 3 florida 123 John Street 4 Royal East 123 Example street 6 showsite C m
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Based on the scenario and the provided CSV files we need to create a relational database that will handle information about horses events trainers and other related data I will provide you with an ove...See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started