NDS203 Networking and Database Systems Assessment Answer

Task 1.

CREATE TABLE TempData (

    FirstName VARCHAR(255),

    LastName VARCHAR(255),

    Gender CHAR(1),

    FactionCode VARCHAR(3),

    FactionName VARCHAR(255),

    FactionMotto VARCHAR(255),

    SkillName VARCHAR(255),

    SkillType VARCHAR(255),

    QuestName VARCHAR(255),

    LocationName VARCHAR(255),

    RegionCode VARCHAR(3),

    RegionName VARCHAR(255),

    RegionType VARCHAR(255)

)

 

-- Create the Regions table

CREATE TABLE Regions (

    Region_Code VARCHAR(255) PRIMARY KEY,

    Region_Name VARCHAR(255),

    Region_Type VARCHAR(255)

);

 

-- Create the Factions table

CREATE TABLE Factions (

    Faction_Code VARCHAR(255) PRIMARY KEY,

    Faction_Name VARCHAR(255),

    Faction_Motto VARCHAR(255)

);

 

-- Create the Skills table

CREATE TABLE Skills (

    Skill_Name VARCHAR(255) PRIMARY KEY,

    Skill_Type VARCHAR(255)

);

 

-- Create the Locations table

CREATE TABLE Locations (

    Location_Name VARCHAR(255) PRIMARY KEY,

    Region_Code VARCHAR(255),

    FOREIGN KEY (Region_Code) REFERENCES Regions(Region_Code)

);

 

-- Create the Quests table

CREATE TABLE Quests (

    Quest_Name VARCHAR(255) PRIMARY KEY,

    Location_Name VARCHAR(255),

    Region_Code VARCHAR(255),

    FOREIGN KEY (Location_Name) REFERENCES Locations(Location_Name),

    FOREIGN KEY (Region_Code) REFERENCES Regions(Region_Code)

);

 

-- Create the NPCs table

CREATE TABLE NPCs (

    First_Name VARCHAR(255),

    Last_Name VARCHAR(255),

    Gender VARCHAR(255),

    Faction_Code VARCHAR(255),

    Skill_Name VARCHAR(255),

    Quest_Name VARCHAR(255),

    Location_Name VARCHAR(255),

    FOREIGN KEY (Faction_Code) REFERENCES Factions(Faction_Code),

    FOREIGN KEY (Skill_Name) REFERENCES Skills(Skill_Name),

    FOREIGN KEY (Quest_Name) REFERENCES Quests(Quest_Name),

    FOREIGN KEY (Location_Name) REFERENCES Locations(Location_Name)

);

Task 2.

 

-- Insert data from the temporary table into the Regions table

INSERT INTO Regions (Region_Code, Region_Name, Region_Type)

SELECT DISTINCT RegionCode, RegionName, RegionType

FROM TempData;

 

-- Insert data from the temporary table into the Factions table

INSERT INTO Factions (Faction_Code, Faction_Name, Faction_Motto)

SELECT DISTINCT FactionCode, FactionName, FactionMotto

FROM TempData;

 

-- Insert data from the temporary table into the Skills table

INSERT INTO Skills (Skill_Name, Skill_Type)

SELECT DISTINCT SkillName, SkillType

FROM TempData;

 

-- Insert data from the temporary table into the Locations table

INSERT INTO Locations (Location_Name, Region_Code)

SELECT DISTINCT LocationName, RegionCode

FROM TempData;

 

-- Insert data from the temporary table into the Quests table

INSERT INTO Quests (Quest_Name, Location_Name, Region_Code)

SELECT DISTINCT QuestName, LocationName, RegionCode

FROM TempData;

 

-- Insert data from the temporary table into the NPCs table

INSERT INTO NPCs (First_Name, Last_Name, Gender, Faction_Code, Skill_Name, Quest_Name, Location_Name)

SELECT FirstName, LastName, Gender, FactionCode, SkillName, QuestName, LocationName

FROM TempData;

 

Task 3.

Queries:

--1

SELECT Skill_Name AS Name, Skill_Type AS Type

FROM Skills;

 

 

--2

SELECT First_Name, Last_Name

FROM NPCs

WHERE Gender = 'F';

--3

SELECT First_Name, Last_Name, Gender, Faction_Code

FROM NPCs

WHERE Faction_Code = 'ROG'

ORDER BY First_Name ASC;

--4

SELECT q.Quest_Name, n.First_Name AS 'NPC First Name'

FROM Quests q

JOIN NPCs n ON q.Location_Name = 'Arcane Capital' AND n.Quest_Name = q.Quest_Name;

--5

SELECT q.Quest_Name AS 'Quest Name', n.First_Name AS 'NPC First Name'

FROM Quests q

JOIN NPCs n ON n.Faction_Code = 'WAR' AND n.Location_Name = 'Large Scale City';

--6

SELECT Location_Name, COUNT(Quest_Name) AS 'Number of quests'

FROM Quests

GROUP BY Location_Name;

--7

-- Adding a one-to-many relationship between 'Factions' and 'Skills'

ALTER TABLE Skills

ADD Faction_Code VARCHAR(255);

 

-- Update each faction with the required skills

UPDATE Skills

SET Faction_Code = CASE

    WHEN Skill_Name = 'Fireball' THEN 'MAG'

    WHEN Skill_Name = 'Lunging Strike' THEN 'WAR'

    WHEN Skill_Name = 'Steal' THEN 'ROG'

    ELSE NULL

    END;

 

-- Print each Faction with its Favourite Skill

SELECT F.Faction_Name, S.Skill_Name AS 'Favourite Skill Name'

FROM Factions F

JOIN Skills S ON F.Faction_Code = S.Faction_Code;

 

--8

-- Update 'Gender' values

UPDATE NPCs

SET Gender = CASE

    WHEN Gender = 'M' THEN 'Male'

    WHEN Gender = 'F' THEN 'Female'

    ELSE NULL

    END;

 

-- Show all NPCs

SELECT First_Name, Last_Name, Gender

FROM NPCs;

 

You Might Also Like:- 

Computer Organization and Architecture Assignment Sample

Computer Science Degree Programs-in-2022-in-Australia

Computer Science Assignment Help