Write SQL CREATE TABLE statements to create the following tables . This assignment teaches how to create
Question:
Write SQL CREATE TABLE statements to create the following tables.
- 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.
- 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.
- You will need to do some research to find the correct SQL SERVER 2019 datatypes. See https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15 .
- 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_HD.
- All attributes that contain the word "NAME" must use a variable length character datatype with a maximum size of 72 bytes. Use the same datatype for PASSWORD and EMAIL.
- All attributes that contain the word "ID" will be assigned a datatype the datatype BIGINT.
- All attributes that contain the word "DATE" must be assigned the datatype DATETIME.
- All attributes that contain the word "NUM" must be a 4 byte integer with no decimal places. Do not use a datatype @ssociated with decimal numbers. Use INT.
- All attributes that contain the word "COST" must be a 8 byte monetary data type. Use MONEY.
- KILL_SCORE must be a decimal number with a total of 7 digits with 2 digits to the right of decimal place.
- DESCRIPTION must use a variable text datatype that can handle up to 2 Gigagytes bytes of Variable length latin characters. Use VARCHAR(MAX).
- 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.
- Create @ PRIMARY KEY for each table as follows:
- For tables with a simple primary key:
Add PRIMARY KEY on the line of the primary key attribute
- 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.
- 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.
/* DIRECTLY MODIFY THESE RELATIONS AND TURN IN THIS FILE */
GAMER (USER_NAME, PASSWORD, 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, USER_NAME)
FOREIGN KEY USER_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)
- You must actually create the selected tables in your SQL Server Manager. Copy all of your SQL CREATE TABLE into a SQL Server Manager window to test.
- RENAME this file to be of the form LastName-FirstName-A07.docx and submit 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.