diff options
| -rw-r--r-- | assignments/2/README.md | 46 |
1 files changed, 43 insertions, 3 deletions
diff --git a/assignments/2/README.md b/assignments/2/README.md index 5c4a31e..7689932 100644 --- a/assignments/2/README.md +++ b/assignments/2/README.md @@ -30,9 +30,49 @@ Draw a composite usage map for the RVH database. Answer the following questions (250 words max/question). -* What are the typical integrity controls performed in both data integrity and referential integrity? -* Using an example for each situation, illustrate the three common situations that suggest relations should be denormalized. -* What are the advantages and disadvantages of horizontal and vertical partitioning? +**What are the typical integrity controls performed in both data integrity and referential integrity?** + +* default value +* range control +* null value control +* referential integrity control + +**Using an example for each situation, illustrate the three common situations that suggest relations should be denormalized.** + +1. Two entities with one-to-one relationship + ```text + User(id: integer, username: string, password_digest: string, profile_id: integer) + Profile(id: integer, first_name: string, last_name: string, email: string) + ``` + + If a web site displays the currently logged in users `username` and `email` address + on all pages, then it might make sense to de-normalize this data to reduce the need for + an INNER JOIN on the two tables to render every screen. + +2. A many-to-many relationship with nonkey attributes + * E.g. +3. Reference data + * E.g. + + +**What are the advantages and disadvantages of horizontal and vertical partitioning?** + +* Horizontal Partitioning + * Advantages: + 1. Efficiency: Data queried together are stored close to one another and separate from data not used together. + 2. Local optimization: Each partition of data can be stored to optimize performance for it's own use. + 3. Security: Data not relevant to one group of users can be segregated from data those users are allowed to use. + 4. Recovery and uptime: Smaller files take less time to back up and recover and other files are still accessible if one file is damaged, so the effects of damage are isolated. + 5. Load balancing: Files can be allocated to different storage areas, which minimizes contention for access to the same storage area or even allows for parallel access to the different areas. + * Disadvantages: + 1. Inconsistent access speed: Different partitions may have different access speeds, thus confusing users. If data needs to be accessed across partitions, this produces slower response times. + 2. Complexity: is usually not transparent to application code, which requires knowledge of which partition to read/write from. + 3. Extra space and update time: Data may be duplicated across partitions, taking extra storage space compared to all data in normalized files. Updates that affect multiple partitions will be slower. +* Vertical Partitioning + * Advantages: + 1. Efficiency: Data queried together are stored close to one another and separate from data not used together. + * Disadvantages: + 1. More joins are needed in order to gather the required data reducing query speed or increasing the need for more indexes which increases the amount of needed disk space. ## Question 3 (12 marks) |