The University of Melbourne Alumni Society manages all aspects of student alumnicommunications including invitations and acceptances to key reunion weekends, and alumnianniversary dinners at the 10th...

1 answer below »
The University of Melbourne Alumni Society manages all aspects of student alumnicommunications including invitations and acceptances to key reunion weekends, and alumnianniversary dinners at the 10th 20th 25th 30th 40th and 50th anniversary and other key dates (e.g.University 150th Anniversary celebrations), since graduation. The Alumni Society have askedyou to design a physical ER model for a MySQL relational database.The Alumni database stores information about every person to graduate from any degree (e.g.Bachelor, Masters, PhD, etc) conferred by the University of Melbourne. Some alumni mayhave completed more than one degree at the university. Once graduation is complete theUniversity of Melbourne Alumni Society sends a written invitation to become a member of theAlumni Society. Currently the Alumni society membership is an annual fee of $130 AUD.About each graduate we store their title, first name, middle name, last name, their personalemail address, work, home and mobile number and their current residential address. We alsoneed to store the date the graduation was awarded (3rd December 2016) Members of theAlumni Society receive a magazine four times a year. We need to store an electronic copy ofeach edition of the magazine in the database. Lastly, we need to keep a history of alladdresses of all graduates over time. Graduates can be located anywhere in the world.The University needs to store the name of every degree awarded. Degree names can changeover time. For example: The Bachelor of Architecture is now the Bachelor of Design and theBachelor of Computer Science has now become the Bachelor of Science (SoftwareEngineering). A history of all degree names needs to be stored. We need to store informationabout the faculty including who was the Dean of the faculty on the date of graduation.Due to reorganisations, some departments disappear and reappear during the life of theuniversity. For example, the Department of Information Systems existed from 1997 to 2014when it was merged with the Department of Computer Science (1983 - 2014) to form theSchool of Computing and Information Systems (2015 - ). The system needs to store acomplete history of all departments in the University and all degree names conferred by adepartment and its faculty. For example: a Master of Information Systems degree could beconferred by the Department of Information Systems in the Science faculty or the School ofComputing and Information Systems in the Engineering faculty depending on the date theaward was conferred to the graduate.It is important that the Alumni Society keeps a record of any honours awarded to members ofthe Alumni Society. Examples of honours which could be awarded include, but are not limitedto, a British peerage (CBE, OBE), an Australian honour (AOM, AM, AO, AC) or the U.S.Congressional Medal of Freedom. It could also include other awards such as recognition fortheir academic achievements including membership of the Royal Society, (RS), fellow of theRoyal Academy (FRA), or honours such as Nobel prize, or Fields medal. Distinguished alumniINFO90002 A1 S1 2020 2are invited to deliver the keynote speech at the reunion dinner. Alumni can be awarded morethan one honour.The Alumni Society sends invitations to all graduates for the alumni reunion weekend includingan invitation to the Alumni dinner. The invitation needs to contain the anniversary year (e.g.25th) the keynote speaker (e.g. Professor Peter Singer), the cost of the alumni dinner (e.g.$300), the dates for the alumni weekend (5th – 7th June 2020) and dinner date (6th June 2020),and total capacity of seats (e.g. max 1200). Alumni Graduates must attend the alumni reunionweekend to be eligible to purchase tickets to the alumni dinner.We need to be able to track all graduates who are attending the reunion, and who have paidfor seats at the alumni dinner. The financial information is NOT stored in this database. Wemust be able to determine if graduates and alumni society members have attended one ormore previous reunion dinners.Frequently Alumni members make donations or bequests to the University of Melbourne. TheUniversity needs to record who made the donation, what the donation was, the year of thedonation. If the donation was an artefact (e.g. artwork, historical text, archive) the databasemust record the artefact type, where it is displayed, stored or archived. Alumni members maymake none, or more than one, bequest to the University of Melbourne over their lifetime, orafter their death. For all alumni members who make a donation, we must store their birthyear,graduation year or years (e.g. Bachelor, Masters, Doctorate), and the year of their death ifthey are deceased.The OLTP Relational DatabaseIt is important that your OLTP relational database design be able to easily answer queriessimilar to those listed below.How many alumni weekends has Marc Forster (student ID 67834) attended?Where is the Germain Greer archive bequest located?How many graduates have obtained their Bachelors, Masters and PhD at the University ofMelbourne?List the different degrees conferred by the Melbourne School of DesignWho was the dean and what year did the Department of Architecture change its name to theMelbourne School of Design?List the members who joined the Alumni, cancelled their membership and then rejoined theAlumni at a later date.How many alumni members attended the 150th Anniversary Celebration reunion dinner?
Answered Same DayMar 22, 2021

Answer To: The University of Melbourne Alumni Society manages all aspects of student alumnicommunications...

Neha answered on Mar 27 2021
123 Votes
52419/Alumni.mwb
document.mwb.xml








{3E046C74-6387-44AC-B0F7-A9C1312AF941}















