1.Write SQL CREATE TABLE statements to create the following tables.
2.This assignment teaches how to create database tables and constraints. It allows you to determine proper SQL SERVER datatypes based on specifications. If you have any questions, ask your professor.
3.Assign the following SQL Server datatypes to each of the attributes listed in the relations below. Oracle, MySQL, or Access syntax will not be accepted. FOLLOW ALL TABLE NAMES, ATTRIBUTE NAMES, and DATATYPE REQUIREMENTS EXACTLY. Your grade will be largely determined on how well you follow these directions.
4.You will need to do some research to find the correct SQL SERVER 2017 datatypes. See https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017 .
5.Add your Assignment 3 featured stock table to the list of all tables. Use the attribute names listed below in the sample featured symbol table. Substitute your first name, last name, and featured stock symbol in the sample table name. My table would be Art_Hendela_CL.
6.All attributes that contain the word "NAME" must use a variable length character datatype with a maximum size of 42 bytes. Use the same datatype for PSWD and EMAIL.
7.All attributes that contain the word "ID" will be assigned a datatype the datatype BIGINT.
8.All attributes that contain the word "DATE" must be assigned the datatype SMALLDATETIME.
9.All attributes that contain the word "NUM" must be a 1 byte integer with no decimal places. Do not use a datatype associated with decimal numbers. Use TINYINT.
10.All attributes that contain the word "COST" must be a 4 byte monetary data type. Use MONEY.
11.KILL_SCORE must be a decimal number with a total of 9 digits with 2 digits to the right of decimal place.
12.DESCRIPTION must use a variable text datatype that can handle up to 8K of text. Use Varchar(nn) with the appropriate value of n not handle the maximum size specified.
13.Use explicit constraint names for the foreign key constraints. Make them of the form C_FK_nn where nn is a unique number in the entire database. DO NOT USE SYSTEM GENERATED CONSTRAINT NAMES.
14.Create a PRIMARY KEY for each table as follows:
a.For tables with a simple primary key:
Add PRIMARY KEY on the line of the primary key attribute
b.For tables with compound primary keys, more than one attribute:
Add a constraint to the bottom of the table in the form:
CONSTRAINT c_pk_nn PRIMARY KEY(pk_attributeName1, pk_attributeName2)
where nn is a unique number in the entire database.
15.Commas separating the field and constraint names MUST be located to the LEFT side of the field name or constraint as shown in the demonstration.
GAMER (U_NAME, PSWD, F_NAME, L_NAME, EMAIL, CREATE_DATE)
CHARACTER (CHAR_NAME, LEVEL_NUM, EXP_NUM, HP_NUM, MP_NUM, STRENGTH_NUM, INTEL_NUM, DEXTERITY_NUM, CLASS_NAME, U_NAME)
FOREIGN KEY U_NAME REFERENCES GAMER
PVP_MATCH (MATCH_ID, WINNER_NAME, LOSER_NAME, KILL_DATE, KILL_SCORE)
FOREIGN KEY WINNER_NAME REFERENCES CHARACTER
FOREIGN KEY LOSER_NAME REFERENCES CHARACTER
GUILD (GUILD_NAME, FOUNDING_DATE, STATUS_NUM)
CHARACTER_GUILD (CHAR_NAME, GUILD_NAME)
FOREIGN KEY CHAR_NAME REFERENCES CHARACTER
FOREIGN KEY GUILD_NAME REFERENCES GUILD
ITEM (ITEM_NAME, VALUE_NUM, STATUS_NUM, TYPE_NUM)
ARMOR (ITEM_NAME, DEFENSE_NUM, TYPE_NUM)
FOREIGN KEY ITEM_NAME REFERENCES ITEM
ACCESSORY (ITEM_NAME, SPECIAL_EFFECTS_NAME, TYPE_NUM)
FOREIGN KEY ITEM_NAME REFERENCES ITEM
WEAPON (ITEM_NAME, SPEED_NUM, DPS_NUM)
FOREIGN KEY ITEM_NAME REFERENCES ITEM
CHARACTER_ITEM (CHAR_NAME, ITEM_NAME)
FOREIGN KEY CHAR_NAME REFERENCES CHARACTER
FOREIGN KEY ITEM_NAME REFERENCES ITEM
SKILL (SKILL_ID, SKILL_NAME, MP_COST , LEVEL_NUM, DESCRIPTION)
CHARACTER_SKILL (CHAR_NAME, SKILL_ID)
FOREIGN KEY CHAR_NAME REFERENCES CHARACTER
FOREIGN KEY SKILL_ID REFERENCES SKILL
PREREQUISITE_SKILL (SKILL_ID, PREREQUISITE_SKILL_ID)
FOREIGN KEY SKILL_ID REFERENCES SKILL
FOREIGN KEY PREREQUISITE_SKILL_ID REFERENCES SKILL
YourFirstName_YourLastName_YourFeaturedStockSymbol (SYMBOL_NAME, PURCH_DATE, OPEN_COST, CLOSE_COST, VOLUME_NUM)
16.You must actually create the selected tables in your SQL Server Manager or MS Access. Write all your SQL CREATE TABLE statements in a file named LastName-FirstName-A06.sql. For me, this file would be Hendela-Art-A06.sql
17.By the due date, submit the LastName-FirstName-A06.sql through Canvas. Your script will be run. If your script runs and your follow the very strict datatype requirements, then you will score very well.