CSE 5330 Fall 2019
In this project, you will get started on how to use a relational DBMS. You can either use the ORACLE RDBMS, or the MySQL system (if you want to use a different system, check with the instructor and GTA first). You will use the interactive SQLPLUS facility, and the SQL programming facility, by creating tables, populating them with data, and querying and updating the tables. You should do the following:
- Create the following tables for the SOCCER (world cup 2014 information) database whose schema diagram is specified in Page 3 of this document: COUNTRY, PLAYER, MATCH_RESULTS, PLAYER_ASSISTS_GOALS, PLAYER_CARDS. Write your CREATE TABLE statements in a text file, and execute the commands from the file through SQLPLUS. You should capture the execution in a spool file that will be turned in. Specify appropriate key and referential integrity constraints. The data types for each attribute are given after the schema diagram.
- Write one or more database programs to load the records that will be provided to you into each of the tables that you created. You can use any programming or scripting language you are familiar with (JAVA with JDBC, Pro*C, PERL, PHP, Python, etc.).
- Write down the queries for the English queries that are listed later. Execute each query and display its results. Capture your commands in spool files for turning in.
- Execute 3 more Insert commands that attempt to insert 3 more records, such that the records violate the integrity constraints. Make each of the 3 records violate a different type of integrity constraint. Capture your commands in spool files for turning in.
- Execute a sql command to Delete a record that violates a referential integrity constraint. Capture your command in a spool file for turning in.
- Repeat 5, but Insert three new records that do not violate any integrity constraints. Capture your commands in spool files for turning in.
You should turn in to the GTA one or more Spool files as part of the assignment, including creating the tables and the query results. You should also turn in the source code for the programming part of the assignment for loading the data.
Document your output when needed by writing down an explanation for each step (by editing the spool file); for example, explain the integrity constraints violated in item 5.
Make a zip file with all the files together and submit it through Canvas.
- This project (as well as project 2) can be done individually, or in a two-person group. If two persons do the project, they will receive the same grade.
- Copying from other students or groups is not permitted and will result in a grade of zero for the entire project.
Due Date: There are two due dates: Sept 25, 2019 before midnight is the due date for creating the tables and loading the data (items 1 and 2). The second due date for the rest of the project (queries and updates; items 3, 4, 5, 6) is Oct 02, 2019 before midnight. (Note: this project can be used before for practice towards test1).
Submit your assignment in Canvas any time before midnight of the due date. You should follow the instructions on how to turn in your project (One Zipped folder containing all files together). If you are doing the projects in a team of two, only one team member will submit the projects. Clearly specify your name, team member’s name in the documentation of this project.
Late policy: -5% out of 100 for each day late.
For item 3)
Apply the following queries and display the result of each query
- Print the name, club, and country name of all midfielder players whose country is ‘USA’.
- Print the name, club, country and age of captains of each country participating in the 2014 world cup (this database)
- Retrieve the names of countries participating in the 2014 world cup (this database) that have won the world cup more than twice.
- Retrieve the names of countries participating in the 2014 world cup (this database) that have never won the world cup.
- Retrieve the name and country of the player who had neither red cards nor yellow cards in the 2014 world cup.
- Retrieve the name and country of the players with the most Red cards in the 2014 world cup.
- For each Host city, retrieve the HostCity and the total number of games played in that city.
- Retrieve the names of host city with the most games played in that city.
- For each country, retrieve the country name and the number of games they played as Team1 in the MATCH_RESULTS table, and the total goals scored (SUM of Team1_score) and the goals against (SUM of Team2_score).
- For each country, retrieve the country name and the number of games they played as Team2 in the MATCH_RESULTS table, and the total goals scored (SUM of Team12_score) and the goals against (SUM of Team1_score)
- Write a query that combines the results of the queries in 9. and 10. To get the total number of games each country has played (either as Team1 or as Team2), their total goals scored and their total goals against. Create a view TEAM_SUMMARY that has the following data attributes to hold the result of the combined query: CountryName, NoOfGames, TotalGoalsFor, TotalGoalsAgainst. Order in descending order of number of games played.
- For each match, print the date of match, name of team1, name of team2, name of winning team and goal difference between teams. Goal difference should be a positive value.
- Find all the matches played with country ‘Brazil’.
- Retrieve the names of the players who have scored at least one goal, the player’s country, and the number of goals each player scored. Order the result by number of goals scored in descending order.
- Repeat 14. But only for the players who have more than 2 goals.
- Make a list of participating countries and their population, ordered in descending order of population.
COUNTRY table attribute data types:
Manager varchar (20),
PLAYERS table attribute data types:
Name varchar (40),
Fname varchar (20),
Lname varchar (35),
MATCH_RESULTS table attribute data types:
PLAYER_CARDS table attribute data types:
PLAYER_ASSISTS_GOALS table attribute data types:
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx