Managing Interprise Data

A college football association has a database to record various data about their teams, players and affiliated university.

The following relational model presents the relationship between the tables:

We have the following information about the attributes:

Team:

- TeamID is an integer and is the Primary Key of the Team table

- TeamName is a chain of no more than 30 characters

- City is a chain of no more than 25 characters

- State is a chain of 2 characters

- Win is an integer

- Loss is an integer

Player:

- PlayerID is an integer and is the Primary Key of the Player table

- PlayerName is a chain of no more than 50 characters

- Age is an integer - Position is a chain of no more than 15 characters

- TeamID is an integer and is a foreign key of the Player table

- CollegeID is an integer and is a foreign key of the Player table

College:

- CollegeID is an integer and is the Primary Key of the College table

- CollegeName is a chain of no more than 30 characters

- City is a chain of no more than 25 characters

- State is a chain of 2 characters

a. Using the previous information above the SQL statement creating the Player table. (Keep in mind

that values are required for primary keys (5 pts)

b. Write a SQL statement that add the following entry in the College table (5 pts):

- CollegeID: 1478

- CollegeName: Gator College

- City: San Francisco

- State: CA

c. Write a SQL statement that change the College Name of the previous entry (in b.) from Gator College to Gator University. Your statement should make sure that only this entry is changed. (5 pts)

d. Assume the following sample of data :

Team

Player

College

Using the previous tables, write the SQL queries that perform the following actions.

Based on the data above, the output of each query is displayed below the question.

· List the name of all the teams with more wins than losses (5 pts)

Your output should be:

· List the player ID and player name of all the players between 25 and 30 years old (5 pts)

Your output should be:

· List the player name, age, position and college name for all the Quarterbacks (QB) coming from Stark College and all the linebackers (LB) coming from Gator University. (5 pts)

Your output should be:

· Using join queries, list the player name, team name , college name and city for all players playing for team located in the same city and state as their college. (5 pts)

Your output should be :

3. (20 pts)

Using the data supplied in the following table, answer the following questions:

a. Identify the appropriate fact table components (attributes). (5 pts)

b. Identify the appropriate dimension tables and appropriate attributes for each. (5 pts)

(Deriving attributes may require some creativity)

c. Draw a star schema diagram for this data warehouse. (10 pts) (Use crows foot natation method and can use hand or lucid chard for diagram)