Mini-case: The Merry Adventures of Robin Hood - Physical Database Design Techniques Robin Hood and his Band of Merry Adventurers have prospered. The developed a database in 3rd normal from the ER Diagram we prepared for them last year, all went well. Here is the logical database design in schema notation:

MERRY ADVENTURERS (Adventurer ID, Adventurer Name, Hat, Birth Date, Personality) unique not null: Hat unique not null: Adventurer Name

PARTICULARLY PROMISING PLACES (Place ID, Place Name, Directions) unique not null: Place Name unique not null: Directions

RICH OPPRESSORS (Oppressor ID, Oppressor Name, Hometown, Title unique: (Name, Hometown, Title) not null: Name, Hometown, Title

WEAPONS (Weapon ID, Weapon Type, Weapon Size)

ADVENTURER SKILLS (Adventurer ID, Skill ID, Level of Ability, Skill Video) FK Adventurer ID references

MERRY ADVENTURERS FK Skill ID references

SKILLS MISSION TEAM MEMBERSHIPS (Mission ID, Adventurer ID) FK Mission ID references

MISSIONS FK Adventurer ID References

MERRY ADVENTURERS MISSIONS (Mission ID, Target, Date, Goal, Haul) FK Target references

RICH OPPRESSORS SKILLS (Skill ID, Skill Name unique not null)

Problem Statement Many peasants and some of the nobility have joined the band to fight the oppression of Bad Prince John and his toady minions. Now, however, they have so many weapons, and so many missions, and so many adventurers that the performance of the normalized database has degraded from wonderful to terrible. Friar Tuck brought you a parchment scroll asking that you help to help the database infrastructure of the Merry Adventurers to the next level. Deliverables 1. For each problem you discover:

a. You my type your answers in this document

b. Recommend a physical database design technique

c. Explain how you would implement the technique on this database

d. Explain any risks or downsides associated with the technique. Here is what you learned:

1. In about 80% of the cases where a user looks up the name of a Merry Adventurer in the Merry Adventurer Table, they also need to see MissionTeamMemberships. The tables are stored on two different parts of the disk. Its taking longer than a Cardinals Crosier to pull the records together so those tables can be joined. ANSWER:

2. Almost every time someone queries the Missions table, they want to know the Hat of a mission team member. They never want to know the Adventurer ID. That means they must join it to the Merry Adventurers table to the Missions table almost every time. It turns out Hat is a unique identifier for Merry Adventurers. ANSWER:

3. Many times per hour, many users look for records in the AdventurerSkills table based on the LevelOfAbility rather than using the primary key, AdventurerName and SkillName. As the band grew, these queries became slower than a noble pulls his purse to pay a peasant. ANSWER:

4. Many times per day, users query the Missions table to add up the haul for each Rich Oppressor. These calculations slow down the process like a skunk stops a garden party. ANSWER:

5. As more people joined the band, they found that names were not unique, nor were hats, but names and hats were unique, so that became the primary key. So they made those the primary key. Now, when they join the Merry Adventurers table to the Mission Table, its very slow. ANSWER:

6. Many users browse around in tables they dont need while looking the data they do need. All these extra queries are bogging down the system like a dead-tired donkey drooping at the end of a day. Not only that, but some of the data should really be kept private, but right now, everybody can see it. Evil Oppressors, for example, can see both current and old mission records, when they should only be able to see old records. Not only that, but they can see things like places and team memberships, when they should be restricted to see only Targets and Dates. ANSWER:

7. Merry Adventures and Rich Oppressors both access the Rich Oppressors table a lot. Their conflicting queries make the database as slow as an oak tree grows. Most of the time, Merry Adventures only need to see the Oppressor ID and Fortune, while the Evil Oppressors only need to see the Oppressor Name, Hometown, and Title. ANSWER:

8. Each AdventurerSkill record now includes a digital Skill Video showing the adventurer executing the skill. Every time someone runs a query that includes this table, it is slower than a stew caldron simmers on a small wood fire. ANSWER:

9. There are so many people hitting the Missions table that, no matter what, the best hardware cant keep up with the demand. People are lined up waiting for the data they request. ANSWER:

10. The Merry Adventurers and the Evil Oppressors both do a lot of queries on the Missions table, and the system cant keep up with their demands. The Merry Adventurers, however, only need recent records, while the Rich Oppressor view limits them only to old records. ANSWER:

answer all the answers in SQL notation