6. You are given requirements for a database schema of the National Football Lea
ID: 3889188 • Letter: 6
Question
6. You are given requirements for a database schema of the National Football League (NFL) as follows: -the ‘NFL' has many teams. . each 'team, has a "name", a ‘city', a ‘coach, a 'captain', and a set of players, each player has a ‘name', a ‘position' (such as quarterback, wingback, etc), a 'skill' level, and a set of injury records (injury records have a 'description' and 'id'), . each player belongs to only one team a team captain is also a player, . a 'game' is played between two teams (referred to as host team and guest team) and has a 'game date, (such as Aug 3rd, 2010) and a ‘score' (such as 4 to 2) Create a SQL database schema for NFL. Your schema must include all primary, foreign key requirements, and constraints defined in the description. The words in quotes correspond to table or attribute names. Do not use any other names to declare these tables or attributes. You may include new attribute names to create relationships between tables, but the attributes mentioned in the requirements must be declared as specified in quotesExplanation / Answer
Hi,
Below is the answer-
SQL DDL commands to CREATE tables with consraints-
1. TEAM
CREATE TABLE TEAM(NAME VARCHAR(20) PRIMARY KEY,
CITY VARCHAR(20),
COACH VARCHAR(20),
CAPTAIN VARCHAR(10));
2. PLAYER
CREATE TABLE PLAYER(PLAYER_ID VARCHAR(10) PRIMARY KEY,
NAME VARCHAR(20),
POSITION VARCHAR(15),
SKILL VARCHAR(20));
3. INJURY_RECORDS
CREATE TABLE INJURY_RECORDS(ID VARCHAR(10) PRIMARY KEY,
DESCRIPTION VARCHAR(20)
PLAYER_ID VARCHAR(10) REFERENCES PLAYER(PLAYER_ID));
4. PLAYER_TEAM
CREATE TABLE PLAYER_TEAM(PLAYER_ID VARCHAR(10) REFERENCES PLAYER(PLAYER_ID),
NAME VARCHAR(10) REFERENCES TEAM(NAME));
5. GAME
CREATE TABLE GAME(HOST_TEAM VARCHAR(20), GUEST_TEAM VARCHAR(20),
GAME_DATE DATE, SCORE NUMBER(10));
Regards,
Vinay Singh
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.