The Scenario: The Scared Hitless Sofball League (SHSL for short) has grown to th

Question

The Scenario:

The Scared Hitless Sofball League (SHSL for short) has grown to the point where it’s become too challenging to keep track of everything on paper. The league has decided it needs a database to keep track of all of the people and events going on. You’ve been asked to develop a database for SHSL and have met with the owners to discuss the project. The notes you see below are what you’ve taken away from that conversation

• The league is made up of teams who are sponsored by schools. While most schools sponsor only one team, a few of the larger ones sponsor several.

• The league has a group of coaches. To become a coach in the league, one must be qualified for the level the given team is at. A level defines the minimum and maximum weight and age of the players. A given coach can qualify for multiple levels, but he or she must possess the qualification for the level that the team has. The league needs to record the first and last name of the coaches as well as their date of birth and the date the individual was brought on as a coach.

• The league has to keep track of the name of each team as well as the team’s, level, number of wins, coaches, players, and the school the team belongs to.

• Every player who participates in the league must have at least one person who can be contacted in case of an emergency. The database must store the first and last name of each contact person, as well as the date of birth, telephone number, and an optional email address for each contact.

• Every player belongs to a specific team and is assigned a position on that team. Some players may not have a specific position assigned to them yet, but most eventually do. The database must keep track of every player’s first and last name, date of birth, team, position, and emergency contacts.

Questions:

1. Provide a set of SQL statements that, when executed, create the tables. Design each SQL statement using the data dictionary you created to define the table. Use the MySQL syntax or the SQLite syntax while building your statements. I suggest you test your statements using the tool/DBMS of your choice (SQL Fiddle, MySQL Workbench, SQLite, etc.).

2. Create a series of SQL statements that add sample data of your choosing into each table. Create surrogate key values for rows that require primary key values, making sure any associated foreign keys in other tables correspond by using the same value in both fields.

Explanation / Answer

CREATE DATABASE SHSL;
Create table League
(
Levels_id number () PRIMARY KEY AUTO_INCREMENT,
Team_name varchar2 () not null,
Wins number (),
Coach_ID number (),
Sponsor_Name varchar2 () not null
);

Create table Coaches
(
Coach_ID number (),
First_name varchar2 () not null,
Last_name varchar2 () not null,
Date_Of_Joining date not null,
Date_Of_birth date not null
);

Create table Players
(
First_name varchar2 () not null,
Last_name varchar2 () not null,
Date_Of_birth date not null,
Team varchar2 () not null,
Position number (),
Emergency_Contact_id Number (),
Weight number () ,
age number ()
);

Create table Emergency
(
Emergency_Contact_id Number () PRIMARY KEY AUTO_INCREMENT,
First_name varchar2 () not null,
Last_name varchar2 () not null,
Tel_Number number(10) not null,
Date_Of_birth date not null,
address varchar2 () not null,
email varchar2 () not null
);

Insert into League values (1,AAA,10,'John_Miller','Don_Bosco');

Insert Into Coaches Values (1,'John','Miller','01/12/2008','01/08/1980');

Insert into Players Values ('Smith', 'Maxwell',''01/12/1990','AAA',5,1,70,27);

Insert into Emergency Values ( 1,'Shane','Warne',7387264526,'05/16/1970', 'xyzstreet','aaa@hotmail.com');

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.