utf8
utf8_general_ci

0



0
mydb
{8C1A7823-3263-4CE0-94C6-83DEDAB9C93F}













0


0

0






0

1
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

id
id
{D7F95B06-C97F-4580-8728-C547E6C71C3D}


0

0





NULL
1

0
40
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

typeOfArtfact
typeOfArtfact
{D7F95B06-C97F-4580-8728-C547E6C71C3D}


0

0





NULL
1

0
10
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

StatusOfArtfact
StatusOfArtfact
{D7F95B06-C97F-4580-8728-C547E6C71C3D}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.date

DisplayDate
DisplayDate
{D7F95B06-C97F-4580-8728-C547E6C71C3D}



utf8mb4
utf8mb4_0900_ai_ci
0






0

0

{CEB02B4E-1746-449D-8E11-AC358F19689F}

{613FC530-21EF-437F-ACB1-5C9BE9374F4A}



0

1


0
PRIMARY
1
PRIMARY
0
0




0
{D7F95B06-C97F-4580-8728-C547E6C71C3D}

PRIMARY









0


0


{613FC530-21EF-437F-ACB1-5C9BE9374F4A}






0
0

0


InnoDB



0
0
0

0
2020-03-30 15:34

2020-03-30 15:34
0
artfact
{4CD1C69C-034F-4D97-AB7D-9AFA0FC44BA6}


artfact



0


0

0






0

1
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

id
id
{F2F19317-11A9-4D96-B4DD-E277F2602419}


0

0





NULL
1

0
3
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

title
title
{F2F19317-11A9-4D96-B4DD-E277F2602419}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

firstName
firstName
{F2F19317-11A9-4D96-B4DD-E277F2602419}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

lastName
lastName
{F2F19317-11A9-4D96-B4DD-E277F2602419}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

middleName
middleName
{F2F19317-11A9-4D96-B4DD-E277F2602419}


0

0





NULL
1

0
50
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

Address
Address
{F2F19317-11A9-4D96-B4DD-E277F2602419}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

Phone
Phone
{F2F19317-11A9-4D96-B4DD-E277F2602419}



utf8mb4
utf8mb4_0900_ai_ci
0






0

0

{F69FA3E9-6FD8-4396-9607-D9382E8851B0}

{5A9A21DF-BD4B-4C9C-BFF1-49653A290A3E}



0

1


0
PRIMARY
1
PRIMARY
0
0



0
{F2F19317-11A9-4D96-B4DD-E277F2602419}

PRIMARY









0


0


{5A9A21DF-BD4B-4C9C-BFF1-49653A290A3E}






0
0

0


InnoDB



0
0
0

0
2020-03-30 15:34

2020-03-30 15:34
0
dean
{4CD1C69C-034F-4D97-AB7D-9AFA0FC44BA6}


dean



0


0

0






0

1
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

Id
Id
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
10
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

DegreeType
DegreeType
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
20
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

department
department
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

DeanId
DeanId
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
40
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

CurrentName
CurrentName
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
100
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

LastName
LastName
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.date

StartDate
StartDate
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.date

EndDate
EndDate
{D1A697CF-BD28-4C46-824F-46D98CB782BD}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

FacultyId
FacultyId
{D1A697CF-BD28-4C46-824F-46D98CB782BD}



utf8mb4
utf8mb4_0900_ai_ci
0


{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}

{1A41736D-89B9-44C6-9B3D-DBE2F33EAB3C}


0

{2E7284DF-C10E-438D-B001-FF7B39E2D8D1}
1
1
0
{D1A697CF-BD28-4C46-824F-46D98CB782BD}

{67A23E75-2E55-433B-9A92-8D6921400256}

1


degree_ibfk_1
degree_ibfk_1


{F2F19317-11A9-4D96-B4DD-E277F2602419}

{889E4025-8E46-436C-A96D-AAD4F0EF6141}


0

{46D4EF8B-690B-4BBE-B8FB-7D2FCDBCD3C2}
1
1
0
{D1A697CF-BD28-4C46-824F-46D98CB782BD}

{F69FA3E9-6FD8-4396-9607-D9382E8851B0}

1


degree_ibfk_2
degree_ibfk_2







0

0

{0B47D958-7F75-4D9C-98D9-2D352C04A422}

{49A18AFF-02C3-418D-911F-C57C51FB4DB8}



0

1


0
PRIMARY
1
PRIMARY
0
0



0
{D1A697CF-BD28-4C46-824F-46D98CB782BD}

PRIMARY





0

0

{1A41736D-89B9-44C6-9B3D-DBE2F33EAB3C}

{2E7284DF-C10E-438D-B001-FF7B39E2D8D1}



0

1


0
INDEX
0
FacultyId
0
0



0
{D1A697CF-BD28-4C46-824F-46D98CB782BD}

FacultyId





0

0

{889E4025-8E46-436C-A96D-AAD4F0EF6141}

