ddl.md

Solution - Data Definition Language

Exercise 1

Recreate the following database called Game_db based on the following schema:

Steps to accomplish this exercise:

1. Create a database named Game_db, then refresh your object explorer in SQL Management Studio so that the database is visible. Then execute the statement USE Game_db to make the database active as the default database.

   CREATE DATABASE Game_db;
   GO
   USE Game_db;

2. Create the Game table.

   CREATE TABLE Game
   (
       [Name]             VARCHAR(20),
       CONSTRAINT PK_Game PRIMARY KEY([Name])
   );

3. Create the Goalcard table.

   CREATE TABLE Goalcard​
   (
       Id              VARCHAR(5),
       [Name]          VARCHAR(30),
       CONSTRAINT PK_Goalcard PRIMARY KEY(Id)
   );

4. Create the Hallcard table.

   CREATE TABLE Hallcard
   (
       Id              VARCHAR(5),
       [Type]          VARCHAR(8),
       Treasure        VARCHAR(20),
       CONSTRAINT PK_Hallcard PRIMARY KEY(Id)
   );

5. Create the Player table
   • The Id column is automagically determined by the database engine
   • Make sure to add a constraint called CH_Player_Colors so that the Player can only choose a red or black color, NULL is also fine.

   CREATE TABLE Player
   (
       Id      	    INT IDENTITY,
       [Name]          VARCHAR(100) NOT NULL,
       Birthyear       INT,
       Color           VARCHAR(10),
       CurrentSquare   VARCHAR(20),
       IsTurnPlayer    BIT,
       GameName        VARCHAR(20),
       CONSTRAINT PK_Player PRIMARY KEY(Id),
       CONSTRAINT FK_Player_Game FOREIGN KEY(GameName) REFERENCES Game([Name]),
       CONSTRAINT CH_Player_Color CHECK (Color IN ('red','black'))
   );

6. Create the Game_Hallcard table.

   CREATE TABLE Game_Hallcard
   (
       GameName	    VARCHAR(20),
       CardId          VARCHAR(5),
       Direction       VARCHAR(20),
       Position        VARCHAR(20),
       CONSTRAINT PK_Game_Hallcard PRIMARY KEY(GameName, CardId),
       CONSTRAINT FK_Game_Hallcard_Gamename FOREIGN KEY(GameName) REFERENCES Game([Name]),
       CONSTRAINT FK_Game_Hallcard_Hallcard FOREIGN KEY(CardId) REFERENCES Hallcard(Id)
   );

7. Create the Player_Goalcard table.

   CREATE TABLE Player_Goalcard​
   (
       PlayerId        INT,
       GoalId          VARCHAR(5),
       [Order]         INT,
       CONSTRAINT PK_Player_Goalcard PRIMARY KEY(PlayerId, GoalId),
       CONSTRAINT FK_Player_Goalcard_Player FOREIGN KEY(PlayerId) REFERENCES Player(Id),
       CONSTRAINT FK_Player_Goalcard_Goalcard FOREIGN KEY(GoalId) REFERENCES Goalcard(Id)
   );

8. Add an extra column Email to the Player entitytype, which is a VARCHAR of max. 50 characters long.

   ALTER TABLE Player
   ADD Email VARCHAR(50);

9. Adjust the column Email from the Player entity type to a maximum length of 100 characters.

   ALTER TABLE Player
   ALTER COLUMN Email VARCHAR(100);

10. Add an extra column Phonenumber to the Player entity type, which entitytype would be a good fit?

    ALTER TABLE Player
    ADD Phonenumber VARCHAR(25);

    Why not an INT value?

11. Remove the column Phonenumber fron the Player entity type since we don't need it anymore.

    ALTER TABLE Player
    DROP COLUMN Phonenumber;

Deep Dive:

1. Why is it sometimes better/mandatory to embrace certain table/column/... names like Name, Type, Order, etc. with square brackets like the following [Name], [Type], [Order] ?
   • This StackOverflow post can give some clarification.

Exercise 2

Solution will be posted before 6/03/2020.

Exercises

Click here to go back to the exercises.