Skip to content

Update design_a_logical_model.md #6

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
Adaobi07 wants to merge 2 commits into
base: main
Choose a base branch
from
Open

Update design_a_logical_model.md #6

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
c870051
Update design_a_logical_model.md
Adaobi07 Oct 23, 2024
af4f07d
Update design_a_logical_model.md
Adaobi07 Oct 23, 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
15 changes: 15 additions & 0 deletions 02_activities/assignments/design_a_logical_model.md
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,7 @@
# Assignment 1: Design a Logical Model

https://viewer.diagrams.net/?tags=%7B%7D&lightbox=1&highlight=0000ff&edit=_blank&layers=1&nav=1&title=Logical%20model%20SQL%20Assignment%201.drawio#R%3Cmxfile%3E%3Cdiagram%20id%3D%22R2lEEEUBdFMjLlhIrx00%22%20name%3D%22Page-1%22%3E7V1bc6M4Fv4t%2B%2BCq6QenuBjbPMZOMt3TyUw26drZnpeUYmSbCQYvxok9v34lbgZJgISBYJuurooR0kHofOcqIfXU6Wr3qwvWywfHgFZPkYxdT73pKYo8UAfoDy7ZhyXjkRKULFzTCMsOBc%2FmPzAslMLSrWnATaqi5ziWZ67ThTPHtuHMS5UB13U%2B0tXmjpV%2B6hosIFXwPAMWXfqnaXjLoHSsSYfyr9BcLKMny1J4ZwWiymHBZgkM5yNVBHfenWN7YRcfobsCNrQ9dOcBuG%2FQ7Wm3S8%2FDb3rdU%2B7Q%2FzmufbVwnIUFwdrcXM2cFSqebVCVuzlYmRYe5wShSUgIPU697alT13G84NdqN4UWZlbEhqBPdxl343FwMV2OBr%2F99fr89vTz99kIbO41E%2F787em5L48CMu%2FA2oYDfLtaW84ewnCQvH008mi81vinB15x0WTjAdcLAaJKqACx3AOmjd5OvZH9a8sC643pVw9KlqZl3IO9s%2FUiQtHVZG7uoPEU4APXRVC5R8TwJSaOR%2Fo57Ay%2BDSxzYaPfM%2FT6%2BIkTF25QX%2B7BxgtrhG8GXQ%2FuMsdMjjmBRAY6K%2Bi5e1QlbDAMcREKy3AcXn8ckKdEyFsmUaeHhSCE0iImfeAQ%2BhEySYRhY4phmYxC7%2BmZwHpCYgjshc%2BzNEvwuBqus%2F4B3AX0woK1Y%2BIRvX2HgSD4g29a1tSxHMxZ27FhVM1%2FO22C%2FqP3nUpXWk9DHZiia%2Flwjf7j6q43deyN5yKE4EdBxKgPiJk18Zx1%2BBwLzqNuuOFo4t%2BvjuchuRJgaw7YaWaH3FU5mTuojbc6xdvH7yLcddCozC1fpy1Nw4B2IJRYD4MDxxnMZHIgHnWSHaQoHsGRMZsjCRaoghwIiR2GRZgasBD8beDBibO1jQ3F1rif5Tkd2RuG2n35dtNClkfaNqg72azBzLQX90HLIYEJrRZM7LKlVKkUI1zkmgCJfMmqXqpT1Y947bhSl66PeFmSuQ2JfXkGZDlWBbo4d8DFVTtFrgmxVRmcHfYUVcYjmlLzcemd6W68l9%2FBCiYKsStLln01Xfhyg%2FqfIglM6%2BXaMJAHvEmUT1xgz5Yvf8zn5izbm2%2BxTakefYI25Ag08tGrFo7T%2Fvv%2B%2Fk3y7K8f6n%2B8h4fF4%2BiVBcfpdoM4EgShZxDhUeEcAzOZUBiMtF4yxOsPhpy2IS48xjawWTa4ZMPPZmw%2BtovZ%2FQkxHrvLGsXbE4rxijky4ObIGcV47KEYUpyehYr3BTVsH8tLxnhVYuKcYzz22ND51wtS9VKdqr7RGI%2Fd5%2BNytY3GeMUMGHEz4KxiPPZg0JnayKl%2B%2BWZwBXZ2uiwI4gAVxD0uEU9f7O3qFfvrcXEc7bUOTpxRXZV4u8SojsLfH67RRXS%2BFScm7fqsgG7MCuhkrSZDQHuCnZHPQ%2FVR4RyLtWpdnD3OfWuXhR9yj35iuAeCo52vX3moNaBdGX7bVOldSy3kbVXmlp%2F5uzSrkqJXLRjaggZWPv9itLdI7kdUew81Tu1dV4BG51lxLg7Rku7OKCfHHzkXaeLSfjMPtQYkmc69%2Bl6zH7Jhrk%2FPJz9XCz5EVX%2F5OKslgKHD%2FAtS%2FXU67jqn6h%2FXpfqj5cxn4bnr3MNfoJPzhltcw5PUGhBYmV4zdVDxcfYsKCKWUQSFPxwPWIlSdlrvmwdXL34DmCxuIYKqig%2F4ISZoJMpD7hMwx15sQoeLAbQEcnGD1ubijlo%2FH2nZvFQcc21FFXqfzazOpPOwNQfomTadJfHMCbdRXcxVT8iol2KAXmjVmTNkeQNeYsKNJNeAjlVpu451rB%2BoxeaXMOg3YP%2FizF%2F%2BhPAtUfqA9OAycf0Tguxpk9bb7SpRJDqNVh5VfPSagJVCwaqzBtVYg9j2F5mDKrJ7bOYel7htvTUIwCtsDcpnZXioNSG0xy2PPX0FnsV4QQV%2BRHqOh1wTSOCYfoXGAkZxDxpW09s%2FQQt4pmPfHu4E0VEQisno7SdLb2X1gjAI2sY1%2Fm4dXd4%2BIST8cB6AvfcDLPRe0Aj5jWi7%2B%2F%2Fiiytd16KCn36BpA6jgptdsv7NPnn1CF0TDYkf%2Fx1UA36BTHxEX%2BBHJidrpIJ4KldXxB%2FKp4xyVOj6g%2Fae7kkOch6xQTtgchQFB8TivIjCxtm6Mxg2OgCFojOW0nTUwfBKH6ZpBYNB0UJMBPtEtdDkZnaZfJQSPymzc0S4q%2BMW0uGfnN96nG49KH6elm4hj%2FTCJsRDeJ6ikk8pbjJKN9GKWxD4UMkW6EfAwGoVCMcKzcYViJxWHlqB6jgjLTHUCS1BrqzjVRMjRb8aKKTkxZKlja9k%2FfBvXIv%2BoFQeuT8HJQKDq%2FS3SEM9Ksluo5NtxkVt0AuTbeTC52hkG00j2tQjoNEYtkJAd6YXGXglvA5EdCiPw%2BuDjOKLfeKCNO6xuPeRfyCP0jI%2F0kb1OgyBIOUM%2FMnoDJmwaEMydcyrMwaEvA4kGTE2oSZq0RIqqSX0fC1B1o%2B8lHqlkGMngsbNJJYbiZAbWSmylky5SUj2eJySbHWsCEt2lVKo01KYPT33eUKokA5fbC6EpZDccomQ5qrEjnjMqAkpovMWE8d5oyTp1NbsJ2S4x5PTKJg3JnzAYRTkJPMPLCwjJyUbtkclEgf0urF8pj1hLTZZOq75D2aVFQ5tkpH%2B9Ye5soANv0JgEEUTx4hUDCMTxZt3Pq3tuQL5yEwos9JaLBzUlk8e0J9xiH263cCSUFZy82NpevB5DXxL8%2BGCdQmZzZGLy0tAD%2Bi0I1bk%2FhKhT0WD8BfcrYBLdto6nhKsBj5c5JrAj8B3wRdtT46bHyuwJ6y1SEwfWavNrzh2LVLlCgQaZkSveWtSbi%2FIXPaUWNxCkmtAH0QeLnvHMPTTCrW5ncLG8H9bvHcwll7M8Wscxi1ef5GCbxmiP198EhLW%2FP1oQ2Jc9Su03qFnzkDi%2FsbXJfiurKx3yRvBQ%2FEd23FXWAPF996BawL0F9ke4G1duCmoNwPrrCofISPwzYEUvLxkQQ9xoB8aM7ql466XwA5JKkEZAp%2FXD63htU%2FDN4jRHdM2fCziW1L0ov4dpK%2FszRxRj57jC1EAGz9kSjzkw3GNdLdiWuhNXt9MRA7TRErQeYP9EHiper4w%2BTTi0Q1FCv1%2BBbO3hZ%2F86BMsVjQ54G7yx5fEaxhw5rh%2B8qXvLc3Zm433CvCbm7aJ9Qi7boLNufUS3UnVM8zN2gL76I6Fwkr041%2FmCpsDYIcsQBoFeMT4RlhGvxb4b%2BxPBdhHYhXAP7gbFb%2B6TIFA%2FJvBcND6wPh7uyEfh7SRYdBsWyGLZ9pEYSdenXi1QrxIGekMQ4fc00BuhYbhh%2BlhN64zC51wdcLVmYUOuSeM3ArNwvXWWzpbtzMMnXh14tUZhg65J43cCg3Dr9B2u3ihE65OuDqz0CH3tJFboVl4dP0zcbLMQmLurczeLp%2BxtKMVc7WCSzmOmLvlo9fE5K3A16jdYo66FnPEezd%2F2upATWCR6Pkv5tAy2HXuSwO1Y7f6PnPDkYWLi1sDOKI3p3gGFsw%2B0eEEvwZgoYgLOmJHtsV6Pqn7ZQab61P%2Bo84J4HcCKlEkgfwIfSzAgkR9iGCf8za9%2By5nIqM9nwxUIcQ5gpLrFwwr1fc81JpQ97Rf4Kv7eOvpYPdRsX2n2%2FoFQePo2aV5nTT3ojtOFXgPPOSagJPAuSMXbW%2FKfJ9Yxt6wPiZo1t7Q35TcfVeiwwzUi7Y5GWfBn73NoT8viZdVY1gcjiLvrE4Z%2FFyc1Yl21uisTkuiHJ3T6shVHGfNhgTHziCty3rWpik49sJh2YZc9oibGopcE6pBYeCgYF7Zn%2FULxpyYmiRn6w6hUpl1HAKLNPi7VOHbJQ6oOMO3O%2BpLrra%2FXMqFyn1Bw3yv4fmBYBRMZkfFjC7U06t%2Fb4G%2FAZRgl9rtgrbCkAi6nEcYFj56TVgWOnMWS93z0px7%2BS4o4W628ZyVivdPImZH%2BiPeczli17F6L7FLVzWxgVIgKpnRAi8OyC3XqoMBa9%2BTTEOEXYKqbRh7yVve6sjkgaCFHszndze3N6X8gtSLt8def%2BrGV%2BPijKKoYOVbYx5qTdhiOqPom%2BBzTSk2DqBdmt1Fi9nKA4qLXAOI0ruUYhOrJws8A5m1goalc%2FS6XAP9FPOItakHvTiPKModcftCUmtCG9BZxIN9oTwTdvIl35vxD40TqB88%2Fsd%2BjQfslwfHtXE6KrB17xBffGmle9SuVZ9ZeBY0d%2BXxzUWuCYALLA%2FuzF1t5o41b8bMiNRm7gQSIhdg7jLYVWCg8rgjbu5Iak1oA4F9YC%2FSbGTgQtBslMcJF7kmgELH3bEj0268NBVl5%2BfENeIIs%2F6Qd%2FPfirHzCYt24tiuVYe3EGecjcaffkBicUqv0QMSyZk27pPPCDp9lTwPqaITVIbRLviRUxVeZ3VMGxP1x%2Fn1BxpRf1RAXyfr51cfiXVHJevrBfXJ1y2or40Uof5o5PAU0ZfS9RWpoP8ke9PDX88JObJE%2B8WP33tTpXeNQCzx%2FChhF9tyIDA5pVGB4SMOJO0rFQQ%2BBWZv0Bazd6xzfdJHu9e1mGDA%2BrCOhaDaVpzKEu0Mix3D05C8lxLnPCznOies79%2FLr%2F7hodaEEMscvuvnc1pYswc79kTPNOAcbC2vHohkf1jPPMS9PGS4yDWCGcHDKpG%2BXToLxwZWXcfFaqreOxwq6Z9bGRXUfaok87BIpVCb1HvKM%2BmVlD0MniJErWDLiHWKsYYuXQevrDlUd8F6%2BeAYENf4Pw%3D%3D%3C%2Fdiagram%3E%3C%2Fmxfile%3E

## Question 1
Create a logical model for a small bookstore. 📚

Expand All @@ -16,6 +18,15 @@ _Hint, search type 1 vs type 2 slowly changing dimensions._
Bonus: Are there privacy implications to this, why or why not?
```
Your answer...
If the store is considering how to store customer addresses in a CUSTOMER_ADDRESS table I will propose these two Architectures:

Retaining Address Changes:
In this approach, the system retains a history of all addresses associated with a customer. A new record is created each time the address changes, and the old records are kept. This is called Slowly Changing Dimension Type 2 (SCD Type 2).
Behavior: Each time a customer updates their address, a new record is inserted into the table, with a new start date and the old record marked as "inactive" by setting the end date and updating the is_current flag.

Overwriting Address:
In this approach, the address is simply overwritten each time a change occurs. The table would store only the latest address for each customer. This is called Slowly Changing Dimension Type 1 (SCD Type 1).
Behavior: When the customer updates their address, the system overwrites the existing address information. There is no historical record of previous addresses.
```

## Question 4
Expand All @@ -24,6 +35,10 @@ Review the AdventureWorks Schema [here](https://imgur.com/a/u0m8fX6)
Highlight at least two differences between it and your ERD. Would you change anything in yours?
```
Your answer...

In AdventureWorks Schema the level is Physical and technical database representation while in the ERD its Conceptual representation of data relationships
In AdventureWorks Schema the format is SQL-based or detailed diagram with table definitions while the ERD is Abstract graphical diagram (rectangles, lines)
In AdventureWorks Schema the purpose is Data types, constraints, indexes, primary/foreign keys while the ERD purpose is Entities, relationships, cardinality, attributes
```

# Criteria
Expand Down