{46D4EF8B-690B-4BBE-B8FB-7D2FCDBCD3C2}



0

1


0
INDEX
0
DeanId
0
0



0
{D1A697CF-BD28-4C46-824F-46D98CB782BD}

DeanId









0


0


{49A18AFF-02C3-418D-911F-C57C51FB4DB8}






0
0

0


InnoDB



0
0
0

0
2020-03-30 15:34

2020-03-30 15:34
0
degree
{4CD1C69C-034F-4D97-AB7D-9AFA0FC44BA6}


degree



0


0

0






0

1
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

id
id
{5A176181-92D1-41DC-9588-962A09AEF230}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.year

YearOfSubmission
YearOfSubmission
{5A176181-92D1-41DC-9588-962A09AEF230}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

amount
amount
{5A176181-92D1-41DC-9588-962A09AEF230}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.year

GraduationYear
GraduationYear
{5A176181-92D1-41DC-9588-962A09AEF230}


0

0





NULL
1

0
30
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

personalMail
personalMail
{5A176181-92D1-41DC-9588-962A09AEF230}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.year

yearOfDeath
yearOfDeath
{5A176181-92D1-41DC-9588-962A09AEF230}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.year

birthYear
birthYear
{5A176181-92D1-41DC-9588-962A09AEF230}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

ArtfactId
ArtfactId
{5A176181-92D1-41DC-9588-962A09AEF230}



utf8mb4
utf8mb4_0900_ai_ci
0


{FD8F935A-903D-4558-B3C4-3E606B0F6415}

{DEEE688F-4A91-4F14-B282-C78A1E4FB28C}


0

{C6A93C7F-F432-4702-B8DA-D49FCE045E6D}
1
1
0
{5A176181-92D1-41DC-9588-962A09AEF230}

{030111CB-736F-4432-8F35-4F38F7367AC2}

1


donation_ibfk_1
donation_ibfk_1


{D7F95B06-C97F-4580-8728-C547E6C71C3D}

{A8E902E6-131B-4053-9EC2-01E6889DEA89}


0

{F0FE6792-D41B-4D3D-A9C6-0B9C5D30BB84}
1
1
0
{5A176181-92D1-41DC-9588-962A09AEF230}

{CEB02B4E-1746-449D-8E11-AC358F19689F}

1


donation_ibfk_2
donation_ibfk_2







0

0

{10220DF3-4024-491B-B368-934AEF039862}

{760BCA0D-EBBE-48D6-9BF0-4EA5D1B7F512}



0

1


0
PRIMARY
1
PRIMARY
0
0



0
{5A176181-92D1-41DC-9588-962A09AEF230}

PRIMARY





0

0

{DEEE688F-4A91-4F14-B282-C78A1E4FB28C}

{C6A93C7F-F432-4702-B8DA-D49FCE045E6D}



0

1


0
INDEX
0
personalMail
0
0



0
{5A176181-92D1-41DC-9588-962A09AEF230}

personalMail





0

0

{A8E902E6-131B-4053-9EC2-01E6889DEA89}

{F0FE6792-D41B-4D3D-A9C6-0B9C5D30BB84}



0

1


0
INDEX
0
ArtfactId
0
0



0
{5A176181-92D1-41DC-9588-962A09AEF230}

ArtfactId









0


0


{760BCA0D-EBBE-48D6-9BF0-4EA5D1B7F512}






0
0

0


InnoDB



0
0
0

0
2020-03-30 15:34

2020-03-30 15:34
0
donation
{4CD1C69C-034F-4D97-AB7D-9AFA0FC44BA6}


donation



0


0

0






0

1
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

Id
Id
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}


0

0





NULL
1

0
3
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

title
title
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

firstName
firstName
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

lastName
lastName
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

middleName
middleName
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}


0

0





NULL
1

0
50
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

Address
Address
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}


0

0





NULL
1

0
15
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

Phone
Phone
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}



utf8mb4
utf8mb4_0900_ai_ci
0






0

0

{67A23E75-2E55-433B-9A92-8D6921400256}

{7EB605EE-97D6-4CED-8546-9CD9BE027566}



0

1


0
PRIMARY
1
PRIMARY
0
0



0
{51CD0DB2-79A9-4D9F-8898-F43BE7D48D1B}

PRIMARY









0


0


{7EB605EE-97D6-4CED-8546-9CD9BE027566}






0
0

0


InnoDB



0
0
0

0
2020-03-30 15:34

2020-03-30 15:34
0
faculty
{4CD1C69C-034F-4D97-AB7D-9AFA0FC44BA6}


faculty



0


0

0





NULL
1

0
20
-1
-1
com.mysql.rdbms.mysql.datatype.varchar

Subject
Subject
{1164444B-54D5-44E7-B1B1-466AE368EAAB}


0

0





NULL
1

0
-1
-1
-1
com.mysql.rdbms.mysql.datatype.int

Duration
Duration
{1164444B-54D5-44E7-B1B1-466AE368EAAB}


...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here