1) Check to see if the database exists first and if so, DROP the database. 2) Cr
ID: 3753352 • Letter: 1
Question
1) Check to see if the database exists first and if so, DROP the database.
2) Create the database (defining the size as 10MB, maxsize as 10MB, location as c:stage, and file name as YourLastName-Baseball.mdf).
3) Create the log file using 25% parameters for read/write.
4) Create the tables for the database
5) Alter the tables and add the primary key constraints
6) Alter the tables and add the foreign key constraints (explicitly turning Cascading on for updates and deletes)
7) Alter at least two of the tables and add a Check Constraint (make one up if necessary)
8) Alter at least one of the tables and add a Default Constraint (make one up if necessary)
9) Finish the script with a DROP DATABASE (commented out). I will run this line separately to remove your Database from the server after grading.
Team Player PK TeamID smallint PK Player!D | smallint TeamName varchar(50) TeamCity TeamManager varchar(50) PlayerName nvarchar(30) varchar(50) PlayerAge smallint has / Belongs Bat PK BatSerial Number smallint Has / Belongs PlayerHistory BatManufactuter varchar(50) Keeps/Belong Has / Works PK smallint FK1 Team smallint - FK1 TeamID FK2 PlayerlD smallint smallint PlayerBatingAverage smallint PlayerStartDate PlayerEndDate PlayerPosition smalldatetime smalldatetime varchar(10) Coach PK CoachlD smallint varchar(30) smallint CoachName CoachPhoneNumber varchar(20) CoachUnits FK1 TeamID smallint Has elongs UnitsOfWork PK UnitsNumber smallint NumberOfYears smallint Experience Type nvarchar(30) FK1 CoachID smallintExplanation / Answer
If you have any doubts, please give me comment...
CREATE DATABASE `ANUNAGA-Baseball`;
CREATE TABLE Team(
TeamID SMALLINT NOT NULL PRIMARY KEY,
TeamName VARCHAR(50),
TeamCity VARCHAR(50),
TeamManager VARCHAR(50)
);
CREATE TABLE Bat(
BatSerialNumber SMALLINT NOT NULL PRIMARY KEY,
BatManufacturer VARCHAR(50),
TeamID SMALLINT,
FOREIGN KEY(TeamID) REFERENCES Team(TeamID) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE Coach(
CoachID SMALLINT NOT NULL PRIMARY KEY,
CoachName VARCHAR(30),
CoachPhoneNumber VARCHAR(20),
CoachUnits SMALLINT,
TeamID SMALLINT,
FOREIGN KEY(TeamID) REFERENCES Team(TeamID) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE Player(
PlayerID SMALLINT NOT NULL PRIMARY KEY,
PlayerName NVARCHAR(30),
PlayerAge SMALLINT
);
CREATE TABLE PlayerHistory(
PlayerHistoryID SMALLINT NOT NULL PRIMARY KEY,
TeamID SMALLINT,
PlayerID SMALLINT,
PlayerBatingAverage SMALLINT,
PlayerStartDate SMALLDATETIME,
PlayerEndDate SMALLDATETIME,
PlayerPosition VARCHAR(10),
FOREIGN KEY(TeamID) REFERENCES Team(TeamID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY(PlayerID) REFERENCES Player(PlayerID) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE UnitsOfWork(
UnitsNumber SMALLINT NOT NULL PRIMARY KEY,
NumberOfYears SMALLINT,
ExperienceType NVARCHAR(30),
CoachID SMALLINT,
FOREIGN KEY(CoachID) REFERENCES Coach(CoachID) ON UPDATE CASCADE ON DELETE CASCADE
);
DROP DATABASE `ANUNAGA-Baseball`;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.