program编程代写、代写Java，Python程序

日期：2022-03-19 02:30

Tutorial Sheet – Week 2

Entity-Relationship (ER) Diagrams

You might find the online module, lecture notes and the file ER Conventions for KIT102- Handout, to be helpful.

Solution Notes

Modelling Scenarios

Note:

Each of the ER diagrams below has attributes and a title.

Your ER diagram in the Final Exam should have attributes and a title.

Help the Children

Notes:

We are modelling Help the Children,

so Help the Children is the title of our ER diagram.

HELP THE CHIDREN should not be an entity in the ER diagram.

Having an attribute Country-name would be a Good Idea.

Country names are unique, so Country-name would be an alternate key

for COUNTRY.

There is no convention for showing alternate keys on our ER diagrams –

we just have to make a note of the fact that Country-name is an alternate key.

We do have a convention for showing composite identifiers, so it is not a Good Idea to ignore the information in the scenario:

“Each sponsor and each child is given an identifying code

which is made up of two parts.”

When we underline two attributes in an ER diagram, we are saying that

both attributes are needed to form a unique identifier.

We are told that project names are unique, so we use Project-name as the identifier of PROJECT on the conceptual ER Diagram

(even though we are almost certain that, at a later step in a database design process, we would allocate a unique code to each project).

We could put in a lives in relationship between CHILD and COUNTRY

– but this is not necessary because we can derive the relationship:

each child is enrolled in one project, and each project runs in one country.

It would be okay to leave out the lives in relationship between SPONSOR and COUNTRY because we can derive it from the identifier of SPONSOR.

The most common *mistake* (which was not a mistake) which students identify

is that the relationship sponsors should be onetomany, instead of many-tomany, because of the sentence “Each child may have only one sponsor at any one time.” in the Scenario. Although a Child cannot have more than one Sponsor at any one time, a child may have several Sponsors, one after the other.  For example, child HTI0023 may have sponsor AUS0042, then sponsor USA0023, then Sponsor USA0042.

Newton’s Outstanding Publications for Education

ER to Relational Model conversion

Help the Children

Step 1: Each entity becomes a relation

SPONSOR (Country-code, Seq-number, [pk] Sponsor-details)

CHILD (Country-code, Seq-number, [pk] Child-details, Date-enrolled)

PROJECT (Project-name, [pk] Project-details)

COUNTRY (Country-code, [pk] Country-category, Country-details)

Step 2: Each many-to-many relationship becomes a relation

SPONSORS (Sponsor-Country-code [fk1], Sponsor-Seq-number [fk1], Child-Country-code [fk2], Child-Seq-number [fk2], [pk] Monthly-payment, Date-sponsorship-began)

Step 3: Each one-to-many relationship is represented by a foreign key

SPONSOR (Country-code [fk], Seq-number, [pk] Sponsor-details)

CHILD (Country-code[fk], Seq-number, [pk] Child-details, Date-enrolled, Project-name [fk])

PROJECT (Project-name, [pk] Project-details, Country-code [fk])

Step 4: Write out the Final Relational Schema

Help the Children

CHILD (Country-code, Seq-number, [pk] Child-details, Date-enrolled, Project-name [fk])

COUNTRY (Country-code, [pk] Country-category, Country-details)

PROJECT (Project-name, [pk] Project-details, Country-code [fk])

SPONSOR (Country-code [fk], Seq-number, [pk] Sponsor-details)

SPONSORS (Sponsor-Country-code [fk1], Sponsor-Seq-number [fk1], Child-Country-code [fk2], Child-Seq-number [fk2], [pk] Monthly-payment, Date-sponsorship-began)