Skip to content

Model design #4

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
NabZaf wants to merge 8 commits into
base: main
Choose a base branch
from
Open

Model design #4

Changes from all commits
Commits
Show all changes
8 commits
Select commit Hold shift + click to select a range
779f347
Update design_a_logical_model.md
NabZaf Jun 2, 2024
42248df
Update design_a_logical_model.md
NabZaf Jun 2, 2024
e524a52
Update design_a_logical_model.md
NabZaf Jun 2, 2024
4fc505a
Update design_a_logical_model.md
NabZaf Jun 2, 2024
6fb23c6
Update design_a_logical_model.md
NabZaf Jun 2, 2024
01584f6
Update design_a_logical_model.md
NabZaf Jun 2, 2024
7238644
Update design_a_logical_model.md
NabZaf Jun 2, 2024
19c6514
Update design_a_logical_model.md
NabZaf Jun 2, 2024
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
25 changes: 23 additions & 2 deletions 02_assignments/design_a_logical_model.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -5,27 +5,48 @@ Create a logical model for a small bookstore. 📚

At the minimum it should have employee, order, sales, customer, and book entities (tables). Determine sensible column and table design based on what you know about these concepts. Keep it simple, but work out sensible relationships to keep tables reasonably sized. Include a date table. There are several tools online you can use, I'd recommend [_Draw.io_](https://www.drawio.com/) or [_LucidChart_](https://www.lucidchart.com/pages/).

![image](https://github.com/NabZaf/sql/assets/67026567/d7bf371f-cf00-43a3-aac6-0c2771f4ee46)


## Question 2
We want to create employee shifts, splitting up the day into morning and evening. Add this to the ERD.


![image](https://github.com/NabZaf/sql/assets/67026567/b2287116-a3e3-41bd-86b9-b9b219c6c493)


## Question 3
The store wants to keep customer addresses. Propose two architectures for the CUSTOMER_ADDRESS table, one that will retain changes, and another that will overwrite. Which is type 1, which is type 2?

_Hint, search type 1 vs type 2 slowly changing dimensions._

Bonus: Are there privacy implications to this, why or why not?
Saving the address should require customer consent.

Does not retain changes - Type 1
![image](https://github.com/NabZaf/sql/assets/67026567/9b7d84b6-9196-4c31-9863-7920f4c6f98d)

Retains Changes in address Type2
![image](https://github.com/NabZaf/sql/assets/67026567/5bcd253c-f160-42dc-9d23-69ad7745050d)


```
Your answer...

```

## Question 4
Review the AdventureWorks Schema [here](https://i.stack.imgur.com/LMu4W.gif)

Highlight at least two differences between it and your ERD. Would you change anything in yours?
```
Your answer...
1. The AdventureWorks Schema has far more tables and far more relationships. Each table is also in greater detail.
2. The AdvenureWorks schema does not whether the relationships are one-to-one, one-to-many or many-to-many.
If I had more time, I would make my diagram in more detail. I would think about what I need in the tables to better understand customer preferences and to build a better relationship with my customers. Of course, One needs to keep data privacy and other ethical issues in mind.

```



# Criteria

[Assignment Rubric](./assignment_rubric.md)
Expand Down