CPTR$213$

ASSIGNMENT $1$: Creating Well$-$Design Tables & Data Models

PART $108$ Marks

Patient

ID

Patient Name Address Contact Insurance Date of Visit

$111$ Usain Lightning

Bolt

$12$ Lightning Heights,

Trelawny

$(876)-1001$ Sagicor $9$ am$,$ June $24,$

$2014,$

$222$ Donald J$.$ trump $1$ Trump Ave, NY$,$ USA $9063456$ Blue Cross Oct. $30,2009$

$111$ Usain Bolt $12$ Lightning Heights,

Trelawny

$(876)1001$ Sagicor Aug $12,2017,$

$11$pm

$333$ Mary Jane Harvey $8$ Church Lane, TX$,$

USA

$956-2390$ Wellcare Aug $30,2017,12$

pm

$444$ Portia F$.$ Simpson $34$ Hardware Street,

Kgn$,$ Ja

$5678912$ Medicus April $02,2015$

$1.$ Outline FOUR $(4)$ reasons why this table is poorly designed $4$ marks

$2.$ Create a well$-$designed table. Modify the table to show ALL corrections $4$ marks

PART $212$ Marks

$1.$ Create the data model for the NEW table above using the crow foot notation. Show all:

i$.$ Entities

ii$.$ Attributes

iii. Relationship type

iv$.$ Primary key

v$.$ Foreign key $5$ marks

$2.$ Create the data model for the NEW table above using the Chen notation. Show all:

i$.$ Entities

ii$.$ Attributes

iii. Relationship type

iv$.$ Primary key

v$.$ Foreign key $5$ marks

$3.$ Write ALL the business rules that apply to the database above $2$ marks

TOTAL $25$ MARKS