Design star schema

3 files changed, 65 insertions, 0 deletions

diff --git a/assignments/3/README.md b/assignments/3/README.md
index 82bb312..9fcbfc4 100644
--- a/assignments/3/README.md
+++ b/assignments/3/README.md
@@ -29,6 +29,8 @@ The facts to be recorded for each combination of these dimensions are:
 
 **Design a star schema for this problem.**
 
+  ![star-schema](./question-1-star-schema.png)
+
 **Using the assumptions stated above, estimate the number of rows in the fact table.**
 
 **Estimate the total size of the fact table (in bytes), assuming an average of 5 bytes per field.**
diff --git a/assignments/3/question-1-star-schema.png b/assignments/3/question-1-star-schema.png
new file mode 100644
index 0000000..97ad8d3
--- /dev/null
+++ b/assignments/3/question-1-star-schema.png
diff --git a/assignments/3/question-1-star-schema.puml b/assignments/3/question-1-star-schema.puml
new file mode 100644
index 0000000..31165d3
--- /dev/null
+++ b/assignments/3/question-1-star-schema.puml
@@ -0,0 +1,63 @@
+@startuml
+!define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
+!define primary_key(x) <b>x</b>
+!define foreign_key(x) <color:purple>x</color>
+hide methods
+hide stereotypes
+
+' entities
+Table(facts, "FactTable") {
+  foreign_key(member_ID)
+  foreign_key(Item_ID)
+  foreign_key(Office_ID)
+  foreign_key(Policy_ID)
+  foreign_key(Period_ID)
+  foreign_key(Claim_ID)
+  Policy_Premium
+  Deductible
+  Monthly_Claim_Total
+}
+
+Table(members, "Member") {
+  primary_key(member_ID)
+  Name
+  Address
+}
+
+Table(insured_items, "InsuredItem") {
+  primary_key(Item_ID)
+  Description
+  Address
+  CoverageType
+}
+
+Table(coop_offices, "CoopOffice") {
+  primary_key(Office_ID)
+  Address
+  Manager_name
+}
+
+Table(policies, "Policy") {
+  primary_key(Policy_ID)
+  Type
+}
+
+Table(periods, "Period") {
+  primary_key(Period_ID)
+  Date_Key
+  Fiscal_Period
+}
+
+Table(claims, "Claim") {
+  primary_key(Claim_ID)
+  Claim_Description
+  Claim_Type
+}
+
+members --> facts : "belongs to"
+insured_items --> facts : "belongs to"
+coop_offices --> facts : "belongs to"
+policies --> facts : "belongs to"
+periods --> facts : "belongs to"
+claims --> facts : "belongs to"
+@enduml