In this assignment you will write an SQL script that will create a database with five tables. In addition, you will use insert statements to add some initial records to the tables. Important: You should test your script periodically by running the source command (don’t forget to save it first). Make sure it is running properly before submission. Instructions You have been chosen to create a database for the NFL (National Football League). You will be constructing five tables and loading a small amount of records into each of them. Your SQL statements will involve DDL (Data Definition Language) syntax, including data types, domain constraints, primary and foreign key designations, among others. Here are the relations you will use as input to build your tables: TEAM(teamID, name, city, state) COACH(first, last, yearsExperience, currentTeam) FK: currentTeam -> TEAM PLAYER(number, team, first, last, hometown, position) FK: team -> TEAM GAME(team1, team2, dateOfGame, winner) FK: team1 -> TEAM team2 -> TEAM FRANCHISEHOLDER(franchiseHolderID, team, number) FK: team -> TEAM number, team -> PLAYER Here is some additional information regarding each table: TEAM “teamID” is an integer and should be automatically incremented each time a new record is inserted automatically. “name” and “city” should be varying-size strings. “state” will always be an abbreviation like “MO” or “CA”. The nfl will like to keep the “name” of the team unique- no copycats, no matter how jealous other teams may be! COACH “first” and “last” names should be varying-size strings. “yearsExperience” should be an integer, at least zero, but no higher than 55. You will have to find out how to add a domain constraint to a column (Hint: check…). Note: Your domain constraint my not actually be enforced in the current version of MySQL, but that’s fine, we just want to see that you can use the proper syntax. PLAYER “number” is an integer. “first”, “last”, “position”, and “hometown” should be varying-size strings. “first” and “last” should never be empty. GAME “team1” and “team2” are integers. “dateOfGame” is of datetime type and has a default value of current_timestamp. “winner” is an integer and should never be empty. FRANCHISEHOLDER “franchiseHolderID” is an integer and should automatically increment. “team” is an integer and should never be empty. “team” should always be unique, i.e. teams can only have one franchise player. “number” is an integer and should never be empty. Inserting Records Add 3 records to TEAM. Only include values for “name”, “city”, and “state” (“teamid” will start at 1 and automatically increment). Add 3 records to PLAYER, one from each team. Add 3 records to COACH, one for each team. Add at least 1 record to GAME. The winner is up to you. Add 3 records to FRANCHISEHOLDER, one for each team.

In this assignment you will write an SQL script that will create a database with five tables. In addition, you will use insert statements to add some initial records to the tables. Important: You should test your script periodically by running the source command (don’t forget to save it first). Make sure it is running properly before submission. Instructions You have been chosen to create a database for the NFL (National Football League). You will be constructing five tables and loading a small amount of records into each of them. Your SQL statements will involve DDL (Data Definition Language) syntax, including data types, domain constraints, primary and foreign key designations, among others. Here are the relations you will use as input to build your tables: TEAM(teamID, name, city, state) COACH(first, last, yearsExperience, currentTeam) FK: currentTeam -> TEAM PLAYER(number, team, first, last, hometown, position) FK: team -> TEAM GAME(team1, team2, dateOfGame, winner) FK: team1 -> TEAM team2 -> TEAM FRANCHISEHOLDER(franchiseHolderID, team, number) FK: team -> TEAM number, team -> PLAYER Here is some additional information regarding each table: TEAM “teamID” is an integer and should be automatically incremented each time a new record is inserted automatically. “name” and “city” should be varying-size strings. “state” will always be an abbreviation like “MO” or “CA”. The nfl will like to keep the “name” of the team unique- no copycats, no matter how jealous other teams may be! COACH “first” and “last” names should be varying-size strings. “yearsExperience” should be an integer, at least zero, but no higher than 55. You will have to find out how to add a domain constraint to a column (Hint: check…). Note: Your domain constraint my not actually be enforced in the current version of MySQL, but that’s fine, we just want to see that you can use the proper syntax. PLAYER “number” is an integer. “first”, “last”, “position”, and “hometown” should be varying-size strings. “first” and “last” should never be empty. GAME “team1” and “team2” are integers. “dateOfGame” is of datetime type and has a default value of current_timestamp. “winner” is an integer and should never be empty. FRANCHISEHOLDER “franchiseHolderID” is an integer and should automatically increment. “team” is an integer and should never be empty. “team” should always be unique, i.e. teams can only have one franchise player. “number” is an integer and should never be empty. Inserting Records Add 3 records to TEAM. Only include values for “name”, “city”, and “state” (“teamid” will start at 1 and automatically increment). Add 3 records to PLAYER, one from each team. Add 3 records to COACH, one for each team. Add at least 1 record to GAME. The winner is up to you. Add 3 records to FRANCHISEHOLDER, one for each team.

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

In this assignment you will write an SQL script that will create a database with five tables. In addition, you will use insert statements to add some initial records to the tables.

Important: You should test your script periodically by running the source command (don’t forget to save it first). Make sure it is running properly before submission.

Instructions

You have been chosen to create a database for the NFL (National Football League). You will be constructing five tables and loading a small amount of records into each of them. Your SQL statements will involve DDL (Data Definition Language) syntax, including data types, domain constraints, primary and foreign key designations, among others. Here are the relations you will use as input to build your tables:

TEAM(teamID, name, city, state)

COACH(first, last, yearsExperience, currentTeam)

FK: currentTeam -> TEAM

PLAYER(number, team, first, last, hometown, position)

FK: team -> TEAM

GAME(team1, team2, dateOfGame, winner)

FK: team1 -> TEAM

team2 -> TEAM

FRANCHISEHOLDER(franchiseHolderID, team, number)

FK: team -> TEAM

number, team -> PLAYER

Here is some additional information regarding each table:

TEAM
1. “teamID” is an integer and should be automatically incremented each time a new record is inserted automatically.
2. “name” and “city” should be varying-size strings.
3. “state” will always be an abbreviation like “MO” or “CA”.
4. The nfl will like to keep the “name” of the team unique- no copycats, no matter how jealous other teams may be!

COACH
1. “first” and “last” names should be varying-size strings.
2. “yearsExperience” should be an integer, at least zero, but no higher than 55. You will have to find out how to add a domain constraint to a column (Hint: check…).
  - Note: Your domain constraint my not actually be enforced in the current version of MySQL, but that’s fine, we just want to see that you can use the proper syntax.

PLAYER
1. “number” is an integer.
2. “first”, “last”, “position”, and “hometown” should be varying-size strings. “first” and “last” should never be empty.

GAME
1. “team1” and “team2” are integers.
2. “dateOfGame” is of datetime type and has a default value of current_timestamp.
3. “winner” is an integer and should never be empty.

FRANCHISEHOLDER
1. “franchiseHolderID” is an integer and should automatically increment.
2. “team” is an integer and should never be empty. “team” should always be unique, i.e. teams can only have one franchise player.
3. “number” is an integer and should never be empty.

Inserting Records

Add 3 records to TEAM. Only include values for “name”, “city”, and “state” (“teamid” will start at 1 and automatically increment).
Add 3 records to PLAYER, one from each team.
Add 3 records to COACH, one for each team.
Add at least 1 record to GAME. The winner is up to you.
Add 3 records to FRANCHISEHOLDER, one for each team.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 6 steps with 5 images

SEE SOLUTION Check out a sample Q&A here