Download
1 / 7
70 likes | 199 Views
Damen Peterson – LS560-901. D&D DB. The Original Site – My LS560 site. The Task. Holding/Displaying character information for the d20 Modern SRD ( http:// www.wizards.com/default.asp?x=d20/article/msrd ). Currently holding character stats, skills, levels, and (general) equipment.
E N D
Damen Peterson – LS560-901 D&D DB
The Task • Holding/Displaying character information for the d20 Modern SRD (http://www.wizards.com/default.asp?x=d20/article/msrd). • Currently holding character stats, skills, levels, and (general) equipment.
Relationships and Tables Note: Originally had a link between tblAttribute.ID and tblSkillList.keyAbility. NEVER MAKE RELATIONSHIP LOOPS. You can always add a join to a query if needed, but you can’t remove one if a relationship is there.
Query 1: Character Levels • Business Rules: Characters can have a combination of classes. We need to count how many levels of each class someone has. • SELECT tblCharacter.charName, tblClass.className, Count(tblLevel.Level) AS CountOfLevelFROM tblCharacter INNER JOIN (tblClass INNER JOIN tblLevel ON tblClass.ID = tblLevel.Class) ON tblCharacter.ID = tblLevel.CharacterWHERE tblLevel.Character=2GROUP BY tblCharacter.charName, tblClass.classNameORDER BY tblCharacter.charName, tblClass.className;
Query 2: Skills with ranks Characters have skills, with ranks “bought” for each one. Each skill has an attribute associated with it (if you are more dexterous, you’re better at driving). Characters have an amount of each attribute from 3 to 18, where 10 is average. This attribute gives a bonus (or penalty) to its associated skills with this formula:int(score/2) – 5 The full Rank is the sum of the ranks and the bonus. SELECT tblCharacter.charName, tblSkillList.SkillName, tblCharSkills.Rank, tblAttribute.AttributeName, tblCharAttrs.Score, Int([Score]/2)-5 AS Bonus, [Rank]+[Bonus] AS FullRank FROM tblSkillList INNER JOIN ((tblAttribute INNER JOIN (tblCharacter INNER JOIN tblCharAttrs ON tblCharacter.ID = tblCharAttrs.Character) ON tblAttribute.ID = tblCharAttrs.Attribute) INNER JOIN tblCharSkills ON tblCharacter.ID = tblCharSkills.Character) ON tblSkillList.ID = tblCharSkills.Skill WHERE (tblCharacter.ID=2) AND ([tblAttribute].[ID]=[tblSkillList].[keyAbility]) AND ([tblCharAttrs].[Attribute]=[tblSkillList].[keyAbility]);
