Answered step by step
Verified Expert Solution
Link Copied!
Question
1 Approved Answer

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


Screenshot (353).png


 Trainer_id Name 0 Unknown 1 Simpson 2 Walker 3 Short 4 Petersen 5 Young trainer Event_id Place 101 101 101 102 102 102 103 103 103 401 401 401 401 401 1 2 3 1 2 3 1 2 3 1 2 3 4 5 prize Judge_id Name 1 Smith 2 Green 3 Gates 4 Smith 5 Jones 6 McLeod 7 Bray 8 Moore Address Melbourne Cootamundra Horse_id Name 101 Flash 102 Star 201 Boxer 301 Daisy 401 Snowy white 501 Bluebell 502 Sally 9998 Unknown N 0 3 5 5 7 event_id show_id event_nar judge_id 1 Dressage 1 Jumping 1 Led in 2 Led in 3 Led in 4 Dressage 101 event_id horse_id place 101 101 101 101 102 103 201 301 401 101 102 201 301 201 201 101 301 102 entry + 1 12 

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... blur-text-image
Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Modern Database Management

Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi

12th edition

133544613, 978-0133544619

More Books

Students explore these related Databases